logowebsideproject

Cloudflare D1

Nuxt Auto API fully supports Cloudflare D1, allowing you to deploy your API to the edge with a serverless SQLite database.

Cloudflare D1

Nuxt Auto API fully supports Cloudflare D1, allowing you to deploy your API to the edge with a serverless SQLite database.

Table of Contents

Overview

Cloudflare D1 is a serverless SQLite database that runs on Cloudflare's global network. Nuxt Auto API uses the same Drizzle ORM schema for both local SQLite (better-sqlite3) and production D1.

Key Benefits

  • Edge Performance: Database runs close to your users worldwide
  • Serverless: No infrastructure management
  • Cost-Effective: Pay only for what you use
  • SQLite Compatible: Same schema and queries as local development

Differences from SQLite

FeatureSQLite (better-sqlite3)Cloudflare D1
ExecutionSynchronousAsynchronous
LocationLocal fileEdge network
ScalingSingle instanceGlobally distributed
CostFree (development)Pay-per-use

Setup Guide

Step 1: Install Dependencies

npm install drizzle-orm wrangler --save-dev

Step 2: Create D1 Database

# Create D1 database
npx wrangler d1 create @websideproject/nuxt-auto-api-db

# Output:
# Database created!
# [[d1_databases]]
# binding = "DB"
# database_name = "@websideproject/nuxt-auto-api-db"
# database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Copy the output to your wrangler.toml.

Step 3: Configure wrangler.toml

Create wrangler.toml in your project root:

name = "@websideproject/nuxt-auto-api"
main = ".output/server/index.mjs"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "@websideproject/nuxt-auto-api-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

# For local development
[env.local]
[[env.local.d1_databases]]
binding = "DB"
database_name = "@websideproject/nuxt-auto-api-db"
database_id = "local"

Step 4: Update Drizzle Configuration

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './server/database/schema.ts',
  out: './server/database/migrations',
  dialect: 'sqlite', // D1 uses SQLite dialect
  driver: 'd1-http', // For remote operations
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.CLOUDFLARE_D1_ID!,
    token: process.env.CLOUDFLARE_API_TOKEN!
  }
})

Step 5: Initialize D1 in Nitro Plugin

Create server/plugins/database.d1.ts:

import { drizzle } from 'drizzle-orm/d1'
import * as schema from '../database/schema'

export default defineNitroPlugin((nitroApp) => {
  // Get D1 binding from Cloudflare
  const binding = nitroApp.cloudflare?.env?.DB

  if (!binding) {
    throw new Error('D1 database binding not found')
  }

  const db = drizzle(binding, { schema })

  // Store in globalThis for auto-api
  globalThis.__autoApiDb = db

  console.log('[@websideproject/nuxt-auto-api] D1 database initialized')
})

Step 6: Update nuxt.config.ts

export default defineNuxtConfig({
  modules: ['@websideproject/nuxt-auto-api'],

  autoApi: {
    prefix: '/api',
    database: {
      client: 'd1' // Specify D1 client
    }
  },

  nitro: {
    preset: 'cloudflare-pages' // or 'cloudflare-workers'
  }
})

Local Development

Option 1: Wrangler Dev (Recommended)

Use Wrangler for local D1 development:

# Start Wrangler dev server
npx wrangler dev

# In separate terminal, run Nuxt
npm run dev

Option 2: Hybrid Setup

Use SQLite locally, D1 in production:

// server/plugins/database.ts
import { drizzle as drizzleSqlite } from 'drizzle-orm/better-sqlite3'
import { drizzle as drizzleD1 } from 'drizzle-orm/d1'
import Database from 'better-sqlite3'
import * as schema from '../database/schema'

export default defineNitroPlugin((nitroApp) => {
  let db

  if (process.dev) {
    // Local development: Use SQLite
    const sqlite = new Database('.data/db.sqlite')
    db = drizzleSqlite(sqlite, { schema })
    console.log('[@websideproject/nuxt-auto-api] SQLite database initialized')
  } else if (nitroApp.cloudflare?.env?.DB) {
    // Production: Use D1
    db = drizzleD1(nitroApp.cloudflare.env.DB, { schema })
    console.log('[@websideproject/nuxt-auto-api] D1 database initialized')
  } else {
    throw new Error('Database not configured')
  }

  globalThis.__autoApiDb = db
})

Database Migrations

Generate Migrations

# Generate migration files
npx drizzle-kit generate

This creates SQL files in server/database/migrations/.

Apply Migrations to D1

# Apply to local D1
npx wrangler d1 migrations apply @websideproject/nuxt-auto-api-db --local

# Apply to remote D1 (production)
npx wrangler d1 migrations apply @websideproject/nuxt-auto-api-db --remote

Manual Migration

You can also apply migrations manually:

# Execute SQL file
npx wrangler d1 execute @websideproject/nuxt-auto-api-db --file=./migrations/0001_initial.sql --remote

Deployment

Deploy to Cloudflare Pages

# Build for Cloudflare Pages
npm run build

# Deploy with Wrangler
npx wrangler pages deploy .output/public

