SQLite Create Database: How to Create a New Database File (Tested)

Ninad Pathak

October 7, 2026

An SQLite database is a file, and SQLite creates it when you open a name that doesn’t exist yet, so there’s no CREATE DATABASE statement. The shell has a quirk, though: it waits for the first write before it creates the file, so opening sqlite3 shop.db and quitting leaves nothing behind. In this article I’ll walk you through creating a database from the shell and from code.

TLDR

SQLite has no CREATE DATABASE statement. A database is one ordinary file.
Run sqlite3 shop.db and create a table. SQLite creates shop.db when you first write to it.

To build a database from a script, run sqlite3 store.db < schema.sql.
From code, connecting to a file name creates it: sqlite3.connect("app.db") in Python, new DatabaseSync("app.db") in Node.js.
The folder must already exist, or you get unable to open database file.

What is an SQLite database?

An SQLite database is a single file on disk that holds all your tables and their rows. There’s no server and no separate “database” object to create first, so the statement other databases use fails:

$ sqlite3 :memory: "CREATE DATABASE shop;"
Error: in prepare, near "DATABASE": syntax error
  CREATE DATABASE shop;
         ^--- error here

Every SQLite database file with data in it starts with the 16-byte header string SQLite format 3 (Database file format). That’s how you can tell an SQLite file from any other file, whatever its extension (you’ll see it in the steps below).

The extension doesn’t matter to SQLite. You’ll see .db and .sqlite3 on SQLite files, and SQLite opens any extension the same way.

SQLite create database syntax

You create a database by opening a file name that doesn’t exist yet:

Where	Command	Creates the file
Shell, from your terminal	`sqlite3 shop.db`	On the first write
Shell, already running	`.open shop.db`	Yes, as an empty file, on `.open`
Shell, from a SQL file	`sqlite3 shop.db < schema.sql`	Yes, if the script writes anything
Python	`sqlite3.connect("shop.db")`	Yes, as an empty file, on connect
Node.js	`new DatabaseSync("shop.db")`	Yes

The SQLite docs put it this way: “If the named file does not exist, a new database file with the given name will be created automatically” (Command Line Shell For SQLite).

How to create an SQLite database

To show you the steps, I will create a small shop.db database with a customers table, using the sqlite3 shell.

Step 1: Open a new database file

Run sqlite3 with the file name you want. If you leave the shell without writing anything, no file appears:

$ sqlite3 lazy.db ".quit"
$ ls lazy.db
ls: lazy.db: No such file or directory

Step 2: Create a table

The first CREATE TABLE writes the file. It’s 8,192 bytes here: two 4 KiB pages, one for the schema and one for the table:

$ sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"
$ ls -l shop.db | awk '{print $5, $NF}'
8192 shop.db
$ sqlite3 shop.db ".tables"
customers

The file now starts with the SQLite header, the text SQLite format 3 and a zero byte:

$ xxd -l 16 shop.db
00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300  SQLite format 3.

That’s a working database. From here you add rows with INSERT (see SQLite CREATE TABLE for column types and constraints).

Examples

Each example below ran in the same demo folder, and the output under it is real.

Create a database from a SQL file

Keep your schema in a file and pipe it into sqlite3. This is the quickest way to rebuild the same database again later:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL
);
INSERT INTO products (name, price) VALUES ('Pen', 1.50), ('Notebook', 4.25);

$ sqlite3 store.db < schema.sql
$ sqlite3 -header -column store.db "SELECT * FROM products;"
id  name      price
--  --------  -----
1   Pen       1.5
2   Notebook  4.25

Create a database from inside the shell

If sqlite3 is already running on its temporary in-memory database, .open switches it to a file. .databases shows the full path of the file you’re now using:

$ printf '.open inventory.db\nCREATE TABLE items (sku TEXT PRIMARY KEY, qty INTEGER);\n.databases\n.tables\n' | sqlite3
main: /private/tmp/sqldocs-f4-sqlite-create-database/inventory.db r/w
items
$ ls inventory.db
inventory.db

Create a database in a specific folder

Give a relative or full path. The folder has to exist, because SQLite creates the file but not the folders above it:

$ mkdir -p data && sqlite3 data/shop.db "CREATE TABLE t (x);" && ls data
shop.db
$ sqlite3 missing/shop.db "CREATE TABLE t (x);"
Error: unable to open database "missing/shop.db": unable to open database file

On Windows, the same works with a Windows path, for example sqlite3 C:\data\shop.db (not executed here).

Create an empty database file

If you need the file to exist before you have any tables, run VACUUM on it. That writes a valid, one-page database:

$ sqlite3 empty.db "VACUUM;"
$ ls -l empty.db | awk '{print $5, $NF}'
4096 empty.db
$ sqlite3 empty.db "PRAGMA page_count;"
1

Create a database in Python

Python’s built-in sqlite3 module creates the file as soon as you connect (sqlite3 module docs):

import os
import sqlite3

con = sqlite3.connect("app.db")  # creates app.db if it doesn't exist
print("exists after connect:", os.path.exists("app.db"), os.path.getsize("app.db"), "bytes")
con.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
con.commit()
print("size after CREATE TABLE:", os.path.getsize("app.db"), "bytes")
con.close()

$ python3 create_db.py
exists after connect: True 0 bytes
size after CREATE TABLE: 8192 bytes

Notice that connect() leaves a 0-byte file. That’s still a valid empty database, and it grows when you create the first table.

Create a database in Node.js

Node.js has a built-in SQLite module, node:sqlite (Node.js docs):

import { DatabaseSync } from "node:sqlite";
import { statSync } from "node:fs";

const db = new DatabaseSync("app-node.db"); // creates the file if it doesn't exist
db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
console.log("tables:", db.prepare("SELECT name FROM sqlite_schema WHERE type = 'table'").all().map(r => r.name));
console.log("size:", statSync("app-node.db").size, "bytes");
db.close();

$ node create_db.mjs 2>&1 | grep -v ExperimentalWarning | grep -v 'node --trace-warnings'
tables: [ 'notes' ]
size: 8192 bytes

Edge cases

No file after .quit: the shell waits for the first write before it creates the file. Create a table, or run VACUUM, if you need the file on disk.

unable to open database file: the folder in the path doesn’t exist, or you can’t write to it. Create the folder first or check its permissions. See unable to open database file.
A typo creates a second, empty database: SQLite creates whatever file name you give it. To open only a file that already exists, see SQLite create database if not exists.

Conclusion

Creating an SQLite database means picking a file name and writing to it. Use the sqlite3 shell for quick work and a SQL file when you want to rebuild the same schema. When your app creates its own database, connect() does it for you.