Connecting to a Database

What is it?

Your application code and your database are two separate programs, so they need a way to actually talk to each other. That happens over a network connection (even if the database is on the same machine), using a connection string that packs together everything needed to reach it: the host, port, username, password, and database name — something like postgres://user:pass@localhost:5432/mydb.

On top of that raw connection, you have a choice of how directly you want to write SQL: a driver (like pg in Node or psycopg2 in Python) sends SQL strings and hands back rows, giving you full control. A query builder (like Knex, or SQLAlchemy Core) lets you construct queries with function calls that still map closely to SQL. An ORM (like Prisma, Sequelize, or SQLAlchemy's ORM layer) goes further, letting you work with rows as objects and generating the SQL for you.

Explain like I'm 10

A raw driver is like speaking directly to a bank teller in exact banking terminology. A query builder is like filling out a structured form that still asks for the same specific details. An ORM is like using the bank's app, where you tap 'send money' and it handles the paperwork underneath without you thinking about it — convenient, but you have less control over exactly what happens.

Examples

A connection string and a raw query

const { Pool } = require("pg");

// postgres://user:password@localhost:5432/mydb
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const result = await pool.query(
  "SELECT * FROM users WHERE id = $1",
  [userId]
);
console.log(result.rows);

The connection string tells the driver exactly where and how to connect; the query itself is plain SQL, with $1 as a safe placeholder for the actual value.

The same query at three levels of abstraction

// Raw driver
await pool.query("SELECT * FROM users WHERE active = $1", [true]);

// Query builder (Knex)
await knex("users").where({ active: true });

// ORM (Prisma)
await prisma.user.findMany({ where: { active: true } });

All three end up running equivalent SQL — they differ only in how much of the SQL you write by hand versus how much the library generates for you.

Full CRUD through a raw driver, inside route handlers

// Create
app.post("/users", async (req, res) => {
  const result = await pool.query(
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
    [req.body.name, req.body.email]
  );
  res.status(201).json(result.rows[0]);
});

// Read
app.get("/users/:id", async (req, res) => {
  const result = await pool.query("SELECT * FROM users WHERE id = $1", [req.params.id]);
  if (result.rows.length === 0) return res.status(404).json({ error: "not found" });
  res.json(result.rows[0]);
});

// Update
app.put("/users/:id", async (req, res) => {
  const result = await pool.query(
    "UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *",
    [req.body.name, req.body.email, req.params.id]
  );
  res.json(result.rows[0]);
});

// Delete
app.delete("/users/:id", async (req, res) => {
  await pool.query("DELETE FROM users WHERE id = $1", [req.params.id]);
  res.status(204).end();
});

The same four SQL operations from basic CRUD, each wired to the matching HTTP method and route — this is what 'connecting to a database' actually looks like end to end in a real API, not just a single SELECT.

Wrapping multiple queries in a transaction

app.post("/transfer", async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
      [req.body.amount, req.body.fromId]
    );
    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
      [req.body.amount, req.body.toId]
    );
    await client.query("COMMIT");
    res.status(204).end();
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
});

A money transfer needs both updates to happen together or not at all — this checks out a single dedicated connection from the pool, runs both queries as one transaction, and rolls back entirely if either one fails, instead of risking money leaving one account without arriving in the other.

How it works

The app reads connection details (usually from an environment variable, never hardcoded) and hands them to a driver, which opens a TCP connection to the database and authenticates. Rather than opening a new connection per query, real applications keep a small pool of open connections ready to reuse (see connection pooling). A query builder or ORM sits on top of that same driver — at some point, everything still becomes SQL text sent over that same connection; the abstraction just decides how much of that SQL you write versus generate.

Why does it exist?

A database speaks its own wire protocol, not JavaScript or Python directly — without a driver translating between your language's data types and that protocol, your application code couldn't talk to it at all. Query builders and ORMs exist on top of that for productivity: generating repetitive SQL, mapping rows to familiar objects, and providing tools like migrations that a raw driver doesn't include.