Deploy with GitHub Actions

# .github/workflows/deploy.yml
name: Deploy to Cloudflare Pages

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - uses: actions/setup-node@v3
        with:
          node-version: 20

      - run: npm ci
      - run: npm run build

      - name: Deploy to Cloudflare Pages
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: pages deploy .output/public --project-name=@websideproject/nuxt-auto-api

Environment Variables

Set in Cloudflare Dashboard or via CLI:

# Set production environment variables
npx wrangler pages deployment create \
  --project-name=@websideproject/nuxt-auto-api \
  --branch=main \
  --d1-binding=DB:@websideproject/nuxt-auto-api-db

Performance Optimization

D1 Limits

  • Max query size: 1MB
  • Max result size: 10MB
  • Read operations: ~50,000 RPM per database
  • Write operations: ~5,000 RPM per database
  • Query timeout: 30 seconds

Optimization Tips

1. Add Indexes

// server/database/schema.ts
import { sqliteTable, integer, text, index } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  email: text('email').notNull().unique(),
  status: text('status')
}, (table) => ({
  emailIdx: index('email_idx').on(table.email),
  statusIdx: index('status_idx').on(table.status)
}))

2. Use Pagination

Always paginate large result sets:

// ✅ Good - Paginated
const posts = await db.query.posts.findMany({
  limit: 20,
  offset: 0
})

// ❌ Bad - No limit
const posts = await db.query.posts.findMany()

3. Reduce Result Size

Select only needed fields:

// ✅ Good - Specific fields
const users = await db.select({
  id: schema.users.id,
  name: schema.users.name
}).from(schema.users)

// ❌ Bad - All fields
const users = await db.select().from(schema.users)

4. Batch Operations

// Use D1's batch API for multiple operations
const batch = [
  db.insert(schema.users).values({ email: 'user1@test.com' }),
  db.insert(schema.users).values({ email: 'user2@test.com' }),
  db.insert(schema.users).values({ email: 'user3@test.com' })
]

await db.batch(batch)

5. Cache Frequently Accessed Data

Use Cloudflare KV or Cache API for hot data:

// Cache expensive query results
const cacheKey = 'popular-posts'
const cached = await caches.default.match(cacheKey)

if (cached) {
  return cached.json()
}

const posts = await db.query.posts.findMany({
  where: eq(schema.posts.published, true),
  limit: 10
})

await caches.default.put(
  cacheKey,
  new Response(JSON.stringify(posts), {
    headers: { 'Cache-Control': 'max-age=300' }
  })
)

Troubleshooting

"Database binding not found"

Problem: D1 binding is not available in the Nitro context.

Solution:

  1. Check wrangler.toml has correct binding configuration
  2. Verify Cloudflare Pages has D1 binding configured
  3. Run npx wrangler pages deployment list to check bindings

"Migrations not applied"

Problem: Tables don't exist in D1.

Solution:

# Check migration status
npx wrangler d1 migrations list @websideproject/nuxt-auto-api-db

# Apply migrations
npx wrangler d1 migrations apply @websideproject/nuxt-auto-api-db --remote

"Query timeout"

Problem: Query exceeds 30-second timeout.

Solution:

  1. Add indexes on filtered/sorted columns
  2. Reduce result set size with pagination
  3. Optimize query with EXPLAIN QUERY PLAN
  4. Consider breaking into smaller queries

"Too many connections"

Problem: Hitting rate limits.

Solution:

  1. D1 handles connection pooling automatically
  2. Implement request queuing on your side
  3. Use caching to reduce database load
  4. Consider upgrading to higher D1 tier

Enable Query Logging

// server/plugins/database.d1.ts
export default defineNitroPlugin((nitroApp) => {
  const db = drizzle(nitroApp.cloudflare.env.DB, {
    schema,
    logger: true // Enable query logging
  })

  globalThis.__autoApiDb = db
})

D1-Specific Features

Query Tracing

D1 returns execution metadata:

const result = await db.prepare('SELECT * FROM users').all()

console.log('Query duration:', result.meta.duration)
console.log('Rows read:', result.meta.rows_read)
console.log('Rows written:', result.meta.rows_written)

Prepared Statements

D1 automatically uses prepared statements:

// Efficiently reuse prepared statement
const stmt = db.prepare('SELECT * FROM users WHERE id = ?')

const user1 = await stmt.bind(1).first()
const user2 = await stmt.bind(2).first()

Migration from SQLite to D1

See Migration Guide for detailed instructions.

Resources

Handler Overrides

SQLite to D1 Migration

This guide walks you through migrating your Nuxt Auto API application from local SQLite to Cloudflare D1 for edge deployment.

Need a Landing Page?

Modern landing pages with optional modules (blog, docs, forms, i18n). Let's discuss your project.

Build Your MVP

Full-stack SaaS development. Expert in database design, multi-tenancy, and scalable architecture.

Deployment Help

Dockerize your backend, set up CI/CD pipelines, deploy to Cloudflare or Hetzner. Early-stage setup.

Suggest a SaaS Tool

Missing a calculator or tool? Suggest what you'd like to see on our site.