One of the most important milestones in computing was figuring out how to keep data alive after a program stops. In the beginning, both the code and the data lived in memory. If the program crashed, the data disappeared too. That’s why engineers created file systems, so data could be saved on disk and loaded again later. For small programs, this worked fine. But as systems grew (think of train schedules or payroll software) people needed better ways to store and manage data. That’s when SQL came in.

SQL is still everywhere today. It powers databases like Postgres and MySQL, but also shows up in tools like New Relic with its own version NRQL, or Salesforce with SOQL.

Common Criticisms of SQL

SQL is dying

SQL remains a backbone of data persistence in most industries.

SQL doesn’t work well with Node.js

Node.js is asynchronous by nature, while many SQL libraries are synchronous. This can create friction, but modern tools like PostgreSQL clients with pooling or asynchronous ORMs address this.

SQL is slow

Poorly written queries can be slow but that’s not SQL’s fault. With proper indexing, caching, and query design, SQL can be incredibly fast.

Free Resources to Refresh Your SQL Knowledge

• Microsoft SQL fundamentals: This is a long, theory-based course, but it provides a solid foundation for understanding SQL concepts.
• Codecademy: An interactive approach, Codecademy saved me a lot of time when I started learning how to program, same for SQL.
• W3Schools SQL Tutorial: This one is interactive but is more for consulting syntax and patterns.
• SQLBolt: Interactive and theory basis, good option.
• Khanacademy SQL: Interesting approach practice & theory focused for web development.

Should you use an ORM?

First, what’s an ORM?

ORM(Object-relational mapping) lets you work with the database using code instead of writing SQL directly. It’s helpful when you want to avoid thinking about SQL or when your data model is simple. But ORMs aren’t perfect. For complex queries or performance-heavy parts of your app, they can get in the way. They sometimes generate SQL that’s hard to control or debug.

That’s why in this series, I’ll show a SQL query approach, some folks are inclined to use a more raw SQL approach instead an ORM.

Installing PostgresSQL using Docker compose

Docker is an excellent tool to avoid bringing garbage to our computers, you can easily install any package and use it as a local service as we will use postgres. Install docker using this link guidelines Download & install Docker the suitable for OS type.

In this post I will use the PostgreSQL database to start creating our CRUD of Note entity, the docker image package is on the postgres_registry. The docker-containers will be managed with Docker Compose, which requires creating a compose.yml file.

Friendly reminder: All code snippets here are illustrative, you can find the complete code in the adaptable-backend-nodejs-persistence repository.

1
services:
2
  postgres:
3
    image: postgres:17-alpine
4
    environment:
5
      POSTGRES_PASSWORD: postgres
6
      POSTGRES_USER: postgres
7
      TZ: America/Bogota
8
      PGTZ: America/Bogota
9
    networks:
10
      - adaptable_backend_nodejs
11
    volumes:
12
      - postgres_data:/var/lib/postgresql/data
13
    ports:
14
      - 5432:5432
15
    healthcheck:
16
      test: ["CMD-SHELL", "pg_isready"]
17

18
networks:
19
  adaptable_backend_nodejs:
20

21
volumes:
22
  postgres_data:

We are defining our first service postgres which use the 17 version, set the variables POSTGRES_USER and POSTGRES_PASSWORD for credentials and TZ & PGTZ for timezone handling. Added networks to keep the application isolated, in case you have running other local services, volumes to keep the persistency even if we restart the machine, ports to forward to host machine (ours) and healthcheck command that postgres docker image provides to guarantee the service ran successfully and ready for connections.

Running the postgres service

To check if the service are working, lets add a new command to package.json scripts to make an alias and avoid type the whole command:

1
"scripts": {
2
    ...
3
  "prepare:dev": "docker compose up -d --wait",
4
},

then run:

1
npm run prepare:dev

Check if the logs are not telling any error or so, go to docker desktop interface and look for the log:

