SQLiteBruv Query Builder

A type-safe, secure SQLite query builder with D1/Turso support with built-in migrations and security features.

Features

• 🛡️ Security-first design with SQL injection prevention
• 📡 JSON interface for http no sql queries
• 🔄 Automatic schema migrations
• 🏃‍♂️ In-memory caching
• 🌐 Cloudflare D1 & Turso support
• 📝 Type-safe queries
• 🔍 Query validation & sanitization
• 📊 Schema management
• 🌠 Bunjs Support 100%

## Installation

npm install sqlite-bruv

🚀 Updates

• Light weight: Zero dependency and small size.
• Bun-Ready: built for Bunjs
• Platform Support:
  • Cloudflare D1
  • Turso
  • Local SQLite
  • raw query output
• Security: SQL injection prevention, query validation, parameter sanitization
• Type Safety: Full TypeScript support with inferred types
• Migrations: Automatic schema diff detection and migration generation
• Caching: Built-in memory caching with invalidation
• Relations: Support for one-to-one and one-to-many relationships

📦 Installation

# bun
bun add sqlite-bruv

# npm
npm install sqlite-bruv

Usage/Examples

import { SqliteBruv, Schema } from "sqlite-bruv";

// Define your schema
const UserSchema = new Schema<{
  name: string;
  email: string;
  role: "admin" | "user";
  createdAt: Date;
}>({
  name: "users",
  columns: {
    name: { type: "TEXT", required: true },
    email: { type: "TEXT", unique: true },
    role: { type: "TEXT", default: () => "user" },
    createdAt: { type: "DATETIME", default: () => new Date() },
  },
});

const PostSchema = new Schema({
  name: "posts",
  columns: {
    title: { type: "TEXT", required: true },
    content: { type: "TEXT" },
    userId: {
      type: "TEXT",
      target: "users",
      relationType: "ONE",
    },
  },
});

const CommentSchema = new Schema({
  name: "comments",
  columns: {
    content: { type: "TEXT", required: true },
    postId: {
      type: "TEXT",
      target: "posts",
      relationType: "MANY",
    },
  },
});

// Initialize database

const db = new SqliteBruv({
  schema: [UserSchema],
});

Platform-Specific Setup Cloudflare D1

const db = new SqliteBruv({
  D1: {
    accountId: process.env.CF_ACCOUNT_ID,
    databaseId: process.env.D1_DATABASE_ID,
    apiKey: process.env.CF_API_KEY,
  },
  schema: [UserSchema, PostSchema, CommentSchema],
});

Turso;

const db = new SqliteBruv({
  turso: {
    url: process.env.TURSO_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
  },
  schema: [UserSchema, PostSchema, CommentSchema],
});

Example usage:

const queryBuilder = new SqliteBruv({
  schema: [UserSchema, PostSchema, CommentSchema],
});

// Insert
await queryBuilder
  .from("users")
  .insert({ name: "John Doe", email: "[email protected]" })
  .then((changes) => {
    // console.log({ changes });
  });

// Update
await queryBuilder
  .from("users")
  .where("id = ?", 1)
  .update({ name: "Jane Doe" })
  .then((changes) => {
    // console.log({ changes });
  });

// Search
await queryBuilder
  .from("users")
  .where("id = ?", 1)
  .andWhere("name LIKE ?", `%oh%`)
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// Delete
await queryBuilder
  .from("users")
  .where("id = ?", 1)
  .delete()
  .then((changes) => {
    console.log({ changes });
  });

// Get all users
queryBuilder
  .from("users")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// Get one user
await queryBuilder
  .from("users")
  .where("id = ?", 1)
  .getOne()
  .then((changes) => {
    // console.log({ changes });
  });

// Select specific columns
await queryBuilder
  .from("users")
  .select("id", "name")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// Where conditions
await queryBuilder
  .from("users")
  .where("age > ?", 18)
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// AndWhere conditions
await queryBuilder
  .from("users")
  .where("age > ?", 18)
  .andWhere("country = ?", "USA")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// OrWhere conditions
await queryBuilder
  .from("users")
  .where("age > ?", 18)
  .orWhere("country = ?", "Canada")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// Limit and Offset
await queryBuilder
  .from("users")
  .limit(10)
  .offset(5)
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

