Vite-Plugin-SQLC Proof of Concept

index.md

Vite-Plugin-SQLC Concept

SQLC can generate typesafe code for interacting with your database. This is done via code-generation.

I thought it would be nice to use vite to hide the code generation and instead import the .sql files where the queries are defined directly.

Thanks to Typescript's typeRoots option this is still entirely typesafe, but offers a better editing experience IMO.

// usage example
import { findAuthorsById } from "./authors.sql"

This gist has a proof-of-concept implementation of this functionality. Do not use this for anything serious.

tsconfig.json

{
  "compilerOptions": {
    "target": "ES2020",
    "useDefineForClassFields": true,
    "module": "ESNext",
    "lib": ["ES2020", "DOM", "DOM.Iterable"],
    "skipLibCheck": true,

    "allowJs": true,
    "checkJs": true,

    /* Bundler mode */
    "moduleResolution": "bundler",
    "allowImportingTsExtensions": true,
    "allowArbitraryExtensions": true,
    "isolatedModules": true,
    "moduleDetection": "force",
    "noEmit": true,

    /* Linting */
    "strict": true,
    "noFallthroughCasesInSwitch": true,
    "noUncheckedSideEffectImports": true,


    "rootDirs": [".", "./.types"],
    "typeRoots": ["./.types", "./node_modules/@types"]
  }
}

vite-plugin-sqlc.js

import fs from "node:fs/promises";
import { basename, dirname, join, relative } from "node:path";
import { tmpdir } from "node:os";
import { randomUUID } from "node:crypto";
import { execSync } from "node:child_process";
import {
  createCompilerHost,
  createProgram,
  ModuleKind,
  ScriptTarget,
} from "typescript";

/**
 *
 * @param {object} options
 * @param {string} options.schema Path to the schema.sql file
 *
 * @returns {import("vite").Plugin | import("vite").Plugin[]}
 */
export function vitePluginSQLC(options) {
  const absoluteSchemaPath = join(process.cwd(), options.schema);
  const typeRoot = join(process.cwd(), ".types");
  const tempdir = realTempdir();

  /**
   * @param {string} path 
   */
  async function updateTypesForFile(path) {
    if (!path.endsWith("sql")) return;
    if (path === absoluteSchemaPath) return;

    // delete the associated .d.ts file if it exists
    const declarationPath = typeDefinitionPath({
      typeRoot,
      path,
    });
    const declarationDir = dirname(declarationPath);
    await fs.mkdir(declarationDir, { recursive: true})

    const { dts } = await compile({
      tempdir,
      schema: absoluteSchemaPath,
      path,
    });

    await fs.writeFile(declarationPath, dts)
  }

  return {
    name: "vite-plugin-sqlc",
    enforce: "pre",

    configureServer(server) {
      server.watcher.add("**/*.sql");

      // Handle query files being deleted
      server.watcher.on("unlink", async (path) => {
        if (!path.endsWith("sql")) return;
        if (path === absoluteSchemaPath) return;

        // delete the associated .d.ts file if it exists
        const declarationPath = typeDefinitionPath({
          typeRoot,
          path,
        });

        try {
          await fs.unlink(declarationPath);
        } catch (e) {}
      });

      server.watcher.on("add", updateTypesForFile);
      server.watcher.on("change", updateTypesForFile);
    },

    async handleHotUpdate({ server, file }) {
      if (file === absoluteSchemaPath) {
        server.hot.send({ type: "full-reload" });
        server.moduleGraph.invalidateAll();
        return [];
      }
    },

    async load(id) {
      if (!id.endsWith(".sql")) return;

      const { js, dts } = await compile({
        tempdir,
        schema: absoluteSchemaPath,
        path: id,
      });

      // write the dts file to the typeroot
      const declarationFile = typeDefinitionPath({
        typeRoot,
        path: id,
      });
      const destinationDir = dirname(declarationFile);

      // create the directory if necessary
      await fs.mkdir(destinationDir, { recursive: true });
      // write the file
      await fs.writeFile(declarationFile, dts);

      return js;
    },
  };
}

