Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 43 ยท Data and Files

Databases with Express

Connect Express to a database: PostgreSQL and MySQL drivers, MongoDB with Mongoose, Prisma, connection pools and the repository pattern, with a SQLite demo.

Express has no database layer at all. It does not know what a table is. You pick a driver or an ORM from npm, connect once when the app starts and call it from your handlers. That freedom is useful, but it raises questions: how many connections should you open, where does the SQL live, and how do you keep route handlers readable?

This lesson answers them. The real drivers (pg, mysql2, Mongoose, Prisma) cannot be installed on this site, so their code is read-only. The runnable examples use node:sqlite, which ships with Node itself, behind the same repository shape you would use with any database. For database basics in Node, see Databases Overview and SQLite in Node.js.

Pick your data layer

SQL driver pg or mysql2. You write the SQL.
Mongoose Models and schemas for MongoDB.
Prisma Typed client from a schema file.

All of them work with Express the same way.

1 / 2

Connecting to SQL with a pool

Opening a database connection is slow, and databases allow only a limited number of them. So servers create a pool once: a small set of open connections that handlers borrow and return. With PostgreSQL and the pg package:

JavaScript
// db.js
import pg from 'pg';

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                  // never more than 10 connections
  idleTimeoutMillis: 30_000,
});
JavaScript
// a route using it
app.get('/users/:id', async (req, res) => {
  const { rows } = await pool.query('SELECT id, name FROM users WHERE id = $1', [req.params.id]);
  if (rows.length === 0) return res.status(404).json({ error: 'User not found' });
  res.json(rows[0]);
});

pool.query borrows a connection, runs the statement and returns it. Express 5 forwards a rejected promise from an async handler to the error middleware, so you do not need a try/catch around every query (Error Handling). With MySQL, mysql2/promise looks almost identical, using ? placeholders:

JavaScript
import mysql from 'mysql2/promise';

export const pool = mysql.createPool({ uri: process.env.DATABASE_URL, connectionLimit: 10 });

const [rows] = await pool.query('SELECT id, name FROM users WHERE id = ?', [id]);

Create the pool at module level, once per process, never inside a request handler. Creating one per request is a classic way to run out of connections.

When you need several statements to succeed or fail together, borrow one client for a transaction:

JavaScript
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [50, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [50, 2]);
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release(); // always give the connection back
}

MongoDB with Mongoose

For document databases, Mongoose adds schemas and models on top of the MongoDB driver:

JavaScript
import mongoose from 'mongoose';

await mongoose.connect(process.env.MONGODB_URI);

const User = mongoose.model('User', new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
}, { timestamps: true }));

app.post('/users', async (req, res) => {
  const user = await User.create({ name: req.body.name, email: req.body.email });
  res.status(201).json(user);
});

Pick the fields explicitly, as above, instead of passing req.body whole. Otherwise a client could set fields you never meant to expose, such as isAdmin. Mongoose manages its own connection pool.

Prisma in a nutshell

Prisma generates a typed client from a schema file, and handles migrations:

PRISMA
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
}
JavaScript
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient(); // one instance for the whole app

app.get('/users', async (req, res) => {
  res.json(await prisma.user.findMany({ orderBy: { name: 'asc' } }));
});

Whether you choose a plain driver, an ORM or a query builder, the rest of your app should not care. That is what the next pattern is for.

Step by step: environment variables and a first connection

The snippets above use process.env.DATABASE_URL without saying where that value comes from. Connection strings contain passwords, and they differ between your laptop, the test server and production, so they must live outside the source code, in environment variables. This section walks through the setup for MySQL and MongoDB, step by step.

Step 1: a .env file and a .env.example

Create a .env file in the project root with one NAME=value per line (never commit it):

Terminal
# .env  (private: add to .gitignore)
PORT=3000
NODE_ENV=development
MYSQL_URL=mysql://app_user:s3cret@127.0.0.1:3306/shop
MONGODB_URI=mongodb://127.0.0.1:27017/shop

Commit a template with fake values so teammates know what to define:

Terminal
# .env.example  (committed)
PORT=3000
NODE_ENV=development
MYSQL_URL=mysql://user:password@127.0.0.1:3306/dbname
MONGODB_URI=mongodb://127.0.0.1:27017/dbname
Terminal
# .gitignore
node_modules
.env

