SQL Database

Overview

The Raindrop framework provides a robust SQL database interface that enables you to execute SQL operations with TypeScript type safety. This database interface offers prepared statements, batched operations, and various query execution methods to efficiently work with your data.

SQL databases in Raindrop allow you to leverage the full power of SQL while maintaining the type safety and developer experience you expect from TypeScript. The interface is designed to be intuitive while providing detailed operation metadata for performance analysis.

Prerequisites

• Basic understanding of SQL query syntax
• Raindrop framework installed in your project
• Familiarity with TypeScript and async/await patterns

Configuration

To add a SQL database to your Raindrop project:

1. Define the database in your application manifest
2. Run raindrop build generate to create the necessary files

application "demo-app" {
    sql_database "demo-db" {}
}

Once deployed, Raindrop automatically:

• Creates your database instance
• Sets up all required bindings
• Makes the database accessible to your services and actors

After running generate, you’ll find a new database directory (db/demo-db/) in your project where you can add migration files for schema management.

Tip

Database names must be unique within your application and should follow kebab-case naming conventions.

Access

SQL databases are made available to your services and actors through environment variables. The database name from your manifest is converted to an uppercase environment variable with underscores replacing dashes.

For example, if your manifest defines a database as sql_database "demo-db", you would access it in your code as env.DEMO_DB.

export default class extends Service<Env> {
  async fetch(request: Request, env: Env): Promise<Response> {
    // Access the database through the environment variable
    const users = await env.DEMO_DB.prepare("SELECT * FROM users").all();
    return new Response(JSON.stringify(users.results));
  }
}

Core Interfaces

SqlDatabase

Main interface for interacting with a SQL database:

interface SqlDatabase {
  prepare(query: string): SqlPreparedStatement;           // Creates a prepared statement from SQL query
  batch<T>(statements: SqlPreparedStatement[]): Promise<SqlResult<T>[]>;  // Executes multiple statements in sequence
  exec(query: string): Promise<SqlExecResult>;            // Executes SQL query directly
}

SqlPreparedStatement

Interface for a prepared SQL statement ready for execution:

interface SqlPreparedStatement {
  bind(...values: unknown[]): SqlPreparedStatement;       // Binds values to statement parameters
  first<T>(colName: string): Promise<T | null>;           // Gets first result column by name
  first<T>(): Promise<T | null>;                          // Gets first result row
  run<T>(): Promise<SqlResult<T>>;                        // Executes statement and returns results
  all<T>(): Promise<SqlResult<T>>;                        // Executes statement and returns all results
  raw<T>(options: { columnNames: true }): Promise<[string[], ...T[]]>;   // Gets raw results with column names
  raw<T>(options?: { columnNames?: false }): Promise<T[]>;               // Gets raw results without column names
  readonly query: string;                                 // The SQL query string
}

SqlResult

SqlResult<T>

Result of a SQL query that returns data:

type SqlResult<T> = {
  success: true;                                          // Indicates successful completion
  results: T[];                                           // Array of query results
  meta: SqlMeta;                                          // Operation metadata
}

SqlMeta

Metadata about a SQL operation:

interface SqlMeta {
  duration: number;                                       // Operation duration in milliseconds
  size_after: number;                                     // Database size after operation in bytes
  rows_read: number;                                      // Number of rows read during operation
  rows_written: number;                                   // Number of rows written during operation
  last_row_id: number;                                    // ID of last inserted row
  changed_db: boolean;                                    // Whether database was modified
  changes: number;                                        // Number of rows modified
}

SqlExecResult

Result of executing a SQL statement using exec():

interface SqlExecResult {
  count: number;                                          // Number of statements executed
  duration: number;                                       // Duration of execution in milliseconds
}

Query Methods

prepare()

Creates a prepared statement from a SQL query string. Prepared statements can be reused with different parameters and provide better performance for repeated queries.

Parameters:

• query: string - The SQL query to prepare

Returns: SqlPreparedStatement - A prepared statement ready for execution

const statement = env.DEMO_DB.prepare("SELECT * FROM users WHERE id = ?");

exec()

Executes a SQL query directly without preparing it first. Use this for schema operations, one-time queries, or DDL statements.

Parameters:

• query: string - The SQL query to execute

Returns: Promise<SqlExecResult> - Execution result with count and duration

const result = await env.DEMO_DB.exec("CREATE INDEX idx_users_email ON users(email)");
console.log(`Executed ${result.count} statements in ${result.duration}ms`);

bind()

Binds values to prepared statements to create parameterized queries. This method supports chaining for convenient one-line statement creation and binding.

Parameters:

• ...values: unknown[] - Values to bind to statement parameters

Returns: SqlPreparedStatement - The prepared statement for chaining

const userStatement = env.DEMO_DB.prepare("SELECT * FROM users WHERE id = ? AND status = ?")
  .bind(123, "active");

Tip

The bind() method supports chaining, so you can create and bind a statement in one line.

Result Methods

first()

Retrieves the first result from a query, either a specific column value or the entire first row. Returns null if no results are found.

Overloads:

• first<T>(colName: string): Promise<T | null> - Gets first result column by name
• first<T>(): Promise<T | null> - Gets first result row

