SQLite behaves differently from other databases in ways that regularly surprise developers. These are the most important differences — sourced directly from the official SQLite documentation.
16 gotchas
⚠
SQLite Is Embedded, Not Client-Server
SQLite runs in-process as a library, not as a separate server process.
✗ Avoid
-- Assuming SQLite can handle high-concurrency multi-process writes
-- like a client/server database:
-- Process A and Process B both open the same file and write simultaneously
→ Potential database locking errors or serialized writes — SQLite allows only one writer at a time
✓ Instead
-- Use SQLite for embedded, single-process, or low-concurrency scenarios
-- For multi-process write workloads, use PostgreSQL or MySQL instead
SELECT * FROM settings; -- perfectly fine for a local app
→ Fast, reliable local query with no network overhead
architectureserverlessembeddedconcurrency
⚠
Flexible Typing
SQLite is flexibly typed — column types are advisory, not mandatory.
✗ Avoid
-- Code that relies on flexible typing and will break on PostgreSQL:
CREATE TABLE products(price INTEGER);
INSERT INTO products(price) VALUES('not-a-number'); -- silently succeeds in SQLite
→ Inserts the text 'not-a-number' into an INTEGER column — no error in SQLite
✓ Instead
-- Use STRICT tables (SQLite 3.37+) to enforce types like other databases:
CREATE TABLE products(price INTEGER) STRICT;
INSERT INTO products(price) VALUES('not-a-number');
→ Error: cannot store TEXT value in INTEGER column of strict table
typesflexible-typingstrictmigration
⚠
No Separate BOOLEAN Datatype
SQLite has no BOOLEAN storage class; TRUE and FALSE are stored as integers 1 and 0.
✗ Avoid
-- Assuming typeof() returns 'boolean' or that TRUE != 1:
SELECT typeof(TRUE);
→ 'integer' — TRUE is just the integer 1
✓ Instead
-- Treat boolean columns as integers; check with = 1 or = 0
SELECT * FROM tasks WHERE is_complete = 1;
SELECT * FROM tasks WHERE NOT is_complete; -- also valid
→ Returns rows where is_complete is true (stored as 1)
typesbooleanintegerliterals
⚠
No Separate DATETIME Datatype
SQLite has no DATETIME storage class; dates and times are stored as TEXT, INTEGER, or REAL.
✗ Avoid
-- Inserting a date and expecting date-aware comparison to work automatically:
CREATE TABLE events(happened DATETIME);
INSERT INTO events VALUES('April 2, 2018'); -- non-ISO format
→ Stored as text, but date functions like date() and strftime() won't parse 'April 2, 2018' correctly
✓ Instead
-- Store dates in ISO-8601 format for reliable sorting and function support:
INSERT INTO events VALUES('2018-04-02 12:13:46');
SELECT * FROM events WHERE happened > '2018-01-01';
→ ISO-8601 text sorts and compares lexicographically, which matches chronological order
typesdatetimeiso8601unix-time
⚠
The Datatype Is Optional
SQLite allows table columns to be declared with no datatype at all.
✗ Avoid
-- Accidentally omitting the datatype:
CREATE TABLE t1(a, b, c, d); -- no types declared
→ Valid SQLite — all four columns have BLOB affinity and will accept any value of any type
✓ Instead
-- Declare explicit types to document intent and enable affinity-based coercion:
CREATE TABLE t1(a INTEGER, b TEXT, c REAL, d BLOB);
→ Columns carry type affinity; coercion and documentation intent are clear
typesschemaaffinityddl
⚠
Foreign Key Enforcement Is Off By Default
SQLite parses FOREIGN KEY constraints but does not enforce them unless explicitly enabled.
✗ Avoid
PRAGMA foreign_keys; -- returns 0 by default, FK not enforced
INSERT INTO orders(customer_id) VALUES(9999); -- no such customer, but no error
→ 0 — inserts with invalid foreign keys succeed silently
✓ Instead
PRAGMA foreign_keys = ON;
-- Now FK constraints are enforced for this connection
INSERT INTO orders(customer_id) VALUES(9999);
In SQLite, a non-INTEGER PRIMARY KEY column is allowed to contain NULL values due to a historical bug.
✗ Avoid
CREATE TABLE colors(name TEXT PRIMARY KEY);
INSERT INTO colors(name) VALUES(NULL); -- unexpectedly succeeds
→ The NULL row is inserted without error — multiple NULLs can coexist since NULLs are not equal to each other in UNIQUE checks
✓ Instead
CREATE TABLE colors(name TEXT NOT NULL PRIMARY KEY);
INSERT INTO colors(name) VALUES(NULL);
→ Error: NOT NULL constraint failed: colors.name
primary-keynullconstraintsbug-compatibility
⚠
Aggregate Queries Can Contain Non-Aggregate Result Columns Not In GROUP BY
SQLite allows output columns in aggregate queries that are not aggregate functions and not in the GROUP BY clause.
✗ Avoid
-- This returns undefined/arbitrary non-aggregate columns on most databases:
SELECT dept, name, max(salary) FROM employees GROUP BY dept;
→ On PostgreSQL: ERROR — column 'name' must appear in GROUP BY or aggregate function
✓ Instead
-- SQLite returns name from the row with the highest salary per dept:
SELECT dept, name, max(salary) FROM employees GROUP BY dept;
-- Or use a subquery for portable code:
→ In SQLite: name is taken from the row achieving max(salary) in each dept
aggregategroup-bynon-standardminmax
⚠
SQLite Does Not Do Full Unicode Case Folding By Default
The upper() and lower() functions only work on ASCII characters by default.
✗ Avoid
SELECT upper('café');
→ 'CAFé' — the ASCII letters are uppercased but 'é' is left unchanged
✓ Instead
-- Option 1: compile SQLite with ICU support
-- Option 2: handle non-ASCII case folding in application code
-- Option 3: use LIKE with the default case-insensitive ASCII matching for basic needs
SELECT * FROM words WHERE lower(word) = lower('Straße');
→ May not match 'STRASSE' — non-ASCII folding requires ICU or application-level handling
unicodecase-foldingupperlowericutext
⚠
Double-quoted String Literals Are Accepted
SQLite accepts double-quoted strings as string literals when they do not match any identifier, which can mask typos in column names.
✗ Avoid
-- Typo in column name — becomes a string literal, not an error:
SELECT "nmae" FROM users; -- 'nmae' is not a column, so it becomes a string constant
→ Returns the string 'nmae' for every row instead of an error
✓ Instead
-- Use single quotes for strings and double quotes only for identifiers:
SELECT name FROM users;
SELECT "name" FROM users; -- correct use of double-quotes (identifier)
SQLite allows SQL keywords to be used as table or column names without quoting in many contexts.
✗ Avoid
-- This creates a BEFORE trigger named 'AFTER', not an AFTER trigger:
CREATE TRIGGER AFTER INSERT ON t BEGIN
INSERT INTO log VALUES('triggered');
END;
→ The trigger is named 'AFTER' and fires BEFORE INSERT — the first AFTER token is parsed as the trigger name identifier
✓ Instead
-- Always quote keywords when used as identifiers to avoid ambiguity:
CREATE TRIGGER "after_insert_t" AFTER INSERT ON t BEGIN
INSERT INTO log VALUES('triggered');
END;
→ A clearly named AFTER INSERT trigger
keywordsidentifiersquotingparserddl
⚠
Dubious SQL Is Allowed Without Any Error Or Warning
SQLite historically follows Postel's Law and accepts malformed or ambiguous SQL silently.
✗ Avoid
-- SQL that is technically invalid but accepted by SQLite:
SELECT * FROM nonexistent_table WHERE; -- incomplete WHERE clause may still parse in some cases
→ SQLite may accept or silently ignore certain malformed constructs instead of raising an error
✓ Instead
-- Write standard SQL and validate against a strict engine (e.g. PostgreSQL) during development:
SELECT * FROM users WHERE active = 1;
→ Clean, portable SQL that works identically on SQLite and other engines
SQLite's AUTOINCREMENT keyword prevents rowid reuse but is not equivalent to MySQL's AUTO_INCREMENT.
✗ Avoid
-- Adding AUTOINCREMENT thinking it is required for auto-IDs (MySQL habit):
CREATE TABLE orders(id INTEGER PRIMARY KEY AUTOINCREMENT, total REAL);
→ Works, but AUTOINCREMENT is unnecessary overhead here — SQLite already auto-assigns rowids for INTEGER PRIMARY KEY without it
✓ Instead
-- INTEGER PRIMARY KEY alone is sufficient for auto-assignment:
CREATE TABLE orders(id INTEGER PRIMARY KEY, total REAL);
-- Only add AUTOINCREMENT if you must prevent rowid reuse after deletion
→ Rows get auto-incrementing IDs; deleted IDs may be reused (usually fine)
SQLite TEXT strings can contain NUL (0x00) characters, which can cause unexpected truncation in some contexts.
✗ Avoid
-- Storing a string with an embedded NUL:
INSERT INTO t(x) VALUES('hello' || char(0) || 'world');
SELECT quote(x) FROM t;
→ quote() returns 'hello' — truncated at the NUL character in the SQL literal representation
✓ Instead
-- Avoid NUL characters in text data; use BLOB if you need arbitrary byte sequences:
INSERT INTO t(x) VALUES(x'68656c6c6f00776f726c64'); -- stored as BLOB
SELECT hex(x) FROM t;
→ '68656C6C6F00776F726C64' — full byte sequence preserved in BLOB storage
nultextstringblobc-apiencoding
⚠
SQLite Distinguishes Between Integer And Text Literals
SQLite considers integer 1 and string '1' to be different values; other databases treat them as equal.
✗ Avoid
SELECT 1 = '1';
→ 0 — SQLite says integer 1 is not equal to text '1'
✓ Instead
-- Use explicit CAST to normalize types before comparison:
SELECT CAST(1 AS TEXT) = '1'; -- returns 1
SELECT 1 = CAST('1' AS INTEGER); -- returns 1
→ 1 — after explicit cast, the comparison succeeds
The biggest differences between SQLite and PostgreSQL are: flexible typing vs strict types, no separate server process, foreign keys off by default, and limited ALTER TABLE support. The Data Types page covers the type affinity system in detail.