no such table

What it means

Before running a statement, SQLite checks every table it names against the schema of the database you opened (plus any attached databases and the temporary schema). If there’s no table or view by that name, the statement fails with SQLITE_ERROR (code 1) and no such table: <name>, and nothing runs.

Table names aren’t case-sensitive in SQLite: CUSTOMERS finds customers. So case is rarely the cause; the table really isn’t in that file.

Common causes

1. You opened a different file. A relative path like app.db is resolved against the current working directory, which differs between your terminal, Xcode, a test runner, a service and a cron job. If nothing exists at that path, SQLite creates a new, empty database there, so opening succeeds and the first query fails.
2. The table hasn’t been created yet. Migrations didn’t run, or ran against another file (dev.db instead of test.db). An in-memory database (:memory:) starts empty for every connection, so tables created on one connection don’t exist on another.
3. A typo, often singular for plural: customer for customers.
4. A wrong prefix. archive.customers needs ATTACH 'archive.db' AS archive first, on the same connection; temp.customers looks only at temporary tables.
5. A view or trigger names a table that’s gone. SQLite lets you drop or rename a table that a view uses; the view fails later with the old name, prefixed with main..

How to fix it

Check which file you opened

Ask the connection itself:

PRAGMA database_list;

0|main|/private/tmp/seo_lite/shop.db

An empty path means a temporary or in-memory database. In your code, build an absolute path (from your app’s data folder or configuration) instead of relying on the working directory.

To make a wrong path fail loudly instead of creating an empty file, open the database in read-write mode without create: a file: URI with mode=rw, or SQLITE_OPEN_READWRITE without SQLITE_OPEN_CREATE in the C API.

sqlite3 "file:missing.db?mode=rw" "SELECT * FROM customers;"

Error: unable to open database "file:missing.db?mode=rw": unable to open database file

List the tables that are there

SELECT name, type FROM sqlite_schema WHERE type IN ('table', 'view') ORDER BY name;

In the sqlite3 shell, .tables does the same. Older SQLite versions know the schema table only by its old name, sqlite_master, which still works everywhere. An empty result on a file that should have data means you’re in the wrong file, or an empty one was created by mistake.

Create the table or run the migrations

If it’s the right file and the table isn’t there, run your migrations against that path, or create the table. For :memory: databases, create the schema on the same connection that queries it.

Fix the name or prefix

Use the exact name .tables shows. Names with spaces or reserved words need double quotes: SELECT * FROM "Order Items". For another file, attach it on the same connection before you use its prefix:

ATTACH 'archive.db' AS archive;
SELECT * FROM archive.customers;

Repair views and triggers

Find what still refers to the old table:

SELECT type, name FROM sqlite_schema WHERE sql LIKE '%legacy%';

Drop and recreate the view or trigger against the table’s new name.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with a customers table in shop.db. A typo:

sqlite3 shop.db "SELECT * FROM customer;"

Error: in prepare, no such table: customer

The shell adds “Error: in prepare,”; SQLite’s message is no such table: customer.

A mistyped file name (shopp.db) gives the same error for a table that does exist, and leaves a new, empty file behind:

Error: in prepare, no such table: customers
-rw-r--r--@ 1 wm  wheel  0 Oct  9 11:05 shopp.db

A prefix for a database that isn’t attached:

Error: in prepare, no such table: archive.customers

A view over a table that was dropped afterwards (CREATE VIEW v_legacy AS SELECT * FROM legacy; DROP TABLE legacy; both succeed), then SELECT * FROM v_legacy;:

Error: in prepare, no such table: main.legacy

In Inlet

Inlet opens the SQLite file you choose, so there’s no working directory to get wrong. ⌘P opens a table by name from the ones in that file, and the query editor completes table names from the live schema, which catches typos before you run the query.