How to host a database for your app

Hosting a database means keeping a database server (PostgreSQL, MySQL, MongoDB or Redis) running around the clock, with disk, network and credentials, and getting your app to talk to it. Installing the engine is rarely the hard part. The real work is deciding where it runs, who may connect, and how the app gets the password without it landing in your repository.

The steps below go from choosing a place to verifying the connection. The examples use PostgreSQL and Node.js, but the logic applies to the other engines.

Where can I host a database?#

There are three common options:

• On your own machine: great for development. Not for production, because the database goes offline when the computer shuts down and nobody outside can reach it.
• On a server you manage (VPS): you install the engine and handle firewall, updates, disk and copies. You get full control, and all the work that comes with it.
• On a database hosting service: the provider starts the instance and hands you the address and credentials. You handle the schema and the data, not the operating system.

If you have not picked an engine yet, read Which database to choose first. For the budget side, see How much does a cloud database cost.

What should I decide before creating the database?#

Five decisions set size and cost:

1. Engine: relational (PostgreSQL, MySQL), document (MongoDB) or in-memory key-value (Redis).
2. Memory: Redis keeps the dataset in RAM. The others use RAM as cache and disk for the rest.
3. Disk space: current data size plus room to grow.
4. Location: as close to the app as possible, because every query pays network latency.
5. Copies: how often you will make copies and how you will test restoring them. Details in How to back up a database.

What does a connection string look like?#

For PostgreSQL, the libpq documentation defines the URI format:

postgresql://user:password@host:5432/dbname?sslmode=require

Each part has a role: the scheme (postgresql:// or postgres://), user:password, the host, the port (5432 is the usual PostgreSQL default), the database name and parameters after ?. sslmode=require forces the connection to use TLS, but without a root certificate configured the client does not verify the server identity (that takes verify-ca or verify-full).

Watch out for special characters. According to the documentation, symbols with a special meaning in the URI need percent-encoding. A password p@ss must become p%40ss. If the connection fails with an authentication error and the password looks right, start there. In Node.js, encodeURIComponent("p@ss") does the conversion.

MySQL (mysql://), MongoDB (mongodb:// or mongodb+srv://) and Redis (redis://) follow the same idea: a URL with user, password, host and port.

How do I store the connection string safely?#

Never write the password in code. Put the string in an environment variable, for example DATABASE_URL, and read it at runtime.

In the .env file (never commit it):

DATABASE_URL="postgresql://user:password@host:5432/dbname?sslmode=require"

Add .env to .gitignore. In production, set the variable in your hosting panel instead of shipping the file. The concept is covered in What are environment variables.

How do I connect a Node.js app?#

Install the pg client (node-postgres):

npm install pg

The examples use ES modules (import), so your package.json needs "type": "module". Create db.js. A Pool reuses connections instead of opening one per query and accepts the connection string directly:

import pg from "pg";

const { Pool } = pg;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

And a test script, test.js:

import { pool } from "./db.js";

const { rows } = await pool.query("SELECT NOW() AS now");
console.log("Connected. Server time:", rows[0].now);
await pool.end();

Run it with node --env-file=.env test.js (the flag needs a recent Node.js version). pg also reads PGHOST, PGUSER, PGPASSWORD, PGPORT and PGDATABASE when you create the Pool with no arguments, the same variables psql uses.

A note on TLS: by default, pg does not follow the libpq rule described above. With sslmode=require it verifies the server certificate, and a certificate Node.js does not recognize (a self-signed one, for example) causes an error. If the provider gives you its CA certificate, point to it with sslrootcert; for libpq semantics, add uselibpqcompat=true to the string.

Create a single Pool and import it in your other files. Opening a Pool per request exhausts the database connections quickly.

How do I check that the connection works?#

Three tests, from simplest to most complete:

1. Minimal query: SELECT NOW(), as in the script above. If it returns a result, network, credentials and TLS are fine.
2. Command-line client: psql "$DATABASE_URL" shows whether the database answers outside your app, which separates network problems from code problems.
3. Write and read: create a test table, insert a row, read it and drop the table. This confirms the user can write, not only read.

What errors show up most often?#

In almost every case, the cause is the string, the firewall or how connections are used. Read the full message before changing anything.

How to do this on Square Cloud#

On Square Cloud, the database follows the third option on the list: you create the instance and own the schema and the data. MongoDB, PostgreSQL, MySQL and Redis hosting starts on the Standard plan (Hobby does not include databases). Of the decisions above, engine and memory are dashboard fields: the engine always comes on a recent version, and memory starts at 512 MB for Redis and 1024 MB for the others. Location is already settled, because the database is provisioned in seconds in the same datacenter as your applications, at about 1 ms average latency.

The dashboard hands you the full connection URL (shown once at creation; resetting the password shows it again) and the certificates to download. TLS is mandatory: the client trusts the downloaded CA and, on PostgreSQL and MongoDB, also presents the .pem certificate. In pg, those files go in the Pool's ssl option (ca, cert and key); in that case, drop sslmode from the URL, because parameters in the string override that option. The URL becomes an environment variable of the application, set from the dashboard, API, CLI or VS Code extension, and the change takes effect on the next restart, with no rebuild. Since the database accepts outside connections, the three tests from the previous section also run from your machine or CI, with the same certificates.

For the copies decision, you take manual snapshots whenever you want, and every plan that includes databases also takes an automatic daily snapshot, kept for 7 days. A snapshot restores into the same database, another one of the same engine or a new database. PostgreSQL is native PostgreSQL, without extensions, and Redis has persistence.

Start with PostgreSQL hosting, the engine in the examples, or see the databases available today on Databases.