or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

database.md ffi.md file-system.md globals.md html-rewriter.md http-server.md index.md process-management.md redis.md s3.md shell.md testing.md utilities.md

tile.json

Database Operations

Fast SQLite database integration with prepared statements, transactions, type-safe operations, and high-performance query execution optimized for modern web applications.

Core Imports

import { Database } from "bun:sqlite";

Capabilities

Database Connection

Create and manage SQLite database connections with comprehensive configuration options.

/**
 * SQLite database connection with high-performance query execution
 */
class Database implements Disposable {
  /**
   * Create a new database connection
   * @param filename - Database file path (use ":memory:" or undefined for in-memory database)
   * @param options - Database configuration options
   */
  constructor(filename?: string, options?: number | DatabaseOptions);
  
  /**
   * Create a new database connection (static factory method)
   * @param filename - Database file path
   * @param options - Database configuration options
   */
  static open(filename: string, options?: number | DatabaseOptions): Database;
  
  /** Database filename */
  readonly filename: string;
  /** Internal database handle */
  readonly handle: number;
  /** Whether database is currently in a transaction */
  readonly inTransaction: boolean;
  
  /**
   * Close the database connection
   * @param throwOnError - Whether to throw if database is in use
   */
  close(throwOnError?: boolean): void;
  
  /**
   * Dispose method for using with disposable syntax
   */
  [Symbol.dispose](): void;
}

interface DatabaseOptions {
  /** Open database in read-only mode */
  readonly?: boolean;
  /** Allow creating new database file */
  create?: boolean;
  /** Open in read-write mode (default) */
  readwrite?: boolean;
  /** Return integers as bigint types instead of numbers */
  safeIntegers?: boolean;
  /** Enable strict parameter binding mode */
  strict?: boolean;
}

Usage Examples:

import { Database } from "bun:sqlite";

// Create/open database file
const db = new Database("myapp.db");

// In-memory database (faster, but data is lost when process exits)
const memoryDb = new Database(":memory:");

// Read-only database
const readOnlyDb = new Database("readonly.db", {
  readonly: true,
  mustExist: true
});

// Configuration options
const configuredDb = new Database("data.db", {
  create: true,    // Create if doesn't exist
  readwrite: true  // Read-write mode
});

Query Preparation

Prepare SQL statements for efficient repeated execution with parameter binding.

/**
 * Prepare and cache a SQL statement for execution
 * @param sql - SQL query string with optional parameter placeholders
 * @returns Prepared statement ready for execution
 */
query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
  sql: string
): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;

/**
 * Prepare a SQL statement (not cached)
 * @param sql - SQL query string
 * @param params - Optional parameter bindings
 * @returns Prepared statement
 */
prepare<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
  sql: string,
  params?: ParamsType
): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;

/**
 * Execute SQL without returning results (for DDL/DML statements)
 * @param sql - SQL statement to execute
 * @param bindings - Optional parameter bindings
 * @returns Changes information
 */
run<ParamsType extends SQLQueryBindings[]>(sql: string, ...bindings: ParamsType[]): Changes;

/**
 * Alias for run() (deprecated)
 * @deprecated Use run() instead
 */
exec<ParamsType extends SQLQueryBindings[]>(sql: string, ...bindings: ParamsType[]): Changes;

Usage Examples:

const db = new Database("users.db");

