MySQL #

MySQL is the most widely used relational database in the world, and integrating it with TypeScript brings significant advantages: type safety helps you catch type mismatches between the database schema and application code long before runtime. The library of choice for TypeScript is mysql2 — the successor to mysql offering a native Promise API, prepared statement support, and much better performance. Understanding how the connection pool works, the importance of parameterized queries to prevent SQL injection, and how to define TypeScript types representing query result rows is the foundation for building a safe, maintainable database layer.

Installation and Setup #

The main package needed is mysql2 along with its type definitions. mysql2 already includes built-in TypeScript types, but for the best experience you need to make sure the TypeScript project is configured correctly.

# Install the package
npm install mysql2

# mysql2 already includes @types, no separate installation needed
# Make sure tsconfig.json has the right configuration

// tsconfig.json — minimal recommended configuration
{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "lib": ["ES2020"],
    "strict": true,
    "esModuleInterop": true,
    "outDir": "./dist",
    "rootDir": "./src"
  }
}

The recommended project structure for good separation of concerns:

src/
  ├── db/
  │   ├── connection.ts      -- pool configuration and singleton
  │   ├── migrations/        -- schema migration files
  │   └── seeds/             -- initial data for development
  ├── models/
  │   ├── user.model.ts      -- types and queries for the users table
  │   └── product.model.ts
  ├── repositories/
  │   ├── user.repository.ts -- data access abstraction
  │   └── product.repository.ts
  └── index.ts

Single Connection vs Connection Pool #

There are two main ways to connect to MySQL: a single connection and a connection pool. For production applications, a connection pool is almost always the right choice.

import mysql, { Connection, Pool, PoolConnection } from 'mysql2/promise';

// ── Single connection — only for one-off scripts or testing
async function koneksiTunggal(): Promise<void> {
  const conn: Connection = await mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'secret',
    database: 'toko_online',
  });

  try {
    const [rows] = await conn.query('SELECT 1 + 1 AS hasil');
    console.log(rows);
  } finally {
    await conn.end();  // REQUIRED: always close the connection
  }
}

