Package Exports

@truto/sqlite-builder

Readme

🏗️ truto-sqlite-builder

Safe, zero-dependency template-literal tag for SQLite queries in any JS environment.

@truto/sqlite-builder provides a secure and ergonomic way to build SQLite queries using tagged template literals. It prevents SQL injection attacks through parameterized queries while offering convenient helper functions for common SQL patterns.

✨ Features

🔒 Injection-safe: All values are parameterized, preventing SQL injection
🚫 Defense in depth: Multiple security layers including stacked query detection
🪶 Zero dependencies: Pure TypeScript/JavaScript with no runtime dependencies
🌍 Universal: Works in Bun, Node.js, Deno, and modern browsers
🎯 TypeScript-first: Full type safety with excellent IDE support
🔧 Helper functions: Built-in utilities for identifiers, IN clauses, and more
🔍 JSON Filter Language: MongoDB-style JSON filters for WHERE clauses
⚡ Lightweight: Minimal bundle size with tree-shaking support

📦 Installation

bun add @truto/sqlite-builder

npm install @truto/sqlite-builder

yarn add @truto/sqlite-builder

pnpm add @truto/sqlite-builder

🚀 Quick Start

import sqlite3 from 'better-sqlite3'
import { sql, compileFilter } from '@truto/sqlite-builder'

const db = new sqlite3('database.db')

// Simple query
const name = 'Alice'
const { text, values } = sql`SELECT * FROM users WHERE name = ${name}`
const users = db.prepare(text).all(...values)

// JSON Filter queries
const filter = {
  name: { like: 'John%' },
  age: { gte: 18, lt: 65 },
  or: [{ email: { regex: '.*@example.com$' } }, { phone: { exists: false } }],
}

const { text: whereText, values: whereValues } = compileFilter(filter)
const query = sql`
  SELECT * FROM users
  WHERE ${sql.raw(whereText)}
`

const results = db.prepare(query.text).all(...query.values)

📖 API Reference

`sql` Tagged Template

The main function for building SQL queries.

const query = sql`SELECT * FROM users WHERE id = ${userId}`
// Returns: { text: "SELECT * FROM users WHERE id = ?", values: [userId] }

Parameters:

Template strings and interpolated values
Returns a frozen SqlQuery object with text and values properties

`sql.ident(identifier: string | readonly (string | SqlFragment)[])`

Safely quotes SQL identifiers (table names, column names, etc.). Accepts single identifiers, arrays of identifiers, or mixed arrays containing both identifiers and SQL fragments.

// Single identifier
const table = 'users'
const query = sql`SELECT * FROM ${sql.ident(table)}`
// Returns: { text: 'SELECT * FROM "users"', values: [] }

// Qualified identifiers (table.column)
const qualifiedQuery = sql`SELECT ${sql.ident('u.name')}, ${sql.ident('u.email')} FROM users u`
// Returns: { text: 'SELECT "u.name", "u.email" FROM users u', values: [] }

// Array of identifiers (useful for column lists)
const columns = ['name', 'email', 'created_at']
const selectQuery = sql`SELECT ${sql.ident(columns)} FROM users`
// Returns: { text: 'SELECT "name", "email", "created_at" FROM users', values: [] }

// Mixed arrays with qualified and simple identifiers
const mixedColumns = ['u.id', 'name', 'u.email', 'p.title']
const joinQuery = sql`SELECT ${sql.ident(mixedColumns)} FROM users u JOIN posts p ON u.id = p.user_id`
// Returns: { text: 'SELECT "u.id", "name", "u.email", "p.title" FROM users u JOIN posts p ON u.id = p.user_id', values: [] }

// Mixed arrays with identifiers and SQL fragments
const mixedColumns = ['id', 'name', sql.raw('UPPER(email) as email_upper')]
const mixedQuery = sql`SELECT ${sql.ident(mixedColumns)} FROM users`
// Returns: { text: 'SELECT "id", "name", UPPER(email) as email_upper FROM users', values: [] }

