Skip to content
huydo862003Public

Repository files navigation

sqlingo

npm version License: MIT SQLGlot

A JavaScript/TypeScript port of SQLGlot which is compatible with browser and Node runtimes.

This package allows you to parse, transpile, optimize, and execute SQL across 32 dialects in JavaScript.

WARNING: This package is still in alpha.

  • Although the SQLGlot tests have all passed, but finding contrived failures may require me to use this package extensively myself.
  • You can also help me report bugs in the Github issue.

NOTICE: AI is not the decision maker, designer or maintainer for this project. It is indeed sometimes used to port straightforward code with less effort. However, the point is to always keep its usage under control & understand what's going on, so I can maintain this project reliably as sqlglot updates.

Features

  • 32 SQL dialects: Postgres, MySQL, BigQuery, Snowflake, DuckDB, ClickHouse, Redshift, Athena, Spark, and many more
  • Some SQLGlot feature set: parsing, transpilation, optimization, column lineage, SQL diffing, and execution
  • Pure JavaScript: no need for WASM or native dependencies
  • TypeScript-first: full type definitions included

Installation

npm install sqlingo luxon

Peer dependency: luxon (^3.7.2) is required for date/time operations.

Quick Start

import { transpile, parseOne, optimize, MappingSchema } from "sqlingo";
import { MySQL } from "sqlingo/mysql";
import { Postgres } from "sqlingo/postgres";

// Transpile between dialects
const [pgSql] = transpile("SELECT DATE_SUB(d, INTERVAL 1 DAY) FROM t", {
  read: MySQL,
  write: Postgres,
});
console.log(pgSql);
// SELECT d - INTERVAL '1 DAY' FROM t

// Optimize a query with schema information
const schema = new MappingSchema({ t: { a: "int", b: "int" } });
const optimized = optimize(
  parseOne("SELECT * FROM t WHERE 1 = 1 AND a > 2"),
  { schema },
);
console.log(optimized.sql());
// SELECT "t"."a" AS "a", "t"."b" AS "b" FROM "t" AS "t" WHERE "t"."a" > 2

Core Usage

Parsing

Parse SQL strings into expression trees (AST).

import { parse, parseOne } from "sqlingo";

// Parse multiple statements
const expressions = parse("SELECT 1; SELECT 2");

// Parse a single statement
const ast = parseOne("SELECT a, b FROM t WHERE a > 1");

Transpiling

Convert SQL between different dialects.

import { transpile } from "sqlingo";
import { DuckDB } from "sqlingo/duckdb";
import { Hive } from "sqlingo/hive";

const [result] = transpile("SELECT EPOCH_MS(1618088028295)", {
  read: DuckDB,
  write: Hive,
});
console.log(result);
// SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))

Tokenizing

Extract tokens from a SQL string for lower-level analysis.

import { tokenize } from "sqlingo";
import { Postgres } from "sqlingo/postgres";

const tokens = tokenize("SELECT 1", Postgres);

SQL Builder

Build queries programmatically using a fluent API.

import { select, condition } from "sqlingo";
import { MySQL } from "sqlingo/mysql";

const query = select(["a", "b"]).from("t").where(condition("a > 1")).limit(10);

console.log(query.sql({ dialect: MySQL }));
// SELECT a, b FROM t WHERE a > 1 LIMIT 10

Optimization & Analysis

Optimization

Simplify and normalize queries based on schema information.

import { optimize, parseOne, MappingSchema } from "sqlingo";

const schema = new MappingSchema({ t: { a: "int" } });
const optimized = optimize(parseOne("SELECT * FROM t"), { schema });

console.log(optimized.sql());
// SELECT "t"."a" AS "a" FROM "t" AS "t"

Column Lineage

Trace the origin of columns through subqueries and joins.

import { lineage } from "sqlingo";

const node = lineage("b", "SELECT a AS b FROM (SELECT x AS a FROM y)");
console.log(node.name);
// "b"
console.log(node.downstream[0].name);
// "_0.a"

Registering a Custom Dialect

You can extend the library by registering custom dialects or overriding existing behavior.

import { Dialect, Generator, ArrayAggExpr, transpile } from "sqlingo";

class MyGenerator extends Generator {
  static get ORIGINAL_TRANSFORMS() {
    return new Map([
      ...Generator.TRANSFORMS,
      // Rewrite ARRAY_AGG as GROUP_CONCAT in this dialect
      [ArrayAggExpr, function (this: Generator, e: ArrayAggExpr) {
        return `GROUP_CONCAT(${this.sql(e, "this")})`;
      }],
    ]);
  }
}

class MyDialect extends Dialect {
  static DIALECT_NAME = "my_dialect";
  static Generator = MyGenerator;
}

Dialect.register(MyDialect);

const [result] = transpile("SELECT ARRAY_AGG(x) FROM t", { write: MyDialect });
console.log(result);
// SELECT GROUP_CONCAT(x) FROM t

Supported Dialects

Athena, BigQuery, ClickHouse, Databricks, Doris, Dremio, Drill, Druid, DuckDB, Dune, Exasol, Fabric, Hive, Materialize, MySQL, Oracle, Postgres, Presto, PRQL, Redshift, RisingWave, SingleStore, Snowflake, Solr, Spark, Spark2, SQLite, StarRocks, Tableau, Teradata, Trino, TSQL

SQLGlot Compatibility

This package tracks SQLGlot v30.5.0 (commit 64d385f). The API surface mirrors SQLGlot's Python API, adapted to TypeScript conventions. See CONVENTION.md for details.

License

MIT. See LICENSE.

Based on SQLGlot by Toby Mao (MIT). See COPYRIGHT_NOTICE.