Self-paced

Explore our extensive collection of courses designed to help you master various subjects and skills. Whether you're a beginner or an advanced learner, there's something here for everyone.

Bootcamp

Learn live

Join us for our free workshops, webinars, and other events to learn more about our programs and get started on your journey to becoming a developer.

Upcoming live events

Learning library

For all the self-taught geeks out there, here is our content library with most of the learning materials we have produced throughout the years.

It makes sense to start learning by reading and watching videos about fundamentals and how things work.

Search from all Lessons


LoginGet Started
← Back to Lessons
  • node

  • typeOrm

  • REST

  • apis

  • express

Edit on Github

Connecting a Database to Our REST API

What is SQL TypeOrm?
Why Use an ORM?
  • For example:

What is SQL TypeOrm?

TypeORM is an Object-Relational Mapper / Mapping-tool, or ORM, which is a library developers use to create databases and manipulate their data without needing to know/use SQL.

Why Use an ORM?

ORMs have gained popularity because dealing directly with SQL language often requires a lot of effort. The goal of an ORM is to simplify the maintenance of your data.

Essentially, with an ORM, you won't need to write SQL again (95% of the time) and you can work with objects.

For example:

To insert a user with SQL, you would write:

1INSERT INTO user (name, last_name) VALUES ('Juan', 'McDonals');

With an ORM, your code remains familiar like this:

1user = User() 2user.name = 'Juan' 3user.last_name = 'McDonals' 4 5# add the user to the database 6user.save(); 7

You simply say: user.save(), and everything you've done with your code will translate into SQL language code.

Decorators

TypeOrm uses the design pattern called decorator to modify the behavior of a class. These decorators help us define elements of a model, like creating a column, defining if it has a primary key, etc.

To build our model, we will use the following decorators provided by TypeOrm.

  • @Entity(): Using this decorator, a class is created that maps to a database table.

  • @PrimaryGeneratedColumn(): Indicates that the column is a Primary Key and that its value should be auto-incremented. You can pass a parameter ('uuid') which makes the values in this column random text strings instead of sequential integers.

  • @Column(): A common database column. We can specify several parameters such as type (varchar, int, tinyint, decimal), whether it is mandatory (nullable: true | false), maximum length (length: int), and many more things. See Documentation

  • @CreateDateColumn() and @UpdateDateColumn(): Special decorators that indicate that the value of these columns is automatically assigned when creating a new record or updating an existing one respectively.

Reviewing the Most Typical Database Operation

Creating our database

The first step would be to define our model

1@Entity() 2export class Users extends BaseEntity{ 3 @PrimaryGeneratedColumn() 4 id: number; 5 6 @Column() 7 first_name: string; 8 9 @Column() 10 last_name: string; 11 12 @Column({unique: true}) 13 email: string; 14 15 @Column() 16 password: string; 17 18}

INSERT: Inserting a record in the database

1 2 const usersRepo = getRepository(User); 3 const user = usersRepo.create(req.body as ObjectLiteral); //I create a user 4 5 const result = await usersRepo.save(user); //I save the new user 6

SELECT: Searching or retrieving records

There are 3 ways to return data from the database: 1. Search/Retrieve/Return all records from a particular Table/Model using getRepository(MyModel).find() 2. Search/Retrieve/Return a single record based on its primary key using getRepository(MyModel).findOne() 3. Search/Retrieve/Return a group of records based on their query Person.query.filter_by(arg1=value, arg2=value, ...)

1# here is how all the people are searched 2const users = await getRepository(Users).find(); 3 4# here is how a group of people with name = alex are searched 5const users = await getRepository(Users).find("first_name":"alex"); 6 7# here is how a person with id = 3 is searched (only works with primary keys) 8user = Users.query.get(3) 9const user = await getRepository(Users).findOne(req.params.id:"3");

DELETE: Deleting a record from the database.

All you have to do is create a new Person object, add it to the database session, and commit!

1const users = await getRepository(Users).delete(ID_USER); 2

UPDATE: Updating a record.

To update, first you need to return/select the record from the database, then you can update the property you want and commit again.

1 const user = await getRepository(Users).findOne(req 2 3.params.id); //I search for the user in the table by the ID received 4 getRepository(Users).merge(user, req.body); // Merges existing data with that received through body 5 const results = await getRepository(Users).save(user); // Stores the change in the database

Relationships

One-to-one

In English one-to-one, the decorator @OneToOne is used, it is a relationship where A contains an instance of B and B contains an instance of A.

Many-to-one

In English many-to-one, the decorator @ManyToOne is used.

One-to-many

In English one-to-many, the decorator @OneToMany is used.

Many-to-many

In English many-to-many, the decorator @ManyToMany is used, it is a relationship where A contains several instances of B and B contains several instances of A.

An example is the relationship between a Question entity and a Category entity. A question can have multiple categories and each category can have multiple questions.

1import {Entity, PrimaryGeneratedColumn, Column} from "typeorm"; 2 3@Entity() 4export class Category { 5 6 @PrimaryGeneratedColumn() 7 id: number; 8 9 @Column() 10 name: string; 11 12} 13 14@Entity() 15export class Question { 16 17 @PrimaryGeneratedColumn() 18 id: number; 19 20 @Column() 21 title: string; 22 23 @Column() 24 text: string; 25 26 @ManyToMany(() => Category) 27 @JoinTable() 28 categories: Category[]; 29 30}

@JoinTable() is used to define the owning entity in the relationship. In this example, the decorator @ManyToMany must be used.

  • To store a @ManyToMany relationship: here is an example where category instances are created and then assigned as an array to the Question entity.
1const category1 = new Category(); 2category1.name = "animals"; 3await connection.manager.save(category1); 4 5const category2 = new Category(); 6category2.name = "zoo"; 7await connection.manager.save(category2); 8 9const question = new Question(); 10question.title = "dogs"; 11question.text = "who let the dogs out?"; 12question.categories = [category1, category2]; 13await connection.manager.save(question);
  • To delete a @ManyToMany relationship:

With cascades enabled, you can delete this relationship with just one save call. To remove a many-to-many relationship between two records, remove it from the corresponding field and save the record.

1const question = getRepository(Question); 2question.categories = question.categories.filter(category => { 3 category.id !== categoryToRemove.id 4}) 5await connection.manager.save(question)

Ready to Start Coding?

We have prepared this live coding example that you can run yourself on Gitpod and use as a basis for your project.

Expressjs Rest Hello: https://github.com/4GeeksAcademy/expressjs-rest-hello