Athena Query Builder

Fluent, immutable SQL builder for AWS Athena (Presto/Trino-style SQL). Build single-table SELECT, INSERT, UPDATE, and DELETE statements with escaped string literals—no query execution, catalog access, or ORM.

Features

• Fluent chain API — Knex/Lucid-style method chaining; each call returns a new immutable instance
• Unified builder — One AthenaQueryBuilder class for SELECT, INSERT, UPDATE, and DELETE
• Single-table SELECT — select, from, whereEq, whereIn, orderBy, limit
• Single-table INSERT — into, values (single or multiple rows)
• Single-table UPDATE — update, set, whereEq, whereIn
• Single-table DELETE — delete, whereEq, whereIn
• Statement isolation — Mixing methods for different statement kinds on the same builder throws
• Safe literals — String values are escaped and embedded via QuoteString / FormatScalar (no bind parameters)
• whereIn empty array — Renders 1=0 (always false) instead of invalid IN ()
• Identifier validation — Unquoted names limited to alphanumeric, dot, and underscore
• TypeScript — Strict types for columns, sort direction, insert rows, update assignments, and scalar values
• Utilities — QuoteString, AssertIdentifier, and FormatScalar classes under utils/ for reuse

Installation

npm

npm install athena-query-builder

yarn

yarn add athena-query-builder

Usage

SELECT

import { AthenaQueryBuilder } from 'athena-query-builder';

const exampleKeys = ['ex-1', 'ex-2'];

const sql = new AthenaQueryBuilder()
  .select(['example_id', { column: 'example_value', as: 'v' }])
  .from('example_table')
  .whereIn('example_key', exampleKeys)
  .whereEq('example_status', 'active')
  .orderBy('example_id', 'asc')
  .limit(1000)
  .toSql();

console.log(sql);

Example output:

SELECT example_id, example_value AS v
FROM example_table
WHERE example_key IN ('ex-1', 'ex-2') AND example_status = 'active'
ORDER BY example_id ASC
LIMIT 1000

INSERT

import { AthenaQueryBuilder } from 'athena-query-builder';

const sql = new AthenaQueryBuilder()
  .into('example_table')
  .values({ example_id: 'ex-1', example_value: 'hello' })
  .toSql();

console.log(sql);

Example output:

INSERT INTO example_table (example_id, example_value)
VALUES ('ex-1', 'hello')

Multiple rows:

const sql = new AthenaQueryBuilder()
  .into('example_table')
  .values([
    { example_id: 'ex-1', example_value: 'a' },
    { example_id: 'ex-2', example_value: 'b' },
  ])
  .toSql();

INSERT INTO example_table (example_id, example_value)
VALUES ('ex-1', 'a'), ('ex-2', 'b')

UPDATE

import { AthenaQueryBuilder } from 'athena-query-builder';

const sql = new AthenaQueryBuilder()
  .update('example_table')
  .set({ example_value: 'hello', example_count: 1 })
  .whereEq('example_id', 'ex-1')
  .toSql();

console.log(sql);

Example output:

UPDATE example_table
SET example_value = 'hello', example_count = 1
WHERE example_id = 'ex-1'

With whereIn and multiple set() calls:

const sql = new AthenaQueryBuilder()
  .update('example_table')
  .set({ example_status: 'archived' })
  .set({ deleted_at: null })
  .whereIn('example_key', ['ex-1', 'ex-2'])
  .toSql();

UPDATE example_table
SET example_status = 'archived', deleted_at = NULL
WHERE example_key IN ('ex-1', 'ex-2')

DELETE

import { AthenaQueryBuilder } from 'athena-query-builder';

const sql = new AthenaQueryBuilder()
  .delete('example_table')
  .whereEq('example_id', 'ex-1')
  .toSql();

console.log(sql);

Example output:

DELETE FROM example_table
WHERE example_id = 'ex-1'

With whereIn:

const sql = new AthenaQueryBuilder()
  .delete('example_table')
  .whereIn('example_key', ['ex-1', 'ex-2'])
  .toSql();

DELETE FROM example_table
WHERE example_key IN ('ex-1', 'ex-2')

Immutable branching

Reuse a base builder and branch without side effects:

const base = new AthenaQueryBuilder()
  .select(['example_id'])
  .from('example_table');

const forKeyA = base.whereIn('example_key', ['ex-a']);
const forKeyB = base.whereIn('example_key', ['ex-b']);

SQL formatting utilities

import { QuoteString, AssertIdentifier, FormatScalar } from 'athena-query-builder';

new QuoteString().execute("it's");              // "'it''s'"
new AssertIdentifier().execute('example_table'); // 'example_table'
new FormatScalar().execute(42);                  // '42'

Options

AthenaQueryBuilder

SELECT

| Method | Description | |--------|-------------| | select(columns) | SELECT list. Each entry is a column name or { column, as? }. | | from(table) | Single table name (validated identifier). | | whereEq(column, value) | column = literal or column IS NULL when value is null. | | whereIn(column, values) | column IN (...); empty values → 1=0. | | orderBy(column, direction) | Append one ORDER BY entry ('asc' | 'desc'). | | orderBy(entries) | Append multiple { column, direction } entries. | | limit(n) | LIMIT n (n must be a non-negative integer). |

toSql() for SELECT requires both select() and from() to have been called.

INSERT

| Method | Description | |--------|-------------| | into(table) | Target table name (validated identifier). | | values(row) | Append one row (InsertRow). | | values(rows) | Append multiple rows with the same column keys as the first row. |

toSql() for INSERT requires both into() and values() to have been called.

UPDATE

| Method | Description | |--------|-------------| | update(table) | Target table name (validated identifier). | | set(assignments) | SET column assignments (UpdateAssignments). Multiple calls merge; later values win for the same column. null → column = NULL. | | whereEq(column, value) | Same as SELECT (column = literal or IS NULL). | | whereIn(column, values) | Same as SELECT (IN (...); empty → 1=0). |

toSql() for UPDATE requires both update() and set() to have been called. WHERE is optional.

DELETE

| Method | Description | |--------|-------------| | delete(table) | Target table name (validated identifier). | | whereEq(column, value) | Same as SELECT (column = literal or IS NULL). | | whereIn(column, values) | Same as SELECT (IN (...); empty → 1=0). |

toSql() for DELETE requires delete() to have been called. WHERE is optional.

Shared

| Method | Description | |--------|-------------| | toSql() | Build the final SQL string (SELECT, INSERT, UPDATE, or DELETE). | | build() | Alias for toSql(). |

Methods for different statement kinds (SELECT / INSERT / UPDATE / DELETE) cannot be mixed on the same builder instance. whereEq / whereIn are shared by SELECT, UPDATE, and DELETE.

Types

WhereScalar

string | number | boolean | null

Used in WHERE, INSERT, UPDATE / SET, DELETE, and VALUES clauses. Non-finite numbers (NaN, Infinity) are rejected by FormatScalar.

SelectColumn

string | { column: string; as?: string }

A bare column name, or an object with an optional AS alias.

OrderDirection

'asc' | 'desc'

OrderByEntry

{ column: string; direction: OrderDirection }

InsertRow

Record<string, WhereScalar>

Column order follows Object.keys insertion order of the first row passed to values().

UpdateAssignments

Record<string, WhereScalar>

Column order follows Object.keys insertion order of the object passed to set() (merged across multiple calls).

Out of scope (current phase)

• JOIN, WITH, subquery FROM, GROUP BY, HAVING, window functions
• StartQueryExecution, result polling, Glue catalog APIs
• Environment variable reads or query-plan optimization

Requirements

• Node.js >= 20.0.0

License

This project is licensed under the Apache-2.0 License.