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

Creating a New Database

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.

Accessing Your Database

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 Concepts

SQL Database Interface

The SqlDatabase interface provides the main entry point for all database operations:

• prepare(query): Creates a prepared statement from a SQL query string. See examples
• bind(…values): Binds values to parameters in a prepared statement. See examples
• batch(statements): Executes multiple prepared statements in sequence. See examples
• exec(query): Directly executes a SQL statement without preparing it first. See examples

Response Types

SQL operations return structured data with detailed metadata:

• SqlResult: Contains query results and metadata (duration, rows read/written, etc.)
• SqlExecResult: Summary information about statement execution using the exec() method

Working with the SQL Database

Preparing Statements

Create a prepared statement using the prepare() method:

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

Binding Parameters

Bind values to prepared statements to create parameterized queries:

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.

Executing Queries

Getting a Single Value

Retrieve 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");

Getting the First Row

Retrieve the entire first row from a result set:

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

Getting All Results

Retrieve all matching rows:

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

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

Executing Statements

Execute statements that modify data and return metadata:

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

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

Raw Results

Get results in a more raw format, with or 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();

Batched Operations

Execute multiple statements in sequence:

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", "[email protected]").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.