cameronapak/bknd-database-provision

Provision Production Database

Set up and configure a production database for your Bknd application.

Prerequisites

• Bknd application with schema defined
• Account on chosen database provider (for cloud databases)
• Environment for storing connection credentials

When to Use UI Mode

• Creating databases via provider dashboards (Turso, Neon, Cloudflare, Supabase)
• Managing database settings and access tokens
• Viewing database metrics and logs

When to Use Code Mode

• Configuring database connection in Bknd
• CLI commands for database creation
• Schema sync and migrations

Database Selection Guide

| Database | Best For | Platform Compatibility | Cost | |----------|----------|------------------------|------| | SQLite File | VPS, Docker, single-server | Node.js, Bun | Free | | LibSQL/Turso | Serverless, edge, global | All platforms | Free tier | | Cloudflare D1 | Cloudflare Workers | Cloudflare only | Free tier | | PostgreSQL | Complex queries, transactions | VPS, Docker | Self-hosted | | Neon | Serverless Postgres | Vercel, Lambda | Free tier | | Supabase | Postgres + extras | Any | Free tier | | Xata | Serverless + search | Any | Free tier |

---

SQLite File (VPS/Docker)

Best for: Single-server deployments with full control

Step 1: Configure Connection

// bknd.config.ts
export default {
  app: (env) => ({
    connection: {
      url: env.DB_URL ?? "file:data.db",  // Relative to cwd
    },
  }),
};

Step 2: Set Environment Variable

# Relative path (project directory)
DB_URL=file:data.db

# Absolute path (recommended for production)
DB_URL=file:/var/data/myapp/bknd.db

Step 3: Ensure Directory Exists

mkdir -p /var/data/myapp

Docker Volume

# docker-compose.yml
services:
  bknd:
    volumes:
      - bknd-data:/app/data
    environment:
      - DB_URL=file:/app/data/bknd.db

volumes:
  bknd-data:

---

LibSQL / Turso

Best for: Serverless, edge deployments, global distribution

Step 1: Install Turso CLI

# macOS/Linux
curl -sSfL https://get.tur.so/install.sh | bash

# Authenticate
turso auth login

Step 2: Create Database

# Create database
turso db create my-bknd-db

# Optional: Specify region
turso db create my-bknd-db --location lax  # Los Angeles

Step 3: Get Connection Details

# Get connection URL
turso db show my-bknd-db --url
# Output: libsql://my-bknd-db-username.turso.io

# Create auth token
turso db tokens create my-bknd-db
# Output: eyJhbGciOi...

Step 4: Configure Bknd

// bknd.config.ts
export default {
  app: (env) => ({
    connection: {
      url: env.DB_URL,       // libsql://...
      authToken: env.DB_TOKEN,
    },
  }),
};

Step 5: Set Environment Variables

DB_URL=libsql://my-bknd-db-username.turso.io
DB_TOKEN=eyJhbGciOi...

Turso Locations

Common regions: ams (Amsterdam), fra (Frankfurt), lax (LA), lhr (London), nrt (Tokyo), syd (Sydney)

turso db locations  # List all regions

---

Cloudflare D1

Best for: Cloudflare Workers deployments

Step 1: Create D1 Database

wrangler d1 create my-bknd-db

Output:

Created D1 database 'my-bknd-db'
database_name = "my-bknd-db"
database_id = "abc123-def456-..."

Step 2: Configure wrangler.toml

name = "my-bknd-app"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "my-bknd-db"
database_id = "abc123-def456-..."

Step 3: Configure Bknd Adapter

// src/index.ts
import { hybrid, type CloudflareBkndConfig } from "bknd/adapter/cloudflare";
import { d1Sqlite } from "bknd/adapter/cloudflare";

export default hybrid<CloudflareBkndConfig>({
  app: (env) => ({
    connection: d1Sqlite({ binding: env.DB }),
    isProduction: true,
  }),
});

D1 CLI Commands

# List databases
wrangler d1 list

# Execute SQL (local dev)
wrangler d1 execute my-bknd-db --local --command "SELECT * FROM posts"

# Execute SQL (production)
wrangler d1 execute my-bknd-db --command "SELECT * FROM posts"

# Export backup
wrangler d1 backup create my-bknd-db

---

PostgreSQL (Self-Hosted)

Best for: Complex queries, large datasets, existing Postgres infrastructure

Step 1: Install Adapter

npm install postgres
# or
npm install pg

Step 2: Configure Connection

Using postgres (recommended):

import { PostgresJsConnection } from "bknd/adapter/postgres";

export default {
  app: (env) => ({
    connection: new PostgresJsConnection({
      connectionString: env.DATABASE_URL,
    }),
  }),
};

Using pg:

import { PgPostgresConnection } from "bknd/adapter/postgres";

export default {
  app: (env) => ({
    connection: new PgPostgresConnection({
      connectionString: env.DATABASE_URL,
    }),
  }),
};

Step 3: Set Connection String

DATABASE_URL=postgresql://user:password@host:5432/database?sslmode=require

---