1
2025-07-25 12:16:55 2025-07-25 17:16:55.792 UTC [1] LOG:  database system is ready to accept connections

Then you are ready for start building.

Integrate PostgreSQL to our node.js project

Following the guidelines of the project, the database stuff will reside inside core/database folder with the purpose of handling all persistence layer there. To begin, install the connector library for postgres, I will use pg minimal, is good maintained and provide the majority of tools required.

1
npm i pg
2
npm i -D @types/pg

In the ER-diagram all entities have in common 3 attributes, id, created_at, and updated_at for that reason I am going to create inside core/database the BaseEntity.ts

1
export class BaseEntity {
2
  id: string | number;
3
  createdAt: Date;
4
  updatedAt: Date;
5
}

The id can be string or number to make it flexible in case you want to use a sequence for integers or string for a hash or uuid values. createdAt & updatedAt will be dates stores for metadata proposal. Then lets create the repository interface that can be implemented several technologies.

Repository pattern

For many years I’ve seen numerous debates about ORM, Doctrine, DAO and other persistence pattern, one of the most adaptable and applicable patterns for several use cases is the Repository Pattern, which basically tells the application’s controllers, “I will take care of persistence” You do your business things. That’s powerful and alleviate the entire application of handling a particular technology.

Creating the interface IRepository.ts

1
import {BaseEntity} from "@core/database/BaseEntity";
2

3
export type AutoGeneratedFields = keyof BaseEntity;
4
export type CreatePayload<T, EntitySpecificOmittedKeys extends keyof T = never> = Omit<T, AutoGeneratedFields | EntitySpecificOmittedKeys>;
5
export type UpdatePayload<T, EntitySpecificOmittedKeys extends keyof T = never> = Partial<CreatePayload<T, EntitySpecificOmittedKeys>>;
6

7
export interface IRepository<Entity extends BaseEntity> {
8
  findById(id: string | number): Promise<Entity | null>;
9
  findAll(): Promise<Entity[]>;
10
  create(data: Partial<Omit<Entity, AutoGeneratedFields>>): Promise<Entity>;
11
  update(id: string | number, data: Partial<Omit<Entity, AutoGeneratedFields>>): Promise<Entity | null>;
12
  delete(id: string | number): Promise<boolean>;
13
  closeConnection(): Promise<void>;
14
}

• AutoGeneratedFields contains the type definition of BaseEntity them are obtained using the keyof operator
• CreatePayload are defined with the generic T, which will be the Entity we will use in the application, EntitySpecificOmittedKeys will contains the definition of the entity wit the omited keys like AutoGeneratedFields for internal usage, it its assigned a Omit operator given T as the replaced generic entity and AutoGeneratedFields or another generic EntitySpecificOmittedKeys specified.
• UpdatePayload is similar to CreatePayload but it uses a Partial operator in favor an entity can not require to update the entire data.

In the interface IRepository we are creating the contract the most defined possible, typescript has their own limitations regarding OOP so I will try to stay closer to fundamentals and make the repository suitable for technologies, which does not mean we can extend in the future, that’s the beauty of good design, be extendable. The majority of methods at IRepository are suitable for a CRUD, the only rare is closeConnection(), that will be useful for testing and reduce database loads. To avoid hardcoding things lets create the databaseEngine.ts, we will add many engines as we need, our current scope is to add postgres for SQL and mongo for NoSQL.

1
export enum DatabaseEngine {
2
  NOSQL = "mongodb",
3
  SQL = "postgres"
4
}

Adding the database environment variables

As we did in compose.yml file, we need to update our .env to load that configuration, add the variables:

1
TZ=America/Bogota
2

3
# Database SQL
4
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres
5
DATABASE_ENGINE=postgres

should be match with the values present in the compose.yml environment section.

• TZ: Will be set the timezone, ideally for timestamps and synchronization present in futures posts.
• DATABASE_URL: A preparation of database url to connect with postgres locally.
• DATABASE_ENGINE: Used to discriminate between a type of database and others.

