Using sqlglot from JavaScript and TypeScript

Pyodide, package persistence, and testing BigQuery SQL on DuckDB

Read time: 15 minutes

The sqlglot library is a Python SQL parser and transpiler. When working with SQL in a JavaScript or TypeScript application, you can call sqlglot directly from Node.js using Pyodide.

This is useful for handling SQL dialect differences if your production environment runs on BigQuery and your local testing environment uses DuckDB. This post explains the initial setup and how to persist packages for regular use.

Minimal setup for sqlglot

You can test the basic functionality with a minimal script. First, install pyodide:

npm i pyodide

Then, write a script to load pyodide and transpile a query:

import { loadPyodide, type PyodideInterface } from "pyodide";

const pyodide: PyodideInterface = await loadPyodide();
await pyodide.loadPackage("micropip");

await pyodide.runPythonAsync(`
import micropip
await micropip.install("sqlglot==30.2.1")
import sqlglot

source_sql = """
SELECT user_id, COUNT(*) AS c
FROM analytics.events
GROUP BY user_id
"""

result_sql = sqlglot.transpile(source_sql, read="bigquery", write="duckdb")[0]
`);

const resultProxy = pyodide.globals.get("result_sql");
const resultSql: string = resultProxy.toString();
resultProxy.destroy?.();

console.log(resultSql);

This approach works well to verify that the tool functions correctly. However, running micropip.install() every time slows down Node.js applications. For long-term use, persisting the package is a better approach.

Package persistence strategy

Pyodide's loadPyodide function accepts options such as packageCacheDir, lockFileContents, and packageBaseUrl. The micropip module also provides a freeze() method. You can use these features together to lock your dependencies.

The setup involves two steps:

Run a bootstrap script to save the lockfile and wheel files.
When starting the application, call pyodide.loadPackage("sqlglot") using the saved lockfile.

Bootstrap script in TypeScript

This script creates the required cache directories and downloads the lockfile.

// scripts/bootstrap-pyodide-sqlglot.ts
import { access, mkdir, writeFile } from "node:fs/promises";
import path from "node:path";
import { loadPyodide, type PyodideInterface } from "pyodide";

const ARTIFACT_DIR = path.resolve(".pyodide-artifacts");
const CACHE_DIR = path.join(ARTIFACT_DIR, "package-cache");
const LOCK_PATH = path.join(ARTIFACT_DIR, "pyodide-lock.json");
const SQLGLOT_SPEC = "sqlglot==30.2.1";

type Lockfile = {
  packages: Record<string, { file_name: string }>;
};

async function fileExists(filePath: string): Promise<boolean> {
  try {
    await access(filePath);
    return true;
  } catch {
    return false;
  }
}

await mkdir(CACHE_DIR, { recursive: true });
if (await fileExists(LOCK_PATH)) {
  console.log("lockfile already exists:", LOCK_PATH);
  process.exit(0);
}

const pyodide: PyodideInterface = await loadPyodide({ packageCacheDir: CACHE_DIR });
await pyodide.loadPackage("micropip");
pyodide.globals.set("sqlglot_spec", SQLGLOT_SPEC);

await pyodide.runPythonAsync(`
import micropip
await micropip.install(sqlglot_spec)
lock_json = micropip.freeze()
`);

const lockProxy = pyodide.globals.get("lock_json");
const lock = JSON.parse(lockProxy.toString()) as Lockfile;
lockProxy.destroy?.();

const sqlglotWheelUrl = lock.packages.sqlglot.file_name;
const sqlglotWheelName = sqlglotWheelUrl.split("/").at(-1) as string;
const sqlglotWheelPath = path.join(CACHE_DIR, sqlglotWheelName);

if (!(await fileExists(sqlglotWheelPath))) {
  const response = await fetch(sqlglotWheelUrl);
  if (!response.ok) {
    throw new Error(`Failed to download wheel: ${response.status}`);
  }
  await writeFile(sqlglotWheelPath, new Uint8Array(await response.arrayBuffer()));
}

lock.packages.sqlglot.file_name = sqlglotWheelName;
await writeFile(LOCK_PATH, JSON.stringify(lock), "utf-8");

console.log("created:", LOCK_PATH);

Runtime service in TypeScript

This code loads Pyodide using the cached files and exposes a wrapper function to transpile SQL.

// src/sqlglot-service.ts
import { access, readFile } from "node:fs/promises";
import path from "node:path";
import { loadPyodide, type PyodideInterface } from "pyodide";