Neon (Serverless Postgres)

Best for: Vercel, serverless, auto-scaling Postgres

Step 1: Create Project at neon.tech

1. Sign up at neon.tech
2. Create new project
3. Copy connection string from dashboard

Step 2: Install Neon Dialect

npm install kysely-neon

Step 3: Configure Connection

import { createCustomPostgresConnection } from "bknd";
import { NeonDialect } from "kysely-neon";

const neon = createCustomPostgresConnection("neon", NeonDialect);

export default {
  app: (env) => ({
    connection: neon({
      connectionString: env.NEON_DATABASE_URL,
    }),
  }),
};

Step 4: Set Environment Variable

NEON_DATABASE_URL=postgres://user:[email protected]/neondb?sslmode=require

---

Supabase

Best for: Full-featured Postgres with extras (auth, storage, realtime)

Step 1: Create Project at supabase.com

1. Sign up at supabase.com
2. Create new project
3. Go to Settings > Database > Connection string

Step 2: Get Direct Connection String

Use "Direct connection" (not pooler) for Bknd:

postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres

Step 3: Configure Connection

export default {
  app: (env) => ({
    connection: {
      url: env.SUPABASE_DB_URL,
    },
  }),
};

Step 4: Set Environment Variable

SUPABASE_DB_URL=postgresql://postgres:[email protected]:5432/postgres

---

Xata

Best for: Serverless Postgres with built-in search

Step 1: Create Database at xata.io

1. Sign up at xata.io
2. Create workspace and database

Step 2: Install Xata Dialect

npm install @xata.io/kysely

Step 3: Configure Connection

import { createCustomPostgresConnection } from "bknd";
import { XataDialect } from "@xata.io/kysely";

const xata = createCustomPostgresConnection("xata", XataDialect);

export default {
  app: (env) => ({
    connection: xata({
      apiKey: env.XATA_API_KEY,
      workspace: "your-workspace",
      database: "your-database",
    }),
  }),
};

---

Schema Sync

After configuring your database, Bknd auto-syncs schema on first request. For manual control:

# Dry run (preview changes)
npx bknd sync --dry-run

# Apply changes
npx bknd sync

# Force sync (use with caution)
npx bknd sync --force

---

Connection Testing

Verify Connection

// test-connection.ts
import { app } from "bknd";

const bknd = app({
  connection: {
    url: process.env.DB_URL!,
    authToken: process.env.DB_TOKEN,
  },
});

async function test() {
  await bknd.build();
  console.log("Connection successful!");
  console.log("Entities:", Object.keys(bknd.modules.data.entities));
  process.exit(0);
}

test().catch((e) => {
  console.error("Connection failed:", e);
  process.exit(1);
});

Run:

npx tsx test-connection.ts

---

Common Pitfalls

"Connection refused" or "ECONNREFUSED"

Problem: Can't connect to database

Fix:

• Verify connection URL format
• Check firewall/security group rules
• Ensure database is running
• For cloud: verify IP allowlist includes your server

"Auth token required" (LibSQL/Turso)

Problem: Missing or invalid auth token

Fix:

# Generate new token
turso db tokens create my-bknd-db

# Set in environment
export DB_TOKEN="eyJhbGciOi..."

"D1 binding not found"

Problem: env.DB is undefined in Cloudflare Workers

Fix: Check wrangler.toml binding name matches code:

[[d1_databases]]
binding = "DB"  # Must match env.DB

"SSL required" (PostgreSQL)

Problem: Connection fails without SSL

Fix: Add ?sslmode=require to connection string:

DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

"Unknown database" or "Database does not exist"

Problem: Database not created

Fix:

# Turso
turso db create my-bknd-db

# D1
wrangler d1 create my-bknd-db

# PostgreSQL
createdb my-bknd-db

Schema Sync Fails

Problem: Migrations fail on production database

Fix:

# Preview changes first
npx bknd sync --dry-run

# If stuck, use --force (data loss possible!)
npx bknd sync --force --drop

---

Migration from Development

Export Development Data

# SQLite
sqlite3 data.db .dump > backup.sql

# Using API
curl http://localhost:3000/api/data/posts > posts.json

Import to Production

# Via seed function (recommended)
# See bknd-seed-data skill

# Direct SQL (SQLite to SQLite only)
cat backup.sql | turso db shell my-bknd-db

---

DOs and DON'Ts

DO:

• Use cloud databases (Turso, D1, Neon) for serverless
• Store credentials in environment variables
• Test connection before deploying
• Use SSL for PostgreSQL connections
• Keep auth tokens secure
• Enable backups for production data

DON'T:

• Use file-based SQLite in serverless/edge
• Hardcode credentials in source code
• Share auth tokens across environments
• Skip connection testing
• Use --force --drop without backups
• Expose database directly to internet (use Bknd API)

---

Related Skills

• bknd-deploy-hosting - Deploy to hosting platforms
• bknd-production-config - Production security settings
• bknd-env-config - Environment variable setup
• bknd-seed-data - Populate database with initial data
• bknd-local-setup - Local development (pre-production)