/**
 * Compiles the given path using SQLC and returns JS + Type-Declarations
 *
 * @param {object} options
 * @param {string} options.path The absolute path to the `query.sql` file that should be compiled
 * @param {string} options.schema The absolute path to the `schema.sql` file
 * @param {string} options.tempdir The absolute path to the tempdir. MUST NOT CONTAIN SYMLINKS
 *
 * @returns {Promise<{ js: string, dts: string }>}
 */
async function compile({ path, tempdir, schema }) {
  const ts = await runSQLCCompiler({
    tempdir,
    schema,
    query: path,
  });

  return compileTsToJs(ts);
}

/**
 * Maps a `query.sql` file path to it's type-declaration path
 *
 * @param {object} options
 * @param {string} options.path The path to the `query.sql` file
 * @param {string} options.typeRoot The typeRoot directory
 *
 * @returns {string} The path to the associated `sql.d.ts` file
 */
function typeDefinitionPath({ path, typeRoot }) {
  const rel = relative(process.cwd(), path);
  const destination = join(typeRoot, rel) + ".d.ts";
  return destination;
}

/**
 * @returns {string} The absolute path to the OS's tempdir without symlinks
 */
function realTempdir() {
  // `tmpdir()` returns a symlink on MacOS
  // The path with symlinks can't be used to create relative paths, we need the real path for that
  return execSync("pwd", { cwd: tmpdir() }).toString();
}

/**
 * @param {string} ts The typescript source
 * @returns {{ js: string, dts: string }}
 */
function compileTsToJs(ts) {
  const options = {
    module: ModuleKind.ESNext,
    target: ScriptTarget.ESNext,
    declaration: true,
  };
  const host = createCompilerHost(options);

  /** @type {Record<string, string>} */
  const createdFiles = {};
  /** @type {Record<string, string>} */
  const files = { "code.ts": ts };
  host.writeFile = (filename, text) => (createdFiles[filename] = text);
  host.readFile = (filename) => files[filename];

  const program = createProgram(["code.ts"], options, host);
  program.emit();

  return {
    js: createdFiles["code.js"],
    dts: createdFiles["code.d.ts"],
  };
}

/**
 * Compiles the given Query file
 *
 * @param {object} options
 * @param {string} options.tempdir The ansolute path to the OS's tempdir
 * @param {string} options.query The absolute path to the query file
 * @param {string} options.schema The absolute path to the schema file
 *
 * @returns {Promise<string>} The contents of the compiled TS file
 */
async function runSQLCCompiler({ tempdir, schema, query }) {
  /** The path to the temporary SQLC project */
  const dir = join(tempdir, "vite-plugin-sqlc", randomUUID());

  try {
    // create the directory
    await fs.mkdir(dir, { recursive: true });

    // add the config file
    const sqlcConfigFile = join(dir, "sqlc.yaml");
    const configFileContent = sqlcConfigFileContent({
      dir,
      schema,
      queries: [query],
    });
    await fs.writeFile(sqlcConfigFile, configFileContent);

    // Run the SQLC compiler in the directory. The output will be in <dir>/out
    try {
      execSync("sqlc generate", { cwd: dir });
    } catch (e) { 
      console.error(e);
      return "";
    }
    const expectedFilename = basename(query).replace(".sql", "_sql") + ".ts";
    const compiled = await fs.readFile(join(dir, "out", expectedFilename), {
      encoding: "utf-8",
    });

    return compiled;
  } finally {
    // finally, delete the directory
    fs.rm(dir, { recursive: true, force: true });
  }
}

/**
 * Creates the contents of a `sqlc.yaml` that is set up with `sqlc-gen-typescript`
 * and has the given schema and query paths configured.
 *
 * @param {object} options
 * @param {string} options.dir Absolute path to the directory this `sqlc.yaml` will be placed in
 * @param {string} options.schema The absolute path to the `schema.sql` file
 * @param {string[]} options.queries A list of absolute paths pointing to the schema files
 */
function sqlcConfigFileContent({ dir, schema, queries }) {
  const relativeSchema = relative(dir, schema);
  const relativeQueries = queries.map((q) => relative(dir, q));

  return `version: "2"
plugins:
- name: ts
  wasm:
    url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasm
    sha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368
sql:
- schema: "${relativeSchema}"
  queries: ${JSON.stringify(relativeQueries)}
  engine: "postgresql"
  codegen:
  - out: out
    plugin: ts
    options:
      runtime: node
      driver: postgres
`;
}