How to fix SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed in SQLite

-- Check if a record with this email already exists
SELECT COUNT(*) FROM users WHERE email = '[email protected]';

-- If count is 0, safe to insert
IF count = 0 THEN
  INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'John');
END IF;

import sqlite3
connection = sqlite3.connect('database.db')
cursor = connection.cursor()

# Check if email exists
cursor.execute('SELECT COUNT(*) FROM users WHERE email = ?', ('[email protected]',))
if cursor.fetchone()[0] == 0:
    cursor.execute('INSERT INTO users (id, email, name) VALUES (?, ?, ?)',
                   (1, '[email protected]', 'John'))
    connection.commit()

-- Will replace any existing user with email '[email protected]'
REPLACE INTO users (id, email, name) VALUES (1, '[email protected]', 'John Smith');

INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'John')
ON CONFLICT(email) DO UPDATE SET name = 'John';

cursor.execute('''
  INSERT INTO users (id, email, name) VALUES (?, ?, ?)
  ON CONFLICT(email) DO UPDATE SET name = 'John'
''', (1, '[email protected]', 'John'))
connection.commit()

-- Will skip this insert if email already exists, no error raised
INSERT OR IGNORE INTO users (id, email, name) VALUES (1, '[email protected]', 'John');

cursor.execute('''
  INSERT OR IGNORE INTO users (id, email, name) VALUES (?, ?, ?)
''', (1, '[email protected]', 'John'))
connection.commit()

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
);

-- Don't specify id, let SQLite generate it
INSERT INTO users (email, name) VALUES ('[email protected]', 'John');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Jane');

cursor.execute('''
  INSERT INTO users (email, name) VALUES (?, ?)
''', ('[email protected]', 'John'))
connection.commit()

import sqlite3

connection = sqlite3.connect('database.db')
cursor = connection.cursor()

try:
    cursor.execute('''
      INSERT INTO users (id, email, name) VALUES (?, ?, ?)
    ''', (1, '[email protected]', 'John'))
    connection.commit()
    print('User inserted successfully')
except sqlite3.IntegrityError as e:
    if 'UNIQUE constraint failed' in str(e):
        print('This email already exists. Updating instead...')
        cursor.execute('''
          UPDATE users SET name = ? WHERE email = ?
        ''', ('John', '[email protected]'))
        connection.commit()
    else:
        raise

const Database = require('better-sqlite3');
const db = new Database('database.db');

try {
  const stmt = db.prepare('INSERT INTO users (id, email, name) VALUES (?, ?, ?)');
  stmt.run(1, '[email protected]', 'John');
} catch (error) {
  if (error.message.includes('UNIQUE constraint failed')) {
    console.log('Email already exists. Updating instead...');
    const updateStmt = db.prepare('UPDATE users SET name = ? WHERE email = ?');
    updateStmt.run('John', '[email protected]');
  } else {
    throw error;
  }
}