How to add custom SQL queries during or after migration in drizzle schema?
Hi all. I would like to execute some SQL queries during (or after) migration. Is there a way how I can do it without calling SQL queries each time at application startup? In my use-case I am trying to make fixed partitions to improve performance, but that way can also be used to create custom and more complex indexes or event triggers.
6 Replies
Could you include these queries in their own migration?
You can create empty custom migration where you write your own sql.
drizzle-kit generate:pg --customHi Raphaël, thanks for your response.
Hmm, i think this solution can do the trick, but is there really no any other way to do it inside schema file?
It depends of what do you mean by complex index but you can define indexes on the schema (they will be run once, with their migration)
For things I can't do with drizzle (creating RLS for Supabase, with custom pg functions), I do that in a migration.
Sometimes I write SQL scripts outside of drizzle or a migration.
I put them in a folder and named them fix a prefix like
1_run-xxx.sql.
I also have node scripts (using drizzle) that I run to generate things that I don't have a ui yet 😂for example, index with sorting one field in ascending order, and other in descending (aka
create index if not exists "idx" on "tablename" ("field1" ASC, "field2" DESC))
or with where clause in it (aka create index if not exists "idx" on "tablename" ("field1" ASC, "field2" DESC) where "field3"='value')
But I guess it is fine to create separate migration file manually for that kind of requirements 🙂
Thanks again!