SQLite: unable to open database file

What it means

SQLite tried to open a file and the operating system said no. It returns SQLITE_CANTOPEN (code 14), “unable to open database file”. Usually that’s the database itself, when you connect; it can also be a file SQLite opens later, such as a database you ATTACH, which reads unable to open database: <path>.

When the file doesn’t exist but its folder does, SQLite normally creates a new, empty database there instead of failing (and then you get no such table). So this error usually means SQLite couldn’t even create it.

Common causes

The folder doesn’t exist. A typo in the path, a relative path resolved against a different working directory than you expected, or a data/ folder your deploy or container never created.
~ in the path. Your shell expands ~ to your home folder; SQLite doesn’t. In a program, ~/app.db means a folder literally named ~.
Permissions. The process can’t read the file, or can’t create a file in the folder. On a Mac, apps also need your permission for the Desktop, Documents and Downloads folders (System Settings › Privacy & Security › Files & Folders); when macOS refuses, SQLite can’t open the file.
The path is a folder, not a file.
Opening without create: read-only (mode=ro, the shell’s -readonly) or read-write without create (mode=rw, or SQLITE_OPEN_READWRITE without SQLITE_OPEN_CREATE in C) on a file that doesn’t exist.
Too many open files. Each connection keeps file descriptors open, and a process has a limit. A program that opens connections and never closes them reaches it, and then every new open fails.

How to fix it

Check the path from the program’s point of view

Print the absolute path and the working directory from inside the program that fails, then check both exist:

ls -ld /path/to/folder /path/to/folder/app.db

Create a missing folder before you open the database (mkdir -p /path/to/folder, or os.makedirs(folder, exist_ok=True) in Python). Build the path from your app’s data folder or configuration rather than relying on the working directory.

Expand ~ yourself

import os, sqlite3
db = sqlite3.connect(os.path.expanduser("~/data/app.db"))

In Swift, start from FileManager.default.homeDirectoryForCurrentUser. In a URI, write the full path: file:/Users/<user>/data/app.db.

Fix permissions

The process needs read permission on the file to open it, and write permission on the folder to create the file. ls -l shows the owner and mode; a service running as another user often owns neither. If opening works but writing fails, that’s a different error: attempt to write a readonly database.

On a Mac, if a program can’t open a database in Desktop, Documents or Downloads, check whether it has access in System Settings › Privacy & Security › Files & Folders.

Point at the file, not its folder

A path that ends at a directory gives the same message as a missing one. Add the file name: data/app.db, not data.

Close connections you’re done with

Look at how many files the process has open, and its limit:

lsof -p <pid> | grep -c '\.db'
ulimit -n

If the count keeps rising, connections are leaking: close each one when you’re done, or reuse one connection or a small pool.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0. A folder that doesn’t exist:

sqlite3 nofolder/app.db "SELECT 1;"

Error: unable to open database "nofolder/app.db": unable to open database file

The shell adds “Error: unable to open database” and the path; SQLite’s message is unable to open database file. The same message came back for a path that was a directory, for '~/nope-seo/app.db' in quotes, for a new file in a folder without write permission (chmod 555), for a file without read permission (chmod 000), and for a missing file opened with file:missing.db?mode=rw, ?mode=ro or -readonly.

Attaching a file in a folder that doesn’t exist:

Error: stepping, unable to open database: nofolder/archive.db (14)

With the open-files limit lowered (ulimit -n 10), attaching and writing to one database after another failed at the seventh:

Error: stepping, unable to open database file (14)

Through Python’s sqlite3 module (SQLite 3.53.4), the missing folder, the directory and the unreadable file each raised sqlite3.OperationalError: unable to open database file, with sqlite_errorname SQLITE_CANTOPEN.

In Inlet

Inlet opens the file you choose, so there’s no working directory or ~ to get wrong. If a saved connection’s file has since moved, or Inlet can’t read it, connecting fails, and What this error means opens this page.

unable to open database file