Getting Started with Sequelize and PostgreSQL

what's up covalence friends today we're going to be getting started with sqlize which is an npm package for connecting to and querying different types of databases we're going to be using it with postgres and piggybacking off a previous video where we actually hooked up a postgres database to a heroku app instance so if you haven't seen that it's linked in the description below and if you haven't checked out our merch store it's also linked in the description below so let's get started alright so we're going to go ahead and get started with our express template the link to this repo is in the description below but if you wanted to use your own express template or not even use express this will work fine without express but we're going to use it for simplicity's sake and to speed things up a little bit so let's go ahead we're going to open up a new terminal here and we're going to install the packages that we need right so we're going to npmi dash save and since we're using postgres we're going to do pg pgh store i think it's h store there we go and then sqlize okay and then we're going to npmi-save save dev and it's going to be the sqlize cli all right so now we have you know everything we need to get started the if you've never been to the sqlize website they have a pretty good website their documentation isn't my favorite to be honest but they have a pretty good getting started guide here and obviously you know we're using postgres so we went down this route but if you needed to use it for a different type of database you know they work with all the different drivers so i think by default they use actually mysql so we're going to change up a few things when we initialize everything but if you want more info on the sqlize cli it's actually under other topics migrations there might be another place but this is the only place i found it um and so this is uh you know obviously the sqlize command line interface and um you know this is something we just did and then we're about to get into some project bootstrapping it kind of goes into the configuration file and then also creating your first model and migration which we'll get into in a second uh but let's go ahead and they tend to to like tell you to do things at the root of the project but i personally like to create a new folder so i'll create a new folder called sqlize and then i will you know change directly directory into that and then in here is where i'll do my sqlize cli init all right so then inside this folder we have our config we have no migrations yet but we do have an index.js file for the models and then we have no seeders either so it created those folders for us and it also created these two files um and so if we go into our config.json we can see that it has essentially a um an object for each of our environments right so we have development test and production now the problem with this is that in reality you're probably going to want to use for production and environment variable right so if we go to even if we go to our little heroku instance here we can see that under resources you know we have our database and then under settings you know if we reveal our config vars it gave us this database url right so this is actually the url we're going to be using to connect and so with sqlize so i personally like to change this up and make this a javascript file and then obviously we have some issues here so what this is going to change too is it's going to be oops i need to go back the module dot exports equals and then we basically already have our javascript file right now i'm not a big fan of how this looks so let's just go ahead and we're going to get rid of all of these little double quotes zoom back in now and we're not going to be using mysql we're going to be using postgres and we're not going to be using any of these properties either we're going to use the url property right so we're going to grab that url reque real quick so we're going to grab this and then we're just going to post it right in here right so um don't worry about copying this down guys i'm going to delete this database so you won't even be able to access it it won't exist but you're going to need to use your url for your database here for you know for this we can obviously just kind of copy these in here we can still use this why not we'll just leave this in here if you want to use a local postgres database and then for production what we'll do is we'll remove this and then we'll just say that this is process.emb dot database url similar to how we'd actually have it in production right so now we have our config file set up if we go into our models index.js file here you can see that this is basically just a setup file right so this allows us to they go through the folder models and they just place each of the models that we're going to add onto this dv object and then they set the default export equal to db right so then it also grabs sqlize which is lowercase sequence is going to be our actual instance of sqlize and then uppercase sqlize is the constructor itself right or the default export of the package itself but you can see that it's actually requiring the json file still so we actually have to change that by default we're going to be using development which is fine and then this has to be different as well because we're actually going to kind of do something a little bit different here right and so basically let's just define our lowercase equalize as a constant and it's going to be a new capital sqlize and what we're going to do is we're going to pass in our url and then our config and now the url is just going to be it's going to be config.url but then we're also going to need to add an ssl mode to this for the postgres and for right now you could get into making this work with ssl but we're not going to worry about ssl for this demo so we're just going to say no verify right so again we have ssl mode here um we can add dialect options to this as well so just so you know for your own edification these all would include you could do a dialect options and this would be a object like this and then you could do ssl here right and then a lot of people do this whole reject unauthorized ordeal we don't necessarily need this but some versions will allow you to just do this and then use it with ssl mode but again we'll go ahead and we'll add it you know in production you might require ssl but for how we're doing this you don't even need this stuff so honestly this is not super important but again just so you know that it does exist there are dialect options in here and you can use them and make it work as well so we're going to use this ssl mode to verify that we're going to put right into the url we're going to pass this url up and hopefully properly connect to this database right so now what we need to do is we actually need to create our first model we're going to npx sqlize cli model generate and then the argument is just name and we're going to call it user and then we do attributes and we're going to do first name string last name i'm using all snake case too with underscores you don't need to do that but you know it's not terrible you can actually add id in here too which i don't mind doing you know id number first name string last name string and then let's just do email string so we're going to click enter and it creates our user.js file here and then it also creates a migration as well now if we go into this user.js file here what we can see is that it creates this class and you can actually convert all of this to work with typescript and we could change this to a ts file and we could just start kind of migrating everything over to typescript we're not going to worry about it too much right now we might create a separate video for that i like commas and then since our id is actually a primary key we do have to change this into an object so it's going to be a type and then what we do is we just say primary key true for this and then we have our first name last name email and then the last thing that we can actually add to this which i personally like to do is i will set an underscored property in the options here so you say underscore true and what that does is it just kind of notes that you're going to be using underscores and not necessarily camel case i'm not a big fan of camelcase with database objects so again that's kind of up to you how you want to do it but i typically like to use underscores and then here we have our migration itself right and so what the migration is is it basically exports two functions it exports an up function and a down function and when we run this migration when we're actually running the migration it runs the up function if we redo or undo the migration it runs the down function right and so what we can see here is that we want to change these because they're camelcase right so we set the underscore property and that's what the underscored property true kind of signals is this created at and updated at it'll actually help you define those a little bit better but again we have our allow no false auto increment id primary key true and we have our type integer and so that corresponds here data type stop number or we could have integer here so actually let's keep this consistent so we want to say integer and oh actually it created two ids so again when you're doing the attributes dash attributes do not include the id otherwise you're going to be deleting that second one so apologies there remove the id number that's not good it doesn't detreat it as the primary key but i will say if you don't put the id in there it will not include it in the model so it won't actually have it here and so you'd have to add that separately so again if you want to be able to like kind of access that id and everything and have it referenceable i would just you know you could do it either way you can add it after the fact or you can put it in attributes and then delete it for the migration there's probably a better way to do that but again there's kind of some fussing around that you have to make this work well so again i deleted the second id that i created now we have the integer here we have the integer here all right so everything kind of aligns and we are good to go now all right all right so the way that we can actually run this migration is and oh just to kind of note there's the static function associate as well this is where all your foreign key associations and whatnot would go um you know the obviously the belongs to and the one to many many to many relationships that you may have we can get into that in a future video as well but for right now we're not going to deal with that because we only have one model all right so we're going to go ahead and npx sqlize cli db migrate and it grabs the config file it looks at the development environment and then it calls the create user migration and so if we go into d beaver right now we can open up our covalence progress postgres test look at schemas we will look at our tables and we can see these two tables now right so we can look at users we can see that it created an id first name last name email created at and updated that so all that looks good obviously we don't actually have any data in here so if we view data right there's nothing in there but it also created this sqlize meta table right and this is kind of the cool thing about sqlize is that it creates this table and then it keeps track of which migrations have run so if you were to add we'll go back to here if you were to add more migrations let's say you wanted to just add a column to user right you just want to add a column phone number right you can add that column and it'll actually add to here and then you run db migrate again it will not create another user table right it only runs the migrations that haven't run yet which is extremely helpful right because you're not double running migrations and then you can undo up to a certain migration as well so you can go back to a certain migration and that will undo everything up to that point and then you can kind of proceed from there so the migrations are super helpful helpful and they came up with a really clever way of just making this super convenient right so if you change databases all you do is you just run db migrate it runs all your migrations and all sudden your database is completely provisioned to set up the way you need it to right with all the correct tables all the correct columns everything along those lines right so they really kind of came up with a great way of you know helping you migrate your stuff and you even migrating data in a sense right and so cders itself would kind of help you with a lot of that stuff i don't use cds very often i kind of just use the api or i'll create an api to do a lot of that stuff but with cders you can actually add data and things like that so we can look at the documentation for something like that i'm not going to worry about it right now because we're going to get right into in future videos as well get right into just using the database connecting to the david database and then just accessing information and storing it directly with the sqlize api or the sqlize library itself all right so we have everything we need in the config we have our migration setup we have our user table setup right as we can see here we have the migration done we have our user table so now the last thing we're going to do in this particular video is just going to be we're going to connect to the database and make sure that we can do that so let's go back we don't need to be in the sqlize folder and we can kind of see here this is our index.ts this is where we're running so what i'm going to do is i'm actually going to just kind of create a self-executing async function here and i'm also going to create a constant connect db this is going to be an async function and what we're going to do in this is we're actually going to just kind of run the sqlize authenticate function and so we need to actually import that object so we saw that the models index.js put sqlize on the instance for sqlize right it puts it as little sqlize and it puts it on the default export so what we're going to do since we haven't set everything up for typescript we're just going to require this but we're going to require sqlize slash models slash just equalize models right because it is the index dot js in our models and so it's going to grab the db object but we want the little sqlize there so that should be good and so in connectdb what we can do is we can console.log and we're going to say checking database connection then we can do a little try catch block right and then if we catch an error what we can just say for now is we can just console.log you know database connection failed oops and then we'll just log the error here and we'll just process that exit one because it was an error and in here what we'll do is we'll await sqlize dot authenticate and if that is successful we'll console.log database connection established period all right so now we have our database connection is established or established and we should be good right so uh inside here we will add the um you know the app.listen but first what we'll do is we'll await the connectdb function so we should get you know the checking database connection once we authenticate and connect we will then attempt to run the actual server on the port and it should if everything is done correctly log everything we need so we're going to run npm rundev it is going to compile everything no errors great checking database connection it says executing select oneplus one as result database connection established attempting to run so now we're actually listening right so now we're ready to go ahead and start querying that database and so that'll be a future video so stay tuned all right so hope you guys enjoyed that i hope it was pretty easy to understand if you guys have any suggestions for future content feel free to drop them in the comments below otherwise we're just going to keep creating what we want to create if you haven't subscribed make sure you hit that subscribe button and we will see you soon

Channel: Covalence

Views: 15,687

Keywords: Covalence, Programming, Coding, Software Development, learn to code, online coding bootcamp, intro to react, intro to javascript, intro to typescript, node.js, MySQL, .NET, React.js, free web development tutorials, software development bootcamp, web development, javascript, typescript, mern stack, express, become a programmer, how to become a software developer, programming tutorials, become a full stack developer, software careers, heroku, postgres, postgresql, sequelize, orm, knex

Id: p-yKR7GusqM

Length: 18min 38sec (1118 seconds)

Published: Fri Aug 19 2022