Ten minutes from an empty PostgreSQL to a typed schema, its queries prepared, an application calling them, and a migration when the file changes. Every command here is the one the test suite runs.
Keal, the toolchain — geneacta.github.io/keal has the three commands; KealSql wants Keal 1.3.0 or later — the release whose loader reads a .kealsql import.
PostgreSQL 14 or later with its client tools on the path: psql, createdb, pg_config. The stored functions need the server headers and a C compiler (postgresql-server-dev-NN on Debian and Ubuntu); the client needs libpq's headers (libpq-dev).
And KealSql itself:
git clone https://github.com/geneacta/kealsql cd kealsql keal fetch tests/run.sh
keal fetch fetches Keal's own lexer, which KealSql imports from the commit pinned in keal.toml. tests/run.sh runs the suite: if PostgreSQL is on the machine it starts a private server in a temporary directory — no root, no configuration — and runs every case on it.
A .kealsql file holds a schema and the queries that go with it. This is the blog the suite uses:
enum Status { Draft, Published }
table User {
id: Id
name: Slug
unique email: String
bio: String?
}
table Post {
id: Id
cascade author: RefId<User>
editor: RefId<User>?
title: String
status: Status
created: Timestamp
// A method on the row, for the programs that read it.
func headline(): String { "${this.title} [${this.status}]" }
}
func byAuthor(name: String): List<(Int, String)> {
from(Post as p)
.where(p.author.name == name)
.where(status == Published)
.orderBy(created.desc)
.select(p.id, p.title)
}
func editorOf(post: Int): String? {
from(Post).where(id == post).select(editor?.name).first()
}
func drafts(): Int {
from(Post).where(status == Draft).count()
}
func publish(post: Int): Post {
update(Post).where(id == post).set(status = Published)
}
proc forget(user: RefId<User>) {
delete(Post).where(author == user)
}Three things to notice. Id is a serial primary key and Slug a unique, non-null text — the two keys a table has, each once. RefId<User> is a reference, and RefId<User>? an optional one, which says what happens on delete (SET NULL) and how a path through it reads (?., a left join). And a query starts at from and ends at select: the evaluation order, so that at select the checker already knows every column.
The compiler prints SQL — the schema as CREATE statements, then one PREPARE per func or proc:
keal src/main.keal blog.kealsql > blog.sql
CREATE TABLE "user" ( id serial PRIMARY KEY, name text UNIQUE NOT NULL, email text UNIQUE NOT NULL, bio text ); CREATE TABLE post ( id serial PRIMARY KEY, author integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, editor integer REFERENCES "user"(id) ON DELETE SET NULL, title text NOT NULL, status status NOT NULL, created timestamp NOT NULL ); -- func byAuthor(name: String): List<(Int, String)> PREPARE by_author(text) AS SELECT p.id, p.title FROM post AS p JOIN "user" AS author ON p.author = author.id WHERE author.name = $1 AND p.status = 'Published' ORDER BY p.created DESC; -- 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 path through editor?. became a LEFT JOIN, and the declared result String? says a post without an editor answers null rather than vanishing. That is what the checker holds every query to.
createdb makes an empty database; the compiled file makes everything in it:
createdb blog psql -d blog -f blog.sql
The CREATE statements run once and stay. The PREPARE statements live for the session that runs them — psql here — so loading the file in a later session prepares them again, and the CREATEs would then fail on tables that exist. For the first time this is exactly right; afterwards, --migrate below is the way the schema changes, and the client prepares its own statements.
In the same psql session, the queries are prepared statements, called by name with their parameters in order:
psql -d blog -f blog.sql
blog=> EXECUTE by_author('ada');
blog=> EXECUTE editor_of(2);id|title 1|Hello (1 row) name bob (1 row) name (1 row)
editor_of(2) answers one row holding null: the post exists, its editor does not — the left join the ?. asked for. Names are the file's, in snake_case.
A Keal program imports the .kealsql itself. Keal's loader has kealsql write a module beside it — .kealsql/blog.client.keal, regenerated whenever the file is newer — in which every query is a method on a connection, with the same types: rows are records, first() answers a T?, a failing query is an exception with the query's name. Rename a column in the file and the program stops compiling:
// An application over tests/cases/blog.kealsql. The import is the .kealsql
// itself: Keal's loader has the compiler write .kealsql/blog.client.keal and
// reads that. The suite builds it with libpq, runs it against a fresh
// database holding blog.sql, and holds its output to blog_app.out.
import "./blog.kealsql"
// A database made from the code: missing, then empty, then there.
val made = createBlog("", "client_blog_made")
println("made: ${made.drafts()} draft(s) in a new database")
made.close()
val again = createBlog("", "client_blog_made") // already there: nothing to do
println("again: ${again.drafts()} draft(s)")
again.close()
val db = connectBlog("") // PG* in the environment says where
proc show(label: String, posts: List<ByAuthorRow>) {
println("${label}: ${posts.size} post(s)")
for (p in posts) { println(" #${p.id} ${p.title}") }
}
db.begin()
show("ada before", db.byAuthor("ada"))
println("drafts: ${db.drafts()}")
val published = db.publish(2)
println("published: ${published.title} (${published.status})")
println("headline: ${published.headline()}")
println("drafts now: ${db.drafts()}")
show("ada after", db.byAuthor("ada"))
val editor = db.editorOf(1)
println("editor of #1: ${editor ?: "none"}")
println("editor of #2: ${db.editorOf(2) ?: "none"}")
db.rollback()
println("drafts after rollback: ${db.drafts()}")
try {
db.publish(999)
println("unreachable")
} catch (e) {
println("caught: ${e}")
}
db.forget(2)
println("posts left: ${db.drafts() + db.byAuthor("ada").size}")
db.close()made: 0 draft(s) in a new database again: 0 draft(s) ada before: 1 post(s) #1 Hello drafts: 1 published: Draft one (Published) headline: Draft one [Published] drafts now: 0 ada after: 2 post(s) #2 Draft one #1 Hello editor of #1: bob editor of #2: none drafts after rollback: 1 caught: publish: expected one row, got 0 posts left: 2
keal build src/main.keal -o kealsql # once: the compiler the import runs export KEALSQL=$PWD/kealsql keal build app.keal -I$(pg_config --includedir) -lpq -o app PGDATABASE=blog ./app
createBlog(conninfo, dbname) makes the database when it is missing and its schema when it holds none of the tables; connectBlog(conninfo) opens one that exists. The connection string is libpq's; an empty one leaves everything to the PG* environment and ~/.pgpass. Build the program against libpq, with kealsql on the path (or named by KEALSQL):
Change the file — say, a bio: String? on User, and Post renamed to Article — and ask for the difference with the live database:
keal src/main.keal --migrate blog.kealsql --db blog > migration.sql cat migration.sql psql -1 -d blog -f migration.sql keal src/main.keal --migrate blog.kealsql --db blog # -- nothing to do: the database matches the declaration
ALTER TABLE post RENAME TO article; ALTER TABLE "user" ADD COLUMN bio text; -- DESTRUCTIVE, held back (pass --destructive to emit it): every row of `legacy` is lost -- DROP TABLE legacy; -- 1 statement held back
The migration is printed for you to read, never applied by the compiler. Additive steps come as they are. Destructive ones — a dropped column, a narrowed type, a new NOT NULL — are held back as comments naming what they would cost, until --destructive. A rename is never guessed: renamed(old) in the file says so, and once the database is renamed the annotation is a note you may leave in place.
Apply it in one transaction, and ask again: the answer must be nothing to do.
A stored func is Keal that PostgreSQL runs as a LANGUAGE C function; a trigger is the same on every row written. Their bodies may call the file's queries, typed, through SPI:
// A URL-safe form of a name: letters and digits, one dash between runs.
stored pure func slugify(s: String): String {
var out = ""
var dash = false
for (c in s.toLower()) {
if ((c >= "a" and c <= "z") or (c >= "0" and c <= "9")) {
out += c
dash = false
} else if (not dash and out != "") {
out += "-"
dash = true
}
}
if (out.endsWith("-")) { out = out.take(out.length - 1) }
return out
}
trigger normalizeSku on Product before insert {
return row.with(sku = row.sku.toUpper(), note = row.note ?: "new")
}keal src/main.keal --plkeal build/ blog.kealsql sh build/build.sh keal src/main.keal --lib $PWD/build/blog.so blog.kealsql | psql -d blog
--plkeal writes a Keal program and a build.sh that turns it into a shared library against the server headers; the file's SQL then carries the CREATE FUNCTIONs, naming the library with --lib:
The docs: the design and its reasons, the grammar with what every construct compiles to, and the README's map of the repository. examples/shop.kealsql is a real file — six tables, twenty-odd queries — that the suite compiles, loads and runs on data.