PostgreSQL from Node with pg

PostgreSQL is a powerful, standards-friendly SQL database — and one of the three managed database types Falix offers. This guide connects Node to one with pg (node-postgres, the standard driver), runs real queries, and shows the two things that matter most in production: parameterized queries and a connection pool.

This picks up where Connect your app to a database leaves off, going deep on Postgres specifically. New to SQL? Read Just enough SQL first.

Setup: the string in .env, the driver installed

The panel gives you a connection string shaped like this:

postgresql://user:pass@host:port/dbname

Copy it from the Databases page and store it as DATABASE_URL in your server's .env — never in your code (why). Then install the driver from the Packages page (search pg, install, restart).

💡 Tip: If your database is your own Postgres server rather than a managed one, the host and port are that server's public address from its Network page, and the user is pterodactyl with the generated password — build the same postgresql://… string from those parts.

Connect and query

Here's a complete index.js — verified on Falix's Node image against PostgreSQL 16. It creates a table, writes to it safely, and reads it back:

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

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

async function main() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS scores (
      user_id TEXT PRIMARY KEY,
      points  INTEGER NOT NULL DEFAULT 0
    )
  `);

  // Parameterized: $1 and $2 are placeholders, values passed separately.
  await pool.query(
    `INSERT INTO scores (user_id, points) VALUES ($1, $2)
     ON CONFLICT (user_id) DO UPDATE SET points = scores.points + EXCLUDED.points`,
    ['alice', 5]
  );

  const { rows } = await pool.query(
    'SELECT user_id, points FROM scores WHERE user_id = $1',
    ['alice']
  );
  console.log(rows); // [ { user_id: 'alice', points: 5 } ]

  await pool.end();
}

main().catch(console.error);

Three things to take from this:

• pool.query returns an object; the data is in .rows — an array of plain objects keyed by column name. Destructure it with const { rows } = ....
• ON CONFLICT (user_id) DO UPDATE is Postgres's upsert: insert a new row, or if that user_id already exists, run the update instead. EXCLUDED.points is the value you tried to insert — so this adds to the existing score. It's the exact shape a scoreboard or economy needs.
• CREATE TABLE IF NOT EXISTS makes the table on first run and does nothing after. When your schema starts changing, move to real migrations.

Parameterized queries are not optional

Notice every value went in as $1, $2 — never glued into the SQL string. This is the single most important habit with any SQL database:

⚠️ Heads up: Building a query by concatenating input — `... WHERE user_id = '${id}'` — is a SQL injection hole. A crafted value can read or destroy your data. Placeholders ($1, $2, …) hand the value to the driver separately, which escapes it safely and can never run it as SQL.

pg uses numbered placeholders ($1, $2), one per value, in the array's order. Always use them for anything that came from a user.

Client vs Pool: use a pool for anything long-lived

pg gives you two ways to connect, and the choice is simple:

A single Client left open for hours can silently die (an idle timeout, a network blip), and then every query throws until something reconnects. A Pool handles that for you: it keeps a few connections ready, hands one out per query, and revives dead ones. For a bot or web app, create one pool when the app starts and reuse it everywhere — never open a connection per request, and don't call pool.end() until the app is shutting down.

// At startup, once — then call pool.query(...) throughout your app.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                     // at most 10 connections
  idleTimeoutMillis: 30000,    // drop idle ones after 30s
});

More on why this matters across drivers: Connection pooling for long-running apps.

Verify it works

Start the server and watch the Console. The example prints the row it just wrote — [ { user_id: 'alice', points: 5 } ]. Seeing that array is the whole round trip confirmed: code to Postgres and back. Run the app again and the count climbs, proving the upsert is adding to the existing row rather than erroring on the duplicate key.

Troubleshooting

• password authentication failed — the user or password in your string is stale. Rotate the password on the Databases page, copy the fresh string into .env, and restart.
• ECONNREFUSED / getaddrinfo errors — wrong host or port. Use the exact host from the database page (not localhost); for your own Postgres server, use its public address and confirm it's running (on free, its timer may have stopped it).
• Cannot find module 'pg' — install it on the Packages page and restart so the app loads it.
• relation "scores" does not exist — the CREATE TABLE didn't run, or you're querying a table name that doesn't match. Run the create first, and mind that unquoted names are lower-cased in Postgres.

Everything past connecting is standard node-postgres — its official docs at node-postgres.com cover transactions, cursors, and more.

Next steps

• MongoDB from Node
• Connection pooling for long-running apps
• Run your own database server
• Designing tables for your bot