389 lines
15 KiB
TypeScript
389 lines
15 KiB
TypeScript
/**
|
|
* One-shot migration script: SQLite → PostgreSQL
|
|
*
|
|
* Reads data from the SQLite backup (prisma/dev.db) via better-sqlite3,
|
|
* connects to PostgreSQL via Prisma, and inserts all rows while converting
|
|
* JSON string fields to native objects (for Prisma Json type).
|
|
*
|
|
* Usage:
|
|
* DATABASE_URL="postgresql://keepnotes:keepnotes@localhost:5432/keepnotes" \
|
|
* npx tsx scripts/migrate-sqlite-to-postgres.ts
|
|
*
|
|
* Prerequisites:
|
|
* - PostgreSQL running and accessible via DATABASE_URL
|
|
* - prisma migrate deploy already run (schema exists)
|
|
* - better-sqlite3 still installed (temporary)
|
|
*/
|
|
|
|
import Database from 'better-sqlite3'
|
|
import { PrismaClient } from '../prisma/client-generated'
|
|
import * as path from 'path'
|
|
|
|
const SQLITE_PATH = path.join(__dirname, '..', 'prisma', 'dev.db')
|
|
|
|
// Parse a JSON string field, returning null if empty/invalid
|
|
function parseJsonField(raw: any): any {
|
|
if (raw === null || raw === undefined) return null
|
|
if (typeof raw !== 'string') return raw
|
|
if (raw === '' || raw === 'null') return null
|
|
try {
|
|
return JSON.parse(raw)
|
|
} catch {
|
|
return null
|
|
}
|
|
}
|
|
|
|
// Parse labels specifically — always return array or null
|
|
function parseLabels(raw: any): string[] | null {
|
|
const parsed = parseJsonField(raw)
|
|
if (Array.isArray(parsed)) return parsed
|
|
return null
|
|
}
|
|
|
|
// Parse embedding — always return number[] or null
|
|
function parseEmbedding(raw: any): number[] | null {
|
|
const parsed = parseJsonField(raw)
|
|
if (Array.isArray(parsed)) return parsed
|
|
return null
|
|
}
|
|
|
|
async function main() {
|
|
console.log('╔══════════════════════════════════════════════════════════╗')
|
|
console.log('║ SQLite → PostgreSQL Migration ║')
|
|
console.log('╚══════════════════════════════════════════════════════════╝')
|
|
console.log()
|
|
|
|
// 1. Open SQLite
|
|
let sqlite: Database.Database
|
|
try {
|
|
sqlite = new Database(SQLITE_PATH, { readonly: true })
|
|
console.log(`✓ SQLite opened: ${SQLITE_PATH}`)
|
|
} catch (e) {
|
|
console.error(`✗ Cannot open SQLite at ${SQLITE_PATH}: ${e}`)
|
|
process.exit(1)
|
|
}
|
|
|
|
// 2. Connect to PostgreSQL via Prisma
|
|
const prisma = new PrismaClient()
|
|
console.log(`✓ PostgreSQL connected via Prisma`)
|
|
console.log()
|
|
|
|
// Helper to read all rows from SQLite
|
|
function allRows(sql: string): any[] {
|
|
return sqlite.prepare(sql).all() as any[]
|
|
}
|
|
|
|
let totalInserted = 0
|
|
|
|
// ── User ──────────────────────────────────────────────────
|
|
console.log('Migrating User...')
|
|
const users = allRows('SELECT * FROM User')
|
|
for (const u of users) {
|
|
await prisma.user.upsert({
|
|
where: { id: u.id },
|
|
update: {},
|
|
create: {
|
|
id: u.id,
|
|
name: u.name,
|
|
email: u.email,
|
|
emailVerified: u.emailVerified ? new Date(u.emailVerified) : null,
|
|
password: u.password,
|
|
role: u.role || 'USER',
|
|
image: u.image,
|
|
theme: u.theme || 'light',
|
|
resetToken: u.resetToken,
|
|
resetTokenExpiry: u.resetTokenExpiry ? new Date(u.resetTokenExpiry) : null,
|
|
createdAt: u.createdAt ? new Date(u.createdAt) : new Date(),
|
|
updatedAt: u.updatedAt ? new Date(u.updatedAt) : new Date(),
|
|
}
|
|
})
|
|
}
|
|
console.log(` → ${users.length} users`)
|
|
totalInserted += users.length
|
|
|
|
// ── Account ───────────────────────────────────────────────
|
|
console.log('Migrating Account...')
|
|
const accounts = allRows('SELECT * FROM Account')
|
|
for (const a of accounts) {
|
|
await prisma.account.create({
|
|
data: {
|
|
userId: a.userId,
|
|
type: a.type,
|
|
provider: a.provider,
|
|
providerAccountId: a.providerAccountId,
|
|
refresh_token: a.refresh_token,
|
|
access_token: a.access_token,
|
|
expires_at: a.expires_at,
|
|
token_type: a.token_type,
|
|
scope: a.scope,
|
|
id_token: a.id_token,
|
|
session_state: a.session_state,
|
|
createdAt: a.createdAt ? new Date(a.createdAt) : new Date(),
|
|
updatedAt: a.updatedAt ? new Date(a.updatedAt) : new Date(),
|
|
}
|
|
}).catch(() => {}) // skip duplicates
|
|
}
|
|
console.log(` → ${accounts.length} accounts`)
|
|
totalInserted += accounts.length
|
|
|
|
// ── Session ───────────────────────────────────────────────
|
|
console.log('Migrating Session...')
|
|
const sessions = allRows('SELECT * FROM Session')
|
|
for (const s of sessions) {
|
|
await prisma.session.create({
|
|
data: {
|
|
sessionToken: s.sessionToken,
|
|
userId: s.userId,
|
|
expires: s.expires ? new Date(s.expires) : new Date(),
|
|
createdAt: s.createdAt ? new Date(s.createdAt) : new Date(),
|
|
updatedAt: s.updatedAt ? new Date(s.updatedAt) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${sessions.length} sessions`)
|
|
totalInserted += sessions.length
|
|
|
|
// ── Notebook ──────────────────────────────────────────────
|
|
console.log('Migrating Notebook...')
|
|
const notebooks = allRows('SELECT * FROM Notebook')
|
|
for (const nb of notebooks) {
|
|
await prisma.notebook.create({
|
|
data: {
|
|
id: nb.id,
|
|
name: nb.name,
|
|
icon: nb.icon,
|
|
color: nb.color,
|
|
order: nb.order ?? 0,
|
|
userId: nb.userId,
|
|
createdAt: nb.createdAt ? new Date(nb.createdAt) : new Date(),
|
|
updatedAt: nb.updatedAt ? new Date(nb.updatedAt) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${notebooks.length} notebooks`)
|
|
totalInserted += notebooks.length
|
|
|
|
// ── Label ─────────────────────────────────────────────────
|
|
console.log('Migrating Label...')
|
|
const labels = allRows('SELECT * FROM Label')
|
|
for (const l of labels) {
|
|
await prisma.label.create({
|
|
data: {
|
|
id: l.id,
|
|
name: l.name,
|
|
color: l.color || 'gray',
|
|
notebookId: l.notebookId,
|
|
userId: l.userId,
|
|
createdAt: l.createdAt ? new Date(l.createdAt) : new Date(),
|
|
updatedAt: l.updatedAt ? new Date(l.updatedAt) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${labels.length} labels`)
|
|
totalInserted += labels.length
|
|
|
|
// ── Note ──────────────────────────────────────────────────
|
|
console.log('Migrating Note...')
|
|
const notes = allRows('SELECT * FROM Note')
|
|
let noteCount = 0
|
|
for (const n of notes) {
|
|
await prisma.note.create({
|
|
data: {
|
|
id: n.id,
|
|
title: n.title,
|
|
content: n.content || '',
|
|
color: n.color || 'default',
|
|
isPinned: n.isPinned === 1 || n.isPinned === true,
|
|
isArchived: n.isArchived === 1 || n.isArchived === true,
|
|
type: n.type || 'text',
|
|
dismissedFromRecent: n.dismissedFromRecent === 1 || n.dismissedFromRecent === true,
|
|
checkItems: parseJsonField(n.checkItems),
|
|
labels: parseLabels(n.labels),
|
|
images: parseJsonField(n.images),
|
|
links: parseJsonField(n.links),
|
|
reminder: n.reminder ? new Date(n.reminder) : null,
|
|
isReminderDone: n.isReminderDone === 1 || n.isReminderDone === true,
|
|
reminderRecurrence: n.reminderRecurrence,
|
|
reminderLocation: n.reminderLocation,
|
|
isMarkdown: n.isMarkdown === 1 || n.isMarkdown === true,
|
|
size: n.size || 'small',
|
|
embedding: parseEmbedding(n.embedding),
|
|
sharedWith: parseJsonField(n.sharedWith),
|
|
userId: n.userId,
|
|
order: n.order ?? 0,
|
|
notebookId: n.notebookId,
|
|
createdAt: n.createdAt ? new Date(n.createdAt) : new Date(),
|
|
updatedAt: n.updatedAt ? new Date(n.updatedAt) : new Date(),
|
|
contentUpdatedAt: n.contentUpdatedAt ? new Date(n.contentUpdatedAt) : new Date(),
|
|
autoGenerated: n.autoGenerated === 1 ? true : n.autoGenerated === 0 ? false : null,
|
|
aiProvider: n.aiProvider,
|
|
aiConfidence: n.aiConfidence,
|
|
language: n.language,
|
|
languageConfidence: n.languageConfidence,
|
|
lastAiAnalysis: n.lastAiAnalysis ? new Date(n.lastAiAnalysis) : null,
|
|
}
|
|
}).catch((e) => {
|
|
console.error(` Failed note ${n.id}: ${e.message}`)
|
|
})
|
|
noteCount++
|
|
}
|
|
console.log(` → ${noteCount} notes`)
|
|
totalInserted += noteCount
|
|
|
|
// ── NoteShare ─────────────────────────────────────────────
|
|
console.log('Migrating NoteShare...')
|
|
const noteShares = allRows('SELECT * FROM NoteShare')
|
|
for (const ns of noteShares) {
|
|
await prisma.noteShare.create({
|
|
data: {
|
|
id: ns.id,
|
|
noteId: ns.noteId,
|
|
userId: ns.userId,
|
|
sharedBy: ns.sharedBy,
|
|
status: ns.status || 'pending',
|
|
permission: ns.permission || 'view',
|
|
notifiedAt: ns.notifiedAt ? new Date(ns.notifiedAt) : null,
|
|
respondedAt: ns.respondedAt ? new Date(ns.respondedAt) : null,
|
|
createdAt: ns.createdAt ? new Date(ns.createdAt) : new Date(),
|
|
updatedAt: ns.updatedAt ? new Date(ns.updatedAt) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${noteShares.length} note shares`)
|
|
totalInserted += noteShares.length
|
|
|
|
// ── AiFeedback ────────────────────────────────────────────
|
|
console.log('Migrating AiFeedback...')
|
|
const aiFeedbacks = allRows('SELECT * FROM AiFeedback')
|
|
for (const af of aiFeedbacks) {
|
|
await prisma.aiFeedback.create({
|
|
data: {
|
|
id: af.id,
|
|
noteId: af.noteId,
|
|
userId: af.userId,
|
|
feedbackType: af.feedbackType,
|
|
feature: af.feature,
|
|
originalContent: af.originalContent || '',
|
|
correctedContent: af.correctedContent,
|
|
metadata: parseJsonField(af.metadata),
|
|
createdAt: af.createdAt ? new Date(af.createdAt) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${aiFeedbacks.length} ai feedbacks`)
|
|
totalInserted += aiFeedbacks.length
|
|
|
|
// ── MemoryEchoInsight ─────────────────────────────────────
|
|
console.log('Migrating MemoryEchoInsight...')
|
|
const insights = allRows('SELECT * FROM MemoryEchoInsight')
|
|
for (const mi of insights) {
|
|
await prisma.memoryEchoInsight.create({
|
|
data: {
|
|
id: mi.id,
|
|
userId: mi.userId,
|
|
note1Id: mi.note1Id,
|
|
note2Id: mi.note2Id,
|
|
similarityScore: mi.similarityScore ?? 0,
|
|
insight: mi.insight || '',
|
|
insightDate: mi.insightDate ? new Date(mi.insightDate) : new Date(),
|
|
viewed: mi.viewed === 1 || mi.viewed === true,
|
|
feedback: mi.feedback,
|
|
dismissed: mi.dismissed === 1 || mi.dismissed === true,
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${insights.length} memory echo insights`)
|
|
totalInserted += insights.length
|
|
|
|
// ── UserAISettings ────────────────────────────────────────
|
|
console.log('Migrating UserAISettings...')
|
|
const aiSettings = allRows('SELECT * FROM UserAISettings')
|
|
for (const s of aiSettings) {
|
|
await prisma.userAISettings.create({
|
|
data: {
|
|
userId: s.userId,
|
|
titleSuggestions: s.titleSuggestions === 1 || s.titleSuggestions === true,
|
|
semanticSearch: s.semanticSearch === 1 || s.semanticSearch === true,
|
|
paragraphRefactor: s.paragraphRefactor === 1 || s.paragraphRefactor === true,
|
|
memoryEcho: s.memoryEcho === 1 || s.memoryEcho === true,
|
|
memoryEchoFrequency: s.memoryEchoFrequency || 'daily',
|
|
aiProvider: s.aiProvider || 'auto',
|
|
preferredLanguage: s.preferredLanguage || 'auto',
|
|
fontSize: s.fontSize || 'medium',
|
|
demoMode: s.demoMode === 1 || s.demoMode === true,
|
|
showRecentNotes: s.showRecentNotes === 1 || s.showRecentNotes === true,
|
|
notesViewMode: s.notesViewMode || 'masonry',
|
|
emailNotifications: s.emailNotifications === 1 || s.emailNotifications === true,
|
|
desktopNotifications: s.desktopNotifications === 1 || s.desktopNotifications === true,
|
|
anonymousAnalytics: s.anonymousAnalytics === 1 || s.anonymousAnalytics === true,
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${aiSettings.length} user AI settings`)
|
|
totalInserted += aiSettings.length
|
|
|
|
// ── SystemConfig ──────────────────────────────────────────
|
|
console.log('Migrating SystemConfig...')
|
|
const configs = allRows('SELECT * FROM SystemConfig')
|
|
for (const c of configs) {
|
|
await prisma.systemConfig.create({
|
|
data: {
|
|
key: c.key,
|
|
value: c.value,
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${configs.length} system configs`)
|
|
totalInserted += configs.length
|
|
|
|
// ── _LabelToNote (many-to-many relations) ─────────────────
|
|
console.log('Migrating Label-Note relations...')
|
|
let relationCount = 0
|
|
try {
|
|
const relations = allRows('SELECT * FROM _LabelToNote')
|
|
for (const r of relations) {
|
|
await prisma.note.update({
|
|
where: { id: r.B },
|
|
data: {
|
|
labelRelations: { connect: { id: r.A } }
|
|
}
|
|
}).catch(() => {})
|
|
relationCount++
|
|
}
|
|
} catch {
|
|
// Table may not exist in older SQLite databases
|
|
console.log(' → _LabelToNote table not found, skipping')
|
|
}
|
|
console.log(` → ${relationCount} label-note relations`)
|
|
totalInserted += relationCount
|
|
|
|
// ── VerificationToken ─────────────────────────────────────
|
|
console.log('Migrating VerificationToken...')
|
|
const tokens = allRows('SELECT * FROM VerificationToken')
|
|
for (const t of tokens) {
|
|
await prisma.verificationToken.create({
|
|
data: {
|
|
identifier: t.identifier,
|
|
token: t.token,
|
|
expires: t.expires ? new Date(t.expires) : new Date(),
|
|
}
|
|
}).catch(() => {})
|
|
}
|
|
console.log(` → ${tokens.length} verification tokens`)
|
|
totalInserted += tokens.length
|
|
|
|
// Cleanup
|
|
sqlite.close()
|
|
await prisma.$disconnect()
|
|
|
|
console.log()
|
|
console.log('╔══════════════════════════════════════════════════════════╗')
|
|
console.log(`║ Migration complete: ${totalInserted} total rows inserted ║`)
|
|
console.log('╚══════════════════════════════════════════════════════════╝')
|
|
}
|
|
|
|
main().catch((e) => {
|
|
console.error('Migration failed:', e)
|
|
process.exit(1)
|
|
})
|