API and Database: How the Two Connect, Where the Connection Lives, and the Mistakes That Leak Credentials

An API connects to a database through a driver: the API process opens a connection to the database over the network using a connection string from its environment, sends queries, and turns the rows into JSON for the HTTP response. The database is never exposed to the client; the API is the only thing that talks to it. Three things decide whether this works in production: the connection string lives in an environment variable rather than in code, the API keeps a pool of connections instead of opening one per request, and the database is reachable from the API over a private network and from nowhere else.

The top results explain the concept at the level of a diagram with three boxes and an arrow. The questions people actually have are underneath that: where does the connection string go, why did the database run out of connections, and why is the database reachable from the internet. This post is those answers, framework-agnostic, with the shape of the code and the shape of the hosting.

Table of contents

• The layers between a request and a row
• Where the connection lives
• Why one connection per request breaks
• Keeping the database off the internet
• Exposing the database as an API, on purpose
• The mistakes that show up in the first week
• How this fits the rest of the stack
• FAQ

The layers between a request and a row

A request to an API endpoint passes through a fixed set of layers on its way to the database and back. Naming them makes the rest of the post concrete.

1. The route. The HTTP method and path map to a handler function. GET /users/42 calls the handler for fetching a user.
2. Validation and auth. The handler checks the caller is allowed and the input makes sense. Nothing has touched the database yet.
3. The data layer. A query builder, an ORM or hand-written SQL. This is where the query is constructed, with parameters, never by pasting user input into a string.
4. The driver and the pool. The library that speaks the database’s wire protocol, holding a set of open connections and lending one to the query.
5. The database. Executes the query, returns rows.
6. Serialisation. Rows become JSON, with a status code and headers, and the response goes back out.

The client never sees layers four and five. That is the whole point of putting an API in front of a database: the database has one caller, and that caller decides what is allowed.

Where the connection lives

The connection string, host, port, database, user, password, is the most sensitive value in the application, and the most common place to find it is in the source code, which is the one place it must not be.

It belongs in an environment variable, read once at startup. Locally that is a .env file that is in .gitignore. In production it is a value set on the host, per service and per environment, so that staging and production point at different databases with no code change.

// Node with the pg driver
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

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) return res.status(404).end();
  res.json(rows[0]);
});

Two things in that snippet carry most of the weight. The pool is created once, at module load, not inside the handler. And the query uses a parameter, $1, rather than string concatenation, which is the entire defence against SQL injection.

Why one connection per request breaks

Opening a database connection costs a TCP handshake, a TLS handshake and an authentication exchange, tens of milliseconds, and the database allocates memory for each one. An API that opens a connection per request spends more time connecting than querying, and under load it opens hundreds of connections until the database refuses more.

A pool holds a fixed number of open connections and lends them out. Requests wait briefly for a free one rather than opening their own. The pool size is the tuning knob: ten is a reasonable default for a small API; more does not help unless the database has spare capacity.

The arithmetic that catches people is instances times pool size. Three API instances with a pool of twenty is sixty connections. A small managed Postgres might allow a hundred, and other things, migrations, an admin tool, a worker, also need some. Size the pool with the instance count in mind, and if the total gets large, put a connection pooler such as PgBouncer between the API and the database.

Serverless functions make this worse, because every cold instance is a new pool. That is the case where an external pooler stops being optional.

Keeping the database off the internet

The database should be reachable by the API and by nothing else. A database with a public IP address, even with a strong password, is on every scanner’s list within hours of coming up.

The right shape is a private network: the API and the database share it, the database listens only on its private address, and the firewall allows the database port from the API’s address range only. Humans who need a query console go through a tunnel or a bastion, not through an open port.

On a managed platform this is the default. A managed Postgres on RunxBuild sits on a private network with the services that use it, has a connection limit you can see on the plan, and manages users and backups from the dashboard; see databases on RunxBuild for the managed instance. The API service gets the private hostname in its environment, and the public internet never learns the database exists.

Exposing the database as an API, on purpose

A different reading of the same search is: I have a database and I want an API on it without writing one. Tools exist for that, from PostgREST, which turns a Postgres schema into a REST API directly, to backend-as-a-service products that generate endpoints from tables.

They work, and they are the right tool for an internal tool or a prototype. The trade is that the API’s shape is the database’s shape. Every table is an endpoint; access control is row-level policy in the database rather than logic in code; and the moment the API needs to do something that is not a table operation, send an email, call another service, compute something, you are writing the backend anyway, now in two places.

The rule of thumb: generate the API when the consumer is you. Write the API when the consumer is a user.

The mistakes that show up in the first week

• Credentials committed to git. The .env file was not ignored, or the connection string was pasted into a config file. Rotate the password immediately; the commit is in history forever.
• localhost in production. The connection string points at the laptop’s database. It belongs in the environment, with the private hostname the host provides.
• No timeout. A slow query holds a pooled connection forever and the pool drains. Set a statement timeout on the connection and a request timeout on the API.
• Returning the row as-is. SELECT * sends password hashes and internal columns to the client. Name the columns.
• Migrations by hand. Schema changes applied in a console and forgotten. Use a migration tool from day one, run on deploy.

The AI-generated form was cute until it asked where the submissions go. Every item above is what the answer to that question actually involves.

How this fits the rest of the stack

An API in front of a database is three decisions made properly: the connection string in the environment, a pool sized against the connection limit, and a private network between the two. Everything else is the code you were going to write anyway. To see what the pair costs before building it, the RunxBuild hosting calculator shows the service and the managed database as separate line items, and the connection limit on the database plan is the number to check against your pool size. The dashboard is where the private hostname ends up in the service’s environment.

Useful related references:

• How to Create a Node Backend: From node app.js to a Live URL Without the Tutorial Cuts
• The Best Database for Next.js Is Almost Always Postgres
• Connect to a Database Remotely Without Leaving It Open to the Internet
• Databases on RunxBuild

FAQ

How does an API connect to a database?

The API process uses a driver library to open a network connection to the database, using a connection string read from its environment. It sends parameterised queries and turns the rows into JSON responses. The client talks to the API; only the API talks to the database.

Where should I store the database connection string?

In an environment variable set on the host that runs the API, never in source code. Locally, a .env file that is listed in .gitignore. If it has ever been committed, rotate the password.

What is a connection pool and why do I need one?

A fixed set of open database connections that request handlers borrow and return. Opening a connection per request is slow and exhausts the database’s connection limit under load. Create one pool at startup with a size in the low tens.

Should my database be accessible from the internet?

No. It should be on a private network with the API, listening only on its private address, with the firewall allowing the database port from the API only. Humans connect through a tunnel or bastion.

Can I turn a database into an API without writing code?

Yes, with tools that generate endpoints from a schema. They suit internal tools and prototypes. Once the API needs logic beyond table operations, such as sending email or calling other services, a written backend is simpler than a generated one plus workarounds.