const ARTIFACT_DIR = path.resolve(".pyodide-artifacts");
const CACHE_DIR = path.join(ARTIFACT_DIR, "package-cache");
const LOCK_PATH = path.join(ARTIFACT_DIR, "pyodide-lock.json");

let runtimePromise: Promise<PyodideInterface> | null = null;

async function assertLockfile(): Promise<void> {
  try {
    await access(LOCK_PATH);
  } catch {
    throw new Error(`Missing ${LOCK_PATH}. Run bootstrap script first.`);
  }
}

async function getPyodide(): Promise<PyodideInterface> {
  if (!runtimePromise) {
    runtimePromise = (async () => {
      await assertLockfile();
      const lockContents = await readFile(LOCK_PATH, "utf-8");

      const pyodide = await loadPyodide({
        packageCacheDir: CACHE_DIR,
        lockFileContents: lockContents,
        packageBaseUrl: `file://${CACHE_DIR}/`,
      });

      await pyodide.loadPackage(["sqlglot"]);
      return pyodide;
    })();
  }
  return runtimePromise;
}

export async function transpileSql(
  sql: string,
  readDialect: string,
  writeDialect: string,
): Promise<string> {
  const pyodide = await getPyodide();

  pyodide.globals.set("sql_in", sql);
  pyodide.globals.set("read_dialect", readDialect);
  pyodide.globals.set("write_dialect", writeDialect);

  await pyodide.runPythonAsync(`
import sqlglot
sql_out = sqlglot.transpile(sql_in, read=read_dialect, write=write_dialect)[0]
`);

  const outProxy = pyodide.globals.get("sql_out");
  const out = outProxy.toString();
  outProxy.destroy?.();
  return out;
}

Running BigQuery SQL in DuckDB

You can now execute the transpiled BigQuery SQL locally using DuckDB. First, install the DuckDB Node.js API:

npm i @duckdb/node-api

Then, create an in-memory database, insert mock data, and run the transpiled query:

import { DuckDBInstance } from "@duckdb/node-api";
import { transpileSql } from "./sqlglot-service";

const prodSql = `
SELECT user_id, COUNT(*) AS c
FROM \`analytics.events\`
GROUP BY user_id
ORDER BY c DESC
`;

const localSql = await transpileSql(prodSql, "bigquery", "duckdb");

const instance = await DuckDBInstance.create(":memory:");
const conn = await instance.connect();

await conn.run("CREATE SCHEMA analytics");
await conn.run("CREATE TABLE analytics.events(user_id BIGINT, event_date DATE)");
await conn.run(
  "INSERT INTO analytics.events VALUES (1, '2026-04-01'), (1, '2026-04-02'), (2, '2026-04-02')",
);

const result = await conn.run(localSql);
const rows: string[][] = await result.getRowsJson();

console.log(localSql);
console.log(rows);

conn.closeSync();

Integrating with a test suite

You can place this logic inside your automated tests to verify your queries locally. Here is an example using the native Node.js test runner:

import test from "node:test";
import assert from "node:assert/strict";
import { DuckDBInstance } from "@duckdb/node-api";
import { transpileSql } from "./sqlglot-service";

test("BigQuery SQL can run on DuckDB after transpile", async () => {
  const bigQuerySql = `
SELECT user_id, COUNT(*) AS c
FROM \`analytics.events\`
GROUP BY user_id
ORDER BY c DESC
`;

  const duckdbSql = await transpileSql(bigQuerySql, "bigquery", "duckdb");

  const instance = await DuckDBInstance.create(":memory:");
  const conn = await instance.connect();

  await conn.run("CREATE SCHEMA analytics");
  await conn.run("CREATE TABLE analytics.events(user_id BIGINT, event_date DATE)");
  await conn.run(
    "INSERT INTO analytics.events VALUES (1, '2026-04-01'), (1, '2026-04-02'), (2, '2026-04-02')",
  );

  const result = await conn.run(duckdbSql);
  const rows: string[][] = await result.getRowsJson();

  assert.equal(rows.length, 2);
  assert.equal(rows[0][0], "1");
  assert.equal(rows[0][1], "2");

  conn.closeSync();
});

References

Pyodide usage guide: https://pyodide.org/en/stable/usage/index.html
Pyodide JavaScript API overview: https://pyodide.org/en/stable/usage/api/js-api.html#globalThis.loadPyodide
Micropip API documentation: https://micropip.pyodide.org/en/stable/project/api.html
DuckDB Node API package page: https://www.npmjs.com/package/@duckdb/node-api
sqlglot GitHub repository: https://github.com/tobymao/sqlglot

45deg/sqlglot_on_ts.md

Select an option

No results found