Generate schema from a sqlite_schema table query

[jlarmstrongiv]

Being able to define the schema is great! It would be nice to have the option to generate it too.

The node script from my project:

import Database from "better-sqlite3";
import fs from "fs-extra";

async function start() {
  const db = new Database("chinook.db", {
    fileMustExist: true,
  });
  db.pragma("journal_mode = WAL");

  const statement = db.prepare("SELECT sql FROM sqlite_schema;");
  const rows = statement.all() as { sql: string }[];
  const schema = rows
    .map((row) => row.sql)
    // sql uses "\r\n"
    .join(";\r\n\r\n")
    // normalize to "\n"
    .replace(/\r\n/g, "\n")
    // remove type affinities
    .replace(/NVARCHAR/g, "TEXT")
    .replace(/DATETIME/g, "TEXT")
    .replace(/NUMERIC/g, "REAL")
    // remove braces
    .replace(/\[/g, "")
    .replace(/\]/g, "")
    // remove quotes
    .replace(/\"/g, "");
  const template = `import { schema } from "@vlcn.io/typed-sql";

export const generatedSchema = schema\`

${schema}

\`;
`;

  await fs.outputFile("./src/generated/schema.ts", template);
}

start();