Starting from version 2.7.0, Makerkit uses the new Supabase Declarative Schema to manage its database schema - bringing quite a few benefits for our local development workflow.
In this post, we'll take a look at the benefits of using Supabase Declarative Schema and how we used it in Makerkit.
Benefits of using Supabase Declarative Schema
One of the biggest criticisms of Supabase is the lack of a good database schema management tool - forcing us to write migrations upon migrations as the database schema evolves.
As you can imagine, this can become a pain when you're working on a large project with a complex database schema - as it's really hard to understand the changes that are happening to the database schema and what's the current state.
While Declarative Schema is not anything new in the database world, it's now a built-in feature in Supabase, making it easier to manage your database schema.
How we used it in Makerkit
Makerkit has adopted the Declarative Schema feature in version 2.7.0 - let's see how we used it in the project.
The "schemas" folder in the project
Just like you would have a migrations folder in your project, you should now also have a schemas folder at apps/web/supabase/schemas. This folder will contain the schema definitions for your database and represents the latest state of the database schema.
If you check the Makerkit project, you'll see that we have the following schemas:
├─ /apps/web/supabase/schemas│ ├── 00-privileges.sql│ ├── 01-enums.sql│ ├── 02-config.sql│ ├── 03-accounts.sql│ ├── 04-roles.sql│ ├── 05-memberships.sql│ ├── 06-roles-permissions.sql│ ├── 07-invitations.sql│ ├── 08-billing-customers.sql│ ├── 09-subscriptions.sql│ └── 10-orders.sql│ └── 11-notifications.sql│ └── 12-one-time-tokens.sql│ └── 13-mfa.sql│ └── 14-super-admin.sql│ └── 15-account-views.sql│ └── 16-storage.sql│ └── 17-roles-seed.sqlThe way it's split and numbered is not mandatory, but it's a good way to organize your schema files. You can split them by features, by tables, or by any other logical grouping you may prefer.
The numbering is used to determine the order in which the schemas are applied, which is important when you have foreign key constraints.
From here on you can go ahead and add your own schemas (or modify existing ones) to the project, based on whichever logic you prefer.
Once you've added a new schema or made changes to an existing schema:
- First you need to make sure Supabase is stopped using the command
pnpm --filter web supabase:stop - Then, run the
supabase:db:diffcommand to apply the new schema to your local database:
pnpm --filter web supabase:db:diffThis command will print out the diff of the new schema against the current state of the database. If you're happy with the changes, you can apply generate a new migration file by running the following command:
pnpm --filter web supabase:db:diff -f <migration-name>This will generate a new migration file in the migrations folder. You can then apply the migration to your local database by running the following command:
pnpm --filter web supabase:db:resetThis will reset the database to the latest state of the migration files.
Make sure to check the generated migration file
The diffing tools are not perfect, and sometimes they might generate a migration file that is not what you expect. Ensure you've reviewed the Known Caveats section before using the generated migration file to make sure it's what you'd expect.
I have found better output using pgSchema, which you can use using a flag:
pnpm --filter web supabase:db:diff -f <migration-name> --use-pg-schemaThis will use the pgSchema tool to generate the migration file, which I have found to be more reliable.
Wrapping up
The new Supabase Declarative Schema feature is a great addition to the local development workflow, bringing more sanity to the migration madness we've been through for so long.
I hope this post has been helpful in showing you how to use Supabase Declarative Schema in your Makerkit project. If you have any questions, please reach out to me on Discord.
Happy coding!