Next steps is to add it into our configuration file

1
// shorten for brevity
2
interface EnvConfig {
3
  NODE_ENV: "development" | "production" | "test";
4
  PORT: number;
5
 TZ: string;
6
 DATABASE_URL: string;
7
 DATABASE_ENGINE: string;
8
}
9

10
// shorten for brevity
11

12
  private readonly envSchema: Record<keyof EnvConfig, EnvVarConfig> = {
13
    NODE_ENV: { required: true, type: "string" },
14
    PORT: { required: true, type: "number" },
15
   DATABASE_URL: { required: true, type: "string" },
16
   DATABASE_ENGINE: { required: true, type: "string" },
17
   TZ: { required: false, type: "string", defaultValue: "America/Bogota" }
18
  };
19
...

Create the SQL setup

As we talk earlier about ORM and other patterns to create the SQL database infra exists other tools to handle schemas and tables updates commonly named as migrations which basically keep a tracking of changes and guarantee certain frictions between a release. In my opinion those migration tools like liquidbase or flyway are overkill, having them does not guarantee your migrations are running well or have frictions further, them deviated of the things that really matter: Write skilled SQL code.

I will adopt a more conservative approach without sacrifice simplicity and scalability, REAL scalability. Lets create our first migration file in raw postgres sql:

1
--- Creating table notes
2

3
BEGIN;
4

5
DO $$
6
BEGIN
7
    CREATE TABLE IF NOT EXISTS notes (
8
        id SERIAL PRIMARY KEY NOT NULL,
9
        content VARCHAR(255) NOT NULL,
10
        times_sent INT NOT NULL DEFAULT 0,
11
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
12
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
13
    );
14
    EXCEPTION
15
        WHEN OTHERS THEN
16
            ROLLBACK;
17
            RAISE;
18
END $$;
19

20
COMMIT;

I created inside DO $$/END $$; statement th declaration to create a table, using the idempotence property create the table notes if not exist. Added the required fields with the particularity of timestamps will be set using a local timezone, which will be useful for deployments reasons later. The statement also allow to catch any exception if there’s any error with the table creation, in case failure, rollback. In next articles we will deep dive into the transactions.

Running migrations

Having our first migration file, next step is to run in a database, you can use any postgres client available in the market, free or paid, your decision. For simplicity lets create a script that will read migrations folder and run each one.

1
import { readdirSync, readFileSync } from "fs";
2
import { resolve } from "path";
3
import { Client } from "pg";
4

5
import { config } from "src/core/configuration/configuration";
6

7
export class Migrate {
8
  private static MIGRATIONS_FOLDER = "src/core/database/sql/migrations";
9
  private static readonly MIGRATIONS_PATH = resolve(process.cwd(), Migrate.MIGRATIONS_FOLDER);
10

11
  static async run() {
12
    const dbConnection = new Client({
13
      connectionString: config.get("DATABASE_URL"),
14
    });
15
    console.log("Running migrations... ");
16
    const files = readdirSync(Migrate.MIGRATIONS_PATH);
17

18
    try {
19
      await dbConnection.connect();
20

21
      for (const file of files) {
22
        console.log(`Running migration: ${file}`);
23
        const fileContent = readFileSync(`${Migrate.MIGRATIONS_PATH}/${file}`, "utf8");
24
        const result = await dbConnection.query(fileContent);
25
        console.log("result query: ", result);
26
      }
27
    } catch (error) {
28
      console.error("error in migrate: ", error);
29
    } finally {
30
      await dbConnection.end();
31
    }
32
  }
33
}
34

35
Migrate.run();

We created a Migrate class and static method run() to read migrations folder, stablish connection with database passing the DATABASE_URL previously configured. It will iterate to all files sorted ascendent by default (That’s why the prefix number name 001_) and execute all of them. When finish, close the connection. For command handy easier, let’s add a new command to scripts section.

