04 · A database without migrations

The database is SQLite running at the edge. It holds five kinds of data: view counters, ratings, comments, the event log and — recently — reader accounts with their sessions.

The first tables were created by hand, pasting statements into a console in a dashboard. That worked for exactly as long as there was one instance. The moment a second one became a prospect, the approach stopped being acceptable: you cannot sell a tool whose setup begins with pasting SQL.

The solution is lazy schema creation. On the first write the engine checks whether the tables exist and creates the missing ones. The statements are written so that running them repeatedly breaks nothing. There is no migration system, no schema version and no command to remember. You connect an empty database and it furnishes itself.

There is one trap in this, which we only discovered while extending the schema with accounts. Every statement ran in one loop, inside one error handler. It was enough for one of them to fail — because an index already existed in a slightly different form, say — and the loop broke and silently skipped everything below it. The database looked ready while being half ready.

Now every statement has its own error handling. It is one of those fixes that take a sentence to describe and separate a tool that works from a tool that works for everybody.