<!-- kealsql-band:start --> <p align="right"> <a href="https://github.com/geneacta/kealsql/releases"><img alt="version" src="https://img.shields.io/badge/version-0.1.0-blue?style=flat&labelColor=2b2b2b"></a> <a href="https://github.com/geneacta/kealsql/tree/main/src"><img alt="written in Keal" src="https://img.shields.io/badge/written%20in%20Keal-72%25-blue?style=flat&labelColor=2b2b2b"></a> <a href="https://github.com/geneacta/keal/releases/tag/v1.3.0"><img alt="Keal" src="https://img.shields.io/badge/Keal-1.3.0-orange?style=flat&labelColor=2b2b2b"></a> </p> <!-- kealsql-band:end -->

<p align="center"><img src="site/assets/k.png" alt="" width="120"></p>

geneacta.github.io/kealsql · getting started · docs · the Keal language

KealSql

A Keal-shaped language over PostgreSQL. A .kealsql file declares a schema and queries in the syntax of Keal, and the compiler — itself a Keal program — turns them into plain PostgreSQL SQL, ahead of time, type-checked against the schema.

table User {
    id:    Id
    name:  Slug
    email: String?
}

table Post {
    id:      Id
    cascade author: RefId<User>
    editor:  RefId<User>?
    title:   String
}

func byAuthor(name: String): List<(Int, String)> {
    from(Post as p)
        .where(p.author.name == name)
        .select(p.id, p.title)
}

func editorOf(post: Int): String? {
    from(Post).where(id == post).select(editor?.name).first()
}

$ keal src/main.keal blog.kealsql
CREATE TABLE "user" (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL,
    email text
);
...
-- func editorOf(post: Int): String?
PREPARE editor_of(integer) AS
SELECT editor.name
FROM post
LEFT JOIN "user" AS editor ON post.editor = editor.id
WHERE post.id = $1
LIMIT 1;

The ?. became a LEFT JOIN, and the result type String? says a post with no editor answers null rather than vanishing. That is the point of the project: Keal's null safety, closed when, and records, applied to a schema — so that a query which reads a column the schema does not have, or puts NULL where the schema forbids it, is a compile error, not a surprise at run time.

It is not a fork of PostgreSQL. The output is SQL for an unmodified server; nothing runs but the planner PostgreSQL already has.

• DESIGN.md — the decisions and the reasons: why a transpiler, Bool3, Id / Slug, references, migrations.
• GRAMMAR.md — the syntax, the well-formedness rules, and what each construct compiles to.
• bench/README.md — what it costs: the client against psql and raw libpq, and Keal inside the server against PL/pgSQL, SQL and C; bench/run.sh reproduces it.
• examples/shop.kealsql — a shop: six tables, two enums, twenty-odd queries and mutations, as an application would ship them; shop.sql is what they compile to and shop.exec.sql runs them on data.

Running it

KealSql needs the Keal toolchain on the path, or beside the repository at ../keal — Keal 1.3.0 or later: runCommand with a standard input, Nothing in the C backend, the public lexer, the entry points and string ABI a host library uses, and a loader that reads a .kealsql import.

keal src/main.keal file.kealsql                          # print the SQL
keal src/main.keal --migrate file.kealsql --db mydb      # the migration from a live database to the file
keal src/main.keal --migrate file.kealsql --db mydb --destructive
keal src/main.keal --plkeal build/ file.kealsql          # the stored functions as a Keal program + build.sh
keal src/main.keal --lib build/file.so file.kealsql      # the SQL, CREATE FUNCTION naming that library
keal src/main.keal --client app/ file.kealsql            # the queries as a Keal module over libpq
tests/run.sh                                              # the suite: every case, byte for byte

From a program, the same queries are methods on a connection. A Keal program imports the .kealsql itself — Keal's loader (1.3.0 or later) has kealsql write .kealsql/blog.client.keal beside it and reads that, regenerating when the file changes — and links with libpq:

import "./blog.kealsql"

val db = createBlog("", "blog")          // the database and its schema, made if missing
for (p in db.byAuthor("ada")) { println("#${p.id} ${p.title}") }
val editor = db.editorOf(1)              // String?
db.close()

