Writing queries

Queries stay SQL. A file under tables/{database}/{table}/{name}.sql becomes one exported function, and its parameters are typed from the SQL itself.

Placeholders use {{name}} syntax.

Inferred parameter types

If you write nothing else, the type is inferred from the surrounding SQL:

WHERE status = '{{status}}'    -- quoted           -> string
LIMIT {{rowLimit}}             -- LIMIT / OFFSET   -> positiveInt
WHERE price >= {{minPrice}}    -- comparison       -> number

Anything the parser cannot place falls back to string.

The placeholder name is free. Scaffolded SQL uses {{rowLimit}} and {{skip}} rather than {{limit}} and {{offset}}, because SQL formatters otherwise mistake the placeholder for the keyword it follows.

Declared parameter types

-- @param annotations take priority over inference and add runtime validation, which runs before the query is submitted rather than after Athena has billed for the scan:

-- @param status enum('active','pending','done')
-- @param rowLimit positiveInt
-- @param startDate isoDate
SELECT *
FROM events
WHERE status = '{{status}}'
  AND created_at >= '{{startDate}}'
LIMIT {{rowLimit}}

That generates:

export interface DefaultParams {
  status: 'active' | 'pending' | 'done';
  rowLimit: number;               // validated > 0
  startDate: string;              // validated YYYY-MM-DD
}

Available annotations

Annotation	Accepts
string	any string, with a SQL injection check
number	a finite number
boolean	true / false
positiveInt	an integer greater than zero
isoDate	YYYY-MM-DD
isoTimestamp	ISO 8601
identifier	a SQL identifier
uuid	an RFC 4122 UUID
s3Path	s3://bucket/path
enum('a','b','c')	one of the listed values, and the generated type is the union

A worked example

The example project in this repository has a query with an inferred parameter and a partition predicate:

-- Complex Glue types: arrays, maps and structs come back parsed, recursively.
--
-- Note which columns are nullable. Athena guarantees NOT NULL only for
-- partition keys, so `dt` is the one column typed without `| null`.
--
-- '{{dt}}'       → string  (quoted)
-- {{rowLimit}}   → number  (LIMIT keyword)

SELECT order_id, placed_at, tags, metadata, address, items, dt
FROM orders
WHERE dt = '{{dt}}'
ORDER BY placed_at DESC
LIMIT {{rowLimit}}

Codegen turns it into this, with schema.string and schema.positiveInt enforcing the annotations at call time:

// Auto-generated by aathena

// Source: tables/sampledb/orders/detail.sql

import { createQuery, schema } from 'aathena/runtime';
import type { Orders } from '../../../types/sampledb/orders';

export interface DetailParams {
  dt: string;
  rowLimit: number;
}

const schemaDef = {
  dt: schema.string,
  rowLimit: schema.positiveInt,
};

export const detail = createQuery<Orders, DetailParams>(
  'tables/sampledb/orders/detail.sql',
  schemaDef,
);

Where files live

project/
├── aathena.config.json        # project root marker, written by `init`
├── tables/                    # the SQL you edit
│   └── sampledb/              # database
│       └── events/            # table
│           ├── default.sql
│           └── daily.sql
├── generated/                 # codegen output, committed by default
│   ├── index.ts               # barrel, re-exports every query
│   ├── types/                 # one file per table, mirroring Glue
│   └── queries/               # one file per SQL file
└── src/
    └── main.ts                # runnable example, written by `init`

Nested grouping under a table works too: events/cart/add.sql is fine, since codegen walks the whole tree under tables/.

Next

• Running queries - calling what you just generated