table already exists

This error occurs when you try to create a table that already exists. Learn how to handle this with IF NOT EXISTS or by checking first.

The table already exists error means you're trying to CREATE a table with a name that's already used.

Understanding the Error

Error: table users already exists

The table users already exists in the database.

Common Causes

1. Running Schema Setup Twice

JAVASCRIPT

// This fails on second run
db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY)');
// Error: table users already exists

2. Migration Running Twice

Migrations without proper tracking run duplicate operations.

3. Copy-Paste Errors

Accidentally duplicating CREATE TABLE statements.

How to Fix It

Solution 1: Use IF NOT EXISTS

SQL

-- Only creates if table doesn't exist
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

-- Safe to run multiple times

Solution 2: Check Before Creating

JAVASCRIPT

function tableExists(db, name) {
  const result = db.prepare(`
    SELECT name FROM sqlite_master
    WHERE type='table' AND name=?
  `).get(name);
  return !!result;
}

if (!tableExists(db, 'users')) {
  db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY)');
}

Solution 3: Drop Then Create

SQL

-- Warning: Deletes all data!
DROP TABLE IF EXISTS users;
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

Solution 4: Use Migrations

Track which migrations have run:

JAVASCRIPT

// Create migrations table
db.exec(`
  CREATE TABLE IF NOT EXISTS migrations (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    applied_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

function runMigration(db, name, sql) {
  const applied = db.prepare(
    'SELECT 1 FROM migrations WHERE name = ?'
  ).get(name);

  if (!applied) {
    db.exec(sql);
    db.run('INSERT INTO migrations (name) VALUES (?)', [name]);
  }
}

runMigration(db, 'create_users', `
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)
`);

Best Practices

Always use IF NOT EXISTS for idempotent setup
Use a migration system for schema changes
Version control your schema files
Test schema setup from scratch regularly