1
"scripts": {
2
    ...
3
  "prepare:dev": "docker compose up -d --wait",
4
 "migrate": "NODE_OPTIONS=--no-warnings npx ts-node -r tsconfig-paths/register src/core/database/sql/migrate.ts"
5
},

We are running the migrate.ts file without needed to transpile by using ts-node and tsconfig-paths/register to match with the imports policy.

Running npm run migrate you should not see any error, then using the client of your preference you can see the table created. I used the built-in database plugin of webstorm jetbrains editor.

Creating the seeders

You can add data as you can to store some values, but, we want to automate it as much we can, then lets replicate the idea of Migrate class but lets name it Seeder. Lets add a seeder folder to keep all sample data:

1
INSERT INTO notes (content)
2
VALUES
3
    ('The only limit to our realization of tomorrow is our doubts of today.'),
4
    ('Do what you can, with what you have, where you are.'),
5
    ('The best way to predict the future is to invent it.');

We will create the script that will run all seeders:

1
import { readdirSync, readFileSync } from "fs";
2
import { resolve } from "path";
3
import { Client } from "pg";
4

5
import { config } from "src/core/configuration/configuration";
6

7
export class Seeder {
8
  private static SEEDER_FOLDER = "src/core/database/sql/seeders";
9
  private static readonly SEEDER_PATH = resolve(process.cwd(), Seeder.SEEDER_FOLDER);
10

11
  static async run() {
12
    const dbConnection = new Client({
13
      connectionString: config.get("DATABASE_URL"),
14
    });
15
    console.log("Running seeders... ");
16
    const files = readdirSync(Seeder.SEEDER_PATH);
17

18
    try {
19

20
      await dbConnection.connect();
21

22
      for (const file of files) {
23
        console.log(`Running seeder: ${file}`);
24
        const fileContent = readFileSync(`${Seeder.SEEDER_PATH}/${file}`, "utf8");
25
        await dbConnection.query(fileContent);
26
      }
27
    } catch (error) {
28
      console.error("error seeding: ", error);
29
    } finally {
30
      await dbConnection.end();
31
    }
32
  }
33
}
34

35
Seeder.run();

then lets update the script to simplify the process of seeeding

1
"scripts": {
2
    ...
3
  "prepare:dev": "docker compose up -d --wait",
4
  "migrate": "NODE_OPTIONS=--no-warnings npx ts-node -r tsconfig-paths/register src/core/database/sql/migrate.ts",
5
 "seeder": "NODE_OPTIONS=--no-warnings npx ts-node -r tsconfig-paths/register src/core/database/sql/seeder.ts"
6
},

Running npm run seeder the note items are stored in database, using the database plugin I see the new changes:

Implementing the SQLRepository

Now we have all the infrastructure (local, when everything runs) required to start interacting with the database, abstracting the application from all complexity that implies work with database, that’s why we are isolating in a sql folder.

1
import { Client } from "pg";
2

3
import { config } from "src/core/configuration/configuration";
4
import {AutoGeneratedFields, IRepository } from "@core/database/IRepository";
5
import {BaseEntity} from "@core/database/BaseEntity";
6

7
export class SQLRepository<Entity extends BaseEntity> implements IRepository<Entity> {
8
  private readonly tableName: string;
9
  private readonly client: Client;
10

11
  constructor(tableName: string) {
12
    this.tableName = tableName;
13
    this.client = new Client({
14
      connectionString: config.get("DATABASE_URL"),
15
    });
16
    this.client.connect();
17
    this.client.on("error", (error) => {
18
      console.log("Something happens: ", error);
19
      process.exit(1);
20
    });
21
  }
22
  // shorten for brevity...
23
}