connectBlog(conninfo) opens an existing one. keal build app.keal -I$(pg_config --includedir) -lpq builds it; kealsql is keal build src/main.keal -o kealsql put on the path, or named by KEALSQL. Without the loader, kealsql --client DIR blog.kealsql writes the same module to import by its own path.

--migrate reads the database through psql (--db is its -d; the PG* environment and ~/.pgpass work as for psql) and prints the statements that bring it to the file, to review. Destructive ones — drops, narrowed types, a new NOT NULL — are held back as comments, each with what it would cost, until --destructive. A rename is written where it happens, renamed(bio) about: String? or renamed(Post) table Article, because a diff cannot tell a rename from a drop and an add.

A stored func is Keal that runs inside PostgreSQL, as a LANGUAGE C function — the fastest procedural language the server has:

stored pure func slugify(s: String): String { ...ordinary Keal... }

func slugs(): List<(String, String)> {
    from(User).select(name, slugify(name))
}

A trigger is the same, on every row written — row and old are the table's record, a before body answers the row to store, a throw refuses the write:

trigger normalizeSku on Product before insert {
    return row.with(sku = row.sku.toUpper())
}

--plkeal writes the Keal program and a build.sh (Keal to C, then the C compiler against the server headers); the file's SQL carries the CREATE FUNCTIONs and CREATE TRIGGERs. A panic inside becomes a SQL error, and the backend lives on. Values are Int, Float, Bool, String; the functions are STRICT, so a null answers null without a call.

Inside a stored function, the file's own queries are Keal functions with Keal results — userNamed(name) answers a User?, a record; posts(id) a list of rows with fields named after the columns — and a query that fails is a Keal exception, rolled back, catchable with try:

stored func totalScore(name: String): Int {
    val u = userNamed(name)
    if (u == null) { return 0 }
    var sum = 0
    for (p in posts(u.id)) { sum += p.score ?: 0 }
    return sum
}

On Windows (Git Bash), the compiler, the SQL and --migrate work — the suite runs its private PostgreSQL over TCP there — but the stored functions and the client are not built yet: an extension is a .dll linking postgres.lib, and build.sh does not know that shape.

When PostgreSQL's initdb is on the machine, the suite also starts a private server in a temporary directory — no root, no configuration — loads every case's SQL into a fresh database, runs the *.exec.sql beside it in the same session, and compares the rows to *.exec.out; each tests/migrations/*/ is built from before.kealsql, migrated to after.kealsql, applied in one transaction, and diffed again until it settles; each tests/plkeal/*.kealsql has its library built (server headers and a C compiler needed) and loaded, and its functions run. Without initdb it says so and compares the SQL only.

The compiler runs on Keal's VM as written, and keal build src/main.keal -o kealsql compiles it to a native binary; the suite builds that binary and holds it to the same bytes as the VM on every case. The lexer is Keal's own, imported from the keal dependency pinned in keal.toml; keal fetch puts it under .keal/deps/ (the suite runs it when missing).

Layout

Status

v1 covers the schema (schema, table, enum, keys, references, on delete, defaults, named checks, indexes, arrays, Range<T> with noOverlap and contiguous, Decimal, Timestamptz, Json and the other common types), the DDL, and queries: from / where / unless / join / leftJoin / orderBy / groupBy / distinct / limit / offset, fullJoin / crossJoin, union / intersect / except, the terminals select / count / exists with first / single, subqueries through val-bound fragments and in, views and materialized views, rows as records with traits and methods, recursive common table expressions, window functions, text, date, cast and json functions, the aggregates, insert of several rows with onConflict, insertInto from a query, update / delete through a join, sql("...") as the typed escape hatch, when, ?:, the eight connectives with Kleene's tables on Bool3, and reference paths as implicit joins; the migration diff against a live database, with renames declared and destructive steps held back; and plkeal, stored functions (scalar or SETOF) and triggers in Keal compiled to LANGUAGE C, calling the file's queries through SPI with typed results — all of it compiled natively as well as run on the VM; and the client, the same queries as typed methods on a libpq connection from a Keal program.

Licensed under Apache-2.0, like Keal.