The SQLITE_MISMATCH error occurs when you attempt an operation that violates SQLite's strict type requirements, most commonly when trying to set a rowid to a non-integer value or when inserting data that conflicts with column type declarations in STRICT tables. SQLite is typically forgiving about type mismatches, but certain operations require exact type compliance.
SQLITE_MISMATCH is an error code that indicates a datatype mismatch in contexts where SQLite enforces strict type checking. Unlike most SQL operations where SQLite is flexible about type coercion, certain operations require exact type compliance. The most common cause is attempting to set a table's rowid (the internal row identifier) to a non-integer value. The rowid in SQLite is a 64-bit signed integer that uniquely identifies each row. Any attempt to set it to a different type (string, blob, real, etc.) results in SQLITE_MISMATCH. Other causes include: 1. **STRICT table violations**: SQLite 3.37.0+ introduced STRICT tables that enforce column type constraints more strictly 2. **CSV import column mismatches**: Importing data with incorrect column counts or types 3. **Foreign key type conflicts**: Referencing columns with mismatched data types 4. **UUID/string primary key issues**: Attempting to use strings where integers are expected 5. **Type affinity violations**: Storing values incompatible with the column's declared type in STRICT mode Unlike the more permissive traditional SQLite tables, STRICT tables will raise SQLITE_MISMATCH when data types don't match the column definitions exactly.
First, understand the two main contexts where SQLITE_MISMATCH occurs:
Context 1: Rowid Type Violation
SQLite tables have an internal rowid (64-bit integer) that uniquely identifies each row. This rowid cannot be changed to a non-integer value:
-- rowid MUST be an integer (or NULL for auto-assignment)
CREATE TABLE users (
id INTEGER PRIMARY KEY, -- Explicitly define rowid
name TEXT
);
-- Valid: rowid will be auto-incremented as integer
INSERT INTO users (name) VALUES ('Alice');
-- INVALID: Cannot set rowid to string
INSERT INTO users (rowid, name) VALUES ('abc', 'Bob');
-- Error: SQLITE_MISMATCH - rowid must be INTEGERContext 2: STRICT Table Type Violations
STRICT tables enforce exact type matching for all columns:
-- STRICT table enforces column type declarations
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
balance REAL NOT NULL,
status TEXT
) STRICT;
-- Valid: types match exactly
INSERT INTO accounts (balance, status) VALUES (100.50, 'active');
-- INVALID: string cannot go into REAL column
INSERT INTO accounts (balance, status) VALUES ('abc', 'active');
-- Error: SQLITE_MISMATCH in STRICT mode
-- Valid: NULL is allowed even in STRICT tables (unless NOT NULL)
CREATE TABLE items (
code TEXT NOT NULL,
quantity INTEGER
) STRICT;
INSERT INTO items (code, quantity) VALUES ('ITEM-1', 5);
INSERT INTO items (code, quantity) VALUES ('ITEM-2', NULL); -- OKCheck your table definition:
-- View table schema
.schema table_name
-- Check if table is STRICT
PRAGMA table_list;
-- Check column types
PRAGMA table_info(table_name);If the error occurs when working with rowid or primary key columns:
Problem: Trying to set rowid to non-integer
-- WRONG: String values for rowid
INSERT INTO users (rowid, name) VALUES ('abc-123', 'Charlie');
-- Error: SQLITE_MISMATCH
-- WRONG: Using UUID string as primary key
INSERT INTO users (id, name) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Diana');
-- Error when id is INTEGER PRIMARY KEY
-- WRONG: Float value for rowid
INSERT INTO users (rowid, name) VALUES (1.5, 'Eve');
-- Error: SQLITE_MISMATCHSolution 1: Use integer primary keys
-- Correct: Use integer values
INSERT INTO users (id, name) VALUES (1, 'Charlie');
INSERT INTO users (id, name) VALUES (2, 'Diana');
-- Correct: Let SQLite auto-assign integer rowid
INSERT INTO users (name) VALUES ('Eve');
-- SQLite will automatically assign next integer idSolution 2: Use TEXT primary key if you need strings
-- Define primary key as TEXT instead of INTEGER
CREATE TABLE users (
id TEXT PRIMARY KEY, -- Explicitly TEXT, not INTEGER
name TEXT
);
-- Now you can use strings as primary key
INSERT INTO users (id, name) VALUES ('abc-123', 'Charlie');
INSERT INTO users (id, name) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Diana');
-- Note: TEXT PRIMARY KEY doesn't use the internal rowid optimization
-- Use INTEGER PRIMARY KEY when performance mattersSolution 3: Map UUIDs to integers
-- Use integer primary key with UUID stored separately
CREATE TABLE users (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL UNIQUE,
name TEXT
);
-- Insert with both ID and UUID
INSERT INTO users (id, uuid, name)
VALUES (1, '550e8400-e29b-41d4-a716-446655440000', 'Charlie');
-- Query by UUID
SELECT * FROM users WHERE uuid = '550e8400-e29b-41d4-a716-446655440000';For STRICT tables, ensure data types match column declarations exactly:
Problem: Type mismatch in STRICT table
CREATE TABLE products (
id INTEGER,
price REAL NOT NULL, -- Must be REAL (not TEXT or INTEGER)
quantity INTEGER, -- Must be INTEGER
name TEXT -- Must be TEXT
) STRICT;
-- WRONG: String value for REAL column
INSERT INTO products VALUES (1, 'nineteen.99', 10, 'Widget');
-- Error: SQLITE_MISMATCH
-- WRONG: String value for INTEGER column
INSERT INTO products VALUES (1, 19.99, 'ten', 'Widget');
-- Error: SQLITE_MISMATCH
-- WRONG: Integer for TEXT column won't work in STRICT mode
INSERT INTO products VALUES (1, 19.99, 10, 123);
-- Error: SQLITE_MISMATCH in STRICT modeSolution 1: Use correct types in INSERT
-- Correct: All types match column declarations
INSERT INTO products (id, price, quantity, name)
VALUES (1, 19.99, 10, 'Widget');
INSERT INTO products (id, price, quantity, name)
VALUES (2, 29.99, 5, 'Gadget');Solution 2: Cast values to correct types
-- When data comes from external sources, cast it
INSERT INTO products (id, price, quantity, name)
VALUES (
CAST('1' AS INTEGER), -- Cast string to INTEGER
CAST('19.99' AS REAL), -- Cast string to REAL
CAST('10' AS INTEGER), -- Cast string to INTEGER
'12-inch Widget' -- Already TEXT
);Solution 3: Enable type checking in application code
// Node.js - Validate types before insert
const validateProduct = (data) => {
const errors = [];
if (typeof data.id !== 'number') {
errors.push('id must be a number');
}
if (typeof data.price !== 'number') {
errors.push('price must be a number');
}
if (typeof data.quantity !== 'number') {
errors.push('quantity must be a number');
}
if (typeof data.name !== 'string') {
errors.push('name must be a string');
}
return errors;
};
const errors = validateProduct({ id: 1, price: '19.99', quantity: 10, name: 'Widget' });
// Returns: ['price must be a number']Solution 4: Convert from traditional to STRICT gradually
-- If converting existing table from traditional to STRICT:
-- 1. First, create new STRICT table with validated data
CREATE TABLE products_strict (
id INTEGER,
price REAL NOT NULL,
quantity INTEGER,
name TEXT
) STRICT;
-- 2. Copy data with explicit casting
INSERT INTO products_strict (id, price, quantity, name)
SELECT
CAST(id AS INTEGER),
CAST(price AS REAL),
CAST(quantity AS INTEGER),
CAST(name AS TEXT)
FROM products_old
WHERE id IS NOT NULL
AND price IS NOT NULL
AND quantity IS NOT NULL
AND name IS NOT NULL;
-- 3. Verify conversion
SELECT COUNT(*) FROM products_old;
SELECT COUNT(*) FROM products_strict;
-- 4. Once verified, rename/replace
DROP TABLE products_old;
ALTER TABLE products_strict RENAME TO products;SQLITE_MISMATCH often occurs during CSV imports when column types don't align:
Problem: CSV import with type mismatches
id,name,price,quantity
1,Widget,19.99,10
2,Gadget,abc,5 -- Price is string, not numeric
3,Gizmo,29.99,unknown -- Quantity is string, not numeric-- If target table is STRICT, import will fail
CREATE TABLE products (
id INTEGER,
name TEXT,
price REAL,
quantity INTEGER
) STRICT;
-- Using SQLite CLI: .mode csv, then .import products.csv products
-- Will fail with SQLITE_MISMATCH on row 2Solution 1: Clean CSV data before import
-- Fixed CSV file
id,name,price,quantity
1,Widget,19.99,10
2,Gadget,29.99,5
3,Gizmo,34.99,8Solution 2: Use temporary table for import
-- Create temporary table that accepts any type
CREATE TABLE products_temp (
id TEXT,
name TEXT,
price TEXT,
quantity TEXT
);
-- Import CSV into temporary table
.mode csv
.import products.csv products_temp
-- Then transfer with validation and casting
INSERT INTO products (id, name, price, quantity)
SELECT
CAST(id AS INTEGER),
name,
CAST(price AS REAL),
CAST(quantity AS INTEGER)
FROM products_temp
WHERE
-- Validate castable values
CAST(id AS INTEGER) IS NOT NULL
AND CAST(price AS REAL) IS NOT NULL
AND CAST(quantity AS INTEGER) IS NOT NULL
AND name IS NOT NULL;
-- Check what failed
SELECT * FROM products_temp
WHERE
CAST(id AS INTEGER) IS NULL
OR CAST(price AS REAL) IS NULL
OR CAST(quantity AS INTEGER) IS NULL
OR name IS NULL;
-- Drop temporary table
DROP TABLE products_temp;Solution 3: Use application-level CSV parsing
// Node.js with csv-parser
const fs = require('fs');
const csv = require('csv-parser');
const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('database.db');
fs.createReadStream('products.csv')
.pipe(csv())
.on('data', (row) => {
// Validate and cast before insert
const id = parseInt(row.id);
const price = parseFloat(row.price);
const quantity = parseInt(row.quantity);
// Check for invalid conversions
if (isNaN(id) || isNaN(price) || isNaN(quantity)) {
console.error('Skipping invalid row:', row);
return;
}
// Insert with validated types
db.run(
'INSERT INTO products (id, name, price, quantity) VALUES (?, ?, ?, ?)',
[id, row.name, price, quantity],
(err) => {
if (err) console.error('Insert error:', err);
}
);
})
.on('end', () => {
db.close();
});Type mismatches can occur with foreign key relationships:
Problem: Foreign key type mismatch
-- Parent table uses INTEGER primary key
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
) STRICT;
-- Child table uses TEXT for foreign key (WRONG)
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id TEXT, -- Mismatched type: TEXT instead of INTEGER
amount REAL,
FOREIGN KEY (user_id) REFERENCES users(id)
) STRICT;
-- This insert will fail with SQLITE_MISMATCH
INSERT INTO orders (user_id, amount) VALUES ('123', 99.99);
-- Foreign key type doesn't match: TEXT vs INTEGERSolution: Match foreign key types
-- Correct: Foreign key type matches referenced column
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER, -- Must match parent type: INTEGER
amount REAL,
FOREIGN KEY (user_id) REFERENCES users(id)
) STRICT;
-- Now this works
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
-- Or use TEXT for both if needed
CREATE TABLE users_str (
id TEXT PRIMARY KEY,
name TEXT
) STRICT;
CREATE TABLE orders_str (
id INTEGER PRIMARY KEY,
user_id TEXT, -- Matches: both TEXT
amount REAL,
FOREIGN KEY (user_id) REFERENCES users_str(id)
) STRICT;
-- This works
INSERT INTO users_str (id, name) VALUES ('user-123', 'Bob');
INSERT INTO orders_str (user_id, amount) VALUES ('user-123', 149.99);Solution: Use consistent ID types across schema
-- Best practice: Use INTEGER PRIMARY KEY everywhere
CREATE TABLE users (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL UNIQUE, -- Store UUID as TEXT if needed
name TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL, -- Integer foreign key
amount REAL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL, -- Integer foreign key
product_id INTEGER NOT NULL, -- Integer foreign key
quantity INTEGER,
FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- All types align correctly
INSERT INTO users (uuid, name) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Charlie');
INSERT INTO orders (user_id, amount) VALUES (1, 199.99);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);Column count mismatches can cause type-related errors:
Problem: Column count mismatch
CREATE TABLE products (
id INTEGER, -- 4 columns total
name TEXT,
price REAL,
quantity INTEGER
) STRICT;
-- WRONG: Only 2 values for 4 columns
INSERT INTO products VALUES ('Widget', 19.99);
-- Error: SQLITE_MISMATCH or column mismatch
-- WRONG: 5 values for 4 columns
INSERT INTO products VALUES (1, 'Widget', 19.99, 10, 'extra');
-- Error: Too many valuesSolution: Use explicit column list
-- Better: Explicitly list columns
INSERT INTO products (id, name, price, quantity)
VALUES (1, 'Widget', 19.99, 10);
-- Or minimal required columns (if others allow NULL/have defaults)
INSERT INTO products (name, price)
VALUES ('Widget', 19.99);
-- id and quantity will be NULL or use defaults
-- For CSV imports, match column order
.mode csv
.headers on
.import products.csv products
-- CSV column order must match table column order or use explicit mappingSolution: Create temporary staging table
-- For complex imports, use a flexible staging table
CREATE TABLE products_import (
id TEXT,
name TEXT,
price TEXT,
quantity TEXT,
extra1 TEXT,
extra2 TEXT
);
-- Import raw data with any number of columns
.mode csv
.import data.csv products_import
-- Validate and transfer to main table
INSERT INTO products (id, name, price, quantity)
SELECT
CAST(id AS INTEGER),
name,
CAST(price AS REAL),
CAST(quantity AS INTEGER)
FROM products_import
WHERE id IS NOT NULL AND name IS NOT NULL;
DROP TABLE products_import;### SQLite Type System and STRICT Tables
Traditional vs. STRICT Tables:
SQLite originally used dynamic typing where any value could be stored in any column. With SQLite 3.37.0 (2021), STRICT tables were introduced for stricter type enforcement.
-- Traditional table: forgiving of type mismatches
CREATE TABLE traditional (
id INTEGER,
value REAL
);
INSERT INTO traditional VALUES ('abc', 'def'); -- Allowed
-- STRICT table: enforces types
CREATE TABLE strict_table (
id INTEGER,
value REAL
) STRICT;
INSERT INTO strict_table VALUES ('abc', 'def'); -- SQLITE_MISMATCHRowid Behavior:
Every SQLite table has an implicit rowid (except WITHOUT ROWID tables). The rowid is always a 64-bit signed integer from -9223372036854775808 to 9223372036854775807.
-- These are equivalent
CREATE TABLE a (id INTEGER PRIMARY KEY, data TEXT);
CREATE TABLE b (data TEXT); -- Still has implicit rowid
-- Accessing rowid
SELECT rowid, * FROM b;
SELECT _rowid_, * FROM b;
SELECT oid, * FROM b; -- All three refer to the same 64-bit integer### Type Affinity Rules
SQLite uses type affinity to suggest what type a column should store, but doesn't enforce it (except in STRICT tables):
| Affinity | Columns | Preferred Type |
|----------|---------|----------------|
| INTEGER | INT, INTEGER | Integer |
| TEXT | CHAR, CLOB, TEXT | Text string |
| REAL | REAL, FLOA, DOUB | Floating point |
| NUMERIC | NUMERIC, DECIMAL | Could be integer or float |
| BLOB | BLOB, (none) | Binary data |
### CSV Import Best Practices
1. Always use a staging table for complex imports
2. Validate data before transferring to main table
3. Use CAST() to convert types explicitly
4. Check for NULL before inserts
5. Log failures for debugging
### UUID Handling Strategies
Option 1: Map to Integer
- Store UUID as TEXT, use INTEGER PRIMARY KEY
- Most efficient for SQLite
- Recommended for most applications
Option 2: Use TEXT PRIMARY KEY
- Define column as TEXT PRIMARY KEY
- Doesn't get rowid optimization
- Useful if UUID is the natural identifier
Option 3: Hybrid Approach
- Integer PRIMARY KEY for performance
- TEXT UNIQUE column for UUID
- Query by UUID when needed
### Performance Implications
- INTEGER PRIMARY KEY: Uses efficient rowid
- TEXT PRIMARY KEY: No rowid optimization
- INTEGER with TEXT UNIQUE: Best of both worlds
- Type mismatches: May disable index usage
### Migration Checklist
When converting tables to STRICT:
- [ ] Review all INSERT/UPDATE code
- [ ] Identify type mismatches
- [ ] Add CAST() where needed
- [ ] Test with sample data
- [ ] Create backup before migration
- [ ] Use transaction for safety
- [ ] Verify row counts match
### Related Errors
- SQLITE_CONSTRAINT: General constraint violation
- SQLITE_CONSTRAINT_DATATYPE: Constraint with type mismatch
- SQLITE_ERROR: General error (may indicate type issue)
Each error requires different debugging approaches.
SQLITE_BUSY: The database file is locked
How to fix 'SQLITE_BUSY: The database file is locked' in SQLite
better-sqlite3: This statement has already been finalized
How to fix "better-sqlite3: This statement has already been finalized" in SQLite
SQLITE_AUTH: Authorization denied
SQLITE_AUTH: Authorization denied
SQLITE_CONSTRAINT_CHECK: CHECK constraint failed
CHECK constraint failed in SQLite
SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified
How to fix "SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified" in SQLite