Connection pooling

There are three ways to connect an app to a database, and two of them cause trouble at scale. Opening one connection per query is wasteful — every query pays the setup cost. Keeping one connection forever is fragile — an idle timeout or a network blip kills it, and then every query throws. The right answer for anything long-running is a connection pool: a small set of connections the driver keeps ready, hands out per query, and quietly replaces when one dies. This guide shows the pattern across the drivers, and — just as important — when you don't need it at all.

When a single connection is fine

Don't add a pool you don't need. A script that runs a task and exits — a one-off import, a nightly report, a migration — should just open one connection, do its work, and close it:

const db = await mysql.createConnection(process.env.DATABASE_URL);
// ...do the work...
await db.end();

The connection can't go stale in the seconds it's alive, and there's nothing to pool. Pooling earns its place the moment your app stays up for hours and handles work concurrently.

Why a bot or web server needs a pool

Two things break the single-connection approach over time:

• Idle connections drop. Databases (and the network in between) close connections that sit unused. The first query after that throws a "connection lost" error, and unless something reconnects, every query after it fails too.
• Concurrent work collides. A web server handling two requests at once, or a bot reacting to three events, needs to run queries in parallel. One connection serializes them; a pool runs them side by side.

A pool solves both: it keeps several connections warm, replaces any that die, and lets concurrent queries each grab their own. You create it once at startup and reuse it for the whole run.

Node + MySQL (mysql2)

Swap createConnection for createPool. The query API is identical — pool.execute(...) per query — and there's no manual end() between queries:

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  waitForConnections: true,
  connectionLimit: 5,      // keep this small — see sizing below
  queueLimit: 0,
});

// use it anywhere, for the app's whole life:
const [rows] = await pool.execute('SELECT * FROM scores WHERE user_id = ?', [id]);

Fire many queries at once and the pool handles them: tested with 20 concurrent queries through a pool of 5 connections, all completed correctly — the pool queued them onto its five connections and drained the queue. That's exactly the concurrency a busy bot or API needs.

Node + PostgreSQL (pg)

Same idea, use Pool instead of Client:

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 5 });

const { rows } = await pool.query('SELECT * FROM scores WHERE user_id = $1', [id]);

pool.query(...) checks a connection out, runs the query, and returns it to the pool automatically.

Node + MongoDB (mongodb)

The MongoDB driver already pools internally — you don't create a pool, you just avoid making more than one client. Create one MongoClient when the app starts and reuse it everywhere:

const { MongoClient } = require('mongodb');
const client = new MongoClient(process.env.DATABASE_URL, { maxPoolSize: 5 });
await client.connect();          // once, at startup
const db = client.db();          // reuse `db` for every query

The classic Mongo mistake is new MongoClient(...) per request — that spawns pool after pool and exhausts connections. One client, reused, is the whole trick.

Python drivers

Python's story is a little different per library:

• PyMySQL has no built-in pool — keep one connection open for the app's life and reconnect if a query fails, or let SQLAlchemy pool for you.
• psycopg (Postgres) offers psycopg_pool.ConnectionPool for the same benefit.
• pymongo, like the Node driver, pools internally — make one MongoClient at startup and reuse it.

Size the pool small — on purpose

Bigger is not better. A managed database allows a limited number of simultaneous connections, and every app sharing that host draws from the same budget. A pool of 5–10 is plenty for a single Falix server; setting connectionLimit: 100 doesn't make anything faster and can get your connections refused.

🎯 Good to know: SQLite has no network pool to worry about — it's a local file, and better-sqlite3 talks to it synchronously. Its answer to concurrency is WAL mode, not a connection pool. Pooling is strictly a networked-database concern.

Verify it works

Start the app and hit it with concurrent work — a few overlapping requests, or a burst of commands. With a pool, they all resolve; watch the Console for query results rather than "connection lost" errors. Leave the app idle for a while (past a typical idle timeout) and query again — a pool reconnects transparently where a lone connection would have thrown.

Troubleshooting

• "Too many connections" / connections refused — your pool limit (times the number of apps sharing the host) exceeds the database's cap. Lower connectionLimit / max to 5–10. See Database connection errors.
• Queries hang then fail — you're opening a pool (or client) per request and never closing them, so connections pile up. Create exactly one at startup and reuse it.
• "Connection lost" after idle periods — you're using a single long-lived connection, not a pool. Switch to a pool so dead connections get replaced.
• Mongo: connections keep climbing — you're calling new MongoClient(...) more than once. One client for the whole app.

Beyond this, pool tuning is standard driver territory — the mysql2, node-postgres, and MongoDB driver docs cover every option.

Next steps

• Connect your app to a database
• SQLite in depth
• Cleaning up old data
• Database connection errors