// Mixed arrays with parameterized fragments
const status = 'premium'
const dynamicColumns = [
  'id',
  'name',
  sql`CASE WHEN status = ${status} THEN 'Premium' ELSE 'Regular' END as user_type`,
]
const dynamicQuery = sql`SELECT ${sql.ident(dynamicColumns)} FROM users`
// Returns: { text: 'SELECT "id", "name", CASE WHEN status = ? THEN \'Premium\' ELSE \'Regular\' END as user_type FROM users', values: ['premium'] }

// In INSERT statements
const insertColumns = ['name', 'email', 'age']
const insertQuery = sql`
  INSERT INTO users (${sql.ident(insertColumns)}) 
  VALUES (${name}, ${email}, ${age})
`
// Returns: { text: 'INSERT INTO users ("name", "email", "age") VALUES (?, ?, ?)', values: [name, email, age] }

Security: Only accepts valid ANSI identifiers (simple: name, qualified: table.column) for string elements. SQL fragments are passed through as-is.

`sql.in(array: readonly unknown[])`

Creates parameterized IN clauses from arrays.

const ids = [1, 2, 3]
const query = sql`SELECT * FROM users WHERE id IN ${sql.in(ids)}`
// Returns: { text: "SELECT * FROM users WHERE id IN (?,?,?)", values: [1, 2, 3] }

Features:

Rejects empty arrays (would create invalid SQL)
Warns for arrays with >1000 items (performance consideration)

`sql.raw(rawSql: string)`

Embeds raw SQL without parameterization. ⚠️ Use with extreme caution!

const query = sql`SELECT * FROM users WHERE created_at > ${sql.raw('datetime("now", "-1 day")')}`
// Returns: { text: 'SELECT * FROM users WHERE created_at > datetime("now", "-1 day")', values: [] }

⚠️ Warning: Never use sql.raw() with user input. Only use with trusted, static SQL fragments.

`sql.join(fragments: SqlFragment[], separator?: string)`

Joins multiple SQL fragments with a separator.

const conditions = [
  sql`name = ${'John'}`,
  sql`age = ${30}`,
  sql`active = ${true}`,
]

const query = sql`SELECT * FROM users WHERE ${sql.join(conditions, ' AND ')}`
// Returns: { text: "SELECT * FROM users WHERE name = ? AND age = ? AND active = ?", values: ['John', 30, true] }

🔍 JSON Filter Language

Build complex WHERE clauses using MongoDB-style JSON filters. Perfect for APIs and dynamic queries.

`compileFilter(filter: JsonFilter): FilterResult`

Compiles a JSON filter object into a parameterized SQL WHERE clause.

import { compileFilter } from '@truto/sqlite-builder'

const filter = {
  status: 'ACTIVE',
  age: { gte: 18, lt: 65 },
}

const result = compileFilter(filter)
// Returns: { text: '(("status" = ? AND "age" >= ? AND "age" < ?))', values: ['ACTIVE', 18, 65] }

// Use with the main sql template
const query = sql`
  SELECT * FROM users
  WHERE ${sql.raw(result.text)}
`

Supported Operators

Operator Family	JSON Form	SQL Fragment	Description
Equality	`"field": value`	`"field" = ?`	Direct value comparison
Inequality	`"field": { "ne": value }`	`"field" <> ?`	Not equal comparison
Comparison	`"field": { "gt": value }`	`"field" > ?`	Greater than, gte, lt, lte
Set Membership	`"field": { "in": [1, 2, 3] }`	`"field" IN (?,?,?)`	Value in array
Negative Set	`"field": { "nin": [1, 2] }`	`"field" NOT IN (?,?)`	Value not in array
NULL Checks	`"field": { "exists": false }`	`"field" IS NULL`	Check for NULL/NOT NULL
LIKE Patterns	`"field": { "like": "john%" }`	`"field" LIKE ?`	Pattern matching
Case-insensitive LIKE	`"field": { "ilike": "%DOE%" }`	`"field" LIKE ? COLLATE NOCASE`	Case-insensitive patterns
Regular Expressions	`"field": { "regex": "^[A-Z]+" }`	`"field" REGEXP ?`	Regex patterns (requires extension)
Logical AND	`"and": [filter1, filter2]`	`(filter1 AND filter2)`	All conditions must match
Logical OR	`"or": [filter1, filter2]`	`(filter1 OR filter2)`	Any condition must match
JSON Path	`"profile.email": "test@example.com"`	`json_extract("profile", '$.email') = ?`	Query JSON column fields

