Data access: drivers and ORMs — Data, ORMs and layered architecture

Talking to the database

A server almost never keeps data in memory: it persists it in a database (PostgreSQL, MySQL, SQLite...). To talk to it, Node uses a driver: a library that opens connections and sends SQL queries.

Connection pools

Opening a connection is expensive. That is why you do not open one per request, but instead keep a pool (a set of reusable connections). Each query borrows a connection from the pool and returns it when done:

import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

Async queries

Talking to the database is an I/O operation: it takes time and must not block the thread. That is why queries are asynchronous and are awaited with async/await:

async function findUser(id) {
  const { rows } = await pool.query(
    "SELECT * FROM users WHERE id = $1",
    [id]
  );
  return rows[0];
}

Notice $1 and the array [id]: they are parameters. Never concatenate values into the SQL (risk of SQL injection); pass them as parameters.

ORMs

Writing SQL by hand is flexible but verbose. An ORM (Object-Relational Mapper) translates between tables and objects in your language. The most used in Node are Prisma, Sequelize and TypeORM. They bring three ideas:

Models: you describe each entity once (fields and types) and the ORM generates the access. With TypeScript, queries become typed.
Migrations: schema changes (create/alter tables) are versioned as reproducible files, instead of touching the database by hand.
Typed queries: instead of SQL as text, you call methods:

// Prisma style (conceptual)
const user = await prisma.user.findUnique({ where: { id } });
const created = await prisma.user.create({ data: { email } });

The ORM is still asynchronous underneath: everything returns a Promise. An ORM is convenience and type safety; for very specific queries you can always drop down to the driver's SQL.