// OrderBy
await queryBuilder
  .from("users")
  .orderBy("name", "ASC")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

await queryBuilder
  .from("users")
  .orderBy("name", "ASC")
  .get()
  .then((changes) => {
    // console.log({ changes });
  });

💡 Advanced Usage

Complex Queries

// Relations and joins
const posts = await db
  .from("posts")
  .select("posts.*", "users.name as author")
  .where("posts.published = ?", true)
  .andWhere("posts.views > ?", 1000)
  .orderBy("posts.createdAt", "DESC")
  .limit(10)
  .get();

// Transactions
await db.transaction(async (trx) => {
  await trx.from("users").insert({ name: "John" });
  await trx.from("profiles").insert({ userId: 1 });
});

// Raw queries with safety
await db.raw("SELECT * FROM users WHERE id = ?", [userId]);

// Cache usage
const users = await db
  .from("users")
  .select("*")
  .where("active = ?", true)
  .cacheAs("active-users")
  .get();

// Cache invalidation
db.invalidateCache("active-users");

Using from over the network via JSON interface

//  JSON interface structure
interface Query {
  from: string;
  select?: string[];
  where?: {
    condition: string;
    params: any[];
  }[];
  andWhere?: {
    condition: string;
    params: any[];
  }[];
  orWhere?: {
    condition: string;
    params: any[];
  }[];
  orderBy?: {
    column: string;
    direction: "ASC" | "DESC";
  };
  limit?: number;
  offset?: number;
  cacheAs?: string;
  invalidateCache?: string;
  action?: "get" | "getOne" | "insert" | "update" | "delete" | "count";
  /**
  ### For insert and update only
  */
  data?: any;
}
// Example usage in an Express.js route
import express from "express";
const app = express();
app.use(express.json());

app.post("/execute-query", async (req, res) => {
  try {
    const queryInput = req.body;
    // do your role authentication here,
    // use query.from to know the table being accessed
    const result = await qb.executeJsonQuery(queryInput);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

🔄 Migrations

Migrations are automatically generated when schema changes are detected:

-- Generated in ./Bruv-migrations/timestamp_add_user_role.sql:
-- Up
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';

-- Down
ALTER TABLE users DROP COLUMN role;

Setting up your schema

This if your DB is new and your are not using any orm, just call toString and query your db with the queryBuilder.raw() method.

Note: raw is not secured, it can be used to apply migrations too. be careful what you do with queryBuilder.raw().

console.log(user.toString());
const raw = await qb.raw(user.toString());
console.log({ raw });

🛡️ Security Features

The query builder implements several security measures to prevent SQL injection and malicious queries:

• Parameter validation (max 100 params)
• SQL injection prevention
• Query timeout limits
• Rate limiting
• String length validation
• Dangerous pattern detection
• Allowed parameter types: string, number, boolean, null

Condition Validation

• Whitelisted operators: =, >, <, >=, <=, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL
• Blocked dangerous patterns: ; DROP, DELETE, UPDATE, INSERT, ALTER, EXEC, UNION
• Parameterized queries enforced

Security Examples

// ✅ Safe queries
db.from("users")
  .where("email LIKE ?", "%@example.com") // ✅ Safe
  .andWhere("role = ?", "admin") // ✅ Safe
  .get();
db.from("users")
  .where("age > ?", 18)
  .andWhere("status = ?", "active")
  .orWhere("role IN (?)", ["admin", "mod"]);

// ❌ These will throw security errors:
db.where("1=1; DROP TABLE users;"); // Dangerous pattern
db.where("col = (SELECT ...)"); // Complex subqueries blocked
db.where("name = ?", "a".repeat(1001)); // String too long
db.where("email = " + userInput);

🎮 Platform-Specific Features

Cloudflare D1

• Automatic edge deployment
• D1 API integration
• Built-in caching Turso
• HTTP API support
• Connection pooling 📊 Performance
• Prepared statements
• Connection pooling
• Query caching 🤝 Contributing

1. Fork the repository
2. Create feature branch (git checkout -b feature/amazing)
3. Commit changes (git commit -am 'Add amazing feature')
4. Push branch (git push origin feature/amazing)
5. Open a Pull Request

📝 License

MIT License - see LICENSE file

🆘 Support

Contributions are always welcome! creates issues and pull requests. Documentation GitHub Issues Discord Community