From a bare machine to a Keal program talking to a PostgreSQL database it made itself. Every command is given, with what it does and what the screen should show before you go on. Budget half an hour, most of it waiting for compilers.
You need a terminal and an internet connection. The commands are for Linux (Debian, Ubuntu) and macOS; Windows notes are given where things differ — the compiler, the SQL and the migrations work there under Git Bash, the stored functions and the client do not yet.
Where a command begins with sudo, your machine will ask for your password: that is installing system packages, nothing else.
Keal's compiler is built with Rust's toolchain, and this is the only reason you need it. rustup installs it in your home directory, without root:
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh . "$HOME/.cargo/env" cargo --version
cargo 1.8x.x (…)
On Windows, download rustup-init.exe from rustup.rs and choose the GNU toolchain (x86_64-pc-windows-gnu) with MinGW, which is what KealSql was tested with.
Keal is the language KealSql is written in and compiles to. Clone it, build it, and put the binary on your path. The build takes a minute or two.
git clone https://github.com/geneacta/keal cd keal cargo build --release export PATH="$PWD/target/release:$PATH" keal version cd ..
keal 1.2.0
The export PATH line lasts for this terminal only; add it to your shell's startup file (~/.bashrc, ~/.zshrc) to keep it. cargo install --path . inside keal/ is the permanent alternative.
KealSql needs Keal 1.3.0 or later — the release whose loader reads a .kealsql import.
The database itself, its client tools, and — for the stored functions and the client — its development headers.
# Debian, Ubuntu sudo apt install postgresql postgresql-server-dev-all libpq-dev # macOS with Homebrew brew install postgresql@17 brew services start postgresql@17 psql --version pg_config --includedir
psql (PostgreSQL) 17.x /usr/include/postgresql
On Windows, the EDB installer gives you psql, createdb and pg_config; add its bin directory to the path.
PostgreSQL has its own users. On Linux the server is installed with one, postgres, that only the system user of the same name may become; the simplest thing is to make a PostgreSQL user with your own login name, so that psql connects without asking anything:
sudo -u postgres createuser --superuser "$USER" psql -d postgres -c 'select current_user'
current_user -------------- renard (1 row)
On macOS with Homebrew the server already knows your user; skip the first line. On Windows, the installer asked you for the postgres password: set PGUSER=postgres and PGPASSWORD=… in the environment, or write them in %APPDATA%\postgresql\pgpass.conf.
If it does not: peer authentication failed means the PostgreSQL user does not exist yet — the first line failed, read its message. connection refused means the server is not running: sudo systemctl start postgresql on Linux.
Clone it, fetch the piece of Keal it imports (its lexer, pinned to a commit), and build the compiler into a binary called kealsql. Keal's loader will look for that binary by name when a program imports a .kealsql file.
git clone https://github.com/geneacta/kealsql cd kealsql keal fetch keal build src/main.keal -o kealsql export PATH="$PWD:$PATH" kealsql tests/cases/blog.kealsql | head -5 cd ..
CREATE TABLE "user" (
id serial PRIMARY KEY,
name text UNIQUE NOT NULL,
email text UNIQUE NOT NULL,
bio textUntil the binary is on the path, keal src/main.keal file.kealsql does the same thing on Keal's VM, and export KEALSQL=/path/to/kealsql tells the loader where it is.
Optional but reassuring: tests/run.sh runs the whole suite. With PostgreSQL installed it starts a private server in a temporary directory and runs every case on it; the last lines should be ok and skip, never FAIL.
Make a directory for the project and write the schema and its queries. This is the blog the suite uses; read the notes under it.
mkdir myblog cd myblog # then create blog.kealsql with the content below
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)
}enum Status becomes a PostgreSQL enum type. table User { … } becomes a table: id: Id is a serial primary key, name: Slug a unique text — every table has one primary and at most one slug. String? is a column that may be null; String may not.
cascade author: RefId<User> is a foreign key to User's primary, deleted along with the user; editor: RefId<User>? is an optional one, set to null when the user goes.
A func is a query with a name, parameters and a declared result. It reads from from to select, in the order the database evaluates it. p.author.name walks the reference — a join the compiler writes. editor?.name walks an optional one — a left join, and the result is String?. A proc is a change that answers nothing.
The compiler turns the file into SQL: the tables as CREATE statements, then one prepared statement per query. Look at the file it wrote; it is plain SQL you could have typed.
kealsql blog.kealsql > blog.sql grep -c PREPARE blog.sql grep -A5 'func editorOf' blog.sql
5 -- 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
If it does not: an error blog.kealsql:L:C … line names the place and says what to write. The most common one for a first file is a column named in a query that the table does not have.
createdb makes an empty database named blog; loading the compiled file into it makes the tables. Then look at them.
createdb blog psql -d blog -f blog.sql psql -d blog -c '\dt'
List of tables Schema | Name | Type | Owner --------+------+-------+-------- public | post | table | you public | user | table | you (2 rows)
The CREATE statements are permanent. The PREPARE statements are not: they live for the psql session that ran them. That is why the next step loads them again, with --queries, in the session that uses them — and why a program (step 12) prepares its own.
One psql session: load the queries — --queries prints the PREPAREs without the CREATEs, which would fail on tables that exist — insert two users and three posts, and call the queries by name. EXECUTE is how a prepared statement is called; the names are the file's, in snake_case.
kealsql --queries blog.kealsql > queries.sql
psql -d blog
blog=> \i queries.sql
blog=> INSERT INTO "user" (name, email) VALUES ('ada', 'ada@x'), ('bob', 'bob@x');
blog=> INSERT INTO post (author, editor, title, status, created) VALUES
(1, 2, 'Hello', 'Published', now()),
(1, NULL, 'Draft one', 'Draft', now()),
(2, NULL, 'Bob post', 'Published', now());
blog=> EXECUTE by_author('ada');
blog=> EXECUTE editor_of(2);
blog=> EXECUTE drafts;
blog=> \q id | title
----+-------
1 | Hello
(1 row)
name
------
(1 row)
count
-------
1
(1 row)editor_of(2) answers one row holding nothing: post 2 exists and has no editor. That is the left join the ?. in the file asked for. by_author('ada') answers one post, not two: the file says status == Published, and Ada's second post is a draft.
Now the same queries from a program. A Keal program imports the .kealsql file itself; Keal's loader runs kealsql to write a module beside it, .kealsql/blog.client.keal, in which every query is a method on a connection. Write app.keal next to blog.kealsql:
import "./blog.kealsql"
// The database and its tables, made if they are missing; a connection to them.
val db = createBlog("", "blog")
println("drafts: ${db.drafts()}")
for (p in db.byAuthor("ada")) {
println("#${p.id} ${p.title}")
}
val editor = db.editorOf(1)
println("editor of #1: ${editor ?: "none"}")
db.close()createBlog("", "blog"): the first argument is a libpq connection string — empty, it uses the same defaults as psql (your user, the local server); the second is the database's name, made if it does not exist. Here it exists, with rows, so the call only connects.
db.byAuthor("ada") answers a list of records with id and title; db.editorOf(1) answers a String?, which is why ?: "none" is there.
The program links against libpq, PostgreSQL's client library; the two flags say where its header and its library are.
keal build app.keal -I$(pg_config --includedir) -lpq -o app ./app ls .kealsql
drafts: 1 #1 Hello editor of #1: bob blog.client.keal
The .kealsql/ directory holds the generated module. Commit it with the project: a checkout then builds without kealsql installed, and Keal regenerates it whenever blog.kealsql is newer.
If it does not: cannot generate … `kealsql` is not installed — the binary of step 6 is not on the path; export KEALSQL=/path/to/kealsql. libpq-fe.h: No such file — install libpq-dev (Linux) and check pg_config --includedir. connection refused or authentication failed — step 5.
Rename the query drafts to draftCount in blog.kealsql and build the program again, without touching it:
sed -i 's/func drafts()/func draftCount()/' blog.kealsql keal build app.keal -I$(pg_config --includedir) -lpq -o app
error: `BlogDb` has no method `drafts` --> app.keal:6:23
The loader saw that the file was newer, regenerated the module, and the program stopped compiling at the line that used the old name. That is the point of the whole thing: a query the file no longer has is an error in the program, not a failure at run time. Put drafts back, or fix the program.
Add a column to the file — bio: String? is already there; add joined: Date? under it in User — and ask what the live database is missing:
kealsql --migrate blog.kealsql --db blog
ALTER TABLE "user" ADD COLUMN joined date;
Nothing was applied: the migration is printed for you to read. Apply it in one transaction and ask again — the answer must be nothing to do:
kealsql --migrate blog.kealsql --db blog > migration.sql psql -1 -d blog -f migration.sql kealsql --migrate blog.kealsql --db blog
-- nothing to do: the database matches the declaration
A dropped column, a narrowed type or a new NOT NULL would be printed as comments, held back until you pass --destructive — the compiler names what they would cost. A rename is never guessed: write renamed(oldName) newName: Type in the file, and the migration renames.
A stored func is Keal that PostgreSQL runs as a native function. Add one to blog.kealsql and use it in a query:
stored pure func shout(s: String): String { s.toUpper() + "!" }
func shouted(): List<String> {
from(Post).orderBy(id).select(shout(title))
}Then generate the library's source, build it against the server headers, and load the functions with the library's path — --queries, because the tables exist:
kealsql --plkeal build/ blog.kealsql
sh build/build.sh
kealsql --lib "$PWD/build/blog.so" --queries blog.kealsql | psql -d blog
psql -d blog -c "SELECT shout('hello')"shout -------- HELLO! (1 row)
If it does not: postgres.h: No such file — the server headers are missing: postgresql-server-dev-all on Debian and Ubuntu. On Windows this step is not available yet.
Getting started is the same journey in five minutes; the docs hold the design, the grammar with what every construct compiles to, and the README. examples/shop.kealsql is a bigger real file to read next.