[Migrations] Postgres functions

[matthewmorek]

Does Orchid support writing migrations for custom Postgres functions? I do have a few I'd like to run as part of using Supabase (example below), but I can see no docs mentioning functions?

create policy "Enable update for authenticated users only"
on "public"."user_profile"
as permissive
for update
to authenticated
using ((auth.uid() = account_id))
with check ((auth.uid() = account_id));

How can I accomplish this using OrchidORM, or do I have to Supabase to run migrations for this functionality in addition to OrchidORM? I'd honestly prefer to have it all in a single place, especially when running on CI (deployment).

[romeerez]

You can execute any SQL in migrations:

import { change } from '../dbScript';

change(async (db, up) => {
  if (up) {
    await db.sql`
      CREATE SOMETHING...
    `;
  } else {
    await db.sql`
      DROP SOMETHING...
    `;
  }
});

There are no special functions to create a postgres policy, but I think writing SQL manually is fine for this purpose.

[matthewmorek]

OrchidORM is so comprehensive that I keep forgetting it's still okay to write plain SQL! Thanks for the reminder! 🙌