Using TypeORM and MySQL on a TypeScript API

Photo by Jan Antonin Kolar on Unsplash

In this tutorial we will be adding TypeORM to our API and we will use it to create a database to hold products information. As a base, we will be using the API of this tutorial. If you prefer, you can get the source files here and start right away.

Database Setup

The first thing that we will have to do is setup a database to use. If you already have a database (even if it’s not a MySQL one) you can’t skip this part.

I’ll be using Docker to speed the process. If you don’t know docker already, I recommend that you take a look at it. You can install it following this page: https://docs.docker.com/get-docker/

Docker works with containers that contain a working OS with the tools and programs that you need. Instead of installing and setting things up by yourself you can use a ‘plug and play’ docker container with everything ready. For this tutorial, I’ll use a MySQL container. After installing docker, just run the following command into your terminal:

docker run --name database -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d mysql:8

This will create a MySQL detached (-d) container with the name ‘database’, a user with the password ‘password’ (-e MYSQL_ROOT_PASSWORD) and with the container’s port 3306 mapped to the host’s 3306 port. This will be a lot of information if you’ve never used docker. The main things to remember here are the name and the password that you used.

You can check if the container is running using ‘docker ps’ at the terminal. If nothing shows up (maybe you restarted your PC), use ‘docker ps -a’ to show all the containers (including the ones that are not started). To start and stop the container, just use ‘docker start database’ and ‘docker stop database’. To summarize everything:

# List all running containers
docker ps
# List all containers
docker ps -a
# Start a container
docker start <name>
# Stop a container
docker stop <name>

The last thing that we have to do is to create our Schema inside our MySQL database. To do this, I’ll use MySQL Workbench and connect to the instance running on the container with this settings:

Hostname: 127.0.0.1
Port: 3306
Username: root
Password: <password>
Default Schema: <blank>

After connecting, type the following on the Query tab and click on the Lightning icon (or press CTRL + Enter).

CREATE SCHEMA `database_name` ;

Configuring TypeORM

Let’s start by installing the dependencies:

# Install TypeORM and a required lib
yarn add typeorm reflect-metadata
# Install the database driver
yarn add mysql2
# Install node types if you haven't already
yarn add @types/node -D

We need to install TypeORM and Reflect Metadata (required). We also have to install the database driver. If you are using another database (like PostgreSQL), you’ll have to install a different driver (check here). We are also installing node types just to make sure (we should have it already installed).

We also have to add the “emitDecoratorMetadata” and the “experimentalDecorators” to the “compilerOptions” of our ‘tsconfig.json’ file:

tsconfig.json

Now we are going to create some folders and the last configuration file. Inside de src folder, add a app and a database folder. Inside the app folder add a entities folder, and inside the database folder add a migrations folder. Finally, add a ‘ormconfig.json’ on the project root (where you have ‘tsconfig.json’ file). The structure should look like this:

> Project Folder
  > node_modules
  > src
    > app
      > entities
    > database
      > migrations
    - app.ts
    - routes.ts
    - server.ts
  - ormconfig.json
  - package.json
  - tsconfig.json

Now open the ‘ormconfig.json’ file and enter the following. Don’t forget to change the ‘password’ and the ‘database’ (this is the schema that we created):

ormconfig.json

Now we just have to instantiate our connection when we start our API. Let’s make this change at the ‘server.ts’ file:

server.ts

Creating the Entities

Now that we have our server and database up and running, we just have to create our entities. For this tutorial, I’ll be creating a Product and a Product Category table, if a relationship of one to many between them. Create both ‘Product.ts’ and ‘ProductCategory.ts’ files inside the entities folder:

ProductCategory.ts

Product.ts

We’ll always create our entities with the Entity decorator and extend it from the BaseEntity (this is optional, but it’ll be easier with it). We’re going to use the PrimaryGeneratedColumn decorator for the id, and everytime we create a column we’re going to use the Column decorator and set the type of it. We’ll also use the CreateDateColumn and UpdateDateColumn decorator for ours createdAt and updatedAt columns.

The hardest part (and it’s actually not that hard) is to set the relationships. We are going to use the ManyToOne decorator on the Product entity (Many Products to One Product Category) and the OneToMany decorator on the ProductCategory entity (One Product Category to Many Products). We’re also specifying the column that we are going to use for this relationship (inside the Product entity) using the JoinColumn decorator (this is optional, TypeORM will handle this for you if you don’t specify).

Using the Entities

Finally, we are going to use our entities to Create, Read, Update and Delete (CRUD). For this we will need to create controllers and the API routes. Each controller can have up to five functions. This is not exactly a law, but it’s a great way to keep each controller with only one responsibility. For example: if you need another function (besides store) to create a record in a different way, maybe you should consider creating another controller for it. The five functions are:

• store: Used to create a record;
• index: Used to return all records from a table;
• show: Used to return the record specified;
• update: Used to update the record specified;
• delete: Used to delete the record specified.

For each entity we are going to create a controller with five API routes. Let’s start by the controllers. Inside the app folder create a controllers folder and add the ‘ProductCategoryController.ts’ and ‘ProductController.ts’ file:

ProductController.ts

ProductCategoryController.ts

The last thing we need to do is to edit the ‘routes.ts’ file:

routes.ts

So we have our routes created, let’s just start the server and test the API. Just run the following command on your terminal.

yarn start:dev

Testing the API

To test our API I’ll be using Insomnia but you can use Postman if you prefer. Don’t forget to change ‘{id}’ with a existing id number.

So we have ten routes to test. First the Product Categories routes:

• GET | http://localhost:3333/product-categories | No body
• POST | http://localhost:3333/product-categories | { name: string }
• GET | http://localhost:3333/product-categories/{id} | No body
• PUT | http://localhost:3333/product-categories/{id} | { name: string }
• DELETE | http://localhost:3333/product-categories/{id} | No body

And the Product routes:

• GET | http://localhost:3333/products | No body
• POST | http://localhost:3333/products | { name: string, price: number, productCategoryId: number }
• GET | http://localhost:3333/products/{id} | No body
• PUT | http://localhost:3333/products/{id} | { name: string, price: number, productCategoryId: number }
• DELETE | http://localhost:3333/products/{id} | No body

Some examples of API calls

And that’s it, we have our API reading and writing to our database using TypeORM. If you had any problem or just want to compare all project files, you can check it here.