When to use it

Reach for a raw driver when you need full control or you're running a handful of simple, performance-sensitive queries. Reach for a query builder when you want dynamic, composable queries with more safety than hand-built SQL strings. Reach for an ORM for typical CRUD-heavy apps, where the productivity of models, relations, and built-in migrations outweighs giving up some fine-grained SQL control.

When not to use it

Avoid forcing complex analytical or reporting queries through an ORM's query API — it often produces slower, harder-to-read SQL than writing it directly; most ORMs let you drop down to raw SQL for exactly this case. For a tiny script that runs one or two queries, pulling in a full ORM is usually more setup than the task needs.

Common mistakes

• Hardcoding database credentials directly in source code instead of reading them from environment variables.

• Building SQL by concatenating strings with user input, opening the door to SQL injection, instead of using parameterized placeholders.

• Opening a brand-new database connection for every incoming request instead of reusing a connection pool, which is slow and can exhaust the database's connection limit under load.

• Forgetting to call client.release() after a transaction (in every code path, including failures), which leaks a connection out of the pool until it eventually runs dry.

Practice exercises

1. Easy:

   Break down the pieces of the connection string postgres://app:secret@db.internal:5432/orders — host, port, user, password, and database name.

2. Medium:

   Rewrite this unsafe query to use a parameterized placeholder instead of string concatenation: db.query("SELECT * FROM users WHERE email = '" + email + "'").

3. Hard:

   Explain what would go wrong, and why, if every incoming HTTP request opened and closed its own new database connection instead of borrowing one from a pool.

Interview questions

What's the difference between a database driver, a query builder, and an ORM?

A driver sends raw SQL and returns rows with no abstraction; a query builder lets you construct SQL through function calls that still map closely to it; an ORM maps rows to objects and generates the SQL for you, trading some control for productivity.

What does a database connection string typically contain?

The protocol, host, port, username, password, and the specific database name needed to establish a connection.

Why should database queries use parameterized placeholders instead of string concatenation?

To prevent SQL injection — the driver safely substitutes values instead of treating attacker-controlled input as part of the SQL itself.

What is connection pooling, and why is it used instead of opening a new connection per request?

A pool maintains a set of already-open database connections ready to be reused; opening a fresh TCP connection and authenticating for every request is slow and, under load, can exhaust the database's own connection limit — borrowing a connection from a pool and returning it when done avoids both costs.

What determines an appropriate pool size for an application?

A balance between the app's expected concurrency and the database's own connection limit, shared across every other service also connecting to it — too small a pool queues requests waiting for a free connection under load; too large risks exhausting the database's total connection capacity, especially across multiple app instances pooling independently.

Why does `pool.query()` work fine for single, independent queries but not for a transaction spanning multiple queries?

pool.query() borrows any available connection for just that one call and returns it immediately afterward — a transaction needs every one of its statements to run on the same connection, since BEGIN/COMMIT are connection-scoped, which requires explicitly checking out one dedicated client via pool.connect() and reusing it for the whole transaction.

What are the four ACID properties a transaction is meant to provide?

Atomicity — all of a transaction's operations succeed together or none do; Consistency — a transaction moves the database from one valid state to another, respecting its constraints; Isolation — concurrent transactions don't see each other's uncommitted intermediate state; and Durability — once committed, the change survives even a crash right afterward.

What does ROLLBACK actually undo, and what does it leave unaffected?

It undoes every change made by statements since the matching BEGIN within that same transaction; it has no effect on already-committed transactions from before, or on anything outside the database, like an email already sent as a side effect of the same request.

Why is forgetting `client.release()` in every code path, including a thrown error, a serious bug rather than a minor cleanup oversight?

A client checked out via pool.connect() and never released stays permanently unavailable to the rest of the pool; repeated leaks gradually shrink the pool's usable connections until eventually none remain, and every new request that needs one hangs or times out — usually surfacing much later and far from the code that actually caused it.

Why is `client.release()` typically placed in a `finally` block rather than just at the end of the `try`?

