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
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/dbpool
A small set of open connections created once and reused. Opening one per request would melt the database.
10 conns, sharedORM
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 = $1how it works
- 1Stash the string
DATABASE_URL goes in .env, gitignored. Your code reads it once at startup via process.env.
- 2Create one pool
At startup, not per request. Every module imports the same pool and shares its connections.
- 3Parameterize everything
Pass user input as $1 values, never by gluing strings into SQL. This one habit kills injection.
- 4Close it politely
On shutdown, call pool.end so connections close cleanly instead of timing out.
the code, explained
connect once, query safely
side by side
raw SQL
- any query you can write
- you learn real SQL
- no types, stringly typed
- one less dependency
an ORM
- type-safe models and queries
- migrations tooling included
- complex joins get awkward
- schema lives in one place
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