SQLite gotchas

TL;DR

Use STRICT tables.

Note: I highly recommend reading Sylvain Kerkour’s Optimizing SQLite for servers post if you’d like a more exhaustive list of SQLite gotchas and tips for using it in production.

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)".