A finally block runs whether the try completes successfully or throws partway through, guaranteeing the connection is always returned to the pool regardless of which path execution takes — placing it only at the end of try would skip it entirely whenever an error occurs.

What's a deadlock in the context of database transactions, and how can it happen with two concurrent transfers?

Two transactions each hold a lock the other needs and are both waiting for the other to release it — e.g. transaction A locks account 1 then tries to lock account 2, while transaction B simultaneously locks account 2 then tries to lock account 1; neither can proceed, and the database typically detects this and forcibly aborts one so the other can continue.

How would you reduce the risk of that kind of deadlock between two concurrent transfers?

Always acquire locks, e.g. update rows, in a consistent, agreed-upon order across every transaction, such as always locking the lower account id first regardless of which account is the sender or receiver, so two concurrent transactions can never be waiting on each other in a circular way.

What is the N+1 query problem, and how does it commonly happen when using an ORM?

Fetching a list of N records and then, for each one, issuing a separate query to fetch a related record, like each order's customer, results in 1 + N total queries instead of a couple — this often happens by accident with an ORM's lazy-loading relations, where accessing a relation inside a loop silently triggers a fresh query per iteration.

How do ORMs typically let you avoid the N+1 problem?

Through eager loading — explicitly telling the ORM up front to fetch the related data in the same query, or one additional batched query, via something like .include(), instead of triggering a separate query lazily each time a relation is accessed.

What's the difference between an ORM's lazy loading and eager loading for a related record?

Lazy loading only fetches a related record the moment code actually accesses it, deferring the query until needed but risking N+1 patterns in a loop; eager loading fetches the related data upfront, alongside the original query, trading a possibly larger single query for avoiding many small ones later.

What's a database migration, and why do ORMs typically include tooling for them?

A versioned, incremental change to a database's schema, like adding a column, tracked and applied in order so every environment's schema stays in sync with what the application code expects; ORMs include this tooling because schema changes need to happen safely alongside code changes, and hand-tracking raw ALTER TABLE scripts across environments is error-prone.

Why is a database index useful, and what's the tradeoff of adding one?

An index lets the database locate matching rows for a query without scanning every row in the table, dramatically speeding up reads on large tables; the tradeoff is that every index must also be updated on every INSERT/UPDATE/DELETE to the indexed column, so more indexes mean slower writes and additional storage.

What's a prepared statement, and how does it relate to preventing SQL injection?

A query where the SQL structure and the actual parameter values are sent to the database separately — the database compiles the SQL template once and substitutes values afterward strictly as data, never as executable SQL syntax, which is why parameterized placeholders like $1 are immune to injection in a way string concatenation isn't.

Why can't a column name or table name be parameterized the same way as a value, like `$1`?

Placeholders like $1 are only valid where the database expects a value in the SQL grammar, not where it expects an identifier like a column or table name; a dynamic identifier, say a user-chosen sort column, has to be checked against an explicit allow-list of known-safe column names instead, since it can't go through the same placeholder mechanism.

Give an example of an app that uses parameterized queries but is still vulnerable to SQL injection.

One that builds an ORDER BY clause or a dynamic sort column by directly concatenating a user-supplied string into the query text, since placeholders can't parameterize identifiers — even though the main WHERE clause uses $1 safely, that one concatenated fragment still lets an attacker inject arbitrary SQL through it.

What's the difference between a foreign key constraint and just remembering an id in application code without one?

A foreign key constraint is enforced by the database itself — it refuses to insert a row referencing a nonexistent parent, or refuses to delete a parent row still referenced unless a cascade is configured — whereas relying only on application code leaves that consistency vulnerable to bugs, direct database edits, or another service bypassing the app entirely.

What does `ON DELETE CASCADE` do on a foreign key, and what's a risk of using it without thinking it through?

It automatically deletes dependent rows when the referenced parent row is deleted, e.g. deleting a user also deletes all their orders; the risk is that a single delete can silently cascade through much more data than intended, especially several relationships deep, with no explicit confirmation of everything it removes.

