Tired of managing endless migration files and losing track of your database changes? What if you could define your entire database structure in one place, a single schema file? And when you want to add a column, you can just simply add a line to that file. In this video, I'm going to explain how Zubase's declarative schema can simplify your database management, making your development process more efficient and airfree. [Music] So here's the problem that we're trying to tackle currently. If you use the superb CLI to manage your database migrations, your migrations directory would look something like this where for example in the first file we have the create table statements for a profiles table having the ID and username column. And later on let's say we said okay we want to add a is admin column. uh we generate another migration file and add a alter table statements to add this inadmin uh column and then sometime even later when we say okay we probably should have created an updated column uh and you know add all the triggers and functions to properly update the updated ad column. Now at this point uh your um schema for the profiles table is scattered across three different files and three different files might not be that bad but like in an actual production app you would have u even possibly have more uh files um that defines how a certain table might look and you know you may have um certain files that's actually deleting columns. So you may have a file that looks like it's adding column but if that particular column was later deleted you have no idea what that table looks like and that's where the database u declarative database schema comes in really handy. So instead of having these migrations directory, we will create a schemas directory and inside the schemas directory we can create a schema file. Now this schema file is going to contain what our database schema is going to look like. Like for example, we'll have a profiles table. And let me just delete this migrations directory for now. Now once we have this schemas.sql file, we can run superbase db diff um and give it the file flag and give it a um migration name something like um profiles or creating profiles or something. So we define the schema inside the schema.sql SQL file and then we run this command which generates the migration automatically for us. So we don't really have to think about running migration. We don't really have to think about um creating um proper migration files. It just is being taken care of automatically by superbase. Okay. So the migration has completed and we can open up our migration files and confirm that our table profiles is being created with additional stuff that automatically comes with superbase but we can kind of ignore that. We can actually kind of ignore the entire migration file cuz um everything will be generated automatically using uh this schema file. So when we want to add a column to the schema we just simply literally add it. So if we want to add is admin boolean and default false we literally add it in and we can run the super east db diff command again um and we can just name it name the migration something like admin or is admin or adding is admin if you want to be more precise but you get the point. We run the dbd command which will generate the migration file again. And there you go. We have a new migration file generated and it's adding the is admin column to the profiles table. So just like that we can kind of build on top of this file to basically grow our database. Whenever we want to add something we can just add it to this file. Let's also double check in the super dashboard. We can start a local super east instance by running super start and head to the dashboard table editor and there is a profiles table and we can confirm that there's a ID username and is admin column right there. Great. The best thing about SQLbased migration is that since it's just SQL a lot of the PCSUS features like views, functions or role security policies are already supported. We can add views the same way we manage tables. So we can just literally add them in the schema file. And that's really it. We can now run the Superbase DB diff command to generate the migrations. And just like that, our migration is generated to create a new view called admins that selects all the profiles where is admin is true. Now if we wanted to edit this view for example let's say we also wanted to get the username then we can just add it like so and generate a new gif view um username. There's a new migration file and this replaces the old view that existed. So this migration is no longer taking effect but instead we have this new view with selecting ID and username as this admin view. The same goes for functions. Let's say we had this get username function. All we have to do is again run the superbase db command with with a name of the migration that you want to um set it to. And just like that a new migration has been generated uh which sets the definition of that function. And let's say if you wanted to change the definition of this function um really we can just change anything and it'll detect it. But for example, for some, I don't know, wild case. So, we said we're just going to ignore the profile ID and we're always going to return the username of whoever is user ID number one just for a simple example. I couldn't really come up with a nice example, but yeah, let's say that was the case. Then we can just say uh function v2 and generate a new diff. And um Subaru CLI is able to detect that there was a change here and it'll generate a new migration uh for this to update the definition of the get username function which is really neat cuz managing functions can be a bit messy. You always have to make sure that you override the definition and um yeah um this is one of the places that really the declarative database schema shines that you know managing function definition is no longer chaos. So as you can see we have the updated uh definition of the get username function and we are always returning whoever user ID number one is which is I don't know what the use case is but like it's just example you get the point hopefully. Now if you use superbase you're probably wondering if this works with rollable security policies and yes it does. So let's create a simple policy where we are just going to expose the profiles table to the world basically. Uh let's generate a new migration file. So subdv diff roable security and just like that we have a migration file that has been generated that applies the ro roll roable security policy. If we wanted to edit the definition of this, for example, let's say we're just going to expose user ID number one to the world. So something like this and everybody else is going to be hidden. Then we can do that and um we can generate the migration file again and sub CLI is able to detect that there has been an update to this role of security policy and it'll generate the correct migration file to override the definition and the migration file has been generated. I want to point out that we have this warning here. It says the diff tool is not foolproof. you may need to manually rearrange the um or modify the generated migration. It's always important to verify that the migration file that I generated properly works. So you want to uh test it locally using the superbase DB reset command to verify that the migration works. And there are some known caveats here and there that the database schema is not able to automatically pick up the differences. And you can read the documentation for all the edge cases where the declarative datative schema fails. But for these use cases, it should be fine. It's things like um uh detailed permission changes or alter statements or statements that uh modify the data itself like insert, update or delete commands. These are not uh picked up by the declarative database schema. Uh so once in a while you might see a warning like this and you might want to just verify that it actually works which we probably should do right now. So why don't we do that? So I've stopped the subase um running locally. So let's run sub restart again and run subbase db reset just in case. And it reseted the database without any errors. Let's refresh the page just in case. And we have the admin view in the profiles table. And if we head over to authentications policy, we have this roll security policy that exposes user ID number one to the world. and nobody else. All right, that's great. Now, as your codebase grows, your schema probably grows as well. And once your schema gets too large for a single file, you can actually divide them into multiple smaller files inside the schemas directory. So, right now in the schema file, we have the definition for the profiles table and bunch of views and functions that that are related to the profiles table. Now, let's say I want to introduce something like a post table which holds um some kind of some kind of content. And we can just paste this table definition right here without any problem. And we can just generate the dbdiff and it all works just fine. But uh you know as we add more and more tables to it, this file is going to get larger and larger. And I've omitted the role of security policies and possible functions that comes with this post table. But we probably need something like that, right? So that's going to add more stuff to it. So the common design pattern once your schema file grows bigger than a certain point is to divide it into multiple files. So we can have like posts do uh SQL file to hold anything related to posts and we can rename this schema.sql file to profiles file because it's all the things that are related to uh profiles. So great, right? Uh we have multiple files. Let's run the superbase db diff command to generate the posts content. And you can see that it's able to detect that there are two files, the post.sql file and profiles.sql file inside the schemas directory. All right, great. It ran the migration and we have a new migration file for generating this um post table. That is awesome. Now let's say we want to expand this post table and have a user ID column or profile ID column that references the um ID column of the profiles table. So at this point uh this post table becomes a dependent of the profiles table. And if we run this dbdiff command again, it might succeed or it might not succeed depending on how Superbase interprets, you know, which schema file gets applied first. And if we look at the ter here, it looks like it's trying to parse the uh post table, a post.sql file first. So, it's probably going to fail. Uh but we don't really need to take chances. There is a way to specify what file comes first and we get we can actually do that in the config.toml file. So within config.totl file we can find the db.m migrations section and in here we can um define how in what order we want the migration to be applied. So, we want to have the schemas dot um /pro files.sql file to be applied first and then we want the rest of the uh files to be applied. So, we can just use a glob pattern to say schemas and star.sql file. So basically we want to apply the profiles.sql um schema first and then once we're done with that we can just proceed to the rest of the schemas. So let's generate another migration file. So um profile ID we can probably come up with a better name for that but let's see null file match subase schemas. What did I miss? What did I miss? Yes, properly spell it out and it should work just fine. And as you can see, it's looking at the profiles. SQL file first and then it's uh looking at everything else after that. And just like that, the dbd command ran without a issue. And we can confirm that there is a new profile ID column with a foreign key pointing to the uh profile tables ID column. Now if you want to get started using this declarative database schema with your existing Subaru project, you can simply run this command right here and it'll pull down the schema information of your database into a single file. And from there, you can organize the code a bit as you like, and you're good to