SQL-Buns 🥯

SQL-Buns is a lightweight tool built for developers who love writing raw SQL but could use a few helpers to simplify their workflow.

It's not an ORM and won't get in your way. Instead of abstracting SQL, it gives you clean, reliable utilities that act as a bridge between your code and the database.

It currently supports PostgreSQL, MySQL, and SQLite.

✨ Why SQL-Buns?

• Direct SQL First: You're in charge of your queries.
• Four Sharp Tools (for now):
  • getSingleRow → A function that fetches exactly one row (and throws an error if it finds zero or more than one).
  • getAllRows → A function that executes a parameterized SELECT query and returns all matching rows.
  • createRowAndReturn → A quick way to insert a row and get the result back right away.
  • batchTransaction → Clean utility function to executes multiple SQL commands as a single atomic transaction.
• It provides clear errors like RecordDoesNotExist and NonUniqueRecordError.
• Simple Setup: No hidden configs. Just respects your .env file setup.
• Lean and Mean: This isn't a bloated ORM; it’s just the SQL help you need.

SQL-Buns is for developers who want to keep their queries clean and avoid writing the same boilerplate code over and over.

---

🚀 Installation

npm install @anclatechs/sql-buns

⚙️ Setup

SQL-Buns connects to your database using environment variables.

You can use a single DATABASE_URL in your .env file or define each setting separately — whichever fits your workflow best.

Option 1: Using a connection URL

Supported formats:

# PostgreSQL
DATABASE_URL=postgres://USER:PASSWORD@HOST:PORT/DBNAME

# MySQL
DATABASE_URL=mysql://USER:PASSWORD@HOST:PORT/DBNAME

# SQLite
DATABASE_URL=file:./database.sqlite

Option 2: Using individual variables

If DATABASE_URL is not provided, SQL-Buns falls back to these:

DATABASE_ENGINE=postgres  # or mysql | sqlite
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_USER=myuser
DATABASE_PASSWORD=secret
DATABASE_NAME=mydb # or ./database.sqlite

Nota Bene:

When both DATABASE_URL and individual configs are present, DATABASE_URL takes priority.

---

🔨 Usage

N.B.: Before using the library, ensure your .env file is initialized. This guarantees that DATABASE_ENGINE and your other database variables are available to SQL-Buns.

How to Import:

const {
  pool,
  getAllRows,
  getSingleRow,
  createRowAndReturn,
  batchTransaction,
  RecordDoesNotExist,
  NonUniqueRecordError,
} = require("@anclatechs/sql-buns");

Or if you like the modular path:

const { getSingleRow } = require("@anclatechs/sql-buns/functions");
const { RecordDoesNotExist } = require("@anclatechs/sql-buns/errors");

---

Simple Pool

If you want to work directly with the underlying driver, SQL-Buns also exposes a pool object.

• Postgres → uses pg.Pool
• MySQL → uses mysql2/promise pool
• SQLite → supports common methods: get, all, run, exec, each, close

You don’t lose any power — all native pool methods are available.

PostgreSQL Example

const { pool } = require("@anclatechs/sql-buns");

async function yourFunction() {
  const result = await pool.query("SELECT NOW()");
  console.log(result.rows);
}

async function yourFunctionII() {
  const connection = await pool.connect();
  try {
    await connection.query("BEGIN");

    await connection.query("...");
    await connection.query("...");
    await connection.query("COMMIT");
  } catch (err) {
    await connection.query("ROLLBACK");
    throw err;
  } finally {
    connection.release();
  }
}

---

MySQL Example

const { pool } = require("@anclatechs/sql-buns");

async function yourFunction() {
  const [rows] = await pool.query("SELECT NOW()");
  console.log(rows);
}

async function yourFunctionII() {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    await connection.query("...");
    await connection.query("...");
    await connection.commit();
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    connection.release();
  }
}

SQLite Example

const { pool } = require("@anclatechs/sql-buns");

async function yourFunction() {
  const row = await pool.get("SELECT 1 as value");
  console.log(row.value);
}

---

Utility Functions

