DbSandbox

💡 Why DbSandbox?

Testing databases is painful. You have to create records, run tests, and then cleanup the mess. If a test fails before cleanup, your database remains dirty, causing subsequent tests to fail.

• ❌ The Problem: Manual cleanup (DELETE FROM users) is error-prone and slow. Spinning up Docker containers for every test suite is resource-heavy.
• ✅ The Solution: DbSandbox wraps your test logic in a database transaction. When your test finishes (success or failure), it issues a ROLLBACK command. Your database remains pristine, always.

✨ Features

• 🛡️ Auto-Rollback: Guaranteed cleanup. Logic runs in a transaction that never commits.
• 🔌 Driver Agnostic: First-class support for PostgreSQL (pg), MySQL (mysql2), and SQLite (better-sqlite3).
• 🚀 Zero-Config: No need to setup complex test environments or Docker containers.
• 🧩 Typescript First: Fully typed for great DX.
• ⚡ Lightweight: Minimal abstraction overhead.

📦 Installation

Install the core package along with the driver for your database of choice.

# For PostgreSQL
npm install db-sandbox pg

# For MySQL
npm install db-sandbox mysql2

# For SQLite
npm install db-sandbox better-sqlite3

🚀 Quick Start

1. PostgreSQL Example

import { Pool } from 'pg';
import { DbSandbox, PostgresDriver } from 'db-sandbox';

// Setup your real connection pool
const pool = new Pool({ connectionString: 'postgres://...' });

// Initialize Sandbox
const sandbox = new DbSandbox(new PostgresDriver(pool));

await sandbox.run(async (tx) => {
  console.log('🔒 Inside Sandbox: Transaction Started');

  // Perform operations (Insert, Update, Delete)
  await tx.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
  
  const res = await tx.query('SELECT * FROM users');
  console.log(res[0].name); // 'Alice' exists here!
  
  // When this function returns (or throws), ROLLBACK is called automatically.
});

console.log('🔓 Outside Sandbox: Data is gone!');

2. SQLite Example

import Database from 'better-sqlite3';
import { DbSandbox, SQLiteDriver } from 'db-sandbox';

const db = new Database('my.db');
const sandbox = new DbSandbox(new SQLiteDriver(db));

await sandbox.run(async (tx) => {
  await tx.query('INSERT INTO items (name) VALUES (?)', ['Sword']);
  // ... test logic
});

🧠 Architecture

DbSandbox uses the Adapter Pattern to provide a unified interface over different SQL drivers.

1. Connect: Acquires a dedicated client from the pool.
2. Begin: Executes BEGIN / START TRANSACTION.
3. Run: Executes your callback function.
4. Rollback: Executes ROLLBACK in a finally block, ensuring it runs even if your code crashes.
5. Release: Returns the connection to the pool.

📚 API Reference

new DbSandbox(driver)

Creates a new sandbox instance.

• driver: An instance of PostgresDriver, MySQLDriver, or SQLiteDriver.

sandbox.run(callback)

Executes the callback within a transaction.

• callback: async (driver) => Promise<void>
• Returns: The result of your callback.

driver.query(sql, params?)

Executes a raw SQL query within the transaction.

• Returns: An array of rows (standardized across all drivers).

driver.getNativeClient()

Returns the underlying database client (e.g., pg.PoolClient). Useful if you need to pass the transactional client to an ORM like Drizzle or TypeORM.

await sandbox.run(async (driver) => {
  const pgClient = driver.getNativeClient();
  // Pass pgClient to your ORM...
});

🤝 Contributing

Contributions are welcome!

1. Fork the project
2. Create your feature branch
3. Commit your changes
4. Push to the branch
5. Open a Pull Request

📄 License

Distributed under the MIT License.