What's the difference between optimistic and pessimistic locking when two requests might update the same row concurrently?

Pessimistic locking acquires an actual database lock on a row before reading it, like SELECT ... FOR UPDATE, blocking any other transaction from touching it until the first finishes; optimistic locking reads a version number or timestamp instead, and the update only succeeds if that version hasn't changed since, otherwise it fails and the caller retries — trading upfront blocking for after-the-fact conflict detection.

Why might an ORM's default behavior of committing each save or query as its own separate transaction be insufficient for a multi-step operation?

If a multi-step operation, like transferring money between two records, needs both changes to succeed or fail together, letting the ORM commit each step independently means a failure partway through leaves the operation half-applied — the ORM has to be explicitly told to wrap the whole sequence in one transaction instead.

What's a connection timeout, and why does a pool need one?

A limit on how long to wait for a connection to become available from the pool, or for the database to respond, before giving up and raising an error instead of hanging indefinitely — without one, a request stuck waiting on a connection could tie up server resources forever if the pool or database never frees one up.

Why isn't retry logic for a failed database query something you'd blindly apply to every query?

It's only safe to retry an operation that's read-only or otherwise idempotent — automatically retrying a write that may have actually succeeded on the database side but failed to acknowledge back to the client, due to a network blip, risks applying that write a second time, like a duplicate INSERT.

Why would a health-check endpoint run a trivial query, like SELECT 1, against the database rather than just returning 200 OK unconditionally?

It verifies the app can actually still reach and use the database at that moment, not just that the web server process itself is alive — a server that's up but has lost its database connection, or exhausted its pool, would otherwise report healthy while every real request is actually failing.

Why should you close or drain a connection pool gracefully during app shutdown, rather than letting the process exit abruptly?

An abrupt exit can leave in-flight queries or transactions cut off mid-way, and the database may keep tracking those connections as open for a period until it notices they're gone; explicitly ending the pool lets in-flight work finish, or a clean cutoff happen, and closes connections properly, freeing them immediately on the database side.

What's the tradeoff between choosing a UUID versus an auto-incrementing integer as a primary key, from a database-connection perspective?

An auto-incrementing integer is smaller and slightly faster to index and join on, but reveals roughly how many rows exist and can collide if generated independently in two places; a UUID is larger and marginally slower to index, but can be generated safely by the application itself before insertion, without a round trip to the database first to get an id.

How does a query builder like Knex offer more safety against SQL injection than hand-built string concatenation, while still allowing dynamic queries?

It builds the underlying parameterized query for you from function calls, like .where({ active: true }), automatically using placeholders for any values you pass in, so you get the flexibility of constructing conditions programmatically without ever manually concatenating a value into the SQL string yourself.

What's a downside of letting an ORM generate the SQL for a complex reporting or analytical query, compared to writing that SQL directly?

An ORM's query API is designed around common CRUD patterns and may generate needlessly complex, slow, or hard-to-read SQL for something like a multi-table aggregation with custom grouping — most ORMs let you drop to raw SQL specifically for these cases instead of forcing everything through the object-mapped API.

What does `RETURNING *` save you from having to do after an INSERT?

Without it, getting the newly created row's full data, including any auto-generated id, would require a second, separate SELECT after the INSERT; RETURNING * returns that row in the same round trip as the insert.

Why can't every incoming HTTP request simply open and close its own fresh database connection instead of using a pool?

Opening a raw TCP connection and authenticating has real overhead, so doing it once per request needlessly repeats that cost every time, and databases enforce a hard cap on total simultaneous connections that a busy app opening one connection per concurrent request can easily exceed, causing new connections, and thus requests, to start failing.

What's a read replica, and what kind of query would you route to one instead of the primary database?

A separate copy of the database kept continuously up to date from the primary and used to serve read-only queries, offloading read traffic from it; you'd route SELECT-only queries that can tolerate being very slightly out of date to it, while all writes must still go to the primary.