Introduction

Query the sqlite_master (or sqlite_schema) system table and filter by type = 'table'. If the query returns a row, the table exists. In many cases, though, you should not check at all. If your goal is to create the table safely, CREATE TABLE IF NOT EXISTS is simpler and avoids the check-then-act race condition entirely.

Query the Schema Table

This is the standard existence check in SQLite:

1SELECT name
2FROM sqlite_master
3WHERE type = 'table'
4  AND name = 'users';

If you prefer a boolean-style result (1 for exists, 0 for does not exist):

1SELECT EXISTS (
2    SELECT 1
3    FROM sqlite_master
4    WHERE type = 'table'
5      AND name = 'users'
6);

Starting with SQLite 3.33.0 (released August 2020), the table was aliased to sqlite_schema. Both names work in modern SQLite, but sqlite_master remains more portable if your code needs to support older versions.

1-- Modern syntax, equivalent to sqlite_master
2SELECT name
3FROM sqlite_schema
4WHERE type = 'table'
5  AND name = 'users';

Using Parameterized Queries in Application Code

When the table name comes from a variable, always use parameterized queries to prevent SQL injection. Here are examples in multiple languages.

Python:

1import sqlite3
2
3conn = sqlite3.connect("app.db")
4cur = conn.cursor()
5
6table_name = "users"
7cur.execute(
8    """
9    SELECT EXISTS (
10        SELECT 1
11        FROM sqlite_master
12        WHERE type = 'table' AND name = ?
13    )
14    """,
15    (table_name,),
16)
17
18exists = cur.fetchone()[0] == 1
19print(f"Table '{table_name}' exists: {exists}")

JavaScript (better-sqlite3, Node.js):

1const Database = require('better-sqlite3');
2const db = new Database('app.db');
3
4const tableName = 'users';
5const row = db.prepare(
6  `SELECT EXISTS (
7    SELECT 1 FROM sqlite_master
8    WHERE type = 'table' AND name = ?
9  ) AS table_exists`
10).get(tableName);
11
12console.log(`Table '${tableName}' exists:`, row.table_exists === 1);

Java (JDBC):

1String tableName = "users";
2String sql = "SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?)";
3
4try (PreparedStatement stmt = connection.prepareStatement(sql)) {
5    stmt.setString(1, tableName);
6    ResultSet rs = stmt.executeQuery();
7    boolean exists = rs.next() && rs.getInt(1) == 1;
8    System.out.println("Table exists: " + exists);
9}

PRAGMA table_info as a Secondary Check

You will also see this approach:

PRAGMA table_info(users);

If the table exists, SQLite returns one row per column with the column name, type, default value, and nullable flag. If the result set is empty, the table does not exist (or has no columns, which is not possible in practice).

This is useful when you also need column metadata, but it is less direct than querying the schema table when existence is the only question. It also cannot be parameterized like a regular SQL query, which makes it harder to use safely with dynamic table names.

CREATE TABLE IF NOT EXISTS

Many existence checks are written only because the code wants to create the table if it is missing. SQLite handles this in a single statement:

1CREATE TABLE IF NOT EXISTS users (
2    id INTEGER PRIMARY KEY,
3    name TEXT NOT NULL,
4    email TEXT UNIQUE,
5    created_at TEXT DEFAULT (datetime('now'))
6);

This is better than the two-step approach (check, then create) for several reasons:

1. It is atomic. No race condition between checking and creating.
2. It is one round-trip to the database instead of two.
3. The code is shorter and harder to get wrong.

If the table already exists, the statement is a no-op. It does not verify that the existing table has the same schema. If you need schema validation, that is a separate concern (see PRAGMA table_info above).

Distinguishing Tables from Views and Indexes

The sqlite_master table contains all schema objects, not just tables. The type column can be table, view, index, or trigger. Without the type = 'table' filter, you might match a view or index with the same name and incorrectly conclude the table exists.

1-- Check for a view
2SELECT name FROM sqlite_master
3WHERE type = 'view' AND name = 'active_users';
4
5-- Check for an index
6SELECT name FROM sqlite_master
7WHERE type = 'index' AND name = 'idx_users_email';
8
9-- List all schema objects
10SELECT type, name FROM sqlite_master ORDER BY type, name;

Temporary Tables

Temporary tables are stored in a separate schema catalog. If you create a table with CREATE TEMP TABLE, it will not appear in sqlite_master. Check sqlite_temp_master (or sqlite_temp_schema) instead:

1-- Create a temp table
2CREATE TEMP TABLE session_cache (key TEXT PRIMARY KEY, value TEXT);
3
4-- This returns nothing, even though the table exists
5SELECT name FROM sqlite_master
6WHERE type = 'table' AND name = 'session_cache';
7
8-- This finds it
9SELECT name FROM sqlite_temp_master
10WHERE type = 'table' AND name = 'session_cache';

If your code needs to check both regular and temporary tables, query both catalogs:

1SELECT name FROM sqlite_master
2WHERE type = 'table' AND name = 'session_cache'
3UNION ALL
4SELECT name FROM sqlite_temp_master
5WHERE type = 'table' AND name = 'session_cache';

Attached Databases

When you attach a database with ATTACH DATABASE, its schema lives in a separate catalog named after the attachment alias:

1ATTACH DATABASE 'analytics.db' AS analytics;
2
3-- Check for a table in the attached database
4SELECT name FROM analytics.sqlite_master
5WHERE type = 'table' AND name = 'events';

The prefix before sqlite_master must match the alias you used in the ATTACH statement.

Comparison of Methods

Common Pitfalls

Checking before creating when IF NOT EXISTS would suffice. The two-step approach adds complexity and can race in multi-threaded scenarios where two threads both check, both see "not found," and both try to create.

Forgetting type = 'table' in the filter. Without this filter, a view named users would match a query looking for a table named users. The result would be a false positive.

Using PRAGMA table_info as a general existence test. It works, but it cannot be parameterized, making it vulnerable to SQL injection if the table name comes from user input. It also returns a full result set when you only need a boolean.

Checking sqlite_master for temp tables. Temporary tables are stored in sqlite_temp_master. Checking the wrong catalog makes a table look missing when it is actually present.

Assuming schema compatibility after an existence check. CREATE TABLE IF NOT EXISTS confirms the table exists but does not verify its columns match your expected schema. A table with the right name but wrong columns will pass the check silently. Use PRAGMA table_info for schema validation.

Summary

• Query sqlite_master (or sqlite_schema) with type = 'table' to check whether a table exists.
• Use SELECT EXISTS(...) when you need a clean boolean result in application code.
• Always use parameterized queries when the table name comes from a variable.
• Prefer CREATE TABLE IF NOT EXISTS when your real goal is safe table creation.
• Use sqlite_temp_master for temporary tables, and prefix with the alias for attached databases.
• Filter by type to avoid false positives from views, indexes, or triggers with the same name.