Skip to content

Latest commit

 

History

History
430 lines (316 loc) · 11.5 KB

02.1-backend-database.md

File metadata and controls

430 lines (316 loc) · 11.5 KB
Raw

Backend - Database

DataBase Abstractions

In order of level of abstraction, where 1 is the low level and 3 the high level.

  1. Native driver: allows to write SQL queries directly.
  2. Query Builder: allows to write SQL queries with a JavaScript abstraction.
  3. ORM - Object-Relational Mapping: creates a relation between the application's models and the database tables.

It's recommended to use the ORM.

docker

  • creates an isolated environment (container).
  • containers expose some ports for communications.

Installation instructions: https://docs.docker.com/engine/install/ubuntu/

concepts:

  • Image: is a service offered via docker
  • Container: is an instance of an image.
  • Docker Registry (Docker Hub): is a centralized service where docker images are stored.
  • Dockerfile - Image Recipe: a script that starts a container.

Creating a PostgreSQL Container

Before installing a postgresql container, check if you already have one available.

Basic docker commands:

docker ps                   # list active containers
docker ps -a                # list available containers in your machine
docker start containerName  # starts containerName
docker stop containerName   # stops containerName
docker logs containerName   # show containerName logs
docker run                  # runs a process in a new container

# by the way, here's a command to check if a specific port is open:
sudo lsof -i :portNumber

Installing a PostgreSQL container:

docker run --name gostack_postgresql -e POSTGRES_PASSWORD=docker -p 5432:5432 -d postgres
#       --name=""
#          Assign a name to the container
#       -e, --env=[]
#          Set environment variables
#       -p, --publish ip:[hostPort]:containerPort | [hostPort:]containerPort
#          Publish a container's port, or range of ports, to the host.
#       -d, --detach=true|false
#          Detached mode: run the container in the background and print the new container ID. The default is false.

Note: in the option -p, the first number is the port of the "real" machine, and the number after : is the container's port.

DB Clients

Configuring TypeORM

TypeORM website: https://typeorm.io/

yarn add typeorm pg # pg for postgresql driver

Create a database named gostack_gobarber (using dbeaver or something).

ormconfig.json:

{
  "type": "postgres",
  "host": "localhost", // host of your database
  "port": 5432,
  "username": "postgres",
  "password": "docker",
  "database": "gostack_gobarber"
}

src/database/index.ts:

import { createConnection } from 'typeorm';

createConnection();

src/server.ts:

import express from 'express';
import routes from './routes';

import './database'; // <-- this is enough to connect to the DB

const app = express();

app.use(express.json());
app.use(routes); // <-- real logic happens here

app.listen(3333, () => console.log('server started'));

Creating the Appointments Table

  1. add migrations[] and cli.migrationsDir to ormconfig.json.
  2. add scripts.typeorm to package.json.
# create a migration
yarn typeorm migration:create -n CreateAppointments