The SQLRepository will be coupled to the technology sql managed by the library pg, it implements the interface extending the generic Entity with BaseEntity, this is useful in case we need to remove/add fields, it will perform automatically. we defined 2 important attributes tableName to parametrize the name of table to write SQL and client that will handling the connection with postgres database. We receive tableName via constructor and initialize the client by getting the DATABASE_URL connection string to the client according to pg connection params. pg expose a method connect() and several EventEmitter, but our interest is on error because if the database connection fails, the entire application should fail, then we force adding exit(1), returning a error.

Write SQL code for search, create, delete or update requires special syntax where we need to specify the column names, order and type of data. As we are in a typescript stack we will receive object or DTOs from restControllers and controllers, so we need to map them into a SQL syntax, then lets create our custom mapper inside SQLRepository class:

Helper to create SQL queries

1
export type QueryPrepare = {
2
  columns: string;
3
  indexes: string;
4
  values: unknown[];
5
  lastNumberKey: number;
6
};
7

8
//shorten for brevity...
9
  private transformQueryData(data: Partial<Omit<Entity, AutoGeneratedFields>>): QueryPrepare {
10
    const keys = Object.keys(data);
11
    const values = Object.values(data);
12
    const sequence = Array.from({ length: keys.length }, (_, i) => 1 + i);
13
    const sequenceMapped = sequence.map(digit => `$${digit}`);
14
    return {
15
      columns: keys.join(","),
16
      values,
17
      indexes: sequenceMapped.join(","),
18
      lastNumberKey: keys.length,
19
    };
20
  }
21
//shorten for brevity...

The private method transformQueryData() receive a data object that explicitily has been ignored the AutoGeneratedFields that will be handling in a database layer, so that can be ignored from the queries. The method returns a QueryPrepare response which contains all fields and values mapped. Lets break down the function:

• keys: As we receive a key-value pair object, to write SQL we want each keys separated
• values: an array of all values.
• sequence: To avoid SQL injection, many libraries implement the parametrized values using binding, so the number of parameters can be generated in a sequence, find more sequences
• sequenceMapped: We just added the prefix char $ to all sequence.

Create Method

Now we have our dedicated SQL helper, now we are able to write the create() method inside SQLRepository class:

1
//shorten for brevity...
2
  async create(data: Partial<Omit<Entity, AutoGeneratedFields>>): Promise<Entity> {
3
    const queryTransformed = this.transformQueryData(data);
4
    const sql = {
5
      text: `INSERT INTO ${this.tableName}(${queryTransformed.columns}) VALUES(${queryTransformed.indexes}) RETURNING *;`,
6
      values: queryTransformed.values,
7
    };
8
    const result = await this.client.query(sql);
9
    return result.rows[0];
10
  }
11
//shorten for brevity...

The create() method receive a generic data and response a Promise wit that generic entity created, use the helper function previously created to prepare the query, we start creating the INSERT statement, following of table name, columns and values. that query is passed to the client to perform the operation and return the results, previously in the query we add the RETURNING statement to return all stored.

Find methods

The findAll() method perform to retrieve all information available of the table name.

1
//shorten for brevity...
2
  async findAll(): Promise<Entity[]> {
3
    const sql = `SELECT * FROM ${this.tableName};`
4
    const result = await this.client.query(sql);
5
    return result.rows as Entity[];
6
  }
7
//shorten for brevity...

The findById() method will look at the specific id if exists.

1
//shorten for brevity...
2
  async findById(id: string | number): Promise<Entity | null> {
3
    const sql = {
4
      text: `SELECT * FROM ${this.tableName} WHERE id=$1;`,
5
      values: [id],
6
    };
7
    const result = await this.client.query(sql);
8
    return result.rows[0];
9
  }
10
//shorten for brevity...

Update method

A SQL update requires similar work as we did at create(), at this case we should to concatenate lastIndex and id to continue the approach of avoiding SQL injection using binding parameters.

