SQLite gotchas

SQLite does not have a DATETIME column type

When searching for how to add a timestamp column to your tables, might come across StackOverflow answers that suggest using inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP.

Unfortunately, the only supported column types in SQLite are:

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

So why do so many results online suggest to use a non-existent column type DATETIME? Unlike other SQL databases, SQLite is “flexibly typed” by default and does not check column types. Not only does it let you insert text into integer columns, it will let you make up any column type you want. So the ‘incorrect’ suggestion of using a DATETIME column type does work in practice.

If you’d like SQLite to actually check your column types, you’ll need to use STRICT tables:

CREATE TABLE user_actions (
  id INTEGER PRIMARY KEY,
  action TEXT NOT NULL,
  user_id TEXT NOT NULL,
  inserted_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

If you’re using CURRENT_TIMESTAMP/datetime('now') (they’re equivalent, unless you pass a second argument to datetime specifying which timezone you’d like), then you’ll want a TEXT column type to store your timestamp.

You can’t specify column lengths

Specifying column lengths (e.g. TEXT(255) and BLOB(16)) doesn’t work - at least not in the way you’d expect. Specifying column length in a table will be accepted by SQLite, provided you don’t declare your table as STRICT. However, column lengths won’t be enforced.

If you do use strict tables, SQLite will let you know it doesn’t support this: Parse error: unknown datatype for foo.foo: "TEXT(255)".

SQLite allows NULL values in a PRIMARY KEY column

From the documentation:

Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column.

The WAL file can grow unboundedly (checkpoint starvation)

From the Write-Ahead logging documentation:

But it is possible to get SQLite into a state where the WAL file will grow without bound, causing excess disk space usage and slow query speeds […] A checkpoint is only able to run to completion, and reset the WAL file, if there are no other database connections using the WAL file […] […] if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound

Other gotchas

See the Quirks, Caveats, and Gotchas In SQLite documentation page for more gotchas.