logowebsideproject

SQLite to D1 Migration

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

SQLite to D1 Migration

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

Table of Contents

Prerequisites

Before starting the migration:

  • ✅ Working Nuxt Auto API application with SQLite
  • ✅ Cloudflare account with Pages or Workers
  • ✅ Git repository for version control
  • ✅ Backup of your SQLite database

Step-by-Step Migration

Step 1: Backup Your Data

Export your SQLite database:

# Export database to SQL file
sqlite3 .data/db.sqlite .dump > backup.sql

# Or use Drizzle to export
npx drizzle-kit push:sqlite --config=drizzle.config.ts

Step 2: Install Dependencies

npm install wrangler --save-dev

Step 3: Create D1 Database

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

# Save the output - you'll need database_id

Example output:

[[d1_databases]]
binding = "DB"
database_name = "@websideproject/nuxt-auto-api-db"
database_id = "abc12345-6789-def0-1234-56789abcdef0"

Step 4: Configure wrangler.toml

Create wrangler.toml in project root:

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

# Production D1
[[d1_databases]]
binding = "DB"
database_name = "@websideproject/nuxt-auto-api-db"
database_id = "abc12345-6789-def0-1234-56789abcdef0"

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

Step 5: Update Drizzle Config

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

export default defineConfig({
  schema: './server/database/schema.ts',
  out: './server/database/migrations',
  dialect: 'sqlite',

  // For D1 remote operations
  ...(process.env.CLOUDFLARE_ACCOUNT_ID && {
    driver: 'd1-http',
    dbCredentials: {
      accountId: process.env.CLOUDFLARE_ACCOUNT_ID,
      databaseId: process.env.CLOUDFLARE_D1_ID,
      token: process.env.CLOUDFLARE_API_TOKEN
    }
  })
})

Step 6: Generate Migrations

If you don't have migrations yet:

# Generate migrations from schema
npx drizzle-kit generate

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

Step 7: Apply Migrations to D1

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

Step 8: Update Database Plugin

Option A: D1 Only (Production)

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

export default defineNitroPlugin((nitroApp) => {
  if (!nitroApp.cloudflare?.env?.DB) {
    throw new Error('D1 database binding not found')
  }

  const db = drizzle(nitroApp.cloudflare.env.DB, { schema })
  globalThis.__autoApiDb = db

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

Option B: Hybrid (SQLite Local, D1 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: SQLite
    const sqlite = new Database('.data/db.sqlite')
    db = drizzleSqlite(sqlite, { schema })
    console.log('[dev] SQLite database initialized')
  } else {
    // Production: D1
    if (!nitroApp.cloudflare?.env?.DB) {
      throw new Error('D1 database binding not found')
    }
    db = drizzleD1(nitroApp.cloudflare.env.DB, { schema })
    console.log('[prod] D1 database initialized')
  }

  globalThis.__autoApiDb = db
})

Step 9: Update nuxt.config.ts

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

  autoApi: {
    prefix: '/api',
    database: {
      client: process.dev ? 'better-sqlite3' : 'd1'
    }
  },

  nitro: {
    preset: 'cloudflare-pages'
  }
})

Step 10: Set Environment Variables

Create .env:

CLOUDFLARE_ACCOUNT_ID=your-account-id
CLOUDFLARE_D1_ID=abc12345-6789-def0-1234-56789abcdef0
CLOUDFLARE_API_TOKEN=your-api-token

Add to .gitignore:

.env
.data/

Data Migration

Method 1: SQL Dump (Recommended)

# 1. Export SQLite data
sqlite3 .data/db.sqlite .dump > data-export.sql

# 2. Clean up SQLite-specific syntax
# Remove lines starting with:
# - PRAGMA
# - BEGIN TRANSACTION
# - COMMIT
sed -i '/^PRAGMA/d' data-export.sql
sed -i '/^BEGIN TRANSACTION/d' data-export.sql
sed -i '/^COMMIT/d' data-export.sql

# 3. Import to D1
npx wrangler d1 execute @websideproject/nuxt-auto-api-db --remote --file=data-export.sql

Method 2: Programmatic Migration

// scripts/migrate-to-d1.ts
import Database from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/d1'
import * as schema from '../server/database/schema'