Filter Examples

Basic Operations

// Equality and comparison
const filter1 = {
  status: 'ACTIVE',
  age: { gte: 18, lt: 65 },
  score: { gt: 80, lte: 100 },
}
// SQL: (("status" = ? AND "age" >= ? AND "age" < ? AND "score" > ? AND "score" <= ?))

// Set membership
const filter2 = {
  role: { in: ['ADMIN', 'EDITOR'] },
  department: { nin: ['ARCHIVED', 'DELETED'] },
}
// SQL: (("role" IN (?,?) AND "department" NOT IN (?,?)))

// NULL checks
const filter3 = {
  email: { exists: true }, // IS NOT NULL
  deleted_at: { exists: false }, // IS NULL
}
// SQL: (("email" IS NOT NULL AND "deleted_at" IS NULL))

Pattern Matching

// LIKE patterns
const filter4 = {
  username: { like: 'john%' }, // Starts with 'john'
  email: { ilike: '%@EXAMPLE.COM%' }, // Case-insensitive contains
}
// SQL: (("username" LIKE ? AND "email" LIKE ? COLLATE NOCASE))

// Regular expressions (requires REGEXP extension)
const filter5 = {
  email: { regex: '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' },
  phone: { regex: '^\\+1[0-9]{10}$' },
}
// SQL: (("email" REGEXP ? AND "phone" REGEXP ?))

Logical Operators

// AND operator (explicit)
const filter6 = {
  and: [
    { status: 'ACTIVE' },
    { age: { gte: 18 } },
    { country: { in: ['US', 'CA', 'GB'] } },
  ],
}
// SQL: ((("status" = ?) AND ("age" >= ?) AND ("country" IN (?,?,?))))

// OR operator
const filter7 = {
  or: [
    { age: { lt: 18 } }, // Minors
    { age: { gte: 65 } }, // Seniors
  ],
}
// SQL: ((("age" < ?) OR ("age" >= ?)))

// Mixed AND/OR logic
const filter8 = {
  status: 'ACTIVE', // Implicit AND
  or: [{ tags: { ilike: '%urgent%' } }, { priority: { gte: 8 } }],
}
// SQL: (((("tags" LIKE ? COLLATE NOCASE) OR ("priority" >= ?)) AND ("status" = ?)))

JSON Path Querying

For SQLite JSON columns, use dot notation to query nested fields:

// Query JSON fields
const filter9 = {
  'profile.email': { regex: '.*@example\\.org$' },
  'profile.age': { gte: 21 },
  'settings.theme': { in: ['dark', 'light'] },
  'metadata.tags': { exists: true },
}
// SQL: ((json_extract("profile", '$.email') REGEXP ? AND
//        json_extract("profile", '$.age') >= ? AND
//        json_extract("settings", '$.theme') IN (?,?) AND
//        json_extract("metadata", '$.tags') IS NOT NULL))

// Complex nested JSON query
const filter10 = {
  and: [
    { 'user.profile.email': { regex: '.*@company\\.com$' } },
    { or: [{ 'user.role': 'ADMIN' }, { 'user.permissions.canEdit': true }] },
  ],
}

Kitchen Sink Examples

Real-world complex filters:

// Active users in specific regions, either minors/seniors or VIP
const complexFilter = {
  and: [
    { status: 'ACTIVE' },
    { or: [{ age: { lt: 18 } }, { age: { gte: 65 } }, { membership: 'VIP' }] },
    { country: { in: ['US', 'CA', 'GB'] } },
    { email: { exists: true } },
    { 'profile.verified': true },
  ],
}

// Content filtering with multiple criteria
const contentFilter = {
  name: { like: 'Project%' },
  category: { nin: ['ARCHIVED', 'DELETED', 'SPAM'] },
  created_at: { exists: true },
  or: [
    { tags: { ilike: '%important%' } },
    { priority: { gte: 8 } },
    { 'metadata.featured': true },
  ],
}

Integration with SQL Template

import { sql, compileFilter } from '@truto/sqlite-builder'

// Build the WHERE clause
const filter = {
  status: 'ACTIVE',
  age: { gte: 18 },
  role: { in: ['USER', 'ADMIN'] },
}

const whereClause = compileFilter(filter)

// Use in complete query
const query = sql`
  SELECT id, name, email, created_at
  FROM users
  WHERE ${sql.raw(whereClause.text)}
  ORDER BY created_at DESC
  LIMIT ${limit}
`

// Execute with driver
const results = db.prepare(query.text).all(...query.values)

Security & Validation

The JSON filter compiler includes comprehensive security measures:

Operator validation: Only known operators are allowed
Identifier safety: Field names are validated using the same rules as sql.ident()
Array limits: IN/NIN arrays limited to 999 items (SQLite limitation)
DoS protection: Nesting depth ≤ 10, total operators ≤ 100
Type validation: Strict type checking for all operator values
SQL injection prevention: All values are parameterized

// ❌ These will throw errors
compileFilter({ age: { unknown: 18 } }) // Unknown operator
compileFilter({ 'user; DROP TABLE': 'value' }) // Invalid identifier
compileFilter({ role: { in: [] } }) // Empty array
compileFilter({ role: { in: new Array(1000).fill('x') } }) // Too large array

// ✅ These are safe and valid
compileFilter({ age: { gte: 18, lte: 65 } }) // Multiple operators
compileFilter({ 'profile.email': { exists: true } }) // JSON path
compileFilter({ or: [{ x: 1 }, { y: 2 }] }) // Logical operators

REGEXP Extension

To use the regex operator, you need to load a REGEXP extension in SQLite:

// With better-sqlite3
import sqlite3 from 'better-sqlite3'

const db = new sqlite3('database.db')

// Load REGEXP extension (varies by implementation)
// This is implementation-specific - check your SQLite setup
db.loadExtension('regexp') // Example - actual method may vary