// Get a single column value from the first row
const username = await env.DEMO_DB.prepare("SELECT username FROM users WHERE id = ?")
  .bind(123)
  .first("username");

// Get the entire first row
const user = await env.DEMO_DB.prepare("SELECT * FROM users WHERE id = ?")
  .bind(123)
  .first();

run()

Executes statements that modify data and returns metadata about the operation. Use this for INSERT, UPDATE, DELETE operations where you need to know the impact.

Returns: Promise<SqlResult<T>> - Result with metadata and any returned data

const result = await env.DEMO_DB.prepare("UPDATE users SET status = ? WHERE id = ?")
  .bind("inactive", 123)
  .run();

console.log(`Updated ${result.meta.changes} rows`);

all()

Executes the statement and returns all matching results. Use this when you need to retrieve multiple rows from a query.

Returns: Promise<SqlResult<T>> - All query results with metadata

const users = await env.DEMO_DB.prepare("SELECT * FROM users WHERE status = ?")
  .bind("active")
  .all();

console.log(`Found ${users.results.length} active users`);

raw()

Returns results in raw format, optionally including column names. This method provides lower-level access to query results for performance-critical scenarios.

Overloads:

• raw<T>(options: { columnNames: true }): Promise<[string[], ...T[]]> - With column names
• raw<T>(options?: { columnNames?: false }): Promise<T[]> - Without column names

With Column Names

const [columns, ...rows] = await env.DEMO_DB.prepare("SELECT id, name, email FROM users")
  .raw({ columnNames: true });

Without Column Names

const rows = await env.DEMO_DB.prepare("SELECT id, name, email FROM users")
  .raw();

Batch Methods

batch()

Executes multiple prepared statements in sequence as a single operation. This provides better performance than executing statements individually and maintains transaction consistency.

Parameters:

• statements: SqlPreparedStatement[] - Array of prepared statements to execute

Returns: Promise<SqlResult<T>[]> - Array of results for each statement

const statements = [
  env.DEMO_DB.prepare("INSERT INTO logs (message) VALUES (?)").bind("Operation started"),
  env.DEMO_DB.prepare("UPDATE users SET last_login = ? WHERE id = ?").bind(Date.now(), userId),
  env.DEMO_DB.prepare("INSERT INTO logs (message) VALUES (?)").bind("Operation completed")
];

const results = await env.DEMO_DB.batch(statements);

Performance Considerations

Metadata Analysis

Every SQL operation returns detailed metadata that can be used for performance analysis:

const result = await env.DEMO_DB.prepare("SELECT * FROM large_table").all();
console.log(`Query took ${result.meta.duration}ms and read ${result.meta.rows_read} rows`);

Key metadata includes:

• Operation duration (milliseconds)
• Database size after operation
• Row counts (read/written)
• Last inserted row ID
• Change information

Optimizing Queries

• Use prepared statements for repeated queries to avoid recompilation
• Leverage batched operations for related changes to minimize overhead
• Consider indexing for performance-critical queries
• Monitor the duration field in metadata to identify slow queries

Code Examples

Complete CRUD Example

Create

const insertUser = env.DEMO_DB.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
const insertResult = await insertUser.bind("Jane Doe", "jane@example.com").run();
const userId = insertResult.meta.last_row_id;

Read

const user = await env.DEMO_DB.prepare("SELECT * FROM users WHERE id = ?")
  .bind(userId)
  .first();

Update

const updateResult = await env.DEMO_DB.prepare("UPDATE users SET name = ? WHERE id = ?")
  .bind("Jane Smith", userId)
  .run();

console.log(`Updated ${updateResult.meta.changes} rows`);

Delete

const deleteResult = await env.DEMO_DB.prepare("DELETE FROM users WHERE id = ?")
  .bind(userId)
  .run();

console.log(`Deleted ${deleteResult.meta.changes} rows`);

Table Creation and Schema Management

In Raindrop, database schemas are managed through migration files rather than direct SQL execution in your application code. This approach provides better version control, reproducibility, and deployment safety.

When you add a database to your raindrop.manifest and run raindrop build generate, the framework automatically creates a database directory in your project root. For example, adding a database named “demo” creates: my-project/db/demo.

Database Migrations

Migration files are SQL scripts that define your database schema and are automatically executed during deployment. Each file represents a discrete change to your database structure.

Tip

Migrations run only once per database and are tracked internally to prevent duplicate execution.

File Naming Convention

Migration files must follow this pattern:

<number>_<description>.sql

• <number>: A 4-digit padded number (e.g., 0001, 0002)
• <description>: Brief explanation of the changes

Example migration sequence:

db/demo/
├── 0000_initial_schema.sql
├── 0001_add_users_table.sql
└── 0002_add_foreign_keys.sql

Execution Order

Files execute in ascending alphabetical order during raindrop build deploy. The numbering system ensures your migrations run in the correct sequence, allowing incremental schema changes.

Seed Data

Include seed data in the last migration file to ensure all schema changes are applied first:

9999_seed_data.sql

This file typically contains INSERT statements to populate your tables with initial data required by your application.

Note

Using migrations for schema management is strongly recommended over direct db.exec() calls in your application code.