Drizzle ORM and statement-breakpoint

That was a long and irritating way to not look at the documentation (or rather overlook the relevant part), only to have it bite me in the backside down the road.

TLDR: Drizzle ORM needs --> statement-breakpoint in certain places in your migrations if you're working with MySQL/MariaDB or otherwise you will have weird syntax errors, when applying them through the library itself.

For a little pet project I decided to take a look at Drizzle ORM as my DB layer. I have opinions. Not all of them good. The library fundamentally seems to disagree with the way my brain thinks about ORMs, especially when it comes to abstracting different DBMS. But that is beside the point for the issue at hand.

The project is a small-ish SvelteKit application to collect 360° reviews of people (well, technically of everything, but I use it at work to collect feedback about colleagues). It allows you to define a set of skills in a survey and send out a bunch of links to different people to provide feedback. It then collects all the data and shows it in a nice radar chart to analyze at your leisure. Just a nice little app to get up to speed with the various bits and pieces and extend my fullstack knowledge a bit.

I've decided on Drizzle a while ago to give it a spin and use it to access a MySQL/MariaDB database. Technically I'd like the app to be DBMS-independent, but I haven't been able to figure this one out yet.

The DB schema's source of truth is the Typescript codebase, so I use Drizzle to generate the necessary migration files whenever I decide to add a feature, that requires a schema change. A few weeks ago I wanted to add survey sharing, so that one could create a default survey and share it with multiple colleagues to use it as a kind of template for their own surveys. To generalize it a bit more, I implemented a permissions table, that allows multiple access entries per survey.

Access management before that was simple: the survey table had an owner column and the DB-query would only return surveys for which your user was the owner. With the new system, the access is decided based on a new table, that contains the necessary permission entries for a survey. This meant overhauling a lot of the access system, but more crucially, creating the necessary "owner" permissions for existing surveys. I let Drizzle create the migration file and added the necessary changes on to it

...
CREATE INDEX `survey_index` ON `survey_permissions_table` (`surveyId`);

INSERT INTO `survey_permissions_table` (`surveyId`, `user`, `access`) SELECT `id`,`owner`,255 FROM `surveys_table`;

This inserts a row with permission 255 (which is the value for "owner") into the new permissions table for each existing survey. At work I handed this migration file off to the DBA colleague, they applied it, everything worked fine. Happy days.

A bit later I wanted to add automated tests, so I wanted to set up a new, empty database, which failed with the following error message:

Since the thing had worked perfectly with the prefilled DB at work and also in my local tests when applying it to an existing database, I naturally got stuck thinking that this somehow failed because - for a new DB - the source table would be empty. Cue a few weeks (on and off. It is a side project after all) of frustrated research and different attempts to figure out how to NOT execute the query if the source is empty and I was nearly ready to give up, just assume, that nobody would have a DB with the old schema anyway and move on. In a last-ditch attempt to figure out, whether the CLI MariaDB client might provide a better error message (and because it had worked for the existing DB), I copied the whole migration into the CLI prompt and wouldn't you know it? It worked flawlessly! What?

After a lot more fiddling and angry confusion, I figured, that those weird SQL comments, that Drizzle inserted in the migration files, must have something to do with it:

--> statement-breakpoint

I hadn't bothered figuring out, what exactly those were. They are just comments after all, so how would they change the meaning of the migration?

Turns out: it's documented (well, mentioned in a sentence in the config docs). These are breakpoints, that Drizzle uses to split the migration into several parts in certain DBMS (MySQL/MariaDB included), because those don't support certain combinations of DDL statements in a single transaction.

So, the migration actually should be:

...
CREATE INDEX `survey_index` ON `survey_permissions_table` (`surveyId`);

--> statement-breakpoint
INSERT INTO `survey_permissions_table` (`surveyId`, `user`, `access`) SELECT `id`,`owner`,255 FROM `surveys_table`;

And that, kids, is how I learned, that comments are sometimes not just comments… (well, OK, I knew that before, but it's been hammered home again).

P.S.: I even did try the modern thing and asked an LLM, why my statement was failing the way it did, only to watch it helplessly claim one thing or the other, instead of saying "I don't know!" (something, that I think is technically impossible for them to do, but that's another story).