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
Step 2: Install Dependencies
npm install wrangler --save-dev
Step 3: Create D1 Database
# Create D1 database
npx wrangler d1 create my-app-db
# Save the output - you'll need database_id
Example output:
[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "abc12345-6789-def0-1234-56789abcdef0"
Step 4: Configure wrangler.toml
Create wrangler.toml in project root:
name = "my-app"
main = "./.output/server/index.mjs"
assets = { directory = "./.output/public/", binding = "ASSETS" }
compatibility_date = "2025-01-01"
compatibility_flags = ["nodejs_compat"]
[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "abc12345-6789-def0-1234-56789abcdef0"
migrations_dir = "server/database/migrations"
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 my-app-db --remote
Step 8: Update Database Plugin
The Worker's bindings exist per request (event.context.cloudflare.env), not while Nitro plugins start — so the
database is initialized on the first request. Full explanation: Cloudflare D1 › Step 5.
Option A: D1 Only (Production)
// server/plugins/database.ts
import { drizzle } from 'drizzle-orm/d1'
import { initializeDatabase } from '@websideproject/nuxt-auto-api/database'
import * as schema from '../database/schema'
export default defineNitroPlugin((nitroApp) => {
let ready = false
nitroApp.hooks.hook('request', (event) => {
if (ready) return
const db = event.context.cloudflare?.env?.DB
if (!db) throw createError({ statusCode: 500, message: 'D1 binding "DB" not found' })
initializeDatabase(drizzle(db, { schema }), 'd1')
ready = true
})
})
Option B: Hybrid (SQLite Local, D1 Production)
// server/plugins/database.ts
import { drizzle as drizzleD1 } from 'drizzle-orm/d1'
import { initializeDatabase } from '@websideproject/nuxt-auto-api/database'
import * as schema from '../database/schema'
export default defineNitroPlugin(async (nitroApp) => {
if (import.meta.dev) {
// Local development: SQLite. Imported dynamically so the native module never reaches the Worker bundle.
const { default: Database } = await import('better-sqlite3')
const { drizzle } = await import('drizzle-orm/better-sqlite3')
initializeDatabase(drizzle(new Database('.data/db.sqlite'), { schema }), 'better-sqlite3')
return
}
// Production: D1, from the first request's bindings
let ready = false
nitroApp.hooks.hook('request', (event) => {
if (ready) return
const db = event.context.cloudflare?.env?.DB
if (!db) throw createError({ statusCode: 500, message: 'D1 binding "DB" not found' })
initializeDatabase(drizzleD1(db, { schema }), 'd1')
ready = true
})
})
Step 9: Update nuxt.config.ts
// nuxt.config.ts
export default defineNuxtConfig({
modules: [
'@websideproject/nuxt-auto-api',
'nitro-cloudflare-dev', // only for Option A: gives `nuxi dev` the local D1 binding
],
autoApi: {
prefix: '/api',
},
nitro: {
preset: 'cloudflare_module',
},
})
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 my-app-db --remote --file=data-export.sql
Method 2: Generated INSERT statements
A Worker is the only place a D1 binding exists, so a local script cannot write to D1 through Drizzle. Generate the
INSERT statements locally instead — one row per statement, one file per table — and apply them with Wrangler
(split large files as under "Data import timeout" below):
// scripts/export-for-d1.ts — run with: npx tsx scripts/export-for-d1.ts
import { writeFileSync } from 'node:fs'
import Database from 'better-sqlite3'
const sqlite = new Database('.data/db.sqlite', { readonly: true })
const quote = (v: unknown) => v === null ? 'NULL' : typeof v === 'number' ? String(v) : `'${String(v).replaceAll("'", "''")}'`
for (const table of ['users', 'posts']) { // parents before children (foreign keys)
const rows = sqlite.prepare(`SELECT * FROM "${table}"`).all() as Record<string, unknown>[]
const lines = rows.map(row => `INSERT INTO "${table}" (${Object.keys(row).map(c => `"${c}"`).join(', ')}) VALUES (${Object.values(row).map(quote).join(', ')});`)
writeFileSync(`.data/d1-${table}.sql`, lines.join('\n'))
}
npx wrangler d1 execute my-app-db --remote --file=.data/d1-users.sql
npx wrangler d1 execute my-app-db --remote --file=.data/d1-posts.sql
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 my-app-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 — a separate Worker and database, declared under [env.staging] in wrangler.toml
npx wrangler deploy --env staging
# wrangler.toml
[env.staging]
name = "my-app-staging"
[[env.staging.d1_databases]]
binding = "DB"
database_name = "my-app-staging-db"
database_id = "…"
migrations_dir = "server/database/migrations"
3. Verify Data
# Query D1 directly
npx wrangler d1 execute my-app-db --remote --command="SELECT COUNT(*) FROM users"
npx wrangler d1 execute my-app-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 { initializeDatabase } from '@websideproject/nuxt-auto-api/database'
import * as schema from '../database/schema'
export default defineNitroPlugin(() => {
const sqlite = new Database('.data/db.sqlite')
initializeDatabase(drizzle(sqlite, { schema }), 'better-sqlite3')
})
2. nuxt.config.ts
Nothing to revert: the engine is the second argument of initializeDatabase() in the plugin above, not a module
option. Switch the Nitro preset back if you changed it.
3. Restore SQLite Data
# Restore from backup: `backup.sql` is SQL text (Step 1), so replay it into a fresh database file
mv .data/db.sqlite .data/db.sqlite.before-rollback
sqlite3 .data/db.sqlite < backup.sql
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 the [[d1_databases]] entry in wrangler.toml, then redeploy
npx wrangler deploy
# Or inspect the Worker's Bindings tab in the Cloudflare dashboard
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 my-app-db --remote --file=$file
done
Post-Migration
After successful migration:
- Monitor Performance
- Check D1 analytics in Cloudflare dashboard
- Monitor query latency
- Watch for rate limit errors
- Optimize Queries
- Add indexes for frequently queried fields
- Implement caching for hot data
- Use pagination everywhere
- Update Documentation
- Document D1-specific configuration
- Update team onboarding guides
- Add troubleshooting tips
- 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
TanStack Query (Vue Query) composables for every generated route: queries, mutations with cache invalidation, bulk, aggregates, M2M, permissions and custom endpoints. They are auto-imported; they can also be imported from @websideproject/nuxt-auto-api/composables.