// ── Connection pool — for server/API applications
const pool: Pool = mysql.createPool({
  host: process.env.DB_HOST ?? 'localhost',
  port: Number(process.env.DB_PORT ?? 3306),
  user: process.env.DB_USER ?? 'root',
  password: process.env.DB_PASSWORD ?? '',
  database: process.env.DB_NAME ?? 'toko_online',
  waitForConnections: true,
  connectionLimit: 10,        // maximum simultaneous connections
  queueLimit: 0,              // 0 = unlimited queue
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

flowchart TD
    A[App needs a query] --> B{Pool has an\nidle connection?}
    B -- Yes --> C[Take a connection\nfrom the pool]
    B -- No --> D{Connection count\n< connectionLimit?}
    D -- Yes --> E[Create a new\nconnection to MySQL]
    D -- No --> F[Enter the queue\n and wait]
    F --> G{A connection\nfinished?}
    G -- Yes --> C
    C --> H[Execute the query]
    E --> H
    H --> I[Return the connection\nto the pool]
    I --> J[Ready for the\nnext request]

Singleton pattern to ensure the pool is only created once across the application:

// src/db/connection.ts
import mysql, { Pool } from 'mysql2/promise';

let poolInstance: Pool | null = null;

export function getPool(): Pool {
  if (!poolInstance) {
    poolInstance = mysql.createPool({
      host: process.env.DB_HOST ?? 'localhost',
      port: Number(process.env.DB_PORT ?? 3306),
      user: process.env.DB_USER ?? 'root',
      password: process.env.DB_PASSWORD ?? '',
      database: process.env.DB_NAME ?? 'app_db',
      waitForConnections: true,
      connectionLimit: 10,
      timezone: '+07:00',  // WIB — important for timestamps
    });
  }
  return poolInstance;
}

export async function closePool(): Promise<void> {
  if (poolInstance) {
    await poolInstance.end();
    poolInstance = null;
  }
}

Don’t forget to call pool.end() when the application shuts down. Connections that aren’t closed prevent the Node.js process from stopping and exhaust connections on the MySQL server side. Add handlers for SIGTERM and SIGINT in your application’s entry point.

Defining Types for Database Rows #

One of the main advantages of using TypeScript with MySQL is the ability to define types that represent rows from a table. This lets the compiler catch column access errors before runtime.

import { RowDataPacket, ResultSetHeader } from 'mysql2';

// Type definition for the users table
// Extends RowDataPacket to be compatible with the mysql2 return type
interface User extends RowDataPacket {
  id: number;
  nama: string;
  email: string;
  password_hash: string;
  role: 'admin' | 'user' | 'moderator';
  aktif: boolean;
  dibuat_pada: Date;
  diperbarui_pada: Date;
}

interface Produk extends RowDataPacket {
  id: number;
  nama: string;
  deskripsi: string | null;
  harga: number;
  stok: number;
  kategori_id: number;
  gambar_url: string | null;
  dibuat_pada: Date;
}

// Type for INSERT/UPDATE/DELETE operations
// ResultSetHeader contains insertId, affectedRows, etc.
interface HasilInsert extends ResultSetHeader {
  insertId: number;
  affectedRows: number;
}

// Using the types when querying
import { getPool } from './db/connection';

async function cariUserById(id: number): Promise<User | null> {
  const pool = getPool();
  const [rows] = await pool.query<User[]>(
    'SELECT * FROM users WHERE id = ? AND aktif = true',
    [id]
  );
  return rows[0] ?? null;
}

// TypeScript now knows rows[0] is a User
// Accessing a nonexistent column will error at compile time
const user = await cariUserById(1);
if (user) {
  console.log(user.nama);   // ✓ TypeScript knows this is a string
  console.log(user.umur);   // ✗ Compile error: 'umur' doesn't exist on User
}

Basic Queries — SELECT #

Reading data from MySQL uses pool.query() or pool.execute(). The key difference: execute() uses server-side prepared statements, which are safer and more efficient for queries executed repeatedly.

import { getPool } from './db/connection';
import { User, Produk } from './types/db';

// ── SELECT all rows
async function semuaUser(): Promise<User[]> {
  const pool = getPool();
  const [rows] = await pool.query<User[]>('SELECT id, nama, email, role FROM users');
  return rows;
}

// ── SELECT with a condition — ALWAYS use parameterized queries!
async function cariUserByEmail(email: string): Promise<User | null> {
  const pool = getPool();
  const [rows] = await pool.execute<User[]>(
    'SELECT * FROM users WHERE email = ? AND aktif = true',
    [email]
  );
  return rows[0] ?? null;
}

// ── SELECT with multiple parameters
async function produkByKategoriDanHarga(
  kategoriId: number,
  hargaMin: number,
  hargaMax: number
): Promise<Produk[]> {
  const pool = getPool();
  const [rows] = await pool.execute<Produk[]>(
    `SELECT * FROM produk
     WHERE kategori_id = ?
       AND harga BETWEEN ? AND ?
       AND stok > 0
     ORDER BY harga ASC`,
    [kategoriId, hargaMin, hargaMax]
  );
  return rows;
}

// ── SELECT with LIKE — needs manual escaping of wildcard characters
async function cariProdukByNama(keyword: string): Promise<Produk[]> {
  const pool = getPool();
  // Escape % and _ so they aren't interpreted as wildcards
  const safeKeyword = keyword.replace(/[%_\\]/g, '\\$&');
  const [rows] = await pool.execute<Produk[]>(
    'SELECT * FROM produk WHERE nama LIKE ? LIMIT 20',
    [`%${safeKeyword}%`]
  );
  return rows;
}

// ── SELECT with IN — for a list of IDs
async function produkByIds(ids: number[]): Promise<Produk[]> {
  if (ids.length === 0) return [];
  const pool = getPool();
  const placeholders = ids.map(() => '?').join(', ');
  const [rows] = await pool.execute<Produk[]>(
    `SELECT * FROM produk WHERE id IN (${placeholders})`,
    ids
  );
  return rows;
}

// ANTI-PATTERN: direct string interpolation — VULNERABLE TO SQL INJECTION!
async function cariUserTidakAman(email: string): Promise<User[]> {
  const pool = getPool();
  // If email = "' OR '1'='1", the query gets broken into!
  const [rows] = await pool.query<User[]>(
    `SELECT * FROM users WHERE email = '${email}'`  // DON'T!
  );
  return rows;
}

// CORRECT: always use parameterized queries
async function cariUserAman(email: string): Promise<User | null> {
  const pool = getPool();
  const [rows] = await pool.execute<User[]>(
    'SELECT * FROM users WHERE email = ?',  // ? is the placeholder
    [email]  // the value is passed separately, can't become SQL
  );
  return rows[0] ?? null;
}

INSERT Operations #

Inserting new data into MySQL and getting the newly created ID.

import { ResultSetHeader } from 'mysql2';
import { getPool } from './db/connection';

interface InputUser {
  nama: string;
  email: string;
  passwordHash: string;
  role?: 'admin' | 'user' | 'moderator';
}

// ── Single INSERT
async function buatUser(input: InputUser): Promise<number> {
  const pool = getPool();
  const [result] = await pool.execute<ResultSetHeader>(
    `INSERT INTO users (nama, email, password_hash, role, aktif, dibuat_pada)
     VALUES (?, ?, ?, ?, true, NOW())`,
    [input.nama, input.email, input.passwordHash, input.role ?? 'user']
  );
  return result.insertId;  // the newly created auto-increment ID
}

// ── INSERT with a direct object (more concise)
async function buatProduk(produk: Omit<Produk, 'id' | 'dibuat_pada'>): Promise<number> {
  const pool = getPool();
  const [result] = await pool.execute<ResultSetHeader>(
    'INSERT INTO produk SET ?',
    [produk]
  );
  return result.insertId;
}

// ── Batch INSERT — insert many rows at once
async function buatBanyakProduk(
  produkList: Array<{ nama: string; harga: number; stok: number; kategoriId: number }>
): Promise<number> {
  if (produkList.length === 0) return 0;
  const pool = getPool();

  const values = produkList.map(p => [p.nama, p.harga, p.stok, p.kategoriId]);
  const [result] = await pool.query<ResultSetHeader>(
    'INSERT INTO produk (nama, harga, stok, kategori_id) VALUES ?',
    [values]
  );
  return result.affectedRows;
}

sequenceDiagram
    participant App
    participant Pool
    participant MySQL

    App->>Pool: execute(INSERT, [params])
    Pool->>MySQL: prepared statement
    MySQL-->>Pool: ResultSetHeader
    Pool-->>App: [ResultSetHeader, fields]
    App->>App: result.insertId
    App->>App: result.affectedRows

UPDATE and DELETE Operations #

Data modification operations return a ResultSetHeader containing information about how many rows were affected.

import { ResultSetHeader } from 'mysql2';
import { getPool } from './db/connection';

// ── Single UPDATE by ID
async function updateUser(
  id: number,
  data: Partial<Pick<User, 'nama' | 'email' | 'role'>>
): Promise<boolean> {
  const pool = getPool();

  // Build the SET clause dynamically from the object
  const fields = Object.keys(data) as Array<keyof typeof data>;
  if (fields.length === 0) return false;

  const setClauses = fields.map(f => `${f} = ?`).join(', ');
  const values = fields.map(f => data[f]);

  const [result] = await pool.execute<ResultSetHeader>(
    `UPDATE users SET ${setClauses}, diperbarui_pada = NOW() WHERE id = ?`,
    [...values, id]
  );
  return result.affectedRows > 0;
}

// ── UPDATE with multiple conditions
async function nonaktifkanUserLama(hariTidakAktif: number): Promise<number> {
  const pool = getPool();
  const [result] = await pool.execute<ResultSetHeader>(
    `UPDATE users
     SET aktif = false, diperbarui_pada = NOW()
     WHERE aktif = true
       AND diperbarui_pada < DATE_SUB(NOW(), INTERVAL ? DAY)`,
    [hariTidakAktif]
  );
  return result.affectedRows;
}

// ── DELETE — hard delete
async function hapusUser(id: number): Promise<boolean> {
  const pool = getPool();
  const [result] = await pool.execute<ResultSetHeader>(
    'DELETE FROM users WHERE id = ?',
    [id]
  );
  return result.affectedRows > 0;
}

// ── Soft delete — safer for production data
async function softDeleteUser(id: number): Promise<boolean> {
  const pool = getPool();
  const [result] = await pool.execute<ResultSetHeader>(
    'UPDATE users SET aktif = false, dihapus_pada = NOW() WHERE id = ?',
    [id]
  );
  return result.affectedRows > 0;
}

Always check affectedRows after UPDATE or DELETE. A value of 0 doesn’t always mean an error — it could mean the record wasn’t found or the WHERE condition didn’t match. Distinguish between “record doesn’t exist” and “database error” by checking affectedRows before returning a response to the client.

Transactions #

Transactions ensure a series of database operations execute atomically — either all succeed or all are rolled back. This is crucial for operations involving multiple tables at once.

import { PoolConnection } from 'mysql2/promise';
import { getPool } from './db/connection';

interface ItemPesanan {
  produkId: number;
  jumlah: number;
  hargaSatuan: number;
}

async function buatPesanan(
  userId: number,
  items: ItemPesanan[]
): Promise<number> {
  const pool = getPool();
  const conn: PoolConnection = await pool.getConnection();

  try {
    await conn.beginTransaction();

    // 1. Create the order record
    const totalHarga = items.reduce(
      (sum, item) => sum + item.hargaSatuan * item.jumlah, 0
    );

    const [hasilPesanan] = await conn.execute<ResultSetHeader>(
      'INSERT INTO pesanan (user_id, total_harga, status) VALUES (?, ?, ?)',
      [userId, totalHarga, 'pending']
    );
    const pesananId = hasilPesanan.insertId;

    // 2. Insert the order items
    for (const item of items) {
      await conn.execute(
        'INSERT INTO pesanan_item (pesanan_id, produk_id, jumlah, harga_satuan) VALUES (?, ?, ?, ?)',
        [pesananId, item.produkId, item.jumlah, item.hargaSatuan]
      );

      // 3. Decrease the product stock
      const [hasilUpdate] = await conn.execute<ResultSetHeader>(
        'UPDATE produk SET stok = stok - ? WHERE id = ? AND stok >= ?',
        [item.jumlah, item.produkId, item.jumlah]
      );

      // If stock is insufficient, roll back the whole transaction
      if (hasilUpdate.affectedRows === 0) {
        await conn.rollback();
        throw new Error(`Stok tidak cukup untuk produk ID ${item.produkId}`);
      }
    }

    await conn.commit();
    return pesananId;

  } catch (error) {
    await conn.rollback();
    throw error;  // re-throw so the caller can handle it
  } finally {
    conn.release();  // REQUIRED: return the connection to the pool
  }
}

flowchart TD
    A[Start transaction\nconn.beginTransaction] --> B[INSERT pesanan]
    B --> C[Loop items]
    C --> D[INSERT pesanan_item]
    D --> E[UPDATE product stock]
    E --> F{affectedRows > 0?}
    F -- No --> G[conn.rollback\nThrow Error]
    F -- Yes --> H{More\nitems?}
    H -- Yes --> C
    H -- No --> I[conn.commit]
    G --> J[conn.release]
    I --> J
    J --> K[Done]

Helper pattern to simplify transaction usage:

// Generic transaction helper — reduces boilerplate
async function withTransaction<T>(
  callback: (conn: PoolConnection) => Promise<T>
): Promise<T> {
  const pool = getPool();
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    const result = await callback(conn);
    await conn.commit();
    return result;
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    conn.release();
  }
}

// Usage — far cleaner
const pesananId = await withTransaction(async (conn) => {
  const [r1] = await conn.execute<ResultSetHeader>(
    'INSERT INTO pesanan (user_id, total_harga) VALUES (?, ?)',
    [userId, total]
  );
  await conn.execute(
    'INSERT INTO pesanan_item (pesanan_id, produk_id) VALUES (?, ?)',
    [r1.insertId, produkId]
  );
  return r1.insertId;
});

Error Handling #

Database operations can fail for various reasons. Correct error handling helps distinguish between recoverable and non-recoverable errors.

import { QueryError } from 'mysql2';

// The most common MySQL error codes
const MYSQL_ERRORS = {
  ER_DUP_ENTRY: 1062,          // UNIQUE constraint violation
  ER_NO_REFERENCED_ROW_2: 1452, // Foreign key violation (INSERT)
  ER_ROW_IS_REFERENCED_2: 1451, // Foreign key violation (DELETE)
  ER_DATA_TOO_LONG: 1406,       // Data too long for the column
  ER_BAD_NULL_ERROR: 1048,      // NULL on a NOT NULL column
  ER_LOCK_DEADLOCK: 1213,       // Deadlock
  ER_LOCK_WAIT_TIMEOUT: 1205,   // Lock wait timeout
} as const;

// Custom error classes for clearer domains
class DatabaseError extends Error {
  constructor(
    message: string,
    public readonly code?: number,
    public readonly sqlState?: string
  ) {
    super(message);
    this.name = 'DatabaseError';
  }
}

class DuplicateEntryError extends DatabaseError {
  constructor(field: string) {
    super(`Duplikat: nilai pada field '${field}' sudah digunakan`);
    this.name = 'DuplicateEntryError';
  }
}

class ForeignKeyError extends DatabaseError {
  constructor(message: string) {
    super(message);
    this.name = 'ForeignKeyError';
  }
}

// Helper function to map MySQL errors to domain errors
function tanganiMysqlError(error: unknown): never {
  if (error instanceof Error && 'errno' in error) {
    const mysqlError = error as QueryError;
    switch (mysqlError.errno) {
      case MYSQL_ERRORS.ER_DUP_ENTRY: {
        // Extract the field name from the MySQL error message
        const match = mysqlError.message.match(/key '(.+?)'/);
        throw new DuplicateEntryError(match?.[1] ?? 'unknown');
      }
      case MYSQL_ERRORS.ER_NO_REFERENCED_ROW_2:
        throw new ForeignKeyError('Referensi ke record yang tidak ada');
      case MYSQL_ERRORS.ER_ROW_IS_REFERENCED_2:
        throw new ForeignKeyError('Record tidak bisa dihapus karena direferensikan');
      case MYSQL_ERRORS.ER_LOCK_DEADLOCK:
        throw new DatabaseError('Deadlock terdeteksi, coba ulang transaksi', mysqlError.errno);
    }
  }
  throw new DatabaseError(`Error database tidak dikenal: ${String(error)}`);
}

// Usage in a repository
async function daftarUser(input: InputUser): Promise<number> {
  try {
    const pool = getPool();
    const [result] = await pool.execute<ResultSetHeader>(
      'INSERT INTO users (nama, email, password_hash) VALUES (?, ?, ?)',
      [input.nama, input.email, input.passwordHash]
    );
    return result.insertId;
  } catch (error) {
    tanganiMysqlError(error);
  }
}

The Repository Pattern #

Wrapping all database access in a Repository class provides an abstraction that makes testing easier, reduces query duplication, and separates business logic from database details.

// src/repositories/user.repository.ts
import { ResultSetHeader } from 'mysql2';
import { getPool } from '../db/connection';

export interface BuatUserInput {
  nama: string;
  email: string;
  passwordHash: string;
  role?: 'admin' | 'user';
}

export interface FilterUser {
  role?: string;
  aktif?: boolean;
  limit?: number;
  offset?: number;
}

export class UserRepository {
  async findById(id: number): Promise<User | null> {
    const [rows] = await getPool().execute<User[]>(
      'SELECT * FROM users WHERE id = ? AND aktif = true',
      [id]
    );
    return rows[0] ?? null;
  }

  async findByEmail(email: string): Promise<User | null> {
    const [rows] = await getPool().execute<User[]>(
      'SELECT * FROM users WHERE email = ?',
      [email]
    );
    return rows[0] ?? null;
  }

  async findAll(filter: FilterUser = {}): Promise<User[]> {
    const { role, aktif = true, limit = 20, offset = 0 } = filter;
    const conditions: string[] = ['1 = 1'];
    const params: unknown[] = [];

    if (role !== undefined) {
      conditions.push('role = ?');
      params.push(role);
    }
    conditions.push('aktif = ?');
    params.push(aktif);
    params.push(limit, offset);

    const [rows] = await getPool().execute<User[]>(
      `SELECT id, nama, email, role, dibuat_pada
       FROM users
       WHERE ${conditions.join(' AND ')}
       ORDER BY dibuat_pada DESC
       LIMIT ? OFFSET ?`,
      params
    );
    return rows;
  }

  async create(input: BuatUserInput): Promise<number> {
    const [result] = await getPool().execute<ResultSetHeader>(
      'INSERT INTO users (nama, email, password_hash, role) VALUES (?, ?, ?, ?)',
      [input.nama, input.email, input.passwordHash, input.role ?? 'user']
    );
    return result.insertId;
  }

  async update(id: number, data: Partial<Pick<User, 'nama' | 'email'>>): Promise<boolean> {
    const entries = Object.entries(data).filter(([, v]) => v !== undefined);
    if (entries.length === 0) return false;

    const setClauses = entries.map(([k]) => `${k} = ?`).join(', ');
    const values = entries.map(([, v]) => v);

    const [result] = await getPool().execute<ResultSetHeader>(
      `UPDATE users SET ${setClauses}, diperbarui_pada = NOW() WHERE id = ?`,
      [...values, id]
    );
    return result.affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const [result] = await getPool().execute<ResultSetHeader>(
      'UPDATE users SET aktif = false WHERE id = ?',
      [id]
    );
    return result.affectedRows > 0;
  }

  async count(aktif = true): Promise<number> {
    interface CountRow extends RowDataPacket { total: number }
    const [rows] = await getPool().execute<CountRow[]>(
      'SELECT COUNT(*) AS total FROM users WHERE aktif = ?',
      [aktif]
    );
    return rows[0].total;
  }
}

// Use as a singleton
export const userRepo = new UserRepository();

Safe Pagination and Sorting #

Dynamic pagination and sorting need extra attention because column names can’t be parameterized — they must be manually validated to prevent SQL injection.

type SortOrder = 'ASC' | 'DESC';
type UserSortableField = 'nama' | 'email' | 'dibuat_pada';

// Whitelist of columns allowed to be sorted
const ALLOWED_SORT_FIELDS: Set<UserSortableField> = new Set([
  'nama', 'email', 'dibuat_pada'
]);

interface PaginasiParams {
  halaman: number;
  perHalaman: number;
  sortBy?: UserSortableField;
  sortOrder?: SortOrder;
}

interface PaginasiResult<T> {
  data: T[];
  total: number;
  halaman: number;
  perHalaman: number;
  totalHalaman: number;
}

async function getUserPaginasi(
  params: PaginasiParams
): Promise<PaginasiResult<User>> {
  const {
    halaman = 1,
    perHalaman = 20,
    sortBy = 'dibuat_pada',
    sortOrder = 'DESC'
  } = params;

  // REQUIRED: validate the column name from the whitelist
  const safeSort = ALLOWED_SORT_FIELDS.has(sortBy) ? sortBy : 'dibuat_pada';
  // REQUIRED: validate the sort order
  const safeOrder: SortOrder = sortOrder === 'ASC' ? 'ASC' : 'DESC';

  const offset = (halaman - 1) * perHalaman;
  const pool = getPool();

  const [[{ total }], [rows]] = await Promise.all([
    pool.execute<Array<{ total: number } & RowDataPacket>>(
      'SELECT COUNT(*) AS total FROM users WHERE aktif = true'
    ),
    pool.execute<User[]>(
      // safeSort is validated from the whitelist — safe to interpolate
      `SELECT id, nama, email, role, dibuat_pada
       FROM users
       WHERE aktif = true
       ORDER BY ${safeSort} ${safeOrder}
       LIMIT ? OFFSET ?`,
      [perHalaman, offset]
    )
  ]);

  return {
    data: rows,
    total,
    halaman,
    perHalaman,
    totalHalaman: Math.ceil(total / perHalaman),
  };
}

When to Switch to Another Approach #

Keep using mysql2 directly if:
  ✓ Complex queries needing full control over SQL
  ✓ Critical performance with already-optimized queries
  ✓ Simple applications without many models/tables
  ✓ The team is familiar with SQL and doesn't need extra abstraction
  ✓ Database migrations are managed by a separate tool (Flyway, Liquibase)

Consider an ORM / Query Builder if:
  ✗ Many models with complex relationships — consider TypeORM or Prisma
  ✗ Need automatic migrations from models — Prisma or TypeORM migrations
  ✗ Multi-database (MySQL + PostgreSQL) — TypeORM or Knex.js
  ✗ Rapid prototyping with little SQL — Prisma is very productive
  ✗ The team is less familiar with SQL — ORMs help but can hide performance issues

Library	Approach	Best For
`mysql2`	Direct queries	Full control, high performance
`Knex.js`	Query builder	Dynamic SQL with type safety
`TypeORM`	ORM + decorators	Enterprise, many relationships
`Prisma`	ORM + schema	Rapid dev, best type safety
`Drizzle`	Lightweight ORM	TypeScript-first, minimalist

Summary #

Always use mysql2/promise, not the callback version — the Promise API is far more ergonomic with async/await and the TypeScript types are more accurate.
Connection pool for server applications — don’t create a new connection per request; use createPool with a connectionLimit matching the application’s load.
execute() for parameterized queries, query() for static queries — execute() uses server-side prepared statements, which are safer and more efficient for repeated queries.
Parameterized queries without compromise — never interpolate user input into SQL strings; always use ? as the placeholder.
Column names can’t be parameterized — for dynamic sorting/filtering, validate column names from a whitelist before interpolating into a query.
interface extends RowDataPacket for query result types — without this, TypeScript can’t match the return type of execute<T[]>.
conn.release() in the finally block for transactions — unreleased connections cause the pool to exhaust and the whole application to hang.
Use a withTransaction helper to reduce the repeated try/catch/rollback/release boilerplate in every transaction.
Check affectedRows after UPDATE/DELETE — a value of 0 could mean the record wasn’t found, not always an error; distinguish the two in API responses.
The Repository Pattern separates concerns — all SQL lives in the repository, the service layer doesn’t know database details, and testing can be done with mocked repositories.

← Previous: YAML Next: MSSQL →