← Connection strings

SQLite connection strings

SQLite has no server, so its connection string is a file path — which turns out to be where the trouble is: relative to what, how many slashes, and how to open it read-only. Below is the same database file in each form.

Build yours (SQLite)

The parts of a SQLite connection string

The shape

sqlite:////var/data/app.db
sqlite:///
Three slashes: the URL's empty host. What follows is the path.
/var/data/app.db
An absolute path adds its own slash: four in total.

One SQLite database, every form

The file /var/data/shop.db, in each form a library expects.

File path

sqlite3 (Python), better-sqlite3, most drivers · connected with on a real server

/var/data/shop.db

SQLAlchemy

Python with SQLAlchemy

sqlite:////var/data/shop.db

Four slashes for an absolute path: three for the URL, one for the path.

JDBC

Java (xerial sqlite-jdbc), DBeaver

jdbc:sqlite:/var/data/shop.db

SQLite URI filename (read-only)

sqlite3 with uri=True, the sqlite3 shell · connected with on a real server

file:/var/data/shop.db?mode=ro

Prisma

Node with Prisma

file:/var/data/shop.db

sqlite3 command

Shell

sqlite3 /var/data/shop.db

What catches people out on SQLite

Three slashes or four

sqlite:///app.db is app.db in the current directory; sqlite:////var/data/app.db is an absolute path. Getting it wrong silently creates a new, empty database somewhere else.

Relative to the working directory, not the code

A relative path resolves against the directory the process was started from. Run the same app from another directory and it opens (and creates) a different file.

Read-only is a URI filename

file:/var/data/app.db?mode=ro opens without write access — in Python with sqlite3.connect(…, uri=True). immutable=1 goes further and skips locking, for files nothing will change.

Keep the -wal and -shm files with it

In WAL mode, recent writes live in app.db-wal until a checkpoint. Copying only app.db while the app runs can lose them; use the .backup command or VACUUM INTO.

Prisma's file: path is relative to the schema

file:./dev.db in Prisma resolves against the directory of schema.prisma, not where you run the command.

Connected. Now what?

Paste the same string into DBShifts to browse SQLite with sensitive columns masked and every read logged, or to migrate it to another database with each table counted and checksummed afterwards.

Questions

What is the SQLite connection string?

Usually just the file path. SQLAlchemy uses sqlite:///relative.db or sqlite:////absolute/path.db, JDBC uses jdbc:sqlite:/path/to.db, and Prisma uses file:./dev.db.

How do I open a SQLite database read-only?

Use a URI filename: file:/path/to.db?mode=ro, and in Python pass uri=True to sqlite3.connect. Writes then fail with "attempt to write a readonly database".

Does SQLite have a user name and password?

No. Access is whatever the operating system allows on the file. Encryption needs an extension such as SQLCipher, which adds a key, not a user.