1
//shorten for brevity...
2
  async update(id: string | number, data: Partial<Omit<Entity, AutoGeneratedFields>>): Promise<Entity | null> {
3
    const queryTransformed = this.transformQueryData(data);
4
    const lastIndex = `$${queryTransformed.lastNumberKey+1}`;
5
    const sql = {
6
      text: `UPDATE ${this.tableName} SET ${queryTransformed.columns} = ${queryTransformed.indexes}, updated_at = DEFAULT WHERE id=${lastIndex} RETURNING *;`,
7
      values: queryTransformed.values.concat(id),
8
    };
9
    const result = await this.client.query(sql);
10
    return result.rows[0];
11
  }
12
//shorten for brevity...

Delete method

The delete() method includes the id to making sure we are deleting the proper information.

1
//shorten for brevity...
2
  async delete(id: string | number): Promise<boolean> {
3
    const sql = {
4
      text: `DELETE FROM ${this.tableName} WHERE id=$1 RETURNING *;`,
5
      values: [id],
6
    };
7
    const result = await this.client.query(sql);
8
    return result.rows[0] || false;
9
  }
10
//shorten for brevity...

Creating the NoteRepository

After create all infraestructure for SQL, now from the features folder lets create the NoteRepository class that will choose a technology to use (SQL, NoSQL) based in the DATABASE_ENGINE env-var we defined earlier. Here we are creating a benefit separation between the business data layer and technologies.

1
export class NoteRepository implements IRepository<Note> {
2
  private DB_COLLECTION_NAME = "notes";
3
  private repository: IRepository<Note>;
4

5
  constructor() {
6
    const dbEngine = config.get("DATABASE_ENGINE");
7

8
    if (dbEngine === DatabaseEngine.SQL) {
9
      this.repository = new SQLRepository<Note>(this.DB_COLLECTION_NAME);
10
    } else if (dbEngine === DatabaseEngine.NOSQL) {
11
      // To be implemented
12
    }
13
  }
14

15
  findAll(): Promise<Note[]> {
16
    return this.repository.findAll();
17
  }
18

19
  create(data: CreatePayload<Note, "timesSent">): Promise<Note> {
20
    return this.repository.create(data);
21
  }
22

23
  delete(id: string | number): Promise<boolean> {
24
    return this.repository.delete(id);
25
  }
26

27
  findById(id: string | number): Promise<Note | null> {
28
    return this.repository.findById(id);
29
  }
30

31
  update(id: string | number, data: UpdatePayload<Note, "timesSent">): Promise<Note | null> {
32
    return this.repository.update(id, data);
33
  }
34

35
  closeConnection(): Promise<void> {
36
    return this.repository.closeConnection();
37
  }
38
}

The NoteRepository class implement the IRepository contract we defined earlier, using in the constructor a Strategy Pattern to choose between a database engine to another, providing flexibility to use a technology or other. The “timesSent” parameter is a field we want to ignore at notes table, because will be managed by another process when the user wants to send by email in futures features.

Registering the NoteRepository

As we are using dependency injection, let’s register the NoteRepository in the container definition

1
export const container = createContainer({
2
  injectionMode: InjectionMode.CLASSIC,
3
  strict: true,
4
});
5

6
container.register({
7
  /* Controllers */
8
  noteController: asClass(NoteController).singleton(),
9

10
 /* Repositories */
11
 noteRepository: asClass(NoteRepository).singleton(),
12
});

Updating the NoteController

Now, The NoteController class is now more concrete and useful, it can interact with NoteRepository to delegate some operations. Thanks to awilix we inject the NoteRepository. Then implement all methods to complete the CRUD approach.

