SQLite Create Database: CLI, Python, GUI, and DbSchema Setup
For someone starting a project on SQLite who needs the database file, the first table, and the settings that matter before any data goes in.
On this page
You type CREATE DATABASE app; into SQLite and get near "DATABASE": syntax error. SQLite has no such statement, because a SQLite database is one ordinary file. SQLite creates the file the first time you use a filename that doesn't exist yet:
| Where you start | What creates app.db |
|---|---|
| The sqlite3 shell | sqlite3 app.db, then a first statement such as CREATE TABLE |
| Python | sqlite3.connect("app.db") |
Create a SQLite database in the sqlite3 shell
Check that the shell is installed:
sqlite3 --version
If the command isn't found, download the sqlite-tools bundle for your system from the SQLite download page. The shell is inside the zip, with nothing else to install.
Start the shell with the name of the new file:
sqlite3 app.db
Then create the first table at the shell's prompt:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
Create the table before you quit. The shell creates a new file only once a command needs the database, so sqlite3 app.db followed by .quit leaves no file. The shell's source code explains why it waits: to prevent "empty database files from being created if a user mistypes the database name". SQLite CREATE TABLE covers the rest of the table syntax.
Open a file from inside the shell
Started without a filename, the shell uses "a transient in-memory database". The command line documentation says this database is deleted when you exit. Double-clicking sqlite3.exe on Windows starts the shell that way. Switch to a file with .open. The documentation says the file "is created if it does not previously exist":
.open app.db
Check what you created
Three dot commands show what the file holds:
.databaseslists the database files that the shell has open.app.dbis listed under the namemain..tableslists the tables..schema customersprints theCREATEstatement that SQLite stored.
.quit closes the shell and leaves app.db on disk.
Create a SQLite database in Python
Python's built-in sqlite3 module creates the file when connect() finds no file of that name:
import sqlite3
connection = sqlite3.connect("app.db")
connection.execute("""
CREATE TABLE IF NOT EXISTS customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
)
""")
connection.commit()
connection.close()
IF NOT EXISTS lets the script run at every start of your application. The file appears as soon as connect() runs, before anything is written. Its size after each step, measured with Python 3.14 and SQLite 3.50.4:
| After this step | Size of app.db |
|---|---|
sqlite3.connect("app.db") | 0 bytes |
CREATE TABLE customers and commit() | 12,288 bytes |
An empty file is a valid, empty database. SQLite stores the file in pages, here of 4,096 bytes, and the first table brings it to three pages. The first page lists the tables, the second stores customers, and the third stores the index that enforces UNIQUE on email.
Create an in-memory database
Pass :memory: for a database that never touches the disk, such as one for a test:
import sqlite3
first = sqlite3.connect(":memory:")
first.execute("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY)")
second = sqlite3.connect(":memory:")
print(second.execute("SELECT count(*) FROM sqlite_master").fetchone()[0])
0
The second connection sees no tables, because "Every :memory: database is distinct from every other", as the in-memory database documentation says. An in-memory database also "ceases to exist as soon as the database connection is closed".
Why SQLite has no CREATE DATABASE
PostgreSQL, MySQL and SQL Server run as a server process. Your program sends the server requests, and the server reads and writes its own data files. CREATE DATABASE is one of those requests. SQLite has no server. In the words of the SQLite documentation, "the process that wants to access the database reads and writes directly from the database files on disk".
The file extension is only a convention: app.db, inventory.sqlite and analytics.sqlite3 all open the same way.
Add a second database with ATTACH
ATTACH DATABASE opens a second database file on the same connection, and creates the file if it doesn't exist:
ATTACH DATABASE 'archive.db' AS archive;
CREATE TABLE archive.orders (order_id INTEGER PRIMARY KEY);
You reach the tables of archive.db through the name after AS, as in archive.orders. The ATTACH documentation has the full syntax. A default build allows ten attached databases, as the limits page documents. The eleventh ATTACH fails with too many attached databases - max 10.
Create a SQLite database in DbSchema
DbSchema connects to a SQLite file and draws its tables as a diagram. The file has no server and no login, so DbSchema needs only its path:
- Install DbSchema and start it. On the Welcome Screen, in Start New, choose Connect to Database.
- Click the Sqlite tile, choose New Connection, and press Connect. DbSchema downloads the JDBC driver for SQLite the first time you connect.
- In the connection dialog, choose the Standard method and give the full path of
app.db. For a file that doesn't exist yet, press Create New and type a full path. DbSchema puts a file name without a folder in your home folder. - Press Connect. In Reverse Engineer the Database, press Load.
DbSchema reads the tables, columns, indexes and foreign keys of app.db and draws them as a diagram:
The diagram belongs to a design model: DbSchema's own copy of the schema, kept apart from the file. While DbSchema is connected, every change that you confirm with OK runs in app.db at once. To keep your changes in the model first, choose Database → Offline (connect later). With DbSchema Pro, you later compare the model with the database and decide difference by difference. Queries against the file run in the SQL Editor.
Settings to decide before data goes in
Switch to WAL when readers and writers overlap
PRAGMA journal_mode = WAL;
In Write-Ahead Logging, "readers do not block writers and a writer does not block readers", says the WAL documentation. Run the statement once. The same page says "PRAGMA journal_mode=WAL is persistent". WAL adds two files beside the database:
While a connection is open, the newest changes can sit in app.db-wal, and a copy of app.db alone misses them. With SQLite 3.50.4, a table committed in WAL mode was missing from such a copy. Every process that uses the file also has to run on the same machine, because "WAL does not work over a network filesystem".
Back up with .backup, not a file copy
.backup app-backup.db
The shell's .backup copies the database through SQLite's backup API, which works while the database is in use. In Python, connection.backup(target) calls the same API. A copy made with another tool, such as cp, can miss what sits in app.db-wal. And if the power or the operating system fails during that copy, "the backup database may be corrupted".
A server would choose the journal mode and run the backups for you. With SQLite both are yours, and each is easier to settle before there's data in the file. Download DbSchema at https://dbschema.com/download.html, connect it to the file you just made, and the schema comes back as a diagram. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition. Saving the design model as a .dbs file and comparing it with the database need Pro.
