A self-hosted UPI & COD payment gateway with zero per-transaction platform fees, universal JavaScript checkout integration, dynamic QR payments, WhatsApp OTP authentication, and automated payment verification.
JavaScript
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';
import { runMigrations } from './migrate.js';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const repoRoot = path.resolve(__dirname, '../../..');
/**
* Checks if core tables exist and executes migrations if not yet initialized.
* @param {Database} db
*/
export function ensureDatabaseInitialized(db) {
try {
const row = db.queryOne("SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='merchants'");
if (!row || Number(row.count) === 0) {
return runMigrations(db);
}
} catch {
return runMigrations(db);
}
}
export class DatabaseBusyError extends Error {
constructor(message = 'Database lock contention timeout after bounded retries', originalError = null) {
super(message);
this.name = 'DatabaseBusyError';
this.code = 'DATABASE_BUSY';
this.isRetryable = true;
this.originalError = originalError;
}
}
export function isRetryableSqliteError(err) {
if (!err) return false;
const msg = (err.message || '').toLowerCase();
const code = (err.code || '').toUpperCase();
return (
msg.includes('database is locked') ||
msg.includes('busy') ||
code === 'SQLITE_BUSY' ||
code === 'SQLITE_LOCKED'
);
}
/**
* Universal Database Client for YOUPI PG.
* Uses native node:sqlite for high-performance, embedded zero-dependency local operation
* with full transactional ACID guarantees.
*/
export class Database {
/**
* @param {{ dbPath?: string, inMemory?: boolean, autoMigrate?: boolean }} [options={}]
*/
constructor(options = {}) {
if (options.inMemory) {
this.db = new DatabaseSync(':memory:');
} else {
let dbPath;
if (options.dbPath) {
dbPath = path.isAbsolute(options.dbPath)
? options.dbPath
: path.resolve(process.cwd(), options.dbPath);
} else if (process.env.SQLITE_DB_PATH) {
dbPath = path.isAbsolute(process.env.SQLITE_DB_PATH)
? process.env.SQLITE_DB_PATH
: path.resolve(repoRoot, process.env.SQLITE_DB_PATH);
} else {
dbPath = path.resolve(repoRoot, 'data/youpi_pg.sqlite');
}
const dir = path.dirname(dbPath);
if (!fs.existsSync(dir)) {
fs.mkdirSync(dir, { recursive: true });
}
this.db = new DatabaseSync(dbPath);
}
// Enable foreign keys, busy timeout, and WAL mode for maximum concurrency and integrity
this.db.exec('PRAGMA foreign_keys = ON;');
this.db.exec('PRAGMA busy_timeout = 5000;');
try {
this.db.exec('PRAGMA journal_mode = WAL;');
} catch {}
// Auto-initialize SQLite on first boot if core tables do not exist
if (!options.inMemory && options.autoMigrate !== false) {
ensureDatabaseInitialized(this);
}
}
/**
* Normalizes parameter markers from PostgreSQL style ($1, $2) to SQLite style (?, ?).
* @param {string} sql
* @returns {string}
*/
normalizeSql(sql) {
return sql.replace(/\$\d+/g, '?');
}
/**
* Executes a statement (INSERT, UPDATE, DELETE, DDL).
* @param {string} sql
* @param {any[]} [params=[]]
* @returns {{ changes: number, lastInsertRowid: number | bigint }}
*/
execute(sql, params = []) {
const normalized = this.normalizeSql(sql);
const stmt = this.db.prepare(normalized);
return stmt.run(...params);
}
run(sql, params = []) {
return this.execute(sql, params);
}
/**
* Queries multiple rows.
* @param {string} sql
* @param {any[]} [params=[]]
* @returns {any[]}
*/
query(sql, params = []) {
const normalized = this.normalizeSql(sql);
const stmt = this.db.prepare(normalized);
return stmt.all(...params);
}
/**
* Queries a single row.
* @param {string} sql
* @param {any[]} [params=[]]
* @returns {any | null}
*/
queryOne(sql, params = []) {
const normalized = this.normalizeSql(sql);
const stmt = this.db.prepare(normalized);
const res = stmt.get(...params);
return res !== undefined ? res : null;
}
/**
* Executes a batch of DDL or SQL statements.
* @param {string} sql
*/
exec(sql) {
this.db.exec(sql);
}
/**
* Executes a callback within an ACID database transaction.
* Automatically rolls back on exception.
* Defaults to BEGIN IMMEDIATE to acquire write intent upfront and avoid lock upgrade deadlocks.
* @template T
* @param {() => T} callback
* @param {{ mode?: 'DEFERRED' | 'IMMEDIATE' | 'EXCLUSIVE' }} [options={}]
* @returns {T}
*/
transaction(callback, options = {}) {
const mode = options.mode || 'IMMEDIATE';
const isNested = (this._transactionDepth || 0) > 0;
const nextDepth = (this._transactionDepth || 0) + 1;
const savepointName = `sp_${nextDepth}`;
if (isNested) {
this.db.exec(`SAVEPOINT ${savepointName};`);
} else {
this.db.exec(`BEGIN ${mode} TRANSACTION;`);
}
// A rejected BEGIN or SAVEPOINT must not leave a phantom nested transaction.
this._transactionDepth = nextDepth;
try {
const result = callback();
if (isNested) {
this.db.exec(`RELEASE SAVEPOINT ${savepointName};`);
} else {
this.db.exec('COMMIT;');
}
return result;
} catch (err) {
try {
if (isNested) {
this.db.exec(`ROLLBACK TO SAVEPOINT ${savepointName};`);
} else {
this.db.exec('ROLLBACK;');
}
} catch {
// Ignore rollback error if already rolled back
}
throw err;
} finally {
this._transactionDepth--;
}
}
/**
* Executes a database operation with bounded retries and exponential backoff on retryable lock contention.
* @template T
* @param {() => T} operation
* @param {number} [maxRetries=3]
* @param {number} [baseDelayMs=50]
* @returns {T}
*/
withRetry(operation, maxRetries = 3, baseDelayMs = 50) {
let attempt = 0;
while (true) {
try {
return operation();
} catch (err) {
attempt++;
if (attempt > maxRetries || !isRetryableSqliteError(err)) {
if (isRetryableSqliteError(err)) {
throw new DatabaseBusyError(`Database lock contention after ${attempt} retry attempt(s): ${err.message}`, err);
}
throw err;
}
const delay = Math.min(baseDelayMs * Math.pow(2, attempt - 1), 500) + Math.floor(Math.random() * 20);
const end = Date.now() + delay;
while (Date.now() < end) {}
}
}
}
/**
* Closes the database connection.
*/
close() {
this.db.close();
}
}
let defaultDbInstance = null;
/**
* Returns the singleton database instance.
* @param {{ dbPath?: string, inMemory?: boolean }} [options={}]
* @returns {Database}
*/
export function getDatabase(options = {}) {
if (!defaultDbInstance) {
defaultDbInstance = new Database(options);
ensureDatabaseInitialized(defaultDbInstance);
}
return defaultDbInstance;
}