Using Sequelize With TypeScript: Basic Project Setup

hey everyone welcome in this video we'll do a basic setup for a project that utilizes sqlize and typescript together if you've tried to use typescript with sqlize before and you found yourself frustrated then you're not alone taking a look at the documentation at sqlize.org you can see that as far as typescript is concerned the documentation is pretty sparse they give you just one long example and says right at the top as sqlize heavily relies on runtime property assignments typescript won't be very useful out of the box so that's not very promising right and if you don't have a lot of experience with either technology and you try to get moving quickly and you start copying and pasting this code into your project you're likely to get pissed off in a hurry but i'm confident that by the end of this video which should be about 40 to 60 minutes you'll feel a lot more comfortable using typescript and sqlize together and as a note i'll be using a postgres database for this video and you can use another database if you like but before we go any further go ahead and pause the video and get your database set up okay so hopefully you've gotten your database set up and now if you have a folder ready to go let's initialize it let's add our typescript dependencies of course we'll need typescript since this is a node project we'll need a package called types node we'll need ts node and rather than use nodemon to watch for file changes i'm going to use ts node dev it's a little faster in my experience and these are all dev dependencies because we want to check for errors during development and not at run time okay and we'll likely use express if we're going to add a server to our project so we'll install express and we'll want to keep our connection string variable values a secret so we'll add a dot env file as well for our sqlize dependencies we'll of course need sqlize and then if you go to the documentation page for sqlize getting started tell you what else you'll need for example if you're using a postgres database you'll need these two dependencies now pg is a dependency that has been used for a long time by people using postgres in a node project and then store is just another thing we'll need to use along with sqlize if you're using another database such as my sql then go ahead and add the necessary dependency you can see they're all listed here so i'm just going to grab these here and if you're very new to typescript and you don't have typescript installed globally go ahead and do that now because if you do then you can run a command tsc-nit and that'll automatically generate a ts config file for you which you'll need right so here it is let's go set the target to es6 let's allow javascript files for now you probably know that typescript doesn't like it when you don't specify an output directory for your compiled javascript files i call mine build and then we can exclude node modules too and since we're doing that let's go ahead and add a git ignore file to our project probably won't be pushing my changes to github but it's always good to add one of these just in case so for now we'll exclude the node modules and the dot env file let's add a dot env file then you'll need some variables for your connection string i'm going to go with db user db name and db pass all right and then i'll go ahead and enter in my values for my connection and you can do the same i'll pause the video okay so we got those values entered in and to bootstrap our project i'm going to use the sqlize cli tool and that'll generate some files and folders or folders and files for us that make it so that we can write less code and get up and running more quickly let's go ahead and install that sqlize cli and if you take a look at the documentation it's under migrations i've got some information about it here so you can see that it's a dev dependency right and then you can use it to initialize your project so once that's installed you can run not npm but npx sqlize cli init and it tells you that it's generated some folders for you first it generated this well i don't know if it did it first but it generated this config folder which has a config.json file in it if we take a look at it it has our connection set up for both for development testing and production of course in this video we're only going to deal with development you can directly put in your connection values here but since we want to keep ours a secret we're going to use dot env and to do that we can't leave it as a dot json file so we'll go ahead and switch this over and make it a javascript file and we'll have to export it all right so we'll need to it seems like we've got an error in our ts config file let's take a look at that okay well it says module common js that's never seemed to be a problem before let me go ahead and pause the video while i figure this out okay i see what i did i meant to um let's go ahead i didn't mean to specify output file but the output directory so let me just all right there we go so it's the output directory that we want to be billed all right so that looks good so going back to our config.js file we'll be using uh dot env here so for the username right that would be process.env and then for me that was let's go ahead and copy this i had minus db pass this was db name now i don't mind exposing the name of the database but the password and maybe the username will just keep it secret just to keep as much well of course the password but the username as well just to keep as much information as secret as possible or you i'm using a postgres database so change the dialect value to postgres all right and since we're using env we'll have to require it here and we can test it to make sure that it's properly importing the values so we'll do that with dbe name since i've already i don't mind sharing that and so we can run this file with node config slash config right we see that the variable value was imported properly so that's good go ahead and get rid of this now this is being used in another file and that is inside of the models folder right this index.js file now what this file does is it allows you to use it basically makes your database connections equalize and your models available throughout your project just by importing the file but you see here it's looking for config.json but we changed it to js so we can do that right and if you're wondering about whether that's legal or not you can just take a look at the documentation and that's under migrations again so if you go down to dynamic configuration it says the configuration file is by default a json file but sometimes you need a dynamic configuration so you can long story short use both json and js files all right let me show you that here okay and since this uh config or sorry this index.js file is inside of the models folder it opens up or it looks at all of the other files in that directory the models directory and it says if they have a file extension of javascript then it will go ahead and return them but we don't want those to be js we want them to be ts right because we're going to make them typescript files right and then for each file that's uh found with that extension you know each file will represent a specific model like a user model or a project model it'll go ahead and inject the sqlize package and also data types into it so you it it'll be available for use inside of your model class i know that's a lot of talking so try to move on from just too much explaining and just get on to the doing also it looks at your associations and handles those as well and you'll see what i mean by association in a minute looking at the other folders that the sqlize cli is added for us by running the init command it's added this migrations folder and if you're going to use sqlize in production you'll want to use migrations that way you can keep track of changes to the database but we're not going to use that for this project or for this video and then you've got a cders folder here but uh don't delete it yet we might just use it to store some files later all right so now let's check our database connection so in the root of our project let's add a file and we'll call that index.ts and we'll just set up a simplexpress server you can see the typescript doesn't like this it says that express is declared but its value is never read but it also shows you how to solve the problem just by importing at type slash express which you're going to see that we have to add a lot of packages that begin with app types just to make sure that our packages work with typescript right but it also allows you just to click this quick fix and that's not what we want to do so never mind that go ahead and hover again grab this part here copy it and then add it and once it's added you can see that the error will go away and it does all right then we want to bring in our database connection and that's located in the index.js file in our models folder so we can just do this since it's index.js it's considered the default file in the models folder so we can just go this far in defining our path and it knows to grab that index.js file right so if we have some models defined which we don't yet but if we do and the index.js file picks those up there's a function we can run it's model.sync and what that'll do is it'll look for a table in the database that corresponds to that model and if it doesn't find it it'll add it automatically so you can see by looking at the documentation under model basics if you go down to model synchronization you can see that that's what it does it says right here user.sync that's for a user model creates a table if it doesn't exist and does nothing if it already exists so if you wanted to create tables for all of your models you can use sqlize.sync right here alright so we're going to use that and then we'll want to run our express server all right and to run to run this file let's go ahead and go to our package json file and add a script we'll just call it dev and remember we're going to use ts node dev so if you go to the documentation for that it tells you just to use this right here as your run command but we're not using a file called server.ts we're using one called index dot ts all right so let's go ahead and try to run our server and run sqlize.sync as well okay good so we know that we're connected because it gave it ran this little test um sql query also we know their our express server is running all right good so now what we want to do is we'll want to set up our models all right we don't have any model set up so we don't have any tables in our database it didn't do anything for us when we ran sqlize.sync you can take a look at my database setup in pg admin so i've got this database called ts underscore sqlize and i've got no tables so i'm going to create models now to well they will eventually be used by sqlize.sync to create the tables so we can actually let's let me go back to the documentation we can generate those models using the sqlite cli so if you go to the migration section again you've got the command right here it's another npx command so if you copy that this is under creating the first model in migration paste it in and then we'll change we're going to create a user model so that can stay the same but attributes for now just add a single attribute we don't need to add them all right now but you need to add at least one otherwise it won't work so we'll add an attribute name and that will be a string all right so it tells us that it added a model and a migration but we're not concerned with migrations in this video so just go right to your models folder and you can see that there is a model called user and the file name is user.js alright so you can see that it brings in the model from sqlize all right also it brings in these two values and if you're wondering where they come from they're brought in from index.js right it goes to each file and then it requires the file and injects the parameter values here right and then it defines a class called user which extends the model class and then it gave us our only attribute that we specified which is name but we're going to add a lot more but for now you see this is a javascript file we want it to be typescript so first go ahead and make this a typescript file right you'll see right off the bat you get a few errors now we have sqlize data types and models they all have the same error right it says sqlize implicitly has and any type and you'll find a lot of times when you look online and you want to know when it's okay to use any as a type you'll see many people be very vocal about not using any as a type at all and not being lazy but in this case and you can take a look at the typescript documentation it says here about using the any type it says in some situations not all type information is available or its declaration would take an inappropriate amount of effort now the part about an inappropriate amount of effort you should probably ignore that as much as possible because if you start thinking everything is an inappropriate amount of effort you'll never really properly do type checking but it says here that these situations may occur for values from a third party library right and that's what we're using sqlize as a third-party library we're going to trust that library if we didn't trust it we wouldn't use it so in that case we're going to be okay just using the any type here so go ahead and do that all right so since we're using es6 let's go ahead and use an import statement all right and if you go to the documentation for sqlize regarding typescript you can see they tell you and if you've used typescript before you'll this will be familiar to you to set up an interface and since our model is user we'll call the interface user attributes all right but our id we're going to use uuid which is a string the name will be a string that's okay we're going to have an email and also a password all right and we'll want to use this so if you look at the documentation and i'm just showing you the documentation so that you feel more comfortable and not just trusting every single thing i say and do you can see here that we'll use the user attributes right so i have that here and then we'll want to implement user attributes all right but once we do that then typescript complains it says that we have incorrectly implemented the interface user attributes so let's rectify that go down here and specify are properties one is id and we're going to make that non-nullable it's required right and that's going to be a string also a name which will be non-nullable also string an email address non-nullable and also a string and then a password of course non-nullable and also a string all right so we're no longer getting that error all right and then down here we only specified one attribute and that was the the name for the user model but we need more we need four in total so first we'll add in the id and you can see the typescript is not happy about something but once we do most of the work and adding these fields in it's going to to let up on us a little bit so go ahead and add in id type data types and that will be uuid all right this is non-nullable so you use allow null as false it's going to be a primary key set that to true and also we'll set the default value here to uuid v4 and so next we have our name type datatypes.string allow now false next is our email of course it's non-nullable as well and we also want to make make it so that no two users can register with the same email address right so we'll set a field called unique to true right and then sqlize will take care of that check for us so we don't have to do it ourselves later then finally we'll need our password and you can see the typescript has finally let up on us all right so this file is mostly completed now here's the association we were talking about earlier right that gets handled here in the index.js file so we're going to have two models for this setup for this basic setup imagine we're creating a project management tool and we're going to have a user model and we're going to have a project model and a user can be a member of several projects and a project can have several users attached to it right so that means we have a many-to-many relationship so in that case we'll use with sqlize the belongs to mini uh function so we'll have user dot belongs to mini all right and it belongs to many we have to use models to bring in project and that's through a junction table called we're going to call project assignments so basically with this junction table we'll add a user id and a project id to associate a user to a project right and we're setting that up here okay so that's all there is for this model all right so we'll do the same thing for the project model so we'll use that uh generator that sqlize cli has for us and we're not using user anymore it's project attributes and we'll instead of calling it a project name we'll call it a project title and that'll be a string we're going to have other attributes as well but we just want to get it generated we'll add those later so again it says it created a new model for us and we see that in our models folder we have a project model it's project.js file so first let's go ahead and change that to typescript and we know we see those three errors that we got last time so we'll set the type to any for each okay and just like before we set up some user attributes this time we'll set up an interface and we'll call it project attributes and the id won't be uuid this time we'll use an integer but in javascript it's just number the title is a string and the project will have a status field so that would be for example completed active meaning not completed on hiatus whatever you want to use and also let's be consistent here and use an import statement right let's implement this in our class all right again we get this error so let's add those fields that we need to add id is required that's a number title required that's a string and status required also a string all right in our first field was id that's an integer it's non-nullable it's required it's primary key and we want to step up the project id number each time we add a new project so one two three four so we'll use auto increment set to true next we have title status okay don't forget to add all the commas you need to add all right and the association looks just like the other one it's now we're just going in the other direction and we're associating a excuse me we're associating a user with a or a bunch of users with a project we could say here project dot belongs to many in this case models dot user and that's through the junction table same junction table project assignments okay so even though we don't have a model for project assignment when we run sqlize.sync and it creates our two tables for us because we've defined this association here and here right that means that we'll get that table project assignments created for us automatically so let's try that all right so anything we want to do before we do that well first since these two product projects here sorry projects these two models here have typescript file extensions they're typescript files let's also make index.js a typescript file so rename that index.ts and you see here you get nine errors all right now first off um it really doesn't know what to do with this database right here all right so if if we make this any things will work out a little bit better for us and we can do that because we're trusting sequel lines if you make this any it makes these errors down here go away right now sqlize if you just hover over it it says it implicitly as type any we could do that or we could just go to quick fix infer the type of sqlize from its usage and it turns out to be any right that means a typescript couldn't really figure out what to do with it either for file here we can do the same thing quick fix infer parameter types from usage turns out to be a string and then here same thing turns out to be any and then we have one more error in this file or actually not in this file in our index.ts all right so our export is somehow screwed up it doesn't like this so because it's not a module anymore right because we've got the any type it really doesn't know what to do with it so if we go back to this file and rather than using module.exports we can just use export default db and we no longer have that problem here okay so we should be able to run um our dev command and see a couple of tables added to the database all right so let's do that okay so you can see here that it says create table if not exists projects all right so it used our project model and it created a table called projects right it takes your model and it pluralizes it for the table name it did the same thing for the user model create table if not exists users right but if you look down here it also said create table if not exists project assignments so that tells us it created a table called project assignments even though we never defined the model just because we implicitly defined it through our associations so let's take a look at our database just to see how things are or how they look so refresh tables now you can see i've got three tables so let's take a look at users right so as we specified there's an id which is a uuid it has a name it's a string email is a string password as a string it also automatically includes created at and updated at time stamps all right if we look at projects so you've got project id as an integer that's the primary key it's got a title as a string status as a string created at updated at going back to users you can see also that our user id was a primary key and then the project assignments table which it created for us because we specified those associations it said there's a project id and a user id value and it made both of those primary keys for us and it made it basically a composite primary key so that if both of these values are repeated then we won't be able to add a value to the table right it would be like trying to assign the same project or to trying to assign a project to the same user twice we don't want that to happen all right good so now what we'll want to do is go ahead and add some values to the database so that we can run a query just to make sure everything's working okay so go back to your code so in your seeders folder and let's go ahead and stop the web server well you can leave it running it's fine but in your seeders folder add a file called dot users.ts actually no let's just make it that okay so we'll just add two users for now so we'll have a user that has an id created by uuid right so we need that package right we don't have it installed yet we didn't install it right good so well stop your dev server now and install uuid right and its usage is found at its home page you want to grab this import statement right here using es6 syntax all right now you can probably guess that because typescript gave us an error here it's going to want a corresponding types package and you can see that it does right and that's this package right here let's see if we can get it to add it just by clicking quick fix install types uuid wait for the error to go away it does so we can take a look at our package json file and c that it added that package for us so typescript being very helpful alright so the id will just be uuid for name will be octavio flores email password all right we'll have another person who is a member of the same company we'll generate the id in the same way of course okay so we have two users here and let's see if we can add them to the database so we'll need to import from this full file to our index.hts let's close this out just because we're going to get confused or at least i am okay it should be users good all right so what we'll need to do is use a sqlize function called create so let's define a function first called create users and we'll map over the users list that we're importing we'll need that user model so to be model.create and then we'll just create a user for each user and you can see the user type just by hovering over it you can see the properties that are associated with it all right and those are brought in from the other file so let's go ahead and make a function call here and run the dev server okay so you can see that it tried to create tables again but it's not going to because those tables already exist and then it's doing an insert function right so it's adding users to the database and if we go to our database and we check so users refresh all right we can see we've got two users in there that's good all right so now let's add some projects so in your seeders folder add a file called projects.ts so you don't have to worry about specifying the id here it's going to be done automatically since we set auto increment to true so we can just set a title and we'll call the first project site upgrade login page and then we'll give it a status of active and we'll create three projects we want to assign two projects to a single person let's say here user dashboard and that'll be active as well and then the last one will say database maintenance let this one be completed all right so we'll do the same thing in our index.ts file let's go ahead and change this not to create users but to create projects we'll need to import that projects list okay and we'll map over that list of projects we're no longer using the user model but the project model all right so let's change our function call as well see if we can add those projects okay so we can see that it inserted into projects three different projects okay so let's take a look at our database so go to projects view edit data and we've got those three projects right but we don't have any way right now of assigning projects to users or users to projects so we'll need to create a model for the project's project assignments table right it already created the table for us but we need a model so that we can use these functions like create and find all right so stop your dev server all right find that model creation or that model generator command right and the name of this model is going to be project assignment right and we'll give it a single attribute for now and if you look at the project assignments table you can see how it special named the columns right so it's got project id right where the leading p is uppercase and i is also uppercase so don't forget to use that when you're specifying your project assignment model or when you're defining your project assignment model and what happened to our command that's weird i somehow we lost that bit of code i want to make sure that it didn't insert that project into or it didn't create that file it didn't okay so sorry just talking to myself here you probably are okay with everything that's going on so again we'll name it project assignment spell it correctly attributes and we'll say project id and that'll be an integer alright so it says it generated a model for us let's go in and do the usual thing make it a typescript file first all right fix our errors use an import statement we need to define an interface of project assignment attributes so it will have a project id and that will be an integer alright so in javascript we'll just say number it'll also have a user id which is uuid but in this case we'll specify it as a string we're not going to define an association because we already have an associate and association between users and projects sorry we'll just finish up down here actually i already defined it here so what am i doing specified the attribute when we generated the model what else do we need here we need to define the foreign key so that's references model and that will be the name of the table which would be projects sorry key id all right and we'll define the user id as well it's going to be very similar okay so that should be good it should allow us now to add some project assignments to the project assignments table so go into your seeders folder and create a new file called project assignments it's a typescript file all right so we'll need first a project id and then we'll need a user id all right now we can get these because we're going to need to use values that are have already been established right we don't want to make up new users and projects on the fly we want to add existing users to existing projects so if you go to your database and get the id for octavio just copy that and put that here under user id and we'll attach him to project one all right we'll also attach him to project two ah let's make it three all right and then the other person the other user we have farah we'll get her id and we'll add her to project one along with octavio okay so we should be able to add these three assignments so go to your index.ts file all right and then we'll change this function not using the best naming for this project but oh well all right and the model is project assignment okay so hopefully we can add these assignments to our database it doesn't look like we have any errors instead it inserted into project assignments those three assignments so let's check our database out go to project assignments and good and we were able to add those now one thing we can do is we could try to add um just as a test we can try to assign the same assign a project to the same person twice and see if we can do that or get an error we should get an error right because we set a composite primary key saying that if both of these are true if the user id and the project id being used together are already have already been used together in another assignment then we shouldn't be able to create a new assignment so going back to your code let's just copy that comment this out before we do anything let's um just stop our dev server we don't want it getting ahead of us and we'll add from here so just try to add this one again this one's already been added so we put that in here should throw an error and that would be good okay good you can see here that there's a sequalized unique constraint error right that means there should be only one project assigned to a person one time we shouldn't be able to assign the same project several times all right and that's good so it's working so we're not able to do that okay so now to see the power of sqlize you can take a look at what gets returned if you look up all the users now if i were using just regular sql queries i might have to use two inner joins to actually get all of the information about a user i need which would include not only their name their email and um id but also a list of projects they belong to but what we can do to find all that information in one lookup this so we can use the find all function we want to include did not want to do that all right let me make sure i'm not putting it in the wrong way so let's go ahead and take a look at the uh go under associations so all right well you can see right here this is what we're going to use so going back to our code and our model will be dba dot project so we want to find all the associated projects all right and let's run that and see what we get back actually we won't see anything unless we log it to the console so we'll do right now typescript is demanding that we specify a type or actually no what i needed to do is put an arrow here now it's demanding that i specify a type right and this result is going to be an object right so what we can do is wrap that with a set of parentheses and specify object okay and then if we get an error we can log that to the console we'll set this also as an object all right but then again we're getting this from sqlize you could just as easily set it to any it's not a problem we're trusting sqlize so go ahead and run npm run dev looks like we've got an error here and so we don't need these see if that okay here we go so you can see here we've got a big response all right and if it's hard to read here we could just send it to localhost 3000 if we like what we could do is say app.cat take all of this put it in here and rather than logging it to the console okay so if we go to port 3000 right we can see that so here's our response right so it gave us a user the user's id their name their email unfortunately we returned the password we didn't want to return that you'll have to think of a way to exclude that information when you make your queries gave us last updated when it was created also gave us that list of projects right so we didn't have to do that double inner join thing that we would normally have to do it did it for us so it's nice in one call we can get all of that information so for octavia we get his two projects and we also get his project assignments so we got everything and you can filter it and get just as much or as little information as you need all right so that's it that's getting set up with typescript using sqlize took a little bit longer than an hour it's a little bit longer than i promised but i hope that at this point now you've got a pretty good idea about how to get yourself up and running with typescript and sqlize and if you have any questions or comments please leave those in the comment section below the video and as always thank you and i'll see you next time

Channel: willjw3

Views: 8,373

Keywords: typescript, sequelize, sql, node.js

Id: VyEKwp6Q4fY

Length: 71min 2sec (4262 seconds)

Published: Mon Apr 05 2021