⚡ Tip: This means you can use SQL-Buns helpers (getAllRows, getSingleRow, batchTransaction, createRowAndReturn etc.) when you want safety, and you can always drop down to the raw driver when you need full flexibility.

// Load environment variables first
require("dotenv").config();

const {
  pool,
  getAllRows,
  getSingleRow,
  createRowAndReturn,
  RecordDoesNotExist,
} = require("@anclatechs/sql-buns");

# Examples
const users = await getAllRows("SELECT * FROM users");

PostgreSQL Example

...
try {
    // Insert and return row
    const user = await createRowAndReturn(
      "users",
      "INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3)",
      ["Olaronke", "Alice", "olaronke.alice@example.com"]
    );

    console.log("Inserted user:", user);

    // Fetch a single row
    const fetched = await getSingleRow(
      "SELECT * FROM users WHERE email = $1",
      ["olaronke.alice@example.com"]
    );

    console.log("Fetched user:", fetched);

  } catch (err) {
    if (err instanceof RecordDoesNotExist) {
      console.error("User not found");
    } else {
      console.error("Unexpected error:", err);
    }
  }

  async function onboardNewUser() {
  const queries = [
    {
      sql: "INSERT INTO users (name, email) VALUES ($1, $2)",
      params: ["Olaronke Alice", "ola@example.com"],
    },
    {
      sql: "INSERT INTO wallets (user_id, balance) VALUES ((SELECT id FROM users WHERE email = $1), $2)",
      params: ["ola@example.com", 1000],
    },
    {
      sql: "INSERT INTO logs (user_id, action) VALUES ((SELECT id FROM users WHERE email = $1), $2)",
      params: ["ola@example.com", "USER_ONBOARD"],
    },
    {
      sql: "INSERT INTO user_roles (user_id, role) VALUES ((SELECT id FROM users WHERE email = $1), $2)",
      params: ["ola@example.com", "basic"],
    },
  ];

  const result = await batchTransaction(queries);
  if(result.success){
    // 
  }else{
    // Automated rollback
  }
}

onboardNewUser();

MySQL or Sqlite Example

const user = await createRowAndReturn(
  "users",
  "INSERT INTO users (first_name, last_name, email) VALUES (?, ?)",
  ["Olaronke", "Alice", "olaronke.alice@example.com"]
);
console.log("Inserted user:", user);

// Fetch a single row
const fetched = await getSingleRow("SELECT * FROM users WHERE email = ?", [
  "olaronke.alice@example.com",
]);


  async function onboardNewUser() {
  const queries = [
    {
      sql: "INSERT INTO users (name, email) VALUES (?, ?)",
      params: ["Olaronke Alice", "ola@example.com"],
    },
    {
      sql: "INSERT INTO wallets (user_id, balance) VALUES ((SELECT id FROM users WHERE email = ?), ?)",
      params: ["ola@example.com", 1000],
    },
    {
      sql: "INSERT INTO logs (user_id, action) VALUES ((SELECT id FROM users WHERE email = ?), ?)",
      params: ["ola@example.com", "USER_ONBOARD"],
    },
    {
      sql: "INSERT INTO user_roles (user_id, role) VALUES ((SELECT id FROM users WHERE email = ?), ?)",
      params: ["ola@example.com", "basic"],
    },
  ];

  const result = await batchTransaction(queries);
  if(result.success){
    // 
  }else{
    // Automated rollback
  }
}

onboardNewUser();

🛣️ Roadmap

SQL-Buns is intentionally not an ORM — it’s about giving you direct access with an extra-light utility belt. But we know some teams want more structure.

• Group transaction helper → wrap multiple SQL operations together as one atomic operation. They either all succeed or all fail. If one query breaks, nothing is saved.

• Bulk insert with return → inserting one row and returning it is straightforward; this will let you insert many rows at once and get them all back in a single call.

• Optional database modeling → Defining lightweight models on top of your queries, without locking you in.

• Migrations management → simple and native, so you can version and evolve your schema alongside your codebase.

We’ll keep things lean, transparent, and optional. Nothing heavy, nothing you can’t see through.

---

📜 License

MIT — use it, hack it, ship it.

🔥 SQL-Buns keeps you close to the metal, gives you sharp tools, and stays out of your way.