How to fix SQLITE_MISMATCH: datatype mismatch in SQLite

SQLiteINTERMEDIATEMEDIUMUpdated May 28, 2026· 247 views

Fix SQLite "datatype mismatch" caused by writing a non-integer rowid or wrong type into a STRICT/typed column. Step-by-step fixes here.

Quick fix

Fixes SQLITE_MISMATCH

-- 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 INTEGER
  1. Understand rowid constraints and STRICT tables
  2. Fix rowid type violations
  3. Fix STRICT table type mismatches
  4. Debug CSV import mismatches
sqlite — error
-- 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 INTEGER
SQLITE_MISMATCH: datatype mismatch

What this error means

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.

Typical symptoms

  • Operations fail with error code SQLITE_MISMATCH
  • Inserting data into rowid column fails when using non-integer values
  • CSV import fails with 'datatype mismatch' errors
  • Setting primary key to string or UUID value fails
  • STRICT table inserts fail when column types don't match exactly
  • Error message indicates specific table and column when using STRICT tables
  • Operations work in traditional tables but fail after converting to STRICT
  • Bulk imports fail due to type incompatibilities

Common causes

  1. Attempting to set rowid to a non-integer value (string, float, blob, etc.)
  2. Inserting string UUIDs into integer primary key columns
  3. Using STRICT table mode without ensuring all inserts match declared types exactly
  4. CSV import with column count or type mismatches
  5. Foreign key columns with mismatched types between parent and child tables
  6. Type affinity violations in STRICT tables with incompatible data
  7. Column count mismatch during INSERT INTO ... VALUES operations
  8. Explicit NULL assignments to rowid or implicit rowid references

How to fix "SQLITE_MISMATCH: datatype mismatch"

1Understand rowid constraints and STRICT tables

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:

sql
-- 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 INTEGER

Context 2: STRICT Table Type Violations

STRICT tables enforce exact type matching for all columns:

sql
-- 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);  -- OK

Check your table definition:

sql
-- View table schema
.schema table_name

-- Check if table is STRICT
PRAGMA table_list;

-- Check column types
PRAGMA table_info(table_name);
2Fix rowid type violations

If the error occurs when working with rowid or primary key columns:

Problem: Trying to set rowid to non-integer

sql
-- 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_MISMATCH

Solution 1: Use integer primary keys

sql
-- 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 id

Solution 2: Use TEXT primary key if you need strings

sql
-- 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 matters

Solution 3: Map UUIDs to integers

sql
-- 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';
3Fix STRICT table type mismatches

For STRICT tables, ensure data types match column declarations exactly:

Problem: Type mismatch in STRICT table

sql
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 mode

Solution 1: Use correct types in INSERT

sql
-- 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

sql
-- 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

javascript
// 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

sql
-- 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;
4Debug CSV import mismatches

SQLITE_MISMATCH often occurs during CSV imports when column types don't align:

Problem: CSV import with type mismatches

csv
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
sql
-- 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 2

Solution 1: Clean CSV data before import

csv
-- Fixed CSV file
id,name,price,quantity
1,Widget,19.99,10
2,Gadget,29.99,5
3,Gizmo,34.99,8

Solution 2: Use temporary table for import

sql
-- 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

javascript
// 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();
  });
5Check foreign key type alignment

Type mismatches can occur with foreign key relationships:

Problem: Foreign key type mismatch

sql
-- 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 INTEGER

Solution: Match foreign key types

sql
-- 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

sql
-- 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);
6Validate column count in INSERT statements

Column count mismatches can cause type-related errors:

Problem: Column count mismatch

sql
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 values

Solution: Use explicit column list

sql
-- 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 mapping

Solution: Create temporary staging table

sql
-- 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;

Advanced notes

### 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.

sql
-- 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_MISMATCH

Rowid 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.

sql
-- 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.

Frequently asked questions

What does "SQLITE_MISMATCH" mean?
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.
How do I fix "SQLITE_MISMATCH"?
Fix SQLite "datatype mismatch" caused by writing a non-integer rowid or wrong type into a STRICT/typed column. Step-by-step fixes here.
What causes "SQLITE_MISMATCH"?
Attempting to set rowid to a non-integer value (string, float, blob, etc.) Inserting string UUIDs into integer primary key columns Using STRICT table mode without ensuring all inserts match declared types exactly
Is the "SQLITE_MISMATCH" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading