DuckDB

How Reylo types queries on DuckDB

Reylo lets you import .sql files into TypeScript, with every query typed from your live database. This page covers how it types and runs queries on DuckDB: column types, null values, row counts, parameters, files and drivers.

create type mood as enum ('happy', 'sad');

create table teams (
  id integer primary key,
  name varchar not null
);

create table users (
  id integer primary key,
  name varchar not null,
  email varchar unique,
  team_id integer references teams (id),
  github_id bigint,
  mood mood,
  created_at timestamptz not null default now()
);

create table events (
  id bigint primary key,
  user_id integer not null references users (id),
  kind varchar not null,
  tags varchar[],
  meta struct(host varchar, pid integer),
  total decimal(12, 2),
  at_day date
);

Data types

DuckDB's prepared statement reports the exact type of every parameter and column, and .run() returns one value for each type:

select id, github_id, count(*) over () as n
from users

void

{ id: number; github_id: bigint | null; n: bigint }[]

select tags, meta, meta.host as host
from events

void

{
  tags: (string | null)[] | null;
  meta: { host: string | null; pid: number | null } | null;
  host: string | null;
}[]

Files

A query can read Parquet, CSV and JSON files with read_parquet, read_csv and read_json. DuckDB reads a file's columns when it prepares the query, so the files, or a sample with the same columns, must exist wherever the type check runs, at the same paths. A relative path starts at your package.json in the type check, and at your app's working directory when it runs.

select level, count(*) as n
from read_parquet('logs/*.parquet')
group by all

Reylo keeps each query's analysis in .reylo/ until your database's schema changes, and a file's columns aren't part of the schema: after changing them, delete .reylo/ so that the next type check reads the files again. Every column of a file may be NULL, since nothing declares one NOT NULL.

Nullability

Every column is typed | null unless Reylo can tell it never is null. DuckDB's own description of a query doesn't say which columns hold NULL reliably, so Reylo reads your schema and the query:

select id, name, email
from users

void

{ id: number; name: string; email: string | null }[]

select u.name, t.name as team
from users u
left join teams t on t.id = u.team_id

void

{ name: string; team: string | null }[]

select u.name, t.name as team
from users u
left join teams t on t.id = u.team_id
where t.id is not null

void

{ name: string; team: string }[]

select upper(email) as shout,
  coalesce(email, name) as contact,
  length(name) as n,
  id / 2 as half
from users

void

{
  shout: string | null;
  contact: string;
  n: bigint;
  half: number | null;
}[]

select team_id, count(*) as members, max(name) as last
from users
group by team_id

void

{ team_id: number | null; members: bigint; last: string }[]

Cardinality

Reylo infers how many rows a query returns, and types .run()'s result to match:

Parameters

Placeholders

Write parameters as :name, and pass .run() an object with those names, without the colon. You can use DuckDB's $1, ? or ?1 instead, but not beside names; then .run() takes an object keyed by number, { 1: 42 }. DuckDB's own named form, $name, is an error that says to write :name.

What a parameter takes

A parameter accepts the same values .run() returns for its type, so you can pass a row straight into the next query. A BIGINT or wider integer also takes a number or a string of digits, and a STRUCT an object of its fields. A list takes no null element, so remove one before passing a list back in, and a UNION takes a member's value, not null.

A fraction for an integer, such as 1.5 for an INTEGER, makes .run() throw before the query runs, where DuckDB would round it to 2.

Parameters without a type

DuckDB types a parameter from where it stands: compared with a column, written to one, or cast. Anywhere else, such as a bare select :x or id + :n, DuckDB can't type the query at all, and Reylo shows an error at the parameter. Cast it:

select :limit::integer + 1 as next

{ limit: number }

{ next: number }

Parameters that may be null

A parameter accepts null where the query lets it be NULL. Such a parameter is optional in .run(), and leaving it out passes null.

select id, name
from users
where name = coalesce(:name::nullable(varchar), name)

{ name?: string | null }

{ id: number; name: string }[]

insert into users (id, name, email)
values (:id, :name, :email)

{ id: number; name: string; email?: string | null }

void

Database errors

With .try(), an error DuckDB raised comes back as { error }:

Drivers

Reylo decodes its query results itself, so settings on your connection don't affect Reylo's queries.

@duckdb/node-api

Pass a connection. A transaction is the connection's own, around the queries you run on it:

import { DuckDBInstance } from "@duckdb/node-api";
import getUser from "./get_user.sql";

const instance = await DuckDBInstance.create("app.duckdb");
const db = await instance.connect();

const user = await getUser.run(db, { id: 1 });

Limits

What Reylo can't type on DuckDB, and what to do instead: