Fix "no such table" in SQLite
Why SQLite says no such table when the table exists: the wrong file, a relative path, a copy missing its -wal file, :memory: or temp tables.
Quick Answer
Your code is almost always connected to a different file than the one you looked at. Opening a path that doesn't exist creates a new, empty database without any error, and the first query fails. Print the absolute path and list the tables in that file:
import os, sqlite3
path = "data/shop.db"
print(os.path.abspath(path)) # is this the file you expect?
conn = sqlite3.connect(path)
print(conn.execute("SELECT name FROM sqlite_schema WHERE type='table'").fetchall())
Build the path from your script's location instead of the working directory, and open existing databases in mode=rw so a wrong path fails loudly.
What the error means
SQLite looked up the name in every database attached to this connection and found nothing. It's a generic SQLITE_ERROR (code 1), so the message is your only clue:
| Client | What you see |
|---|---|
| sqlite3 CLI | Error: in prepare, no such table: orders, exit status 1 |
| Python sqlite3 | sqlite3.OperationalError: no such table: orders |
| Node better-sqlite3 | SqliteError: no such table: orders, err.code === "SQLITE_ERROR" |
The error fires when the statement is prepared, before anything runs, so nothing in that statement was executed.
In VisuaLeaf
See which tables the file really has in VisuaLeaf
Most "no such table" errors come down to the app opening a different file from the one you're looking at. In VisuaLeaf you open a SQLite database by picking the file itself, with no server or connection string. Choose the exact .db path your app is configured with, and the sidebar lists the tables that file contains. If the table is there, the app is opening some other file, which is cause 1 below. If it isn't, the table was never created in this file, which is cause 2.
To see the whole file at once, open Visual Schema. It reads the tables, columns, keys and foreign keys from the file and draws them as an ER diagram. That's useful when a migration created tables under a different name or case than the code expects. See VisuaLeaf for SQLite.

What we found testing this
- A relative path quietly creates a new file. The same script worked from the project folder and failed from its parent folder with
no such table: orders. The second run had silently created an emptyshop.db(0 bytes) in the wrong place. The sqlite3 CLI does the same with a mistyped file name. mode=rwturns that into a clear error.sqlite3.connect("file:missing.db?mode=rw", uri=True)raisedunable to open database fileinstead of creating anything.- Copying a live WAL database without its
-walfile loses tables. 5,000 rows sat inw.db-wal(313 KB) whilew.dbwas still 4 KB. A copy of justw.dbgaveno such table: t: the table had only been created in the WAL. Back up with.backuporVACUUM INTO. - Table names are not case-sensitive, despite what several guides say. A table created as
Ordersanswered toorders,ORDERSand even quoted"orders". Case is almost never the cause of this error. :memory:and temp tables belong to one connection. A second:memory:connection gotno such table: t, and aCREATE TEMP TABLEwas invisible to another connection on the same file.
Cause 1: the wrong database file
Check from SQL which file a connection really has open:
PRAGMA database_list; -- shows the full path of main and any attached databases
SELECT name FROM sqlite_schema WHERE type = 'table';
Then anchor the path to your code instead of wherever the process happens to start:
from pathlib import Path
DB = Path(__file__).resolve().parent / "data" / "shop.db"
conn = sqlite3.connect(f"file:{DB}?mode=rw", uri=True) # fails if the file is missing
const path = require('path')
const Database = require('better-sqlite3')
const db = new Database(path.join(__dirname, 'data', 'shop.db'), { fileMustExist: true })
In packaged apps (Electron, mobile, Docker), check the database was actually shipped or mounted to that path. A missing volume mount gives an empty file at the expected path.
Cause 2: the table was never created in this file
- Migrations ran against another database, for example a development file, or a test run that used
:memory:. - The
CREATE TABLEwas rolled back. In Python, schema changes inside a transaction are only saved bycommit(). - A typo. SQLite doesn't suggest names. Compare against
.tablesin the CLI or thesqlite_schemaquery above.
Cause 3: attached, temporary and in-memory databases
ATTACH, temp tables and :memory: databases only exist on the connection that created them. A connection pool or a new request handler gets a fresh connection without them.
ATTACH 'archive.db' AS arch;
SELECT * FROM arch.orders_2024; -- qualify the name with the schema
If you must share an in-memory database between connections, use a named shared-cache URI such as file:memdb1?mode=memory&cache=shared, and keep at least one connection open, or it disappears.
Frequently Asked Questions
Why does SQLite say no such table when the table exists?
Your program is almost certainly opening a different file. SQLite creates an empty database when a path does not exist, so a wrong or relative path gives a valid but empty database. Print the absolute path and run PRAGMA database_list.
Are SQLite table names case-sensitive?
No. A table created as Orders can be queried as orders or ORDERS. Case differences do not cause "no such table".
How do I stop SQLite from creating a new database file?
Open it with a URI and mode=rw, for example sqlite3.connect("file:app.db?mode=rw", uri=True) in Python, or new Database(path, { fileMustExist: true }) in better-sqlite3.
Why is my table missing after copying the database?
If the database uses WAL mode, recent changes live in the -wal file. Copying only the .db file can lose them, including newly created tables. Use .backup or VACUUM INTO to copy it.