async function migrate() {
  // Connect to SQLite
  const sqlite = new Database('.data/db.sqlite')
  const sqliteDb = drizzle(sqlite, { schema })

  // Connect to D1 (requires Wrangler)
  // This would run in a Cloudflare Worker context
  const d1Db = drizzle(env.DB, { schema })

  // Fetch all data from SQLite
  const users = await sqliteDb.select().from(schema.users)
  const posts = await sqliteDb.select().from(schema.posts)

  // Insert into D1
  if (users.length > 0) {
    await d1Db.insert(schema.users).values(users)
  }

  if (posts.length > 0) {
    await d1Db.insert(schema.posts).values(posts)
  }

  console.log('Migration complete!')
}

migrate()

Method 3: CSV Export/Import

# Export to CSV
sqlite3 -header -csv .data/db.sqlite "SELECT * FROM users;" > users.csv
sqlite3 -header -csv .data/db.sqlite "SELECT * FROM posts;" > posts.csv

# Convert CSV to SQL INSERT statements
# (Use a script or tool like csv2sql)

# Import to D1
npx wrangler d1 execute @websideproject/nuxt-auto-api-db --remote --file=insert-data.sql

Testing the Migration

1. Test Locally with D1

# Start Wrangler dev server
npx wrangler dev

# In another terminal, start Nuxt
npm run dev

# Test API endpoints
curl http://localhost:3000/api/users
curl http://localhost:3000/api/posts

2. Deploy to Staging

# Build for Cloudflare
npm run build

# Deploy to staging
npx wrangler pages deploy .output/public --project-name=@websideproject/nuxt-auto-api-staging

3. Verify Data

# Query D1 directly
npx wrangler d1 execute @websideproject/nuxt-auto-api-db --remote --command="SELECT COUNT(*) FROM users"
npx wrangler d1 execute @websideproject/nuxt-auto-api-db --remote --command="SELECT COUNT(*) FROM posts"

# Compare with SQLite counts
sqlite3 .data/db.sqlite "SELECT COUNT(*) FROM users"
sqlite3 .data/db.sqlite "SELECT COUNT(*) FROM posts"

4. Run Integration Tests

# Run tests against D1
npm run test:d1

Deployment Checklist

  • Migrations applied to D1
  • Data migrated successfully
  • Environment variables set in Cloudflare
  • D1 bindings configured in Cloudflare Pages
  • Local development tested with D1
  • Staging deployment tested
  • Integration tests pass
  • Performance benchmarks acceptable
  • Monitoring/alerting configured

Rollback Plan

If you need to rollback:

1. Revert Database Plugin

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

export default defineNitroPlugin(() => {
  const sqlite = new Database('.data/db.sqlite')
  const db = drizzle(sqlite, { schema })
  globalThis.__autoApiDb = db
})

2. Revert nuxt.config.ts

export default defineNuxtConfig({
  autoApi: {
    database: {
      client: 'better-sqlite3'
    }
  }
})

3. Restore SQLite Data

# Restore from backup
cp backup.sql .data/db.sqlite

4. Redeploy

npm run build
# Deploy to your original hosting

Common Issues

Issue: "Binding not found"

Cause: D1 binding not configured in Cloudflare.

Fix:

# Check bindings
npx wrangler pages deployment list

# Add binding via dashboard or CLI

Issue: "Migration failed"

Cause: SQL syntax incompatible with D1.

Fix:

  • Remove SQLite-specific pragmas
  • Check for unsupported data types
  • Use standard SQL syntax

Issue: "Data import timeout"

Cause: Importing too much data at once.

Fix:

# Split into smaller batches
split -l 1000 data-export.sql data-batch-

# Import each batch
for file in data-batch-*; do
  npx wrangler d1 execute @websideproject/nuxt-auto-api-db --remote --file=$file
done

Post-Migration

After successful migration:

  1. Monitor Performance
    • Check D1 analytics in Cloudflare dashboard
    • Monitor query latency
    • Watch for rate limit errors
  2. Optimize Queries
    • Add indexes for frequently queried fields
    • Implement caching for hot data
    • Use pagination everywhere
  3. Update Documentation
    • Document D1-specific configuration
    • Update team onboarding guides
    • Add troubleshooting tips
  4. Clean Up
    • Archive SQLite database
    • Remove SQLite-specific code
    • Update CI/CD pipelines

Resources

Cloudflare D1

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

Frontend Composables

Nuxt Auto API provides TanStack Query-powered composables for seamless data fetching, caching, and mutations on the frontend.

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.