Hosting a website with a database: how it works

Almost every website that stores something (users, orders, comments, posts) has a database behind it. The question beginners ask is where the database "fits": it is not part of the page, and the browser cannot (and should not) talk to it.

Between the two sits a back end: a program that runs on a server, receives HTTP requests, queries the database and returns the result. This guide shows that design and a working example with Node.js and PostgreSQL.

How does a website talk to its database?#

A request travels through three points:

1. Browser: asks for a page or calls a route, for example GET /api/posts.
2. Back end: validates the request, takes a connection from the pool and runs a SQL query (or the MongoDB equivalent).
3. Database: returns the rows; the back end builds the JSON or HTML and responds.

The browser only sees the final result. The database password exists only on the server, in an environment variable. If it were in the page's JavaScript, any visitor could read it in the developer tools.

Can a static website use a database?#

A static site is just files (HTML, CSS, JS) served as they are. It runs no code on the server, so it does not query a database. To show dynamic data, it calls an API somewhere else that runs the query.

Frameworks that render on the server (such as Next.js) combine both parts in one project. The difference between rendering at build time and on every request is covered in SSR, SSG and static sites. Either way, the code that talks to the database runs on the server.

What do I need before starting?#

• A recent Node.js version that Express 5 supports, plus npm.
• A reachable PostgreSQL database, local or hosted, and its connection string in the form postgresql://user:password@host:5432/database. See how to host a database if you do not have one yet.
• A simple table to test with:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

How do I write the back end with Node.js and PostgreSQL?#

Install the dependencies:

npm install express pg

Create index.js. The file uses ES modules, so add "type": "module" to package.json:

import express from "express";
import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});
// without this listener, an error on an idle connection crashes the process
pool.on("error", (err) => console.error("PostgreSQL pool error", err));

const app = express();
app.use(express.json());

app.get("/api/posts", async (req, res) => {
  const { rows } = await pool.query(
    "SELECT id, title, created_at FROM posts ORDER BY id DESC LIMIT 20"
  );
  res.json(rows);
});

app.post("/api/posts", async (req, res) => {
  const { title } = req.body ?? {};
  if (typeof title !== "string" || title.trim() === "") {
    return res.status(400).json({ error: "title is required" });
  }
  const { rows } = await pool.query(
    "INSERT INTO posts (title) VALUES ($1) RETURNING id, title, created_at",
    [title.trim()]
  );
  res.status(201).json(rows[0]);
});

app.listen(process.env.PORT || 3000);

Three things matter here:

• Pool: keeps connections open and reuses them. Opening a new connection per request is slow and can exhaust the database's connection limit. The pg documentation says a single pool per application is usually enough. pool.on("error") handles errors on idle connections, such as the database restarting; without it, the process crashes.
• $1 and the array: this is a parameterized query. The value travels separately from the SQL, so malicious text never becomes a command. OWASP lists this as the first defense against SQL injection.
• async without try/catch: in Express 5, an error thrown or a promise rejected inside a handler is passed to the error handler automatically. In Express 4 you had to call next(err) yourself.

Never build SQL by concatenating user input, as in "... WHERE title = '" + title + "'".

How do I configure the credentials safely?#

Set DATABASE_URL in the environment, never in the code or the repository:

export DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
node index.js

If the password contains characters that mean something in a URL, such as @, /, # or %, percent-encode them (p@ss becomes p%40ss, pa/ss becomes pa%2Fss). In development, a .env file kept out of Git works; in production, use your provider's environment variable settings. More in what are environment variables.

How do I check that it works?#

With the server running, write and read:

curl -X POST http://localhost:3000/api/posts \
  -H "Content-Type: application/json" \
  -d '{"title":"First post"}'

curl http://localhost:3000/api/posts

The first response returns the created record, with id and created_at. The second lists the posts. For the website to use this, the front end served by the same back end calls fetch("/api/posts") and renders the JSON.

What are the common errors?#

• connect ECONNREFUSED: nothing accepted the connection on that host and port. The database is down, or the host or port is wrong. A firewall that drops packets usually produces a timeout instead.
• password authentication failed for user "...": wrong user or password.
• Invalid URL: the connection string is malformed, often because of a password character that was not percent-encoded.
• sorry, too many clients already: the database hit its connection limit. Use a single shared Pool, created once when the program starts. See too many connections for more.
• Database and website far apart: every query pays the network latency, and a page with several queries gets slow. Keep them in the same datacenter.
• Password in the front end or the repository: set a new password on the database and move it to an environment variable.

How to do this on Square Cloud#

On Square Cloud, the back end and the database live in the same account. You deploy the website (Node.js, Python, PHP, Go and other runtimes) and create a PostgreSQL, MySQL, MongoDB or Redis database in the dashboard, with average latency of about 1 ms between application and database because both sit in the same datacenter. The dashboard gives you the full connection URL and the certificate.

TLS is required: the client has to trust the downloaded CA and, on PostgreSQL, also present the downloaded .pem, so pass those files in the ssl option of pg.Pool. Each database accepts up to 512 connections with no pooler in front, so keep the pool's max low. Database hosting starts on the Standard plan; Hobby does not include databases.

Set the connection URL as the application's DATABASE_URL environment variable through the dashboard, API or CLI. A variable change takes effect on the next restart, with no rebuild. Every web application gets a *.squareweb.app subdomain with HTTPS, and custom domains are available from Standard up. Web applications use at least 512 MB of RAM.

See the plans and how to publish your site in website hosting on Square Cloud.