Connecting a Database

Stateless API meets stateful database. Friendship.

▶ the lesson · narrated sketch video · tap for sound

the intro

An API becomes a real app when it can remember things. You connect to a database with a connection string, run queries through a driver or ORM, and return the results as JSON. The skills to learn: connecting safely, pooling connections, and never trusting raw input.

fun fact ✦

Connection pools exist because opening a database connection is expensive — reuse them.

the docs

the deep dive

the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid

Your API is goldfish-brained on purpose: restart it and it remembers nothing, which makes it easy to replace and scale. Everything worth keeping lives in the database, and the connection string is the key to that memory. Which means the string is a password, so treat it like one.

the API forgets, the database remembers

your APIpool: 10 readyPostgres
one pool, opened at startup, shared by every request

key terms

connection string

The full address and credentials in one URL. Whoever holds it can read and write everything.

postgres://user:pw@host:5432/db

pool

A small set of open connections created once and reused. Opening one per request would melt the database.

10 conns, shared

ORM

A library that maps rows to objects and queries to functions. Convenient and type-safe, occasionally fighting you.

prisma.user.findMany()

$1 placeholder

A slot for user input in a parameterized query. The driver keeps it data, never code.

WHERE id = $1

how it works

  1. 1
    Stash the string

    DATABASE_URL goes in .env, gitignored. Your code reads it once at startup via process.env.

  2. 2
    Create one pool

    At startup, not per request. Every module imports the same pool and shares its connections.

  3. 3
    Parameterize everything

    Pass user input as $1 values, never by gluing strings into SQL. This one habit kills injection.

  4. 4
    Close it politely

    On shutdown, call pool.end so connections close cleanly instead of timing out.

the code, explained

connect once, query safely

1import pg from 'pg'
2
3export const pool = new pg.Pool({
created once at startup, imported everywhere
4 connectionString: process.env.DATABASE_URL,
the secret lives in the environment, never in git
5})
6
7const { rows } = await pool.query(
8 'SELECT * FROM cats WHERE id = $1', [id])
user input arrives as data — it can never become SQL

side by side

raw SQL

  • any query you can write
  • you learn real SQL
  • no types, stringly typed
  • one less dependency
VS

an ORM

  • type-safe models and queries
  • migrations tooling included
  • complex joins get awkward
  • schema lives in one place
concatenatedparameterizedSQL
one of these lets a search box read your users table
🪤 gotcha

The moment you build SQL by gluing strings together, user input becomes code: a search box ending with OR 1=1 can read your whole users table. Parameterized queries make input stay input. There is no middle ground here.

quick answers

SQL or NoSQL?

SQL when data has structure and relations, which is most products. NoSQL for flexible documents or exotic scale. Modern Postgres even does documents via JSONB.

How many connections should my pool have?

Small: five to twenty per app instance. Total connections equals instances times pool size, and Postgres defaults to a limit near 100.

Schema migrations: auto-sync or files?

Files, always, in anything real. Auto-sync is a dev convenience that will one day drop a column in production with great confidence.

quick hits about Connecting a Database

short answers · interviews, tips, trivia & hidden gems

🎤 interview

SQL or NoSQL?watch ↗

📚 tutorial

ORM or raw queries?watch ↗

🔧 useful

Why does your database melt under load?watch ↗

💎 deep

Never auto-sync productionwatch ↗

🔧 useful

Realistic test data in one command?watch ↗

🆕 update

Your queries, type-checkedwatch ↗