// Now regex filters work
const filter = {
  email: { regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$' },
}

🛡️ Security Model

What's Protected

SQL Injection: All interpolated values are parameterized
Stacked Queries: Queries containing ; followed by additional SQL are rejected
Identifier Safety: sql.ident() validates against ANSI identifier rules
Length Limits: Queries exceeding 100KB are rejected
Filter Security: JSON filters validate operators, identifiers, and enforce limits

What's Your Responsibility

Never use sql.raw() with user input
Validate identifiers before using sql.ident() (though it has built-in validation)
Use sql.in() instead of string concatenation for arrays
Keep your SQLite driver updated
Load REGEXP extension safely if using regex filters

Supported Value Types

// ✅ Safe types (automatically parameterized)
const query = sql`
  INSERT INTO users (name, age, active, created_at, data, deleted_at)
  VALUES (
    ${'John'},           // string
    ${30},               // number  
    ${true},             // boolean
    ${new Date()},       // Date → 'YYYY-MM-DD HH:MM:SS'
    ${null},             // null
    ${undefined}         // undefined → null
  )
`

// ❌ Unsafe types (will throw TypeError)
sql`SELECT * FROM users WHERE data = ${Buffer.from('test')}` // Use sql.raw() for buffers
sql`SELECT * FROM users WHERE id = ${Symbol('test')}` // Unsupported type

📋 Examples

Basic CRUD Operations

import { sql } from '@truto/sqlite-builder'

// CREATE with array identifiers
const insertColumns = ['name', 'email', 'age']
const insertUser = sql`
  INSERT INTO users (${sql.ident(insertColumns)})
  VALUES (${name}, ${email}, ${age})
`

// READ with specific columns
const selectColumns = ['id', 'name', 'email', 'created_at']
const getUser = sql`
  SELECT ${sql.ident(selectColumns)} FROM users 
  WHERE id = ${userId}
`

// UPDATE
const updateUser = sql`
  UPDATE users 
  SET name = ${newName}, updated_at = ${new Date()}
  WHERE id = ${userId}
`

// DELETE
const deleteUser = sql`
  DELETE FROM users 
  WHERE id = ${userId}
`

Dynamic Queries

// Dynamic WHERE conditions
const filters = []
if (name) filters.push(sql`name = ${name}`)
if (minAge) filters.push(sql`age >= ${minAge}`)
if (isActive !== undefined) filters.push(sql`active = ${isActive}`)

const whereClause =
  filters.length > 0 ? sql.join(filters, ' AND ') : sql.raw('1=1')

const query = sql`
  SELECT * FROM users 
  WHERE ${whereClause}
  ORDER BY created_at DESC
`

// Dynamic column selection (simplified with array support)
const columns = ['id', 'name', 'email']
const selectQuery = sql`SELECT ${sql.ident(columns)} FROM users`

// Alternative approach for more complex column expressions
const complexColumns = [
  sql.ident('id'),
  sql.ident('name'),
  sql.raw('UPPER(email) as email_upper'),
]
const complexQuery = sql`SELECT ${sql.join(complexColumns)} FROM users`

Dynamic Queries with JSON Filters

import { sql, compileFilter } from '@truto/sqlite-builder'

// API endpoint that accepts JSON filter
app.get('/api/users', (req, res) => {
  // User sends filter as JSON
  const filter = req.body.filter || {}

  // Safely compile to SQL
  const whereClause = compileFilter(filter)

  const query = sql`
    SELECT id, name, email, created_at
    FROM users
    WHERE ${sql.raw(whereClause.text)}
    ORDER BY created_at DESC
    LIMIT ${req.query.limit || 20}
  `

  const users = db.prepare(query.text).all(...query.values)
  res.json(users)
})

// Example API calls:
// POST /api/users { "filter": { "status": "ACTIVE", "age": { "gte": 18 } } }
// POST /api/users { "filter": { "or": [{ "role": "ADMIN" }, { "verified": true }] } }

Array Identifiers & Qualified Identifiers

The sql.ident() function supports simple identifiers, qualified identifiers (table.column), arrays, and mixed arrays with SQL fragments:

// ✅ Simple identifiers
const table = 'users'
const column = 'name'
const simpleQuery = sql`SELECT ${sql.ident(column)} FROM ${sql.ident(table)}`
// Result: SELECT "name" FROM "users"

// ✅ Qualified identifiers (table.column)
const qualifiedQuery = sql`SELECT ${sql.ident('u.name')}, ${sql.ident('p.title')} FROM users u JOIN posts p ON u.id = p.user_id`
// Result: SELECT "u.name", "p.title" FROM users u JOIN posts p ON u.id = p.user_id

// ✅ Pure identifier arrays (clean and concise)
const columns = ['id', 'name', 'email', 'created_at']
const arrayQuery = sql`SELECT ${sql.ident(columns)} FROM users`
// Result: SELECT "id", "name", "email", "created_at" FROM users

// ✅ Mixed qualified and simple identifiers in arrays
const mixedColumns = ['u.id', 'name', 'u.email', 'p.title', 'created_at']
const mixedQuery = sql`SELECT ${sql.ident(mixedColumns)} FROM users u LEFT JOIN posts p ON u.id = p.user_id`
// Result: SELECT "u.id", "name", "u.email", "p.title", "created_at" FROM users u LEFT JOIN posts p ON u.id = p.user_id

// ✅ NEW: Mixed arrays with identifiers and SQL fragments
const mixedColumns = [
  'id',
  'name',
  sql.raw('UPPER(email) as email_upper'),
  sql.raw('COUNT(*) as total'),
]
const mixedQuery = sql`SELECT ${sql.ident(mixedColumns)} FROM users GROUP BY id, name, email`
// Result: SELECT "id", "name", UPPER(email) as email_upper, COUNT(*) as total FROM users GROUP BY id, name, email

// ✅ Mixed arrays with parameterized fragments
const status = 'premium'
const dynamicColumns = [
  'id',
  'name',
  sql`CASE WHEN status = ${status} THEN 'Premium User' ELSE 'Regular User' END as user_type`,
  sql.raw('created_at'),
]
const parameterizedQuery = sql`SELECT ${sql.ident(dynamicColumns)} FROM users WHERE active = ${true}`
// Combines identifiers, raw SQL, and parameterized values seamlessly

// ✅ Works great for INSERT statements
const insertData = { name: 'John', email: 'john@example.com', age: 30 }
const insertColumns = Object.keys(insertData)
const insertValues = Object.values(insertData)
const insertQuery = sql`
  INSERT INTO users (${sql.ident(insertColumns)}) 
  VALUES (${insertValues[0]}, ${insertValues[1]}, ${insertValues[2]})
`

// ✅ Dynamic column selection
const userFields = ['name', 'email']
const includeTimestamps = true
if (includeTimestamps) {
  userFields.push('created_at', 'updated_at')
}
const dynamicQuery = sql`SELECT ${sql.ident(userFields)} FROM users`

// 🆚 OLD: Manual joining approach (still works, but much more verbose)
const oldWay = sql`SELECT ${sql.join([
  sql.ident('id'),
  sql.ident('name'),
  sql.raw('UPPER(email) as email_upper'),
])} FROM users`

Complex Joins

const getUsersWithPosts = sql`
  SELECT 
    ${sql.ident('u')}.id,
    ${sql.ident('u')}.name,
    COUNT(${sql.ident('p')}.id) as post_count
  FROM ${sql.ident('users')} u
  LEFT JOIN ${sql.ident('posts')} p ON u.id = p.user_id
  WHERE u.created_at > ${startDate}
    AND u.status IN ${sql.in(['active', 'premium'])}
  GROUP BY u.id
  HAVING post_count > ${minPosts}
  ORDER BY post_count DESC
  LIMIT ${limit}
`

// For simpler cases, you can use array identifiers directly
const getUsers = sql`
  SELECT ${sql.ident(['id', 'name', 'email', 'created_at'])}
  FROM ${sql.ident('users')}
  WHERE status = ${'active'}
`

Transactions

// Works great with better-sqlite3 transactions
const insertUsers = db.transaction((users) => {
  const stmt = db.prepare(
    sql`
    INSERT INTO users (name, email) 
    VALUES (?, ?)
  `.text,
  )

  for (const user of users) {
    const { values } = sql`${user.name}, ${user.email}`
    stmt.run(...values)
  }
})

insertUsers([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
])

🧪 Testing

# Run tests
bun run test

# Run tests in watch mode
bun run dev

# Run tests with coverage
bun run test:coverage

# Run tests with UI
bun run test:ui

🤝 Contributing

We welcome contributions! Please see our Contributing Guide for details.

Development Setup

git clone https://github.com/truto/truto-sqlite-builder.git
cd truto-sqlite-builder
bun install
bun run dev  # Start tests in watch mode

Release Process

We use changesets for version management:

# Add a changeset
bunx changeset

# Release
bunx changeset version
bun run build
git commit -am "Release"
git push --follow-tags

🔒 Security Policy

If you discover a security vulnerability, please email eng@truto.one or create an issue.

📄 License

MIT © Truto

💡 Inspiration

This library was inspired by:

Built with ❤️ for the SQLite community.