If .env was ever committed by accident, treat every secret in it as leaked and rotate it. Deleting the file in a later commit does not remove it from history.

Step 2: load the file

Variables in .env do nothing until something puts them into process.env. Three ways:

JavaScript
// 1. The classic package (npm install dotenv). Put this FIRST in your entry file.
import 'dotenv/config';
Terminal
# 2. Built into Node 20.6+: no package needed
node --env-file=.env src/server.js
JavaScript
// 3. Also built in: load the file from code
process.loadEnvFile();            // reads ./.env, or pass a path

Useful rules: values already set in the real environment win over the file by default, so production (where the platform sets the variables) is never overridden by a stray .env. Values are always strings: Number(process.env.PORT) when you need a number. Load the file before importing modules that read process.env, otherwise they see undefined. In production you normally do not ship a .env at all; the hosting platform or container injects the variables (Deployment).

Step 3: one validated config module

Read process.env in one place, check it, and export plain values. Crash at startup if something required is missing (see Project Setup):

JavaScript
// config.js
const required = ['MYSQL_URL', 'MONGODB_URI'];
const missing = required.filter((name) => !process.env[name]);
if (missing.length > 0) throw new Error(`Missing environment variables: ${missing.join(', ')}`);

export const config = {
  env: process.env.NODE_ENV ?? 'development',
  port: Number(process.env.PORT ?? 3000),
  mysqlUrl: process.env.MYSQL_URL,
  mongoUri: process.env.MONGODB_URI,
};

Step 4: connect to MySQL with mysql2

Install the driver (npm install mysql2), create the pool once, and check it at startup so a wrong password fails immediately instead of on the first request:

JavaScript
// db/mysql.js  (read-only: needs a MySQL server)
import mysql from 'mysql2/promise';
import { config } from '../config.js';

export const pool = mysql.createPool({
  uri: config.mysqlUrl,
  waitForConnections: true,   // queue requests when all connections are busy
  connectionLimit: 10,        // at most 10 open connections
  queueLimit: 0,              // 0 = unlimited queue
  namedPlaceholders: true,    // allows WHERE id = :id with an object
});

export async function checkMysql() {
  const [rows] = await pool.query('SELECT 1 AS ok');
  return rows[0].ok === 1;
}
JavaScript
// server.js
import 'dotenv/config';
import app from './app.js';
import { config } from './config.js';
import { checkMysql, pool } from './db/mysql.js';

await checkMysql();                                   // fail fast if the database is unreachable
const server = app.listen(config.port, () => console.log(`Listening on ${config.port}`));

process.on('SIGTERM', () => {                         // graceful shutdown: stop requests, then the pool
  server.close(async () => {
    await pool.end();
    process.exit(0);
  });
});

A route then uses placeholders, never string concatenation (Never build SQL from input below):

JavaScript
app.get('/products/:id', async (req, res) => {
  const [rows] = await pool.query('SELECT id, name, price FROM products WHERE id = ?', [req.params.id]);
  if (rows.length === 0) return res.status(404).json({ error: 'Product not found' });
  res.json(rows[0]);
});

With mysql2, pool.query returns a pair: [rows, fields], which is why the code destructures const [rows]. Managed MySQL services usually require TLS: add ssl: { rejectUnauthorized: true } (and a CA file if the provider gives you one) to the pool options.

Step 5: connect to MongoDB with Mongoose

JavaScript
// db/mongo.js  (read-only: needs a MongoDB server)
import mongoose from 'mongoose';
import { config } from '../config.js';

mongoose.connection.on('connected', () => console.log('MongoDB connected'));
mongoose.connection.on('error', (err) => console.error('MongoDB error:', err.message));
mongoose.connection.on('disconnected', () => console.warn('MongoDB disconnected'));

export async function connectMongo() {
  await mongoose.connect(config.mongoUri, {
    serverSelectionTimeoutMS: 5000,   // give up after 5 seconds instead of hanging
  });
}
export const disconnectMongo = () => mongoose.disconnect();
JavaScript
// server.js
await connectMongo();                                 // before accepting traffic
// ... start the server; on SIGTERM: server.close(() => disconnectMongo())

