Package Exports

sqlitebruv
sqlitebruv/dist/index.js

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sqlitebruv) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

SQLiteBruv Query Builder

A Tiny 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: "john@example.com" })
  .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();

// 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

🎮 Features

Cloudflare D1

D1 API integration

Turso

HTTP API support

📊 Performance

Prepared statements
Connection pooling
Built-in Query caching

🚔 Security

Block dangerous patterns
Block Complex subqueries
Block very long string parameters

🤝 Contributing

Fork the repository
Create feature branch (git checkout -b feature/amazing)
Commit changes (git commit -am 'Add amazing feature')
Push branch (git push origin feature/amazing)
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