Connecting to a Database

In this article we will be connecting our application to a database, there are two main ways to connect to one:

• Using raw SQL (Structured Query Language)
• Using an ORM (Object Relational Mapping)

Raw SQL

To connect using raw SQL we need to know the SQL language syntax, this work with Relational databases, which we discussed on my previous article where I discussed on selecting a database system for your project.

Advantages of using raw SQL

Some of the main advantages of using raw SQL in your project are the following

• Compatibility with the database engine you are using
• Been able to use the full features of the database engine
• Great for complex queries
• Fast results

Disadvantages of using raw SQL

Some of the main disadvantages of using raw SQL in your project are the following

• Forced to use the database engine you first selected, you can not easily change from MySQL to PostgreSQL
• Even though it is not hard to learn the basics of SQL, you will still need to learn it in order to use it
• Harder to read code

ORM

Using an ORM will make it easier to connect to a database and make queries to it

Advantages of using ORM

Some of the main advantages of using an ORM are the following

• Speeds up development by eliminating the need of repetitive SQL code
• Reduces development time
• Overcome vendor specific SQL diferences

Disadvantages of using ORM

Some of the main disadvantages of using an ORM are the following

• Need to learn to program with the ORM
• Lack of understanding of what the code is actually doing, the developer is more in control using SQL
• ORM have a tendency to be slow
• ORM fail to compete against SQL with complex queries

In summary, it's my believe that is better to use an ORM in the majority of cases, so in our project we will be implementing it.

ORM for TypeScript

There are several ORMs available to use in TypeScript, but in this article I will talk about two of them

• Prisma
• TypeORM

Primsa

Prisma is an open source type safe query builder for both TypeScript and Node.js applications that allows developers to easily manage and interact with their database

Features

Prisma is a both SQL and NoSQL oriented type of ORM with current support for PostgreSQL, MySQL, Microsoft SQLServer, SQLite, CockroachDB and MongoDB.

When it comes to features, Prisma includes the following:

• A Prisma client
• A type safe ORM that allows developers to think about how they work with their data
• A migration tool that's useful for running database migrations as the schema evolves, making it easier to deploy your database schema for both development and production time
• A GUI to play with the database data, useful for development

TypeORM

TypeORM is an open source ORM that runs in both TypeScript and Node.js that allows developers to write high quality, scalable, maintainable, production ready applications

Features

TypeORM supports MySQL, PostgreSQL, MariaDB, CockroachDB, SQLite, Microsoft SQLServer, SQL.js, Oracle, in addition to a basic MongoDB support.

In their documentation we can find a rich feature list, some of the popular ones are

• CLI
• Query caching
• Connection pool
• Decorator API with an extensive reference

Working with Prisma

For our project, we will be using Prisma, so lets install it, for that we will need to run the following command:

npm install prisma --save-dev

Finally, set up Prisma with the init command of the Prisma CLI

npx prisma init

After this we will have a prisma directory with a schema.prisma file

To change which database vendor we will be using we just need to change the provider, for this project we will be using SQLite and add the DATABASE_URL variable in the .env file. Just remember that in your .env file you will save all of the secrets and keys you will be using in your application and it should be included in your .gitignore file

After this we will be focusing on the schema.prisma file. Lets add our user model.

We can run the following command to format our schema.primsa file and validate that we have a correct schema

npx prisma format

So let's change the type of the id to be an integer

and run the previous command again

Next step is to run a migration to create the database and it's schema represented by our model, for that we need to run the following command

npx prisma migrate dev

And lets give it a name to the migration to init

This will do two main things, first it will create the database and schema and also will generate the prisma client we will be using to connect to the database.

By running npx prisma studio we will be accessing a GUI interface to interact with our database, but we are more interested in using our code base to interact with it, so let's add some routes to do it.

So lets start by modifying our src/index.ts file so we can separate our logic into files:

here we deleted the GET request we had earlier and added:

import router from "./routes/users"
app.use(express.json()) // this allows to the express route to receive JSON as the body
app.use(router) // This will connect to the users routes we will generate

next we have to create the connection to the prisma client, this has to be done once on the application, so we added the following code to src/db/prisma.ts

Finally we create the src/routes/users.ts with the following code

Now with all of that done, we need to start our server

npm run build
npm run start

And now we can open Postman so we can test our application

And if we try to create the same user again

Finally lets get all the users

Conclusions

In today's post we learned about adding an ORM to our application and more specific adding Prisma, this way we can talk to our database from the Express TypeScript Server