Node Sequelize tutorial with Postgres | Sequelize migrations and seed data

now what is going on guys today we're going to take a look at sqlize and how to do migrations and see data with postgres and i think they have like uh like sqlize overall has a pretty good documentation i just found it a little bit overwhelming when i started taking a look at this and that's why today i want to talk about like migrations and see data and like how to get started with this library so let's see yeah so to get started with this we kind of need some example schema that we want to build and i decided to just use the one we also used for the objection tutorial so to say okay let's try to model a small part of youtube so there are users and every user can have at maximum one channel so that means either you have zero channels or you have exactly one channel you cannot have multiple channels now the thing is obviously there are youtubers who have multiple channels but then they have multiple users so with user i mean you have a registered user user with a unique email address right okay and yeah so a user can have zero or one channels and every channel can have an arbitrary amount of videos and the thing is you can also sign up for youtube without actually having a channel right so you need to explicitly create so this is what we want to model and it's the same thing that we also did in the objection.js tutorial and i did it because i want to show you the differences um i think it's just nice if we model the same schema and then you can figure out like what works for you so to sum this up you have a user table and this user has like an id it has a couple of other values and it can have zero or one channels but every channel belongs to exactly one user so you cannot have one channel belonging to multiple users and then every channel can have an arbitrary amount of videos that's why i put n here and every video belongs to exactly one channel yeah and this is basically the thing that we want to model so i would say let's just get started um i'm just going to pull open my terminal and i'm going to make a new directory and i'm going to say see qlis tutorial i'm going to cd into this directory and i'm going to say npm init yes to create a package.json file okay that should be it now i have this and we can directly go to documentation and you can see on getting started how to set everything up so we need sqlize and then we need the respective database driver so i'm going to use postgres you can use any other database listed here even cqlite then you don't have to set everything up manually i will also include the code or a link to the repo in this in the description so you can just have a look at the repo and there i will also put instructions on how to set up postgres if you're not sure how to do it okay so i'm just going to say i want to copy this and i'm just going to say npm install sqlize and then we also need these two over here so i'm going to install them as well okay and while this is installing um let's take a look at what sections they have in the documentation so they have a lot of different things like core concepts and querying and all that kind of stuff but before we get to all of this it's important to understand that to query something we first need a schema and to get the schema we need to work with migrations now there is some like a section that talks about syncing and all these kind of things like don't use that like directly go for migrations migrations are like one of the most important features of when it comes to these tools because your database schema is going to continually evolve right so if you scroll down to this um like to other topics i think it's where is it uh it's over here yeah so just click on that and then it will explain how to perform like migrations yeah let's look at this file so you can or let's look at this chapter here and they similar to next.js and to objection which is based on next.js they have a dedicated cli for that okay and this is what we want to install so take this thing uh go over here and install that as well and this cli is going to allow you to create migrations and also to create seed data so let's install this and then what we also want is we want an empty project okay if you want an empty project just execute this command that's the next thing we're going to do and the reason is that to have like or to make use of this cli it expects a certain structure right so you want to have migrations and you want to have c or they call it seeders so files that contain example data essentially and you can use the cli to kind of auto-generate all of this which is kind of nice if we go over here and we just copy this command from the documentation and execute it you can see that it creates like a whole bunch of directories so for one it creates like a configuration uh directory which yeah contains a json file where you need to put in the credentials for your database which is kind of a little bit weird because typically you don't want to hard code your credentials anywhere but for development and for this tutorial we're just going to do it for production obviously you you don't want to have like your database password in any file in any git repository right you always want to use uh environment variables but anyway like this is fine i think we're not going to make use of production and test anyway uh we're just going to focus on development here okay and then we have migrations so this is where we're going to specify the initial database schema models and they have like this index.js file which does a little bit of magic so basically it's going to iterate or it's going to look at all the contents of the folder and it's going to register the models for it so you don't need to touch this i mean you can take a look at if you want like you can see it's kind of looking at the directory and then it's stripping away like the dot js prefix and then it's actually with this statement wiring up the models that we haven't created yet and the other thing is seeders so basically files that where you can put example data into so as you can see like there's quite a bunch of folders it's probably more than with objection or with next but that is totally fine okay cool so that's it let's go to our to the documentation and let's see what we need to do so they tell us okay we first need to set up this configuration and i have a postgres instance running and this is pg admin and if you look at like the databases here on the left you can see i have a database called sqlize tutorial and currently it does not have any tables so it's like empty and this is the database that i'm going to use in case you also use cql make sure that you have some empty database if you don't have one just click right click on databases and then on create and then on database and by the way this tool is called pg admin so it's like a free and open source tool so you need to install postgres and then you it's just a ui for managing your postgres okay cool so let's just do that let's go back to our file and let's first take care of the database configuration so my username is this your username might be different so when you install postgres on your system per default is going to create a user with the same name as you are logged into your operating system okay and if you're in a unix-based system you can just run who am i to find out like what username you have okay and this is my username for default it created a user and that's why like i'm just going to put this here uh per default the password is also null so no password and the database is secures underscore tutorial so i always tend to use underscore you should use underscores in databases because some databases are case sensitive others are not host is localhost yeah you can leave it like that and the dialect is in my case postgres so obviously if you have a different database you need to you know put the different dialect if you're using mysql you need to put mysql and for the sake of completeness i'm just going to copy and paste this in these three things even though i know i'm not going to use the production um maybe i'm going to use the test one though but for production i always want to have like some antivirus but never hard code like the password or usernames that's a big security vulnerability okay so here we go we are done with filling up the uh the configuration and now you can read like all of this and actually the main point or the main idea now is to start creating your models and your migrations they have like some instruction on how to like running run migrations how to create them basically you can auto generate models and what is what it's going to do is um it's going to yeah create like some files for you automatically so you don't have to do it so in our case we can just take this uh copy and paste this and we're just going to say okay what do we want say we want to have a user and user has an email we don't care about last name we also don't care about first name so let's just leave it like that and that is fine and let's just execute this bam and now you see that all of a sudden you have a where is it here uh you have a new file in here and this is called user over here and this is like some specific or some specific code that allows you to so every model basically corresponds to one database table and it basically already created like the javascript class for you and it even created like a migration for you so it automatically created like this file here and similar to next.js you have up and down so up means apply the migration so create a specific table and down means undo the current migration and this is actually like very similar comparable to objection only the syntax is slightly different right so you can see okay there's like an id which is fine we're going to leave it as an integer not as uuid there is like an email there's a created ad and there's an updated ad so one more note here um sqlize all automatically takes care of created and updated at so for default it adds these things automatically to every model because it's just useful thing to have but it can create a couple of issues i just wanted to say that upfront because we will need to do a few fixes there cool so now that we have this let's think about what we actually want to do um i want the the id thing here over there i think it's fine right okay uh here in the email what i want is i want to say i don't allow null why because i always require that the email is there and i also want to make this thing unique simply because i want i don't want two users with the same email right otherwise i don't know what to do okay and that's it pretty much and the thing here is that the names here are the name are going to be the names that sqlize is going to generate in the database for us so if we leave it like this it's going to actually create a column that is named exactly like this and as you can see this is like camel case which is kind of not the best thing right your database columns and names should be named with underscore that means in your migrations you should make sure that whatever tables you create that you use underscores at least that's what i do some people don't do it but pretty much everyone is using underscores in sqlize they kind of support it sort of but they just say okay with javascript you have you use camel case per default so that's why we also use that for the database but i'm not a big fan of it honestly so that's why i'm just going to do it like that yeah and another thing i want to do is i maybe want to add like uh maybe a name right okay let's just say type is equalize dot string so as you can see there's like different type definitions and actually what you need to do is you just need to add uh like the columns that you want to have right so now we have id name email created and updated so i think that is fine um yeah let's see what else did we want if we go back to our browser then we said okay we will have users and we have channels and we have videos so we are going to need like two more tables and that's why let's we can just go back over here we can maybe make this lowercase and also make it singular by the way this is also a huge discussion should you make your table name like singular or plural i always go with singular because it creates less problems but there's like very different opinions about that but i'm just going to make it singular okay so this is going to be the model name and uh yeah these are going to be the columns and what i'm going to do is i'm just going to take this oops it's hard to copy this here okay i'm just going to take this and make sure to also copy the weight and i'm just going to take this and i'm going to go a little bit in here and i'm going to paste this in here right because for first migration we also want to have a channel table and this one is pretty similar so a channel will also have an id that is fine it's going to have a name because every channel has a name maybe we should make this allow null oops allow null false and not like in cq the columns are nullable per default so if you don't specify allow now it can be not unless it's a primary key obviously yeah so we don't need like the email um we don't need that so let's get rid of this and i think that's it for now we will add the foreign keys later okay and the next thing i'm going to do i'm going to i think i still have it copied going to go in here and i'm going to paste it again because now i want to create the video column the video table so which is basically this one over here yeah and same game so i'm just going to leave the id as an integer again i already mentioned you probably want to have your ids as usual ids in general because that has a couple of advantages because you can you have the id before you push the value to the database this is very important if you have a microservice based system where different services are acting on a new event but that's like that's like details so for the tutorial we're going to leave it as integer because uuids are just hard to to kind of see or it would make it more complicated okay we have video what do we need we want to have a title i would say right and the title should be allow null false oh and i think actually we should um put this in here as well right yeah forgot that okay um a title and yeah we don't need the email right cool so these are the basic columns technically we could add more columns but i wanted to add one more detail which is interesting database design question so if we think about this scenario right every user can have one channel or can have zero or one channel and the question is always where do you put the foreign key right so technically we have two options we could make something like in the user table we could add a column which says channel id and then in here we're going to add the id so for example here first user has null because he doesn't have a channel and the second user has another id and then we can leave the channel table as it is that's the first option and then there's the second option which is we're just going to put the like the user id on the channel table right so we have to decide on which table do we put our id the foreign key do we put in user or do we put it on channel and actually i want to give an update on that because i think in the last objection.js videos we did different differently i think the best way to put it here is on the channel why because we know for sure that every channel must have a user you know so with this setup you might have like a lot of nulls in this column right because there's a lot of people who just consume on youtube and then you always have this null here in the column but you know for sure that a channel cannot exist without the user so if you ever have the situation that one item is kind of like mandatory so to say and the other one is optional then you always put the primary key of the mandatory item in our case user id inside of the channel table because like so you avoid having a lot of nulls in this column and you also avoid potential issues so let's just say in here you want to delete your channel right you're done with youtube you delete your channel and then in order to delete this you first need to set the channel id here to null and then you can delete like the entry in here right because if you don't set this to null then the database will say oh wait hold on a second like you're trying to delete a record that is uh that has a reference or foreign key in another table i'm not going to allow this which is like good behavior right you don't have that problem over here so if one user says okay i want to delete my channel you can just delete that one row and it's gone totally fine yeah so this is basically the the idea and that's why it's better to put the user id like in that case inside of the channel table very good interview question by the way also these are like the details that people ask sometimes okay and that's why our channel is going to have an additional table which is going to be like an addition column which is going to reference our user so let's go over here let's go to our channel and the way you can do this is you define like the name of the foreign key so you say channel id right because it references the primary key of the channel and you just say okay the type is like an integer because remember the um oh no that's wrong actually oh no it's not channel id it's user id right user id something like that yeah okay and it's user id and it references the user table right and you can see here it's an integer so our foreign key also needs to be an integer obviously okay and then you can say allow null false and what you now need to do in order to make this like a foreign key you can say references and then you put in the model name which is user and the key which is like the id oops and bear in mind that these things uh like they need to be exactly the same right so user here is lowercase user here is lowercase and the user that we have over here is lowercase okay so let's just do it like this right ah maybe let's make it okay actually what i just realized ah let's go back over here yeah so basically you need to make sure that you need to make sure that these match right so this basically references this table so it needs to be the same or maybe not depending on what database you use that's why you always want to have lower case and snake case to avoid that problem and the key here is like the column so in that case it's the id yeah and that's it we can take this and what we can do is we can just copy this and do the same here so we're just going to say instead of user id we have channel id and it's going to reference the channel and it's also going to reference the id column and important thing here allow now is false so that means we do not allow a video to not like a video must be released by a certain channel right there's no way that a video exists out of like just it's just created right someone needs to upload it that's why this always needs to be um needs to be non-null yeah so that's it pretty much um i think yeah i think we are good on that i'm just thinking like did we forget anything oh yeah um one last thing obviously now we have been talking about how to create this migration we also need to to talk about how to undo this so you can just or like in here it basically says query interface drop table users the thing is that you know now that our table has like foreign keys the easiest way to undo all of this would be to say query interface dot drop all tables okay so now drop all tables and basically the only reason we can do that is because this is the first migration and it's also kind of nice right you kind of see here the date and um i think i'm going to rename this because this create user is annoying i'm going to name this uh init okay and let's see maybe i can i'm just going to copy this and i'm going to make a script out of it simply for documenting how it works so i'm going to name this make i don't know model right so in case you're looking at the code and you don't know exactly what you need to run yeah maybe like this what you need to run then like you can just copy and paste this command and then you will be fine cool yeah and i think what we can now do is let's try to run this okay and the way you can run this is if you scroll down here it says npx sequel icli database migrate so let's also copy this and while we're at it let's make another script so i'm just going to call this migrate and i'm going to paste this in here okay cool and i just want to show or to make sure that we don't have any tables per accident by accident so let's scroll down here and you can see in tables yeah it's empty so let's try to run this okay so i am going to cancel this and i'm going to run npm run migrate let's see if it works bam nice so you can see like our migration ran it even says like how long it took right 0.03 seconds that's pretty good and if we now go to our tables and we refresh refresh then you can see whoa cool all of a sudden we got four tables even now you might ask yeah but why do we actually have four we only right we only specified three so why do we have four now well the reason is uh that the secularized meta contains some information which migrations have been run on ran run or not and uh yeah but i just want to show you you can see here the created ad has like an underscore here yeah and you can see okay the string per default is a varchar with a maximum of 255 characters if you want more like you can allow more for now i would say it's fine let's take a brief look at um yeah maybe this one yeah so you can see all the tables or all the columns that we wanted are there that's pretty good last one is like video yeah nice video also works right we have the title and this is really important so once you figure out like how to do this migrations you can put it inside of your workflow right so before you start your application before you run node index.js and before your server starts up you would basically execute the command which is executed and say hey if there's any new migrations please execute all migrations and this is really handy right because you have a way of going forward and you have a way of going backward yeah and speaking of backward i think yeah here they have a command to actually undo all of this and this is what i'm also going to copy simply if you have a problem you can just execute this and then it's going to remove everything okay i can actually show that so at the moment we have these things so i'm going to run npm run and renamed it down bam so you see it kind of undid everything and now theoretically speaking if we did everything right there shouldn't be any table anymore so let me refresh this yeah so the only thing you see okay you got this equalize metadata and in here like there's nothing so it says i did not run any migration right we did we ran one migration the first migration we had and the only one and then we undid it and now we don't have any migration and if you want to run again you can just uh like run npm run migrate oops and nice now your tables should be there again so that's pretty handy also pretty nice for development right so now they're here very good so i think this is the most important thing uh another thing or something else let's talk about let's talk about seed data so if you're developing you want to have some example data and it's annoying to you know go in here and write insert into table whatever and that's why they provide these seeders so basically you can insert some example data inside of your database this is what we're going to talk about next so as you can see we're not talking about like this how to query like user or how to create this model because you know i trust you can go to you can go to the documentation over here oh wow it's very slow you can go over here and i trust you can copy paste this right but we're actually talking about what you what you need like from all of this over here to to kind of get started and the most important part is migrations okay so if we go down uh you can see that you we can actually create seed data and uh yeah let's copy this so this is the command we actually have to execute and uh yeah i'm just going to take this and make a new script simply to document it so i'm going to say oops make seed then i'm going to paste this and here it says okay it's going to generate like a seed and the name is going to be demo user okay let's just execute this and see what happens so i'm going to run npm run make seed okay so let's see what it did in the seeders directory you can now see okay it has the current timestamp um so at the moment it's beginning of february 2021 and it added like this demo user kind of thing and now you have pretty much the same thing like you have in next.js or in objection which is based on next.js you have this up and you have this down so you can see the pattern is always the same for migrations you always have this up like making the create creating the tables you have down which is like undoing everything and then for seed data it's the same thing okay um yeah let's go inside of here and what we want to do is we i just want to add a couple of uh things and i'm not going to i'm not going to do or to explain a lot here simply because it's boring in my opinion i'm just going to explain uh like a couple of things so i have like the code already here so i'm just going to copy and paste it and by the way um if you are or if you also want to copy and paste it like the code or the repo is in the description down below so just check this out so what i'm basically just doing here is i'm saying okay i want to insert multiple users inside of our user database and i'm just going to give it ids right so in this case it's three users with id 1 id 2 id 3 and it's email user 1 add test user 2 add test and so on and they created that and updated that and this is like a little caveat here so remember this created at an updated ad um this is not like it's not nullable and that's why we need to put a value in here and what's important to note is that sqlize does not do this created at an updated ad with sql triggers because otherwise we could just leave it out you know and the database would take care of it but this is actually done like as part of the secularized npm package and since we are talking about seeds at the moment so since we are not using the uh like the sqlize package to insert data we actually need to specify the created at and the updated at date so that's why you have this over here okay because you might wonder well but why do i need to specify this can't this thing just do it automatically yeah and yeah basically the idea here is that you say bulk insert you say that you name the table and uh here at the bottom you have like options so in my or in our example we just don't need them so that's why it's empty yeah and this is interesting like this so this bulk insert return like returns a promise and we're just avoiding it and for this we don't care about exceptions because the seed data is just uh well it's just test data so if it throws it's fine you know it's just for development okay and yeah by the way the order matters this is important right because remember we did or we said that we are going to go with the second solution and that is we're going to have put the user id inside of the channel table so what would not have been possible would be to like insert two channels first and then afterwards the user because we need to reference like in this column an existing user right so also depending on where you put the foreign key column it determines in what order records need to be inserted like both in your production environment or like in your server but also for your seed data just that you know so we could not have started with channel we need to start with user and that's why we're also using this await here so we're waiting for our three users to be inserted and then in the next step what we're just doing is wait i just need to copy and paste this from my finished code already why is this so difficult okay my mouse going here a little bit crazy i just need to copy and paste this yeah here it is and then afterwards and this is the interesting thing you create two channels so we're just going to pretend user one and user two have a channel and user three does not so it's important that the users already exist before we insert the channel records and the same is actually true for the videos so basically there's only one valid order in which you can insert data because in order to insert a channel you need a user so you need to insert the user first then you can insert the channel and in order to insert a video you need a channel right so it's like transitive dependency okay so i'm just going to take the video thing and i'm going to insert this one as well here so nothing special over here we just have three videos and the first two videos are by channel one so user one and the third video is by the second channel so by user two and yeah i just put like these names here so it's like a little bit easier to understand so this is pretty much our seed data file now you could insert like more and you could uh you know you could do more here but i think this is fine for now okay um and this is how you get like seed data okay that's nice so let's go back to our documentation and here you can see that okay you can generate it like this and you can run your seats like this so i'm going to copy this i'm going to go back and again i'm going to make a script oh here yeah maybe down here i'm going to say uh not make seed but actually seed so to just say hey just execute all seeds we we have and let's just try this out shall we so if we didn't make a mistake it should actually work so i'm just going to run to clear this i'm going to run npm run seed nice so it means that we just insert it like some seed data in the database okay that's good so technically if we refresh there should be some data in here somewhere okay let's check out the channels uh where is it query tool no this one yeah nice so you can see here like we have two entities like the first channel and the second channel and they both have timestamps i think you can see it because my image is there but it's there yeah and the same thing like for for users so we have three users user one user two user three all fine looks good and then with the video yeah so here are the videos yeah so i would say that's it pretty much i think we covered a lot we covered migrations we covered seed data and this is actually everything you need to get started with sqlite so once you got this done you can finally like go to the documentation you know and start copying pasting the commands for finding like individual items i think the only thing we haven't done though is we haven't set up the models but since this video is already quite long i thought let's just leave it like that if you guys want we can make a follow-up on this let me just know in the comments below yeah so that's it pretty much uh thank you so much for watching leave a like and subscribe to the channel um leave me a comment if you have a question you can also reach out to me on twitter my twitter handle is at productioncoder so again thank you so much for watching and see you in the next one bye

Channel: productioncoder

Views: 9,487

Rating: 4.939394 out of 5

Keywords: sequelize tutorial, sequelize migrations, sequelize node js, sequelize migrations tutorial, sequelize postgres node js tutorial, sequelize postgresql, node js sequelize, node js sequelize postgres, node sequelize postgres, node sequelize tutorial, sequelize seeders, sequelize seeding, sequelize seeder example, sequelize example project, sequelize example nodejs, sequelize example express, sequelize js tutorial, sequelize orm, sequelize orm tutorial

Id: Eu-h3iUk45o

Length: 40min 6sec (2406 seconds)

Published: Mon Feb 15 2021