universal-db-config

Install drivers you need (e.g., mysql2, pg, mongodb, redis, sqlite3, mssql) alongside this package.

Quick start

const { connect } = require('universal-db-config');
(async () => {
	const db = await connect({ type: 'postgres', database: 'postgres' });
	const rows = await db.query('SELECT 1 as x');
	console.log(rows);
	await db.close();
})();

Query Builder placeholders: Postgres $1..$n, MSSQL @p1..@pn, others ?

API: connect(config), connectFromURL(url), db.query, db.batch, db.transaction, db.raw, db.builder, db.healthCheck, db.explain, db.analyze, db.getMetrics.

Postgres schemas

• Set default schema(s) via config schema: 'service_a' or env DB_SCHEMA=service_a,public.
• Adapter sets search_path on connect. QueryBuilder auto-qualifies unqualified tables with the first schema.

License: MIT

Use with ORM/ODM

• Use this library alongside your ORM/ODM. Let the ORM manage its own pool, and use UniversalDB for cached/retried raw queries and utilities.

Sequelize (Postgres example)

const { connect } = require('universal-db-config');
const { Sequelize } = require('sequelize');

const db = await connect({ type: 'postgres', host: 'localhost', database: 'app' });
const sequelize = new Sequelize('postgres://user:pass@localhost:5432/app');

// ORM models via Sequelize
await sequelize.authenticate();

// Raw/cached queries via UniversalDB (parameterized to prevent injection)
const [{ count }] = await db.query('SELECT count(*)::int AS count FROM users WHERE email LIKE $1', ['%@x.com'], { cache: true });

Mongoose (MongoDB example)

const { connect } = require('universal-db-config');
const mongoose = require('mongoose');

const db = await connect({ type: 'mongodb', host: 'localhost', database: 'app' });
await mongoose.connect('mongodb://localhost:27017/app');

// ODM models via Mongoose
// Native driver via UniversalDB when needed
const nativeDb = db.getConnection();
await nativeDb.collection('users').findOne({});

Security

• Always use parameter binding with raw SQL:
  • Postgres: $1, $2, ...
  • MSSQL: @p1, @p2, ...
  • MySQL/SQLite: ?
• Avoid string interpolation in SQL. The adapters and the QueryBuilder handle placeholders correctly.

TypeORM (Postgres)

// data-source.ts
import 'reflect-metadata';
import { DataSource } from 'typeorm';
export const AppDataSource = new DataSource({
	type: 'postgres',
	url: 'postgres://user:pass@localhost:5432/app',
	synchronize: false,
	logging: false,
});

// usage.ts
import { AppDataSource } from './data-source';
const { connect } = require('universal-db-config');
await AppDataSource.initialize();
const db = await connect({ type: 'postgres', database: 'app' });

// ORM query
const repo = AppDataSource.getRepository('users');
await repo.find({ where: { email: '[email protected]' } });

// Raw parameterized query
await db.query('SELECT * FROM users WHERE email = $1', ['[email protected]']);

Prisma (Postgres)

// schema.prisma
// datasource db { provider = "postgresql" url = env("DATABASE_URL") }

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
const { connect } = require('universal-db-config');
const db = await connect({ type: 'postgres', database: 'app' });

// ORM query
await prisma.user.findMany({ where: { email: { contains: '@x.com' } } });

// Raw parameterized query
await db.query('SELECT * FROM users WHERE id = $1', [1]);

Security hardening

• Parameter binding enforced in examples; never concat user input into SQL.
• MySQL multipleStatements disabled by default.
• Timeouts configured for pool connections and queries where supported.
• Retries only on transient errors; avoids retry storms with backoff.

Examples by database

MySQL / MariaDB

const { connect } = require('universal-db-config');
const db = await connect({ type: 'mysql', host: 'localhost', database: 'app' });
await db.query('CREATE TABLE IF NOT EXISTS users(id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255))');
await db.transaction(async (trx) => {
	await trx.query('INSERT INTO users(email) VALUES(?)', ['[email protected]']);
});
const users = await db.query('SELECT * FROM users WHERE email = ?', ['[email protected]']);