// Simple queries
const createTable = db.query(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Parameterized queries (safer and faster)
const insertUser = db.query(`
  INSERT INTO users (name, email) VALUES (?, ?)
`);

const getUserById = db.query(`
  SELECT * FROM users WHERE id = ?
`);

const getUserByEmail = db.query(`
  SELECT id, name, email FROM users WHERE email = $email
`);

// Complex queries with multiple parameters
const searchUsers = db.query(`
  SELECT * FROM users 
  WHERE name LIKE ? AND created_at > ?
  ORDER BY created_at DESC
  LIMIT ?
`);

Statement Execution

Execute prepared statements with different return patterns for various use cases.

interface Statement<ReturnType = unknown, ParamsType extends SQLQueryBindings[] = any[]> implements Disposable {
  /**
   * Execute statement and return all matching rows
   * @param params - Parameters to bind to the statement
   * @returns Array of all matching rows
   */
  all(...params: ParamsType): ReturnType[];
  
  /**
   * Execute statement and return first matching row
   * @param params - Parameters to bind to the statement
   * @returns First matching row or null if no results
   */
  get(...params: ParamsType): ReturnType | null;
  
  /**
   * Execute statement and return execution info
   * @param params - Parameters to bind to the statement
   * @returns Execution result metadata
   */
  run(...params: ParamsType): Changes;
  
  /**
   * Execute statement and return raw values as arrays
   * @param params - Parameters to bind to the statement
   * @returns Array of arrays containing raw column values
   */
  values(...params: ParamsType): Array<Array<string | bigint | number | boolean | Uint8Array>>;
  
  /**
   * Execute statement and return raw bytes as Uint8Arrays
   * @param params - Parameters to bind to the statement
   * @returns Array of arrays containing raw byte data
   */
  raw(...params: ParamsType): Array<Array<Uint8Array | null>>;
  
  /**
   * Execute statement and return an iterator over rows
   * @param params - Parameters to bind to the statement
   * @returns Iterator over result rows
   */
  iterate(...params: ParamsType): IterableIterator<ReturnType>;
  
  /**
   * Iterator symbol implementation
   */
  [Symbol.iterator](): IterableIterator<ReturnType>;
  
  /**
   * Column names in the result set
   */
  readonly columnNames: string[];
  
  /**
   * Number of parameters in the prepared statement
   */
  readonly paramsCount: number;
  
  /**
   * Actual column types from first row execution
   */
  readonly columnTypes: Array<"INTEGER" | "FLOAT" | "TEXT" | "BLOB" | "NULL" | null>;
  
  /**
   * Declared column types from table schema
   */
  readonly declaredTypes: Array<string | null>;
  
  /**
   * Finalize the statement and free resources
   */
  finalize(): void;
  
  /**
   * Dispose method for using with disposable syntax
   */
  [Symbol.dispose](): void;
  
  /**
   * Return expanded SQL string with bound parameters
   */
  toString(): string;
  
  /**
   * Set custom class for result objects
   * @param Class - Class constructor to use for results
   */
  as<T = unknown>(Class: new (...args: any[]) => T): Statement<T, ParamsType>;
  
  /**
   * Native SQLite statement handle (untyped)
   */
  readonly native: any;
}

interface Changes {
  /** Number of rows affected by the statement */
  changes: number;
  /** Row ID of the last inserted row */
  lastInsertRowid: number | bigint;
}

Usage Examples:

const db = new Database("app.db");

// Setup table
db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category_id INTEGER
  )
`);

const insertProduct = db.query(`
  INSERT INTO products (name, price, category_id) VALUES (?, ?, ?)
`);

const getProduct = db.query(`
  SELECT * FROM products WHERE id = ?
`);

const getAllProducts = db.query(`
  SELECT * FROM products ORDER BY name
`);

const updatePrice = db.query(`
  UPDATE products SET price = ? WHERE id = ?
`);

// Insert data
const result = insertProduct.run("Laptop", 999.99, 1);
console.log("Inserted product with ID:", result.lastInsertRowid);
console.log("Rows affected:", result.changes);

// Get single row
const product = getProduct.get(1);
if (product) {
  console.log("Product:", product.name, product.price);
}

// Get all rows
const products = getAllProducts.all();
products.forEach(p => console.log(`${p.name}: $${p.price}`));

// Update data
const updateResult = updatePrice.run(899.99, 1);
console.log("Updated rows:", updateResult.changes);

// Raw values (arrays instead of objects)
const rawValues = db.query("SELECT name, price FROM products").values();
rawValues.forEach(([name, price]) => {
  console.log(`${name}: $${price}`);
});

Transaction Management

Execute multiple operations atomically with automatic rollback on errors.

/**
 * Create a transaction function that executes multiple operations atomically
 * @param insideTransaction - Function to execute within the transaction
 * @returns Transaction function with different transaction modes
 */
transaction<A extends any[], T>(
  insideTransaction: (...args: A) => T
): {
  /** Execute the transaction */
  (...args: A): T;
  /** Execute transaction using "BEGIN DEFERRED" */
  deferred: (...args: A) => T;
  /** Execute transaction using "BEGIN IMMEDIATE" */
  immediate: (...args: A) => T;
  /** Execute transaction using "BEGIN EXCLUSIVE" */
  exclusive: (...args: A) => T;
};

Usage Examples:

const db = new Database("banking.db");

db.exec(`
  CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER PRIMARY KEY,
    balance REAL NOT NULL
  )
`);

const getBalance = db.query("SELECT balance FROM accounts WHERE id = ?");
const updateBalance = db.query("UPDATE accounts SET balance = ? WHERE id = ?");

// Atomic money transfer
const transferMoney = db.transaction((fromId: number, toId: number, amount: number) => {
  const fromAccount = getBalance.get(fromId);
  const toAccount = getBalance.get(toId);
  
  if (!fromAccount || !toAccount) {
    throw new Error("Account not found");
  }
  
  if (fromAccount.balance < amount) {
    throw new Error("Insufficient funds");
  }
  
  // Both operations happen atomically
  updateBalance.run(fromAccount.balance - amount, fromId);
  updateBalance.run(toAccount.balance + amount, toId);
  
  return { success: true, newBalance: fromAccount.balance - amount };
});

// Execute transfer (automatically rolled back on error)
try {
  const result = transferMoney(1, 2, 100.00);
  console.log("Transfer completed:", result);
} catch (error) {
  console.error("Transfer failed:", error.message);
}

// Manual transaction control
const manualTx = db.begin();
try {
  updateBalance.run(500, 1);
  updateBalance.run(1500, 2);
  manualTx.commit();
} catch (error) {
  manualTx.rollback();
  throw error;
}