SQLITE_MISMATCH: datatype mismatch

This error occurs when you try to store a value that doesn't match the expected type, typically with PRIMARY KEY columns. Learn about SQLite's type affinity system.

The datatype mismatch error usually occurs with PRIMARY KEY constraints when the value type doesn't match expectations.

Understanding the Error

SQLITE_MISMATCH: datatype mismatch

This commonly happens with INTEGER PRIMARY KEY columns when you try to insert a non-integer value.

Common Causes

1. Non-Integer in INTEGER PRIMARY KEY

SQL

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

-- Fails: 'abc' is not an integer
INSERT INTO users (id, name) VALUES ('abc', 'Alice');

2. ROWID Alias Mismatch

INTEGER PRIMARY KEY is an alias for SQLite's internal ROWID:

SQL

-- These are equivalent
CREATE TABLE t (id INTEGER PRIMARY KEY);
CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT);

-- Must be an integer (or NULL for auto-generation)
INSERT INTO t (id) VALUES (3.14);  -- Fails

3. Strict Tables (SQLite 3.37+)

Strict mode enforces types:

SQL

CREATE TABLE users (
  id INTEGER,
  name TEXT
) STRICT;

-- Fails in strict mode
INSERT INTO users VALUES ('not-an-int', 123);

How to Fix It

Solution 1: Use Correct Types

SQL

-- For INTEGER PRIMARY KEY, use integers or NULL
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (name) VALUES ('Bob');  -- id auto-generated

Solution 2: Use TEXT Primary Key

If you need string IDs:

SQL

CREATE TABLE users (
  id TEXT PRIMARY KEY,  -- Not INTEGER
  name TEXT
);

INSERT INTO users VALUES ('user_abc123', 'Alice');

Solution 3: Convert Types in Application

JAVASCRIPT

// Ensure integer before inserting
const id = parseInt(userInput, 10);
if (isNaN(id)) {
  throw new Error('ID must be a number');
}
db.run('INSERT INTO users (id, name) VALUES (?, ?)', [id, name]);

SQLite Type Affinity

SQLite is generally flexible with types, but INTEGER PRIMARY KEY is special:

SQL

-- These work (flexible typing)
CREATE TABLE flexible (value NUMERIC);
INSERT INTO flexible VALUES ('123');  -- Stored as integer 123
INSERT INTO flexible VALUES ('abc');  -- Stored as text 'abc'

-- This is strict
CREATE TABLE strict_id (id INTEGER PRIMARY KEY);
INSERT INTO strict_id VALUES ('abc');  -- FAILS

Best Practices

Use NULL for auto-increment IDs instead of omitting the column
Validate types in application code
Consider TEXT keys for UUIDs or string identifiers
Test with edge cases (strings, floats, nulls)