SQLite Create Database: How to Make a New .db File

A SQLite Database Is Just a File

If you've used MySQL or Postgres, you're used to running CREATE DATABASE myapp; and having the server bookkeep a new database somewhere out of sight. SQLite doesn't work that way. There's no server. There's no CREATE DATABASE statement. A database is a single ordinary file on disk — usually with a .db, .sqlite, or .sqlite3 extension, but the extension is just convention.

To create one, you point the sqlite3 tool at a filename that doesn't exist yet:

sqlite3 mydata.db

That's it. If mydata.db doesn't exist, SQLite is ready to create it. If it does, SQLite opens it. Same command, both cases. The mental model is closer to "open a document" than "spin up a database server."

The File Doesn't Appear Until You Write Something

Here's a thing that trips people up. Run the command above, type .quit, and look in your directory. The file isn't there.

SQLite is lazy. It doesn't bother creating the file on disk until there's something to put in it. The moment you create a table or commit data, the file materializes:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

Now mydata.db exists on disk. Until that first write, the "database" lives only in the connection's memory. This is occasionally confusing — and occasionally useful, since it means an aborted session leaves no trace.

Creating a Database From the CLI

The full workflow from a fresh shell:

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
sqlite> .databases
main: /home/you/mydata.db r/w
sqlite> .quit

A few useful CLI commands while you're in there:

• .databases — list attached databases and their file paths.
• .tables — list tables in the current database.
• .schema — show CREATE TABLE statements for everything.
• .quit — exit.

If you're already inside sqlite3 with no file open (you started it without an argument), you can attach a file with .open:

sqlite> .open mydata.db

Same rule: creates it if missing, opens it if not.

"If Not Exists" Is Built In

A common worry from other databases: what if the file is already there? Will I clobber it? No. Opening an existing file just opens it. Nothing is overwritten. The closest equivalent to CREATE DATABASE IF NOT EXISTS is just... opening the file. SQLite handles both cases with the same command.

If you specifically want to start fresh, delete the file first:

rm mydata.db
sqlite3 mydata.db

Be sure that's what you want, though. There's no undo, and no admin tool to recover it from.

Creating a Database From Python

Most of the time you won't be in the CLI — you'll be creating databases from application code. Python ships with a sqlite3 module in the standard library, no install needed:

-- Equivalent Python code:
-- import sqlite3
-- conn = sqlite3.connect("mydata.db")
-- conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
-- conn.commit()
-- conn.close()

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

sqlite3.connect("mydata.db") follows the same rule as the CLI: creates the file if missing, opens it if there. Every other language binding (Node's better-sqlite3, Go's database/sql driver, Rust's rusqlite, etc.) works the same way — they all wrap the same C library underneath.

One Python-specific shortcut: passing ":memory:" as the path gives you a database that lives entirely in RAM and disappears when the connection closes. That's the next page.

Where Should the File Live?

Since the database is a file, "where do I put it?" is a real question. A few rules of thumb:

• For an app: somewhere under the user's data directory or your app's working directory. Don't put it next to the executable on systems where that path isn't writable.
• For a project: at the repo root or in a data/ folder. Add it to .gitignore if it holds local state — checking a binary database into Git rarely ends well.
• For tests: use :memory: or a temp file. Fast and self-cleaning.

Permissions matter too. The user running the process needs read and write access to both the file and its parent directory (SQLite creates lock files alongside the database during writes).

A Quick Sanity Check

To prove the whole pipeline works end-to-end:

CREATE TABLE greetings (
    id INTEGER PRIMARY KEY,
    message TEXT NOT NULL
);

INSERT INTO greetings (message) VALUES ('hello'), ('world');

SELECT * FROM greetings;

You should see two rows back. If this works, your database file exists, your schema is in it, and your data is queryable. That's the whole "create a database" story for SQLite — no server, no users, no permissions table. Just a file.

Next: In-Memory Databases

The same sqlite3.connect(...) call that creates a file will, with the right argument, create a database that never touches disk at all. In-memory databases are the fastest way to run tests, prototype a schema, or hold transient data. That's coming up next.