Today we’re releasing declarative schemas to simplify managing and maintaining complex database schemas. With declarative schemas, you can define your database structure in a clear, centralized, and version-controlled manner.
What are declarative schemas?
Declarative schemas store the final desired state of the database in .sql files that can be saved and versioned alongside a project. For example, here is the declarative schema for a classic products table:
_10create table "products" (_10 id bigint generated by default as identity,_10 name text not null,_10 category text,_10 price numeric(10, 2) not null,_10 created_at timestamptz default now()_10);_10_10alter table "products"_10enable row level security;
Declarative schemas offer numerous benefits over making changes to your database schema directly:
- Single pane of glass. Maintain your entire database schema in one place, reducing redundancy and potential errors.
- Versioned migrations. Automatically generate migration files, ensuring consistent schema updated across environments. Store your declarative schema files alongside your project files in your version control system.
- Concise code reviews. Easily review changes to tables, views, and functions without manually repeating complex migration scripts.
Declarative schemas vs migrations
It's best practice to use Migrations to track and apply changes to databases. Every time you make a change, you create a new file with all the changes, keeping changes versioned and reproducible.
However, as the complexity of a database schemas grows, it becomes increasingly difficult to develop using versioned migrations as there isn't a single place to see the entire database schema.
For example, at Supabase we have a complex and frequently-updated projects table. Here's partially what it looks like with RLS enabled:
_32create table private.projects (_32 id bigint not null,_32 name text not null,_32 organization_id bigint not null,_32 inserted_at timestamp not null,_32 updated_at timestamp not null_32);_32_32alter table private.projects_32enable row level security;_32_32create policy projects_insert_32 on private.projects_32 for insert_32 to authenticated_32with check auth.can_write(project_id);_32_32create policy projects_select_32 on private.projects_32 for select_32 to authenticated_32using auth.can_read(project_id);_32_32-- Users can only view the projects that they have access to_32create view public.projects as select_32 projects.id,_32 projects.name,_32 projects.organization_id,_32 projects.inserted_at,_32 projects.updated_at_32from private.projects_32where auth.can_read(projects.id);
The projects table is created in a private schema, with a public view exposed for reads. Attribute-based access control (ABAC) is implemented on top of RLS policies to ensure queries only return projects that the user has access to.
Since Postgres views are not updatable by default, we have defined trigger functions to cascade writes to the underlying table when a Supabase user creates a new project. This makes development easier because the projects view can be inserted with regular PostgREST calls while invoking the appropriate RLS policies on the underlying table.
_24-- Triggers to update views from PostgREST: select('projects').insert({ ... })_24create function public.public_projects_on_insert() returns trigger_24as $$_24begin_24 insert into private.projects(_24 name,_24 organization_id,_24 inserted_at,_24 updated_at_24 ) values (_24 NEW.name,_24 NEW.organization_id,_24 coalesce(NEW.inserted_at, now()),_24 coalesce(NEW.updated_at, now())_24 ) returning * into NEW;_24 return NEW;_24end_24$$ language plpgsql;_24_24create trigger public_projects_on_insert_24 instead of insert_24 on public.projects_24 for each row_24execute function public.public_projects_on_insert();
This complexity slows down development velocity, as changes to the table might break other views or functions. Back in early 2022, a simple change to add a new column involved the following steps.
- Find the latest schema for
projectstable in our migration files or by querying our database. - Write the
alter tablestatement in a new migration file. - Copy and update the
projectsview definition to include the new column. - Copy and update the trigger function definition to include the new column.
- Add new pgTAP tests and verify that existing tests pass.
- Submit the new migration file for review, which would be at least a few hundred lines.
This process is tedious and it's frustrating to have multiple engineers working on the projects table concurrently. Merging PRs would result in a merge conflict that must be resolved by repeating steps 1-5.
Using declarative schemas in production
Adopting declarative schemas gave our engineers a single pane of glass when updating database schemas. Instead of manually duplicating affected postgres entities in a migration file, we only need to change the schema definition in one place.
We then use a schema diff tool, like migra, to figure out the necessary updates to views and functions when generating the migration file.
For example, adding a new metadata column to the projects table now becomes a single line diff.
_10--- a/supabase/schemas/projects.sql_10+++ b/supabase/schemas/projects.sql_10@@ -2,6 +2,7 @@ create table private.projects (_10 id bigint not null,_10 name text not null,_10 organization_id bigint not null,_10+ metadata jsonb,_10 inserted_at timestamp not null,_10 updated_at timestamp not null_10 );
The same process also applies to views, database functions, RLS policies, role grants, custom types, and constraints. While manual reviews are still required on the generated migration file, it has cut down our development from hours to minutes. It's also much easier to rebase on merge conflicts introduced by other PRs.
Get started with declarative schemas
Declarative schemas are available today on Supabase.
- Read the documentation to learn how to manage your database schemas in one place and generate versioned migrations.
- Sign up for Supabase and get started today.
We added the same set of tools that we used internally for the last 2 years to Supabase CLI. Whether you are just getting started with migrations or already fed up with managing hundreds of migration files, give declarative schemas a try as it will likely simplify your development process.
Check out our blog post on Postgres Language Server for better tooling and IDE integration when developing with declarative schemas.