Sequelize (MySQL)

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('mysql://root:pass@localhost:3306/app');
await sequelize.authenticate();
// Keep using ORM for models/migrations;
// use universal-db-config for parameterized raw queries, caching, retries, and health checks
await db.query('SELECT * FROM users WHERE email = ?', ['[email protected]']);

PostgreSQL (multi-schema)

const db = await connect({ type: 'postgres', database: 'app', schema: ['service_a','public'] });
await db.query('CREATE SCHEMA IF NOT EXISTS service_a');
await db.query('CREATE TABLE IF NOT EXISTS service_a.users(id SERIAL PRIMARY KEY, email TEXT)');
await db.query('INSERT INTO service_a.users(email) VALUES($1)', ['[email protected]']);
const rows = await db.query('SELECT * FROM service_a.users WHERE email = $1', ['[email protected]']);

TypeORM (Postgres)

import { DataSource } from 'typeorm';
const ds = new DataSource({ type: 'postgres', url: 'postgres://user:pass@localhost:5432/app' });
await ds.initialize();
// Parameterized raw via UniversalDB
await db.query('SELECT * FROM service_a.users WHERE id = $1', [1]);

MongoDB

const db = await connect({ type: 'mongodb', host: 'localhost', database: 'app' });
await db.query(async (mdb) => mdb.collection('users').insertOne({ email: '[email protected]' }));
const one = await db.query(async (mdb) => mdb.collection('users').findOne({ email: '[email protected]' }));

Mongoose (MongoDB)

const mongoose = require('mongoose');
await mongoose.connect('mongodb://localhost:27017/app');
// Use ODM for models; native driver via UniversalDB when needed
await db.query(async (mdb) => mdb.collection('users').findOne({ email: '[email protected]' }));

Redis

const db = await connect({ type: 'redis', host: 'localhost' });
await db.query('set', ['key', 'value']);
const value = await db.query('get', ['key']);

SQLite

const db = await connect({ type: 'sqlite', filename: ':memory:' });
await db.query('CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT)');
await db.batch([
	{ sql: 'INSERT INTO users(id, email) VALUES(?, ?)', params: [1, '[email protected]'] },
	{ sql: 'INSERT INTO users(id, email) VALUES(?, ?)', params: [2, '[email protected]'] },
]);
const all = await db.query('SELECT * FROM users WHERE id IN (?, ?)', [1, 2]);

MSSQL

const db = await connect({ type: 'mssql', host: 'localhost', database: 'master', user: 'sa', password: 'Secret!' });
await db.query('IF OBJECT_ID(N"dbo.users", N"U") IS NULL CREATE TABLE dbo.users(id INT IDENTITY(1,1) PRIMARY KEY, email NVARCHAR(255))');
await db.transaction(async (trx) => {
	await trx.query('INSERT INTO dbo.users(email) VALUES(@p1)', ['[email protected]']);
});
const users = await db.query('SELECT * FROM dbo.users WHERE email = @p1', ['[email protected]']);

Microservices pattern (Postgres schemas)

// service-a uses schema service_a; service-b uses service_b
// Shared DB, isolated schemas. Each service sets its search_path.

// Service A
const serviceADb = await connect({ type: 'postgres', database: 'app', schema: ['service_a','public'] });
await serviceADb.query('CREATE TABLE IF NOT EXISTS service_a.orders(id SERIAL PRIMARY KEY, total NUMERIC)');

// Service B
const serviceBDb = await connect({ type: 'postgres', database: 'app', schema: ['service_b','public'] });
await serviceBDb.query('CREATE TABLE IF NOT EXISTS service_b.users(id SERIAL PRIMARY KEY, email TEXT)');

// Each service queries only its schema by default; cross-schema is explicit
const aOrders = await serviceADb.query('SELECT * FROM service_a.orders');
const bUsers = await serviceBDb.query('SELECT * FROM service_b.users');