Connecting to a Database
Tags: webdev, typescript, tutorial, express
Published: 2023-03-13 04:10:18

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](https://dev.to/alcb1310/saving-your-data-j14) 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](https://typeorm.io/) 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:

```cmd
npm install prisma --save-dev
```

![Prisma installation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/68b5pdh9167oti4i535g.png)

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

```cmd
npx prisma init
```

![Prisma initialization](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ehtejd2hncvdyyv8a813.png)

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

![Directory Structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k4axr92yu4xajzbrvd5y.png)

![schema.prisma](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxktc1av2qqp3xes94ev.png)

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

![.env](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/61kqetsgak4d7udvo4l1.png)

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

![user model](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/trx5hjyxg19lstu4b5ga.png)

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

```cmd
npx prisma format
```

![Error](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y2lwjapx1qrx5jvqkiia.png)

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

![Fixed](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gufvqvghi5nf3pzthztw.png)

and run the previous command again

![Success](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6n4qxu37e6rofin795x3.png)

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

```cmd
npx prisma migrate dev
```

![Migration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s28jsugag5icva8glefy.png)

And lets give it a name to the migration to `init`

![Migration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kttguk6d20i1sewad9h6.png)

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:

![index.ts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8nz5sbjgo0f27ofu97ia.png)

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

```TypeScript
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`

![prisma.ts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eyfwpq94qne502c8xqlx.png)

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

![users.ts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zeel1ov7q1ieufbxnhbo.png)

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

```cmd
npm run build
npm run start
```

And now we can open Postman so we can test our application
![Postman success](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyljgctjk7i4jrhbxif5.png)

And if we try to create the same user again
![Postman error](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvspw0o9esaj1667kpmr.png)

Finally lets get all the users
![Postman all users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8q17od8hxhju1a5a05nn.png)

## 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