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
Top comments (0)