One of the reasons SQLite has become so widely used — from mobile apps to browsers to desktop software — is how little ceremony is involved in creating a database. There’s no server to install, no user accounts to configure, no network ports to open. A SQLite database is just a file. In this guide, I’ll walk through every practical way to create one, what actually happens when you do, and the habits worth building right from the start.
The Simplest Way: The Command-Line Shell
If you have the sqlite3 command-line tool installed, creating a database is as simple as running:
sqlite3 my_database.db
This opens an interactive SQLite prompt. Here’s the part that surprises a lot of beginners: at this point, no file has actually been created on disk yet. SQLite is lazy about this — it won’t create the physical file until you actually do something that requires it, like creating a table.
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
content TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
Now, if you check your filesystem, my_database.db genuinely exists. Type .quit to exit the shell, and the file remains on disk, ready to be reopened any time.
ls -la my_database.db
You’ll see it sitting there as an ordinary file, typically a few kilobytes at minimum once it has at least one table.
Verifying the Database Was Created Correctly
Once you’re back inside the SQLite shell (or reopening the file), you can confirm your table exists with:
.tables
And you can inspect the full schema with:
.schema
Both are dot-commands specific to the sqlite3 CLI tool, not standard SQL, and they’re incredibly useful for quickly sanity-checking a database you just created or one you’re revisiting after a while.
Creating a Database with the Command Line, Non-Interactively
You don’t have to enter the interactive shell at all. You can create a database and run a command against it in a single line, which is especially useful for scripting:
sqlite3 my_database.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, content TEXT);"
This creates the file (if it doesn’t already exist) and immediately executes the CREATE TABLE statement, all without dropping you into an interactive session.
Creating a Database from a SQL Script File
For anything beyond a single table, it’s much cleaner to define your schema in a .sql file and run it all at once. Create a file called schema.sql:
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER,
FOREIGN KEY (author_id) REFERENCES authors (id)
);
INSERT INTO authors (name) VALUES ('Bapsi Sidhwa'), ('Mohsin Hamid');
Then run:
sqlite3 library.db < schema.sql
This creates library.db, executes every statement in schema.sql in order, and leaves you with a fully initialized database. I use this pattern constantly for setting up new projects, since it means your schema lives in a file you can track in version control, rather than existing only as commands you typed once into a terminal and might forget.
Creating an In-Memory Database
Sometimes you don’t want a file at all — you just need a temporary database that exists only for the life of your program or session, useful for testing or scratch work.
sqlite3 :memory:
Anything you create in this session — tables, data, indexes — exists purely in RAM and disappears completely the moment the session ends. Nothing ever touches your disk. This is especially popular in automated testing, where you want a completely fresh, fast database for every single test run, without leftover files cluttering your filesystem afterward.
Creating a Database Programmatically
Most of the time, you won’t be creating databases by hand — your application code will do it. Here’s how that looks in a few common languages.
Python, using the built-in sqlite3 module:
import sqlite3
connection = sqlite3.connect('my_database.db')
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS notes (
id INTEGER PRIMARY KEY,
content TEXT
)
''')
connection.commit()
connection.close()
Just like the command-line tool, sqlite3.connect() creates the file automatically if it doesn’t already exist, and again, the file doesn’t fully materialize until you actually write something to it, like running a CREATE TABLE statement.
Node.js, using a common SQLite package:
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('my_database.db');
db.serialize(() => {
db.run(`CREATE TABLE IF NOT EXISTS notes (
id INTEGER PRIMARY KEY,
content TEXT
)`);
});
db.close();
The pattern is consistent across virtually every language with SQLite bindings: you provide a file path, the library creates the file if needed, and you execute SQL against it just like you would from the command-line shell.
Understanding What “Creating a Database” Really Means
It’s worth pausing on something conceptually important here: in SQLite, there’s no separate “create database” command the way there is in something like MySQL or PostgreSQL, where you’d run CREATE DATABASE my_db; as its own explicit step. In SQLite, a database simply is a file, and that file comes into existence the moment you connect to a path that doesn’t yet exist and then perform an operation that needs to persist something, like creating a table or inserting data.
This is a genuinely different mental model from client-server databases, and it’s part of why SQLite is described as “serverless” — there’s no separate database server process running in the background managing multiple databases. Your application talks directly to the file.
Choosing Where to Put Your Database File
Where you place your .db file matters more than it might seem. A few things worth thinking about:
Avoid putting it inside a directory that’s synced by cloud storage tools like Dropbox or OneDrive if your application might have multiple processes writing to it simultaneously. SQLite handles concurrent access from multiple processes on the same machine reasonably well, but cloud sync tools can interfere with file locking in ways that cause corruption.
Keep it out of version control if it contains real or frequently changing data. Track your schema (as a .sql file) instead, and add the actual .db file to .gitignore. Binary database files don’t diff meaningfully in Git and will bloat your repository history quickly.
Consider a dedicated data/ or db/ directory in your project structure, separate from source code, so backups, gitignore rules, and permissions can all be managed cleanly in one place.
Setting Useful PRAGMAs Right After Creation
Once your database is created, there are a few PRAGMA settings worth configuring immediately, before you start adding real data:
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
Turning on foreign key enforcement, as covered in other articles in this series, ensures relationships between tables are actually validated. Switching to WAL (Write-Ahead Logging) mode generally improves performance for applications with concurrent reads and writes, which is a common need even in small applications with a background process and a main application both touching the same database file.
Common Mistakes to Avoid
Expecting the file to appear the instant you run sqlite3 filename.db. As covered above, it doesn’t exist on disk until you perform an operation that requires writing something, like a CREATE TABLE statement. If you exit without doing anything, no file gets created.
Accidentally creating a new, empty database because of a typo in the filename. Since SQLite silently creates a new file for any path that doesn’t exist, a small typo (my_databse.db instead of my_database.db) results in a brand-new, empty database rather than an error telling you the file wasn’t found. This can be genuinely confusing when you expect to see existing data and instead find an empty table list.
Not setting up a schema file and relying purely on manually typed commands. This makes your database setup impossible to reproduce reliably, especially for teammates or future you setting things up on a new machine.
Storing the database file somewhere it can be affected by cloud sync tools or unreliable network storage, particularly for applications with concurrent access.
Best Practices Worth Adopting
Keep your schema definition in a version-controlled .sql file, and create your database from that file rather than typing commands ad hoc.
Use :memory: databases for automated tests, so every test run starts completely fresh, without leftover state from previous runs.
Set PRAGMA foreign_keys = ON and consider PRAGMA journal_mode = WAL immediately after creating a database meant for real, ongoing use.
Double check your file paths carefully, especially in scripts, since a typo results in a silently created empty database rather than a clear error.
Separate your database files from your source code directory structure, and exclude the actual data file from version control while keeping the schema tracked.
Wrapping Up
Creating a SQLite database is about as close to friction-free as database setup gets — no server, no configuration files, no accounts. That simplicity is exactly why SQLite has become the default choice for so many applications, from mobile apps to embedded systems to local development environments. But simplicity at the start doesn’t mean you should skip good habits: track your schema, choose your file location deliberately, and configure a few sensible PRAGMA settings early, and you’ll set yourself up well for whatever you build on top of that empty file.