Mongoose keeps its own pool, retries lost connections, and buffers commands issued before the connection is ready, which can make a missing await mongoose.connect() look like a hang rather than an error. A hosted cluster uses a mongodb+srv://user:[email protected]/shop URI; URL-encode special characters in the password (@ becomes %40).

Runnable: config loading, a startup check and graceful shutdown

The real drivers cannot run here, but the steps around them can. Node's own util.parseEnv is the parser behind --env-file, so the first part below is real. The database part uses node:sqlite behind a createPool function that mimics the mysql2/promise interface (query returning [rows, fields], ? placeholders, end()), so the route, the startup check and the shutdown order are exactly the code from the steps above.

Node.js Env parsing, a mysql2-shaped pool and graceful shutdown

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The parser handled the comment, the quoted values and the empty value, and a real variable (NODE_ENV=production) beat the file's value. The config check failed fast for a missing variable and passed once it was supplied. The OR 1=1 request returned 404 because the placeholder treated the whole text as one value rather than as SQL, which is the whole point of parameterised queries. Finally, the server stopped first and only then was the pool closed, so no in-flight request lost its connection; afterwards any query reports the closed pool.

In a real project the same code runs against mysql2/promise (note that real MySQL uses ? placeholders and AUTO_INCREMENT instead of SQLite's rowid) or Mongoose. The runnable example demonstrates the structure: environment variables in, one config module, a startup check, parameterised queries, ordered shutdown.

The repository pattern

A repository is a small module that owns all the queries for one kind of data. Handlers call users.findById(7) and never see SQL. Benefits: routes stay short, tests can swap in a fake repository, and changing databases touches one file. It builds on the split from Controllers and Services.

Here is a repository over SQLite, plus Express-style routes that use it. The database lives in memory and disappears when the run ends.

Node.js A repository over SQLite behind routes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The prepared statements use ? placeholders. Real code would read the name and email from a validated req.body; the demo uses fixed values because our stand-in has no body parser. Swap node:sqlite for pg and only createUserRepository changes (and its functions become async).

Never build SQL from input

The most important database rule in a web app is to use placeholders. Watch what happens when you do not:

Node.js String concatenation versus placeholders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The concatenated query returns every user because the quote in the input closed the string and added a condition that is always true. The placeholder version treats the whole input as one value and finds nobody. Drivers for every database offer placeholders ($1, ? or named parameters); use them for values. Table and column names cannot be parameters, so if you must choose them dynamically, check them against a fixed allow list. More in Security Best Practices.

Connection lifecycle

Connect before you start listening, and fail fast if you cannot:

JavaScript
import { pool } from './db.js';
import { app } from './app.js';

await pool.query('SELECT 1'); // crash on boot if the database is unreachable
const server = app.listen(3000);

process.on('SIGTERM', async () => {
  server.close(async () => {
    await pool.end(); // let queries finish, then close connections
    process.exit(0);
  });
});

Closing the pool on shutdown is part of graceful shutdown (Deployment and Production). Keep the connection string in an environment variable, and use the database's own migration tool (Prisma Migrate, node-pg-migrate, Knex) instead of editing tables by hand.

Add a timeout and keep queries short. A handler that holds a pooled connection while it waits for a slow external API can starve every other request of connections.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do servers use a connection pool?

    A pool keeps a bounded number of open connections that handlers borrow and return.
  2. 2. Where should you create the pool?

    One pool per process is shared by all requests. Creating pools per request exhausts the database's connections.
  3. 3. What is the main benefit of the repository pattern?

    Handlers depend on a small interface, which also makes testing with a fake repository easy.
  4. 4. Which query is safe from injection?

    Placeholders keep data and SQL apart. Hand-escaping is error-prone.

Summary

  • Express has no database layer: choose pg, mysql2, Mongoose, Prisma or another tool.
  • Create one connection pool at startup, and release borrowed clients in finally.
  • Use placeholders for every value; allow-list dynamic identifiers.
  • Select the fields you accept from req.body instead of passing it whole.
  • Hide queries behind repositories, so routes, tests and migrations stay simple.
  • Fail fast on a bad connection and close the pool during shutdown.

Next: File Uploads.