从MySQL到Turso：Nuxt4项目数据库迁移全流程

import "dotenv/config";
import { defineConfig } from "prisma/config";

export default defineConfig({
    schema: "prisma/schema.prisma",
    migrations: {
        path: "prisma/migrations",
    },
    datasource: {
        url: process.env["DATABASE_URL"],
    },
});

import { createClient } from '@libsql/client';
import { execSync } from 'child_process';
import fs from 'fs';

async function runMigration() {
    const encryptionKey = process.env.TURSO_ENCRYPTION_KEY;
    const targetDbUrl = process.env.DATABASE_URL!;
    const schemaPath = "./prisma/schema.prisma";

    // 比较 Schema 和当前的加密数据库文件，生成增量 SQL
    // 注意：如果是第一次运行，--from-empty 会生成全量建表语句
    const command = `npx prisma migrate diff --from-empty --to-schema ${schemaPath} --script > migration.sql`;

    try {
        execSync(command);
        const sql = fs.readFileSync('migration.sql', 'utf8');
        const client = createClient({ url: targetDbUrl, encryptionKey: encryptionKey });

        // 先创建元数据表
        await client.execute(createMigrationsTableSql);
        await client.close();
    } catch (e) {
        console.error("迁移失败:", e);
    }
}

runMigration();

import { createClient } from '@libsql/client';
import * as fs from 'fs';
import * as path from 'path';

async function runExport() {
    const dbUrl =process.env.DATABASE_URL!;
    const authToken = process.env.TURSO_AUTH_TOKEN;
    const encryptionKey = process.env.TURSO_ENCRYPTION_KEY;

    const dbName = path.basename(dbUrl)
    const outputFile = `./prisma/backup/backup_${dbName}_${new Date().getTime()}.sql`;
    const client = createClient({ url: dbUrl, authToken, encryptionKey });

    try {
        // 1. 获取所有元数据（表、索引、视图等）
        const masterResult = await client.execute(
            "SELECT type, name, sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%' AND sql IS NOT NULL"
        );
        let sqlDump = `-- Database Dump (${dbName})\n-- Generated at: ${new Date().toLocaleString()}\n`;
        sqlDump += "PRAGMA foreign_keys=OFF;\n";

        // 2. 分类处理：先处理表，再处理索引和视图
        for (const row of masterResult.rows) {
            const type = row.type as string;
            const name = row.name as string;
            const sql = row.sql as string;

            // 写入清理语句和建表/建索引语句
            sqlDump += `\n-- ${type.toUpperCase()}: ${name}\n`;
            sqlDump += `DROP ${type.toUpperCase()} IF EXISTS "${name}";\n`;
            sqlDump += `${sql};\n`;

            // 3. 如果是表，则导出其中的数据记录
            if (type === 'table') {
                const dataResult = await client.execute(`SELECT * FROM "${name}"`);

                for (const dataRow of dataResult.rows) {
                    const keys = Object.keys(dataRow);
                    const columns = keys.map(k => `"${k}"`).join(', ');

                    const values = Object.values(dataRow).map(v => {
                        if (v === null) return 'NULL';
                        if (typeof v === 'string') {
                            return "'" + v.replace(/'/g, "''") + "'";
                        }
                        return v;
                    }).join(', ');

                    sqlDump += `INSERT INTO "${name}" (${columns}) VALUES (${values});\n`;
                }
            }
        }
        sqlDump += "\nPRAGMA foreign_keys=ON;";

        fs.writeFileSync(outputFile, sqlDump);
    } catch (error: any) {
        console.error("导出失败:", error.message);
    } finally {
        client.close();
    }
}

runExport();

import { createClient } from '@libsql/client';
import * as fs from 'fs';
import * as path from 'path';
import * as readline from 'readline';

async function runImport() {
    // 1. 获取参数: npm run dev:import <file_path>
    const filePath = process.argv[2];

    const dbUrl =process.env.DATABASE_URL!;
    const authToken = process.env.TURSO_AUTH_TOKEN;
    const encryptionKey = process.env.TURSO_ENCRYPTION_KEY;
    const dbName = path.basename(dbUrl)
    const client = createClient({ url: dbUrl, authToken, encryptionKey });

    try {
        const sqlContent = fs.readFileSync(filePath, 'utf8');
      
        // 暂时关闭外键检查，防止 DROP TABLE 时因关联关系报错
        await client.execute("PRAGMA foreign_keys = OFF;");
        await client.executeMultiple(sqlContent);
        // 恢复外键检查
        await client.execute("PRAGMA foreign_keys = ON;");
    } catch (error: any) {
        console.error(`导入失败: ${error.message}`);
    } finally {
        client.close();
    }
}

runImport();

import { createClient } from '@libsql/client';
import fs from 'fs';
import path from 'path';
import crypto from 'crypto';
import { v4 as uuidv4 } from 'uuid';

function calculateChecksum(filePath: string): string {
    const fileBuffer = fs.readFileSync(filePath);
    return crypto.createHash('sha256').update(fileBuffer).digest('hex');
}

async function syncMigrations() {
    const encryptionKey = process.env.TURSO_ENCRYPTION_KEY;
    const authToken = process.env.TURSO_AUTH_TOKEN;
    const dbUrl = process.env.DATABASE_URL!;
    const migrationsPath = path.join(process.cwd(), 'prisma', 'migrations');

    const client = createClient({ url: dbUrl, authToken, encryptionKey });

    try {
        // 获取 PRD 库中已经应用的迁移记录
        const appliedMigrationsResult = await client.execute(
            "SELECT migration_name FROM _prisma_migrations WHERE finished_at IS NOT NULL"
        );
        const appliedNames = new Set(appliedMigrationsResult.rows.map(r => r.migration_name as string));

        // 读取本地所有的迁移文件夹
        const localMigrations = fs.readdirSync(migrationsPath)
            .filter(f => fs.statSync(path.join(migrationsPath, f)).isDirectory())
            .sort(); // 确保按时间戳顺序排列

        // 找出尚未应用的迁移
        const pendingMigrations = localMigrations.filter(name => !appliedNames.has(name));

        if (pendingMigrations.length === 0) {
            return;
        }

        for (const migrationName of pendingMigrations) {
            const sqlPath = path.join(migrationsPath, migrationName, 'migration.sql');
            const sql = fs.readFileSync(sqlPath, 'utf8');

            // 计算文件的 SHA256 Checksum
            const checksum = calculateChecksum(sqlPath);
            // 生成标准的 UUID
            const migrationId = uuidv4();
            // 执行迁移 SQL 
            await client.executeMultiple(sql);

            // 手动在 _prisma_migrations 表中插入记录
            await client.execute({
                sql: `INSERT INTO _prisma_migrations 
              (id, checksum, finished_at, migration_name, logs, rolled_back_at, started_at, applied_steps_count)
              VALUES (?, ?, datetime('now'), ?, NULL, NULL, datetime('now'), 1)`,
                args: [
                    migrationId,
                    checksum,
                    migrationName
                ]
            });
        }
    } catch (e: any) {
        console.error("迁移失败:", e.message);
        process.exit(1);
    } finally {
        client.close();
    }
}

syncMigrations();