SQLite connection strings: file paths, file: URIs and in-memory databases

A plain file path

Most of the time you open a SQLite database by naming its file:

sqlite3 shop.db
sqlite3 /Users/<you>/data/shop.db

A relative path is relative to the program’s current directory, which for an app or a server process may not be the directory you expect. Use an absolute path when in doubt. If the file doesn’t exist, sqlite3 and most libraries create it when you first write. There’s no user name or password: anyone who can read the file can read the database.

Two special names:

• :memory: opens a new, empty database in memory (see below).
• An empty name ("") opens a temporary database on disk that’s deleted when you close it.

file: URIs

A file: URI is a path plus options:

file:[//[localhost]]<path>[?param=value[&...]]

Any other authority is an error:

Error: unable to open database "file://example.com/…/seo_strings.db": invalid uri authority: example.com

URI support has to be switched on. The sqlite3 command-line shell accepts URIs. In C, pass SQLITE_OPEN_URI to sqlite3_open_v2() (or build SQLite with SQLITE_USE_URI=1). In Python, pass uri=True:

import sqlite3
con = sqlite3.connect("file:shop.db?mode=ro", uri=True)

Where URI support is off, SQLite treats file:shop.db?mode=ro as an ordinary file name and opens (or creates) a file called exactly that. Whether it’s on by default depends on how your copy of SQLite was built: Homebrew’s Python on macOS honoured mode=ro even without uri=True. Pass the flag anyway, so the code works everywhere.

Characters to encode in a file name

In a URI, ? starts the options and # starts a fragment, which SQLite ignores. A file name that contains them must encode them, and % itself becomes %25. We created databases with sqlite3 and listed the files it made:

Spaces worked unencoded in our tests, but %20 is the safe choice. A plain path (no file:) needs no encoding at all.

Parameters

What we saw with sqlite3 3.51.0 on macOS:

sqlite3 'file:seo_strings.db?mode=ro' 'select * from t; insert into t values (2);'

1
Error: stepping, attempt to write a readonly database (8)

sqlite3 'file:missing.db?mode=rw' 'select 1'

Error: unable to open database "file:missing.db?mode=rw": unable to open database file

mode=rwc created the missing file instead. immutable=1 also refused the insert with the same “readonly database” error.

Read-only, three ways. mode=ro opens read-only but still takes locks and sees other processes’ changes; use it for a database another program is writing. immutable=1 goes further and assumes nothing will change. SQLite’s documentation warns that if the file does change, you can get wrong results or SQLITE_CORRUPT errors; keep it for files on read-only media or copies nothing writes. The shell’s -readonly flag (and .open --readonly) is the same as mode=ro.

Shared cache is discouraged. SQLite’s documentation calls cache=shared an obsolete feature and recommends WAL mode (PRAGMA journal_mode=WAL) for letting readers and a writer work at the same time. Its remaining use is sharing an in-memory database, below.

In-memory databases

:memory: gives each connection its own private database, deleted when the connection closes. Two connections in the same program don’t see each other’s tables. In Python (SQLite 3.53.4):

c = sqlite3.connect(":memory:")
d = sqlite3.connect(":memory:")
c.execute("create table t(x)")
d.execute("select x from t")   # OperationalError: no such table: t

To share one in-memory database between connections in the same process, name it and turn on shared cache:

a = sqlite3.connect("file:scratch?mode=memory&cache=shared", uri=True)
b = sqlite3.connect("file:scratch?mode=memory&cache=shared", uri=True)
a.execute("create table t(x)"); a.execute("insert into t values (42)"); a.commit()
print(b.execute("select x from t").fetchall())   # [(42,)]

Connections share only if they use exactly the same name. The database disappears when the last connection to it closes: after closing a and b, a new connection to the same URI found no such table: t. file::memory:?cache=shared is the unnamed version.

Recent SQLite versions also offer the memdb VFS, which shares an in-memory database without shared cache. The name must start with /; with file:scratch?vfs=memdb the second connection saw no table:

a = sqlite3.connect("file:/scratch?vfs=memdb", uri=True)
b = sqlite3.connect("file:/scratch?vfs=memdb", uri=True)
# a table created and committed through a is visible through b: [(7,)]

No form of in-memory database is visible to other processes. To keep one, write it to disk: .save <file> in the shell, or VACUUM INTO '<file>' in SQL.

In Inlet

Inlet opens SQLite database files and creates new ones (File › New SQLite Database…). To try it with data already in place, File › Open Sample Database opens a small music store.