src/database/migrations/*-CreateAppointments.ts.

import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export default class CreateAppointments
  implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'appointments',
        columns: [
          {
            name: 'id',
            type: 'varchar', // in a later lesson it's changed to 'uuid'
            isPrimary: true,
            generationStrategy: 'uuid',
            default: 'uuid_generate_v4()',
          },
          {
            name: 'provider',
            type: 'varchar',
          },
          {
            name: 'date',
            type: 'timestamp with time zone',
          },
        ],
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('appointments');
  }
}
# run the migrations
yarn typeorm migration:run

IMPORTANT: if while running the migration you receive this error:

QueryFailedError: function uuid_generate_v4() does not exist

Then run this command inside PostgreSQL, in the gostack_gobarber database:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

# create a migration
yarn typeorm migration:create -n CreateAppointments

# run the migrations
yarn typeorm migration:run

# revert the migrations
yarn typeorm migration:revert

# show the executed migrations
yarn typeorm migration:show

Why migrations? - To assure the database is equal to every developer.

IMPORTANT!!!: You can only change a migration BEFORE commiting it to your version control system (git). Otherwise, create a new migration changing the table(s) the you way you need.

If wanted, disable the eslint's class-method-use-this rule:

.eslintrc.json:

{
  // ...
  "rules": {
    "class-methods-use-this": "off",
    // ...
  }
  // ...
}

Creating the Appointment Model

Using the Entity from the typeorm package.

In tsconfig.json, enable experimentalDecorators e emitDecoratorMetadata, and disable strictPropertyInitialization.

When using Entity there's no need to use the model's constructor.

src/models/Appointments.ts:

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity('appointments')
class Appointment {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  provider: string;

  @Column('timestamp with time zone')
  date: Date;
}

export default Appointment;

TypeORM Repositories

In order to use decorators, TypeORM requires you to use the following package:

yarn add reflect-metadata

And in the src/server.ts:

import 'reflect-metadata';

Also add entities[] to ormconfig.json.

Most of the repositories methods (such as constructor(), all(), create()) are already provided by the TypeORM. We just need to extend the Repository class.

src/repositories/AppointmentsRepository.ts:

import { EntityRepository, Repository } from 'typeorm';

import Appointment from '../models/Appointment';

@EntityRepository(Appointment)
class AppointmentsRepository extends Repository<Appointment> {
  public async findByDate(date: Date): Promise<Appointment | null> {
    const findAppointment = await this.findOne({
      where: { date }, // `SELECT * FROM appointments WHERE date = '${date}';`
    }); // test the getSql(), looks useful as a learning tool

    return findAppointment || null;
  }
}

export default AppointmentsRepository;

After changing the AppointmentsRepository, it's needed to adapt the Service and the Route accordingly.

Summary

docker run --name gostack_postgresql -e POSTGRES_PASSWORD=docker -p 5432:5432 -d postgres

# Basic docker commands:
docker ps                   # list active containers
docker ps -a                # list available containers in your machine
docker start containerName  # starts containerName
docker stop containerName   # stops containerName
docker logs containerName   # show containerName logs
docker run                  # runs a process in a new container

  • install a DB client (recommended: DBeaver)

  • create the gostack_gobarber database in the postgresql (via DB client).

  • install typeorm and other dependencies

yarn add typeorm pg reflect-metadata
  • add typeorm to the packages.json:
"scripts": {
  "typeorm": "ts-node-dev ./node_modules/typeorm/cli.js"
}
  • ormconfig.json:
{
  "type": "postgres",
  "host": "localhost", // host of your database
  "port": 5432,
  "username": "postgres",
  "password": "docker",
  "database": "gostack_gobarber",
  "entities": [
    "./src/models/*.ts"
  ],
  "migrations": [
    "./src/database/migrations/*.ts"
  ],
  "cli": {
    "migrationsDir": "./src/database/migrations"
  }
}
  • src/database/index.ts for connection to the database:
import { createConnection } from 'typeorm';

createConnection();
  • in src/server.ts:
import 'reflect-metadata';
// ...
import './database';
// ...
  • create the CreateAppointments migration:
yarn typeorm migration:create -n CreateAppointments
  • edit the src/database/migrations/*CreateAppointments.ts migration like this and then run it:
yarn typeorm migration:run

  • if needed, add the rule "class-methods-use-this": "off" to .eslintrc.json.

  • in tsconfig.json be sure to have experimentalDecorators and emitDecoratorMetadata as true, and strictPropertyInitialization as false.

  • create/edit the model src/models/Appointments.ts (using Entity):

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity('appointments')
class Appointment {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  provider: string;

  @Column('timestamp with time zone')
  date: Date;
}

export default Appointment;
  • create/edit the repository src/repositories/AppointmentsRepository.ts:
import { EntityRepository, Repository } from 'typeorm';

import Appointment from '../models/Appointment';

@EntityRepository(Appointment)
class AppointmentsRepository extends Repository<Appointment> {
  public async findByDate(date: Date): Promise<Appointment | null> {
    const findAppointment = await this.findOne({
      where: { date }, // `SELECT * FROM appointments WHERE date = '${date}';`
    });

    return findAppointment || null;
  }
}

export default AppointmentsRepository;

  • Adapt src/services/CreateAppointmentService.ts using the repository.

  • Adapt src/routes/appointments.routes.ts using the repository.

  • Test the application with insomnia.

    • Create some appointments.
    • List all appointments.
    • Turn off the server and then launch it again, and check if the previously created appointments persisted.

My GoBarber codebase up to this point

https://github.com/meleu/gobarber/tree/59fc063f4bac19803731253851cbaeb47a895918