NestJS Tutorial #9 - Connecting to MySQL using TypeORM

Introduction & Why Use an ORM

What is going on guys? In this video I'm going to show you guys how to connect to MySQL using TypeORM in NestJS. So we are going to be using TypeORM as our ORM, but you can honestly do this with really any ORMs like Sequelize or Prisma if you want, but TypeORM is pretty popular and I personally like it a lot. So we're going to stick with TypeORM, and maybe in other videos I'll show you how to do it with other database ORMs such as Prisma and maybe we'll also do a tutorial on MongoDB as well.

One thing I would also suggest is to definitely use an ORM. It's pretty standard to use ORMs, especially when it comes to production-level applications. There's a lot of reasons; one is because you can focus more on actually interacting with your database without having to worry about writing Structured Query Language because it provides an API where you can interact with. And you can just focus more on writing the code that is relevant to your codebase rather than Structured Query Language. You're also less likely to get things such as SQL injections occurring because it takes care of sanitizing and preparing your statements for you. And it's just a lot more productive to use an ORM anyways.

Installing Dependencies

So we'll go ahead and install a couple of dependencies. I'll go ahead and install the @nestjs/typeorm package. So that's yarn add or npm i, and then we're going to add @nestjs/typeorm. We're also going to need to install the typeorm package, okay? So these are two separate packages. One is a wrapper around TypeORM, and the other one is the actual TypeORM package itself. And then we're going to install the mysql2 package. So this is a library that is pretty much a driver that actually will allow you to connect to your database.

Now, before we get started, if I haven't mentioned already, definitely make sure that you have a MySQL server up and running. I have MySQL installed on my system, but you can honestly either install it or you can use a Docker container for it.

Configuring the Database Connection

All right so we are pretty much done with installing our dependencies. That was pretty easy. And what I'm going to do is I'm going to go ahead and go into the app.module.ts, okay?

And what we're going to do is we're going to import a module called TypeOrmModule, and this comes from the @nestjs/typeorm package. And this is why you need to install the NestJS TypeORM package, because it gives you that module that you can interact with to configure your connections.

So we're going to call this forRoot method and we're going to pass in an object which is of type TypeOrmModuleOptions. So for the connection options, since we're using MySQL, we're going to specify the type to be mysql. So for example, you can see over here that there's a bunch of different options that are available. So if you're using MongoDB with TypeORM, you can definitely do it as well. But I'll just type mysql.

And for the host, so this is going to be the host or the domain or the IP address of where your MySQL server is hosted. So we're going to connect to localhost. For the port, this is going to be a numeric value, so we're going to pass in the port. So default port is going to be 3306. For the username, I'm just going to test user because that's the name of my MySQL account. And then password must just be testuser123. And then database name, so I'll just call this tutorial_db.

Understanding TypeORM Entities and synchronize

okay. And for entities, we're going to go ahead and just leave this an empty array. And then synchronize, we'll set this to true.

Okay, so what am I doing so far is, well from all the properties from type up to database, those are pretty straightforward. Database is just the name of the database which we're going to have to create manually. And when it comes to entities, so basically with TypeORM, so if you choose to use TypeORM, basically there's a concept called entities. And these entities are pretty much just classes that are annotated with the @Entity decorator.

And when it comes to entities you can use entities to describe how your SQL tables will look like. And what happens is TypeORM will translate the entity from a class into an actual MySQL table. And there's a lot of things that we can do, such as we can even specify what that property name is in our code and then on the database it can actually save it as a different name. They save it as an actual different column in the table, and this is why ORMs are very powerful.

Okay, the synchronize allows you to auto-create the schemas. So for example, if you have an entity it will be automatically created. And if you update things such as like the the column names or even if you change like the column type, MySQL or the ORM should actually synchronize that with the database.

You want to set this to false in production mode because you can actually lose data if you use this in production mode. In production mode you want to do something called migrations, which is where when you are modifying something with your table, like if you're changing the structure of a table, then you don't want to actually let synchronize take care of that. You want to actually create a migration to prevent any data loss.

Creating the Database & Verifying Connection

Okay so, let's go ahead and open up a new terminal. Let's just do yarn start:dev. And we should get an error because we don't have the database created, and that's fine. We can go ahead and create that. So it says unknown database, and that's completely okay.

So I'll go ahead and... oops. Let me just go ahead and... didn't mean to do that. I meant to open up a new PowerShell but that's okay. So let's just go ahead and type CREATE DATABASE tutorial_db; and we can just go ahead and I'll leave this open for now so that way we can reference it. Let me actually do this. Let me just create a new one.

