How to create an empty SQLite database

You can create an empty SQLite database by creating an empty file. That’s the whole procedure. SQLite treats a zero-length file as a valid empty database.

I found this out while setting up SQLite for Prisma. I was looking for some CREATE DATABASE command to run first, like I would do with Postgres or MySQL.

I was overcomplicating it. There is no such step in SQLite.

Why an empty file is enough

Postgres and MySQL run a server. You connect to the server and ask it to create a database for you.

SQLite has no server. It’s a library that reads and writes a single file. The file is the database. So creating the file means creating the database.

Use touch from the command line to create a database named storage.db:

touch storage.db

Now point your app or your ORM at it. The first time you create a table, SQLite writes its internal header and structures into the file.

Creating it with the sqlite3 CLI

If you have the sqlite3 command line tool installed, you can also create the file already initialized with the SQLite header:

sqlite3 storage.db "VACUUM;"

The difference is small but visible. Run file on both and you’ll see it:

file storage.db
# storage.db: SQLite 3.x database ...

The touch version reports empty instead. Both work fine as a starting point, but the initialized one is recognizable as a SQLite database by other tools right away.

Duplicating and backing up

Since the database is one file, copying the file copies everything: tables, indexes, data.

cp storage.db backup.db

This is great for backups, or for cloning a database to run experiments on it.

Be careful with one thing. If an application is writing to the database while you copy it, the copy can end up corrupted. And if the database runs in WAL mode, recent writes live in a separate storage.db-wal file, so copying only storage.db misses them.

The fix is the .backup command of the sqlite3 CLI:

sqlite3 storage.db ".backup backup.db"

This takes a consistent snapshot, even while the database is in use.