Working with Drizzle ORM in Edge Functions

today we're looking at drizzle now we've heard from many of you that you like drizzle and since super base is at its foundation at its core just a fully fletched postest database you can use of course all the tools that are available for postes like for example drizzle now drizzle has a bunch of cool features uh and specifically what we're looking at today is also uh to use it in Edge functions so you can use it both in superbase Edge functions as well as for example verel uh Edge functions so that works out of the box and um you also get migrations which is quite useful so you don't have to write kind of the SQL yourself you can actually uh Define it in kind of a you know typescript Manner and then use uh drizzle kit to generate those um SQL migrations so let's have a look at that kind of how can we get get this set up and there's a good um quick start guide on the the drizzle kit so here I just have an empty uh project I called it edgy drizzle because we'll be using uh superbase Edge functions later to um retrieve the data for example and then we'll just um use npm here we'll just do npm in it um so in order to kind of do you know this locally we'll need some local dependen we'll just kind of De create a default here and then what we can do is we can just um copy these so we'll need um the drizzle orm we need postes and um as a development dependency we need the drizzle kit okay and then the way this works is basically we have um kind of a a schema do um TS file and then um we're using kind of PG core here from the drizzle umm to then create our table kind of our user table and Define this so rather than you know writing SQL ourselves we're kind of defining this in um sort of typescript here so let's do that let's create a new file call it schema. um TS and then we'll paste this in here uh and as you can see kind of we have the dependency is installed so um we get kind of our ID name what have you um and you know then in the background it will generate the the SQL for us so let's have a look at how this works so basically we need um a drizzle configuration file so that's just this here um drizzle config dots um so let's say new file ronig dots uh and then we'll just import um here from drizzle kit our config and so now we can basically say okay where is our schema so in our case the schema is just here in um the root and then we can define an out file and so this is where it actually becomes interesting because what we can do is um we can generate the SQL file and put it into the superbase migrations for example so what we can do is um when we say super base in it uh and it asks us if we want to generate vs code settings for Dino uh let's say yes because later we want to use our um super base Edge functions which use Doo um and then now we have here super base and if you you know if you're familiar with like super super base um migrations new uh sorry super base migrations uh new and we say in it so you can see that then here we have a migrations folder and we generate kind of a a SQL file in here so what we can do is actually we can point um drizzle config here to our superbase migrations um folder so superbase migrations so here super Bas migration so basically can say um drizzle kit you know take our schema and generate our um postgress um migrations in the superbase migrations folder and so what we can do then now is we can say okay so what they do is you know um create a script so in scripts we say generate script and specifically drizzle kid um because it's a development dependency so if we look in our um package.json here we have our def dependency is the the drizzle kit um and so we can say generate drizzle kit um generate PG and so now if we say um npm run generate uh we're basically taking the schema file and then uh drizzle kit is generating here um this migration for us and it's generating the the SEO create table if not exist um with kind of the correct um structures here and so now if we say super base start um we're spinning up our local superbase stack and um we're applying all the SQL migrations here in um our superbase migrations folder so um you know this is basically kind of an alternative approach here to um running the migration with you know uh drizzle so you can obviously do this as well where you can say Okay um you provide the connection string and then you run this through um using the mic rate function from um drizzle orm but since we kind of have this already you know sort of built in within super base we can just you know have the migrations in here and so if we run super base start we can look at our um local Studio here um and you can see we have our users table and uh name email password uh roll kind of all that so this is exactly the the schema that was um created Here and Now what we um could say if we're you know or to um create another table so like if we now update our schema and we say for example um we want another table and we'll call this um countries and then we'll just have an email uh no country doesn't have an email could have an email um but we'll just say okay uh we have an idea a name for our countries and this is um PG table countries and so now what we can say is um again npn run generate and so what you can see now is we have um a new migration so uh every time there's kind of a change and we run generate we get a new migration now this isn't Auto applied right because our stack is running locally so if we're running locally already what we can do is super base DB um reset now obviously that resets and runs kind of all the migrations again so if you had kind of data locally in your um superbase database that would be uh gone but then you have kind of all the the schemas applied here and so you can now see countryes um users yeah lovely and so now what we can do is maybe we can um here in our countries we can create a new country let's just do this quickly um so I'm here in Singapore uh save that so we have a new country maybe um another country um a lot of you are in the United States um I guess off America if we want to be well let's just say USA might be easier um okay we have a couple countries here and then um maybe do another one uh United Kingdom well now I'm spelling it out obviously have no convention here but okay so we have um three countries in here now and so now let's have a look at how we can use this with um super base now the the great thing is we can um you know here our our queries so what we can do is we can use this um portion here connection pooling so um in the last launch week we have uh launched supervisor now supervisor is a very powerful um here um postgress connection Pooler uh and so especi especially if you're working with something like um superbase Edge functions you know you want to use um a connection pool up or in general is if you're using kind of a serverless environment where you're spinning up you know kind of edge functions very quickly and basically spinning up a lot of connections to the database you want to make sure you have a connection Pooler so you don't overload um kind of the the database and so um using supervisor you know here we're basically using this um connection pooling so let's uh create a new function super base functions new and we'll call it drizzle and so now here we have a new um within super base we have our functions and now the great thing is because we've generated here our vs code settings you can see here we have the dino enable paths so we're basically enabling Dino for our super base functions and so now if we go here in here we can see okay we have um all of this set up correctly um and so one thing now we can actually import um dependencies directly from npm so this is this is actually really cool so we can say Okay um well let's copy just um actually let's just copy over the dependencies first so we'll say here our um dependencies um we need drizzle um as a dependency uh and then as well as our schema now if we're having functions um generally the the recommended approach would be um to have like a shared you know the schema we can share um kind of across different functions so have like a shared folder um and then we'll just for now we'll copy the schema so we need to have it kind of in the shared folder here um let's paste it in there and then um um we can here from our shared we can import our schema and um you can see here we have our countries and our users let's just get our um countries out of it and now another thing is we need our um import map so what we can do is within our functions we can create um a new file called import map do Json so this is a a similar approach here as our um uh package.json so we have a functions import map and what we can do is now we can um basically import kind of all the dependencies so similar here in our um package.json we have um these dependencies here right so what we can do is in our import map um we Define these um as Imports uh and so what we need to do is we need to tell um Dino so normally in Dino you give the entire um path to the dependency so for example if it's hosted on uh esm Dosh you would do like your https SL uh esm Dosh right but so now we also support um npm out of the box so we can say um within the npm direct give us um drizzle RM and then we'll pin kind of this exact um exact version here so same here we'll s npm um get the postest node postest um sorry as the version 3.3 here we are and so now we can use these named Imports in our functions so here we'll have um you know drizzle from um drizzle rm/ postgress JS okay and so now we have our um dependencies here so we can just use the the named Imports um and so what we can do now is we can just use our um connection pooling uh enabled um code snippet here and we will need to get the connection string so if you um do superbase status here you know remember we're running fully locally uh we actually have the database URL so we can use our um locally running postes so the nice thing is we can say um connection string uh equals doo. um n.g and we have the superbase specific um variables we have superbase underscore um DB URL um exported so we can you know we don't need to manage the secret so basically when we're running locally it's pointing to our local instance and then when we're deploying this to our project then um it's using our correct project kind of um database URL so let's say yep connection string here and then DB from countries and then we're saying all all countries and so the nice thing here now is we you know have kind of all countries fully typed so you know if we were to access kind of that then we get the ID and the name of the country so that's kind of a you know nice thing with Drizzle and then let's just return all um the countries here as application Json um yep and uh we're not using this so we can just give that an underscore so yeah let's see let's maybe run this function locally super base functions Surf and we can pass the no verify JWT uh flag and then we can just pull it up with um Local Host functions drizzle and there we have it okay we're getting all our countries uh Singapore is a United Kingdom um yep that's pretty much it and so now let's actually um deploy this um superbase functions deploy drizzle no verify JWT now the thing is we need to first um link this to a project so let's actually do that um quickly so we'll go data database. new we'll uh create a new project in my organization here we'll call it edgy drizzle so if you're generating a password Here uh make sure to copy it and save it for example in um a password manager somewhere because if you want to directly connect to database later you will need this um database password and then let's create a new project Okay so we've got our project spun up here so we can go to Project settings database so you can see for um you know serverless serverless connections so you know super base Edge functions or um verell Edge functions you want to use the connection pooling mode you can use kind of the default settings and then you just want to make sure that you use this URL here that has the puller do superbase um in it and then you know put in the the password that I mentioned earlier now if we're deploying this we can just use the um superbase DB URL which has the password already set in so now what we can do is we take our um project ID which is um this one here in the URL or you can go to General as well there's the the reference ID can copy that and we can say superbase link um project ref equals our project ID and then paste in your um database password Here and Now we can say superbase DB push and so we're connecting to our database and then we're applying the migrations that drizzle kit had generated here so now if we go to our table editor we can see um our user and our countries now obviously the the data that was locally in our database um is not being copied over so let's just um insert a row let's just insert Singapore just for now as an example great and so we have that in there now and so what we can do is super base functions uh we had it here already uh deploy trizzle no verified JWT and so now we're deploying this to our project uh we can open the link that the CLI gives us so here's our function we can copy this paste it over and boom there we are we have our connection to our real database um if you want me to prove that it's this one uh let's add another country save that and now if we go and refresh our function we can now see yeah super base Singapore so as you can see you can use the super based uh DB URL here and then you don't have to worry about managing the secret so these are set based on your project but otherwise if you're you know for example using something like versel Edge functions for example with um the T3 turbo stack um you can see here in the nf. example um this is kind of the the postgress URL that you want to strive for so um that is you know the Pooler dos superbas URL from your project uh and then uh currently to use it with the versel postest library you need to um put in this workaround you know super B Bas hyen pool. verel uh because they are checking kind of a specific uh format of the connection string so that's how you can get this to work um with versel Edge functions for example all right there we are this is just a little look at how you can use drizzle uh within super base and specifically um Edge functions with you know postest uh let us know if this was helpful for you and what you'd like to learn next and I'll see you next time [Music] bye-bye

Channel: Supabase

Views: 6,083

Keywords:

Id: _OqMRtg4Gy8

Length: 21min 40sec (1300 seconds)

Published: Wed Jan 03 2024