1
export class NoteController {
2

3
 constructor(private noteRepository: NoteRepository) {}
4

5
 public getNotes(): NoteDto[] {
6
 async getNotes(): Promise<ListNoteDto[]> {
7
   const result = await this.noteRepository.findAll();
8
   return result.map((note: Note) => {
9
     return {
10
       id: note.id, content: note.content
11
     };
12
   });
13
   return [
14
     {
15
       id: 1,
16
       content: "Hello 1 - from controller"
17
     },
18
     {
19
       id: 2,
20
       content: "Hello 2 - from controller"
21
     },
22
   ];
23
  }
24

25
  async createNote(payload: CreateNoteDto): Promise<void> {
26
    await this.noteRepository.create(payload);
27
  }
28

29
  async updateNote(id: string | number, payload: UpdateNoteDto): Promise<void> {
30
    await this.noteRepository.update(id, payload);
31
  }
32

33
  async deleteNote(id: string | number): Promise<void> {
34
    await this.noteRepository.delete(id);
35
  }
36
}

The differentiating method is getNotes() which is required to map a database entity to a specific DTO. In next articles we deep dive in mapper best practices.

Complete the NoteRestController CRUD

Before complete the CRUD lets add a common response data format that our RestAPI will use as a contract to tell to different clients (web, ios, android) the expected response object to make it predictable and follow the same convention. Lets add the responseTypes.ts file where we will add all responses types in the Rest API layer:

1
export class SuccessResponse<T> {
2
  data: T;
3

4
  constructor(data: T) {
5
    this.data = data;
6
  }
7
}

A SuccessResponse class that receive a generic T class or object that tell to clients the response has been successfully along with HTTP status 2xx. Now, lets implement the rest of methods.

1
// shorten imports for brevity
2

3
@Controller("notes")
4
export class NoteRestController {
5
  private noteController: NoteController;
6
 private static ID_PARAM_DETAIL = {
7
   name: "id",
8
   description: 'Unique identifier of note',
9
   type: String,
10
   required: true,
11
 };
12

13
  constructor() {
14
    this.noteController = container.resolve("noteController");
15
  }
16

17
  @Get()
18
  @ApiOkResponse({
19
    type: [NoteDto],
20
    description: "Get all notes",
21
  })
22
 public getNotes(): NoteDto[] {
23
 async getNotes(): Promise<SuccessResponse<ListNoteDto[]>> {
24
   return this.noteController.getNotes();
25
   const results = await this.noteController.getNotes();
26
   return new SuccessResponse(results);
27
  }
28

29
 @Post()
30
 @ApiOkResponse()
31
 async createNote(@Body() payload: CreateNoteDto): Promise<void> {
32
   await this.noteController.createNote(payload);
33
 }
34

35
 @Put(`:${NoteRestController.ID_PARAM_DETAIL.name}`)
36
 @ApiParam(NoteRestController.ID_PARAM_DETAIL)
37
 @ApiOkResponse()
38
 async updateNote(@Param(NoteRestController.ID_PARAM_DETAIL.name) id: string | number, @Body() payload: UpdateNoteDto): Promise<void> {
39
   await this.noteController.updateNote(id, payload);
40
 }
41

42
 @Delete(`:${NoteRestController.ID_PARAM_DETAIL.name}`)
43
 @ApiParam(NoteRestController.ID_PARAM_DETAIL)
44
 @ApiOkResponse()
45
 async deleteNote(@Param(NoteRestController.ID_PARAM_DETAIL.name) id: string | number): Promise<void> {
46
   await this.noteController.deleteNote(id);
47
 }
48
}

With Swagger nestjs decorators we are able to generate the proper swagger and the methods required to complete.

Testing CRUD

Finally after adding all required steps the remaining one is to run the application to see if all methods works, we will use the swagger page to test each endpoint, make sure the app is running (run npm run start:restAPI:dev) and access to /docs.

Conclusion

The persistence is a crucial part of all backend architecture, the chosen tools and type of database make bigger differences. Using best practices and classical computer science sometimes is not enough. We need to deep dive into the requirements and the needs of each project to start creating good foundations without external dependencies like ORM or Unmaintainable libraries that difficult the scalability of the backend application. Here we implement our custom ORM, everything that happens in this application is our responsibility and provide flexibility for next iterations and facilitate the switch between an engine to another thanks to interface contract and the separation of SQLRepository, in the next article we will work in switch a database engine without a dramatic refactoring session.