SQLite Create Database If Not Exists

Ninad Pathak

October 7, 2026

Creating an SQLite database only if it doesn’t exist takes no special statement: opening a file name that isn’t there yet creates the database when it’s first used. In this article I’ll walk you through how that works from the shell and from Python, and how to refuse to create a file when you only want to open one that’s already there.

TLDR

SQLite has no CREATE DATABASE. A database is a single file.
Connecting to a path creates the file if it’s missing and opens it if it exists: sqlite3.connect("web_analytics.db") in Python, or sqlite3 web_analytics.db in the shell.

Use CREATE TABLE IF NOT EXISTS to create the tables inside it only when they’re missing.
To open an existing file and fail if it’s missing, connect with file:web_analytics.db?mode=rw as a URI.

What does “create database if not exists” mean in SQLite?

In server databases such as MySQL or PostgreSQL, CREATE DATABASE IF NOT EXISTS asks the server to make a new database unless one with that name is already there. SQLite has no server. Each database is one ordinary file on disk, so the statement doesn’t exist:

$ sqlite3 :memory: "CREATE DATABASE IF NOT EXISTS web_analytics;"
Error: in prepare, near "DATABASE": syntax error
  CREATE DATABASE IF NOT EXISTS web_analytics;
         ^--- error here

The SQLite equivalent is to connect to the file. If the file doesn’t exist yet, SQLite creates it. If it does exist, SQLite opens it and changes nothing.

Create database if not exists syntax

There’s no SQL statement for this, so the “syntax” is the way you open the file:

Where	Create if missing, open if present	Open only if it exists
sqlite3 shell	`sqlite3 web_analytics.db`	none
Python	`sqlite3.connect("web_analytics.db")`	`sqlite3.connect("file:web_analytics.db?mode=rw", uri=True)`
Inside SQL	`ATTACH DATABASE 'archive.db' AS archive;`	none

The tables inside the file have their own guard: CREATE TABLE IF NOT EXISTS table_name (...).

How to create an SQLite database if it doesn’t exist

To show you how this works, I will use a small web analytics database with one web_traffic table.

Step 1: Write the schema with IF NOT EXISTS

Save the table and its first rows as setup.sql:

CREATE TABLE IF NOT EXISTS web_traffic (
  id INTEGER PRIMARY KEY,
  url TEXT,
  visits INTEGER,
  date TEXT
);
INSERT INTO web_traffic (url, visits, date)
VALUES
  ('https://www.example.com/', 500, '2023-06-01'),
  ('https://blog.example.com/post1', 250, '2023-06-01'),
  ('https://www.example.com/products', 750, '2023-06-02');

Step 2: Run it against a file that doesn’t exist yet

The shell creates web_analytics.db, then creates the table and inserts the rows:

$ ls web_analytics.db
ls: web_analytics.db: No such file or directory
$ sqlite3 web_analytics.db < setup.sql
$ sqlite3 -header -column web_analytics.db "SELECT * FROM web_traffic;"
id  url                               visits  date
--  --------------------------------  ------  ----------
1   https://www.example.com/          500     2023-06-01
2   https://blog.example.com/post1    250     2023-06-01
3   https://www.example.com/products  750     2023-06-02

Step 3: Run the guard again

Running CREATE TABLE IF NOT EXISTS a second time does nothing, and the three rows are still there. Without IF NOT EXISTS, the same statement fails because the table already exists:

$ sqlite3 web_analytics.db "CREATE TABLE IF NOT EXISTS web_traffic (id INTEGER PRIMARY KEY); SELECT count(*) FROM web_traffic;"
3
$ sqlite3 web_analytics.db "CREATE TABLE web_traffic (id INTEGER PRIMARY KEY);"
Error: in prepare, table web_traffic already exists
  CREATE TABLE web_traffic (id INTEGER PRIMARY KEY);
               ^--- error here

Examples

Each example below runs in the same folder as the steps above.

Create an empty SQLite database file

The shell doesn’t create the file when you open it. It creates the file when you run the first command that reads or writes the database. So opening and quitting leaves nothing behind, while .tables leaves an empty file:

$ sqlite3 empty.db ".quit"
$ ls empty.db
ls: empty.db: No such file or directory
$ sqlite3 empty.db ".tables"
$ ls -l empty.db | awk '{print $5, $NF}'
0 empty.db
$ sqlite3 empty.db "CREATE TABLE notes (body TEXT);"
$ ls -l empty.db | awk '{print $5, $NF}'
8192 empty.db

The new file is 0 bytes. It grows to 8192 bytes once it holds its first table.

Create it if missing, or refuse to create it, in Python

Python’s sqlite3.connect() creates the file as soon as it connects. To open only an existing file, open it as a URI with mode=rw. Use this when a wrong path should fail loudly instead of quietly creating an empty database:

import os, sqlite3

# Default mode creates the file if it is missing
con = sqlite3.connect("web_analytics.db")
con.execute("CREATE TABLE IF NOT EXISTS web_traffic (id INTEGER PRIMARY KEY, url TEXT, visits INTEGER, date TEXT)")
print(con.execute("SELECT count(*) FROM web_traffic").fetchone()[0])
con.close()

# mode=rw opens an existing file and refuses to create a new one
try:
    sqlite3.connect("file:missing.db?mode=rw", uri=True)
except sqlite3.OperationalError as e:
    print("OperationalError:", e)
print(os.path.exists("missing.db"))

$ python3 create_or_refuse.py
3
OperationalError: unable to open database file
False

The first connection found the existing file with its 3 rows. The mode=rw connection refused to create missing.db, and no file was left behind.

Create a second database file from inside SQL

Inside an open connection, ATTACH DATABASE plays the role that CREATE DATABASE plays elsewhere. It opens another file under a schema name, and creates the file if it doesn’t exist:

ATTACH DATABASE 'archive.db' AS archive;
CREATE TABLE IF NOT EXISTS archive.web_traffic_2022 (id INTEGER PRIMARY KEY, url TEXT);
SELECT name FROM pragma_database_list;

$ sqlite3 web_analytics.db < attach.sql
main
archive
$ ls *.db
archive.db
empty.db
web_analytics.db

Edge cases

Relying on “connect creates it” has two traps.

Inserts aren’t guarded. Running setup.sql again would insert the three rows a second time, because INSERT has no IF NOT EXISTS. For repeatable setup scripts, keep the inserts separate or use an upsert.
A typo creates a new database. sqlite3.connect("web_analytcs.db") creates a second, empty file without complaint. Use mode=rw where the file must already exist.

Conclusion

To create an SQLite database if it doesn’t exist, connect to its file path. SQLite creates the file on first use and opens it every time after. Guard each table with CREATE TABLE IF NOT EXISTS, and connect with mode=rw wherever a missing file should be an error.