How to fix SQLITE_MISMATCH: datatype mismatch in SQLite

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

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

-- View table schema
.schema table_name

-- Check if table is STRICT
PRAGMA table_list;

-- Check column types
PRAGMA table_info(table_name);

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

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

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

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

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

-- 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');

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

// 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']

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

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 2

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

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

// 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();
  });

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

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

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

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

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

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