Node.js with PostgreSQL — pg driver vs Knex for SQL queries that don't sink your revenue

Your Node.js backend works, but every query to the database feels like negotiating with a supplier who never replies. Response times go up, connections pile up, and the client asks why "the site is slow". The problem is almost never Node or PostgreSQL alone. It's how you connect them.

We, at Meteora Web, work on this every day. We've seen projects slow down not because of the database, but because of poorly configured drivers or query builders used as crutches. In this guide, we show you the real difference between using the pg driver directly and relying on Knex, when to use one over the other, and how to avoid the mistakes that cost dearly in production.

Why is the PostgreSQL connection the first bottleneck of your app?

PostgreSQL handles thousands of connections but doesn't give them away. Each open connection consumes memory and processes. If your Node server opens a connection per request, you're building a queue in front of the database. The result? Growing latency and users abandoning their carts.

The solution is a connection pool. The pg driver provides pg.Pool, which reuses existing connections instead of creating new ones. It's the first thing we check when a client says the site slows down during peak hours.

How to configure a connection pool with pg

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

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 10, // maximum number of clients in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

module.exports = pool;

This is the bare minimum. Without a pool, every request opens and closes a connection: a huge cost. With the pool, connections are reused and the database breathes.

Configuration checklist:

• Always use pg.Pool in production, never new Client() for each query.
• Set connectionTimeoutMillis to avoid hanging requests.
• Put credentials in environment variables, never in code.

When to use the raw pg driver and when to switch to Knex?

The pg driver is the lowest level: you write raw SQL and send it. Knex is a query builder: you build queries in JavaScript and it translates them into SQL. The question isn't "which is better", but "which makes you lose less time and money".

If your project has simple queries and few tables, the raw driver is enough. If you have complex joins, migrations to manage, and want to avoid SQL syntax errors, Knex gives you a safety net. We use Knex when the data model grows and queries become hard to maintain by hand.

A concrete query example with pg

const pool = require('./pool');

async function getOrdersByUser(userId) {
  const result = await pool.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );
  return result.rows;
}

Note the $1 for parameters: never concatenate values directly into the SQL string. It's the fastest way to open the door to SQL injection, and we see it more often than we'd like.

The same query with Knex

const knex = require('./knex');

async function getOrdersByUser(userId) {
  return knex('orders')
    .select('*')
    .where('user_id', userId)
    .orderBy('created_at', 'desc');
}

Knex handles parameters automatically and the code is more readable. But beware: Knex won't save you from inefficient queries. If you write a where that ignores indexes, the result is the same disaster.

How to manage database migrations without going to production with a lit match?

Migrations are the way to version your database schema. Without them, every change to the structure is a leap in the dark. Knex has an integrated migration system that works well: you create a file, define up and down, and you're done.

Creating and applying a migration with Knex

npx knex migrate:make create_orders_table

exports.up = function(knex) {
  return knex.schema.createTable('orders', (table) => {
    table.increments('id');
    table.integer('user_id').unsigned().notNullable();
    table.decimal('total', 10, 2).notNullable();
    table.timestamps(true, true);
    table.foreign('user_id').references('users.id');
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('orders');
};

With the raw pg driver, you'd have to write SQL scripts by hand and manage execution order. Knex gives you a clear structure and rollback capability. For a growing project, it's an investment that pays off.

Common mistake: applying migrations in production without testing them locally first. The result is a broken database and customers who can't order. Always test in a staging environment.

Which performance mistakes make you lose customers and how to avoid them?

The perfect query doesn't exist if indexes don't. PostgreSQL has a great optimizer, but without proper indexes, every SELECT becomes a full table scan. Data grows and response time increases linearly.

How to spot slow queries

Enable slow query logging in PostgreSQL and use EXPLAIN ANALYZE to understand what's happening. We always do this before touching anything.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

If you see Seq Scan on a large table, it's time to add an index.

CREATE INDEX idx_orders_user_id ON orders(user_id);

This single command can reduce response time from seconds to milliseconds. An e-commerce client with thousands of orders feels it immediately.

Performance checklist:

• Use EXPLAIN ANALYZE on every query that takes more than 100ms.
• Add indexes on columns used in WHERE, JOIN, and ORDER BY.
• Don't use SELECT * in production: select only the columns you need.

How to protect data and credentials in a Node.js app with PostgreSQL?

Security is systematically underestimated in Italian SMEs. Credentials in plain text in code, databases exposed to the internet, no backups. We see it every day. With pg and Knex, you can make a difference with a few precautions.

Protecting credentials with environment variables

// .env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_USER=app_user
DB_PASSWORD=a_strong_password

// config.js
require('dotenv').config();

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

Never commit the .env file to GitHub. Add it to .gitignore and use a secret manager for production.

Also, limit the database user's permissions: if the app only needs to read and write on some tables, don't grant admin privileges. A SQL injection attack with a limited user does much less damage.

What to do now for a Node.js backend with PostgreSQL that handles traffic

You don't need to rewrite everything. Just start with the points that burn the most.

Immediate actions:

• Configure pg.Pool with timeout and max connections parameters suited to your traffic.
• Replace queries with concatenated parameters using prepared statements or Knex.
• Analyze slow queries with EXPLAIN ANALYZE and add missing indexes.
• Move credentials to environment variables and limit database user permissions.
• If the project grows, adopt Knex for migrations and schema management.

The database shouldn't be the bottleneck of your app. With the right practices, Node.js and PostgreSQL form a duo that handles traffic and grows with your business. If you want to dive deeper into choosing Node.js for your next project, read our main guide on Node.js for the backend.