Okay. So we should be able to connect successfully. If we don't connect successfully, we'll just throw an error. And there you go, you can see that it works just fine. And we can see over here, if I do USE tutorial_db; SHOW TABLES; we have an empty set. There's no tables.

Creating a User Entity

But let's go ahead and create a table. So what we'll do is we'll go inside... I guess I'll create a new folder called typeorm. And what I'll do is I'll go ahead and just create a new file and I'll just call this user.entity.ts. Okay, and what we'll do is we'll go ahead and use the decorator called @Entity. And then we'll go ahead and export this class and I'll call this User.

Now, you also want to make sure that you have emitDecoratorMetadata as well as experimentalDecorators enabled in your tsconfig.json file. If you don't, you're going to run into a compilation issue, and so you'd like to just make sure that you have those things enabled. By default with NestJS projects it should be enabled by default because NestJS literally uses decorators for pretty much almost everything. But if you don't have this enabled then you make sure you have it enabled. Okay, just wanted to mention that.

All right so let's go ahead and set up our user entity. So what we're going to do is we're going to set up a primary key. So you can see how we don't even have to manually create the table, we can just do that by doing everything inside our entity class. So to set up a primary key, or a primary generated column and it will auto-increment, we can just do @PrimaryGeneratedColumn. And I'll just call this id and I'll just set the type to be a number.

And inside of the decorator, inside the parentheses, we can pass in an object. And you can specify things, so if you really need larger integers or a larger numeric value you can do things like bigint. If you want to change the name, you can definitely do that as well. Maybe you want to follow the old school or the common convention when it comes to naming your primary keys. So you want to name it something like user_id_pk for primary key. If you want to do that you can do it as well. But I'll just leave that as user_id.

Customizing Entity Columns

Alright, so now that we have our primary key or the primary column, let's go ahead and just create another column. So you can do that by using the @Column decorator that comes from the TypeORM package, and we'll go and just call this the username. And for the password, we'll do this as well. And we'll also do one more, email address.

And so with the @Column decorator we can also provide our own options too. So if you don't like the name of the username or how it's going to be named in the database you can change that to whatever you want. In MySQL I tend to prefer to stick with a snake case, similar to Python's case convention. In JavaScript or TypeScript it's preferred to use camelCase.

But let's say for example if you have a field that is something like, I don't know, let's say @Column and let's just call this, maybe if we didn't call it email, let's call it emailAddress. Okay, and we can change how it's being shown in the database, so the column in the database, like this. Okay?

We can also set other properties too, so if we allow it to be nullable, we can set it to be nullable. But obviously I don't want it to be that way. And you can also set a default value too, so you can leave that as an empty string. So it's a good way to be more robust. So let's do that.

Okay, and then we'll do that as well. So nullable: false, default: ''. All right.

Registering Entities and Verifying Schema Sync

So let's go ahead and... I think we should have it auto-restart for us because we're in dev mode. Let me zoom out a little bit because I know it's getting a little bit, it's getting a little bit cluttered. Okay so we have our entity. Let's go inside our SQL database and let's do SHOW TABLES;.

And you'll see, let's see what's going on, it seems like... Oh whoops, I forgot. So we need to actually import the entity into this entities array. Completely forgot about that. So what I like to do is inside my typeorm folder, I like to create a file, an index.ts file, and this is basically just going to be a file where I'm going to import all of my entities, which is really just in the same directory, or it belongs in the TypeORM directory. And what I like to do is I like to export the actual entity itself. And I also like to create a variable called entities and put all my entities in there and then export that entities and export the entities array itself. So I think what I'll do is I'll just export default entities. Okay? So basically this allows me to import User independently, and if I want to import all the entities I can do that as well.

So for example if I type User over here you can see that it'll literally be imported from src/typeorm, which is literally just reading it from this index.ts file. But if I wanted to import entities, okay, I can do it as well. Now keep in mind that this is an array so you can actually just remove the square brackets. Okay and if you want to do it short and one you can also leave it like that too.

Okay, so if I go into my SQL, you can see now we have the user in our database. And if I do DESCRIBE user;, you can see that we have user_id, that's the name of the column, username, email and password. Okay, and if I were to modify anything, so if I were to change, if I were to get rid of this name and let's get rid of the email_address name as well, if I were to describe it again, you should see that it auto-updates for us, and that's because we have synchronize: true. Okay?

And if we were to modify anything else, such as nullable for example, so let's say if the emailAddress is nullable, there we go. So you can see that before... seems like I always look at the wrong one... so before we have Null: NO for the email, and over here it says for email, it can be NULL. Okay, so that is pretty straightforward. So yeah, and that's pretty much how you connect to a database. So that's going to be pretty much it for this video. In the next video I'll show you guys how we can actually save items to the database. So I'll see you guys in that video. Peace out.