db/sql

A query is a string, so a typo in it is found by the database, at run time, on the one code path nobody tested. This package reads the string instead: it lexes and parses SQL for one of four dialects, and when you hand it your schema it also resolves every table and column, names the ones that do not exist, and tells you the type of every placeholder and every column that comes back. Nothing here connects to a database; it is all static, so it belongs in a test or in a build step.

It is the same idea as sqlc in Go, with the schema taken from the SQL you already write: the CREATE TABLE statements in your migrations are the catalog, so there is no second file to keep in step.

Start here: one walk from a schema to checked queries

The six sections after this one take the package apart piece by piece. This one puts it together, in the order you would actually meet it on a project that already has a database and a pile of query strings. The program below is the whole arc, and you can run it as it stands - no server, no connection, nothing to set up.

import db.sql
import str

mut g_schema := sql.Catalog {}

func str_of(n: int): str:
    ret str.FromInt(n as i64)
end

func step(n: int, title: str):
    println ""
    println "step " + str_of(n) + ": " + title
end

func try_query(q: str):
    mut r := sql.Check(q, g_schema)
    if r.ok and r.warnings == 0:
        println "  ok   " + q
    else:
        println "  bad  " + q
        mut i := 0
        until i < r.items.len():
            d := r.items.get(i)
            println "       " + d.code + " " + d.message
            if not str.IsEmpty(d.help):
                println "       " + d.help
            end
            i = i + 1
        end
    end
    sql.FreeReport(r)
end

func main:
    step(1, "the schema you already have")
    g_schema = sql.NewCatalog(sql.DialectOf(sql.SQLITE))
    defer sql.FreeCatalog(g_schema)
    mut ddl := sql.Apply(g_schema, """
        CREATE TABLE authors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            country TEXT
        );
        CREATE TABLE books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            author_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            year INTEGER,
            price REAL
        );
    """)
    println "  loaded " + str_of(sql.TableCount(g_schema)) + " tables, " + str_of(sql.ColumnCount(g_schema, "books")) + " columns on books"
    sql.FreeReport(ddl)

    step(2, "the queries you already have")
    try_query("SELECT id, titles FROM books WHERE author_id = ?")
    try_query("SELECT b.title, a.nationality FROM books b JOIN authors a ON a.id = b.author_id")
    try_query("INSERT INTO books (title) VALUES (?)")
    try_query("SELECT id FROM books WHERE year = 'recent'")
    try_query("DELETE FROM books")

    step(3, "the same five, put right")
    try_query("SELECT id, title FROM books WHERE author_id = ?")
    try_query("SELECT b.title, a.country FROM books b JOIN authors a ON a.id = b.author_id")
    try_query("INSERT INTO books (author_id, title) VALUES (?, ?)")
    try_query("SELECT id FROM books WHERE year = 2024")
    try_query("DELETE FROM books WHERE id = ?")

    step(4, "what a query takes and gives back")
    mut a := sql.Analyze("SELECT books.title, books.price, authors.country FROM books JOIN authors ON authors.id = books.author_id WHERE books.year > ?", g_schema)
    println "  it is a " + sql.StmtKindName(a.kind) + " on " + a.table
    p := a.params.get(0)
    println "  takes " + p.column + " as " + sql.SalamType(p.kind, p.not_null)
    mut i := 0
    until i < a.results.len():
        c := a.results.get(i)
        println "  gives " + c.name + " as " + sql.SalamType(c.kind, not c.nullable)
        i = i + 1
    end
    sql.FreeAnalysis(a)

    step(5, "the values go in safely")
    mut args := sql.NewArgs()
    sql.AddText(args, "O'Brien")
    sql.AddInt(args, 1988 as i64)
    mut rep := sql.Report {}
    filled := sql.Fill("INSERT INTO books (title, year) VALUES (?, ?)", args, sql.DialectOf(sql.SQLITE), rep)
    println "  " + filled
    sql.FreeReport(rep)
    sql.FreeArgs(args)

    step(6, "or let it write the code for you")
    mut gen := sql.Report {}
    mut queries := sql.ParseQueries("""
-- name: BooksByAuthor :many
SELECT id, title, year FROM books WHERE author_id = ?;
""", gen)
    code := sql.Generate("store", queries, g_schema, gen)
    println code
    sql.FreeQueries(queries)
    sql.FreeReport(gen)
    ret 0
end

Step 1 is the only setup there is. You do not write a schema description for this package; you hand it the CREATE TABLE statements you already have. Point Apply at your migration files in order and the catalog is the schema your program really runs against.

Step 2 is why you came. Five queries of the kind that live in every codebase, and five different ways of being wrong: a column name remembered in the plural, a column that belongs to the other table in the join, an INSERT that leaves out a NOT NULL column with no default, a number compared against a quoted word, and a DELETE with no WHERE. Each one is found without a database, and each one names what to do about it. The last is a warning rather than an error, because sometimes clearing a table is the intent - the point is that you decide, in a review, rather than at two in the morning.

Step 3 is the same five, put right, and it is worth running your own queries through this pair of steps once before reading any further. The quickest way is the command: salam sql check src --schema=db reads the SQL out of your .salam files as well as your .sql ones.

Step 4 is where it stops being a linter. Once the names resolve, the types follow: the placeholder is an option.Option<i64> because year can be null, title is a plain str because the column is NOT NULL. That is the signature your code should have, derived from the schema instead of remembered.

Step 5 is the one that prevents the oldest bug in the book. Fill writes the values into the query with the quoting the engine wants - note what happens to the apostrophe in O'Brien - and because the query was lexed first, a ? that lives inside a string is left alone.

Step 6 is the part you can stop writing by hand. Annotate a query with a name and a kind and the generated function has the parameters and the row struct that step 4 worked out. Run it with salam sql generate queries --schema=db --output=gen/store.salam.

Two things to carry away. The schema is the DDL you already have, so nothing can drift out of step with it. And every one of these checks happens before the program runs, which is the only time a query mistake is cheap.

Reading a query without a database

Lint parses one statement, or a whole script of them separated by ;, and returns a Report. r.ok is false as soon as there is an error; ReportText renders the whole list the way a compiler would, with the line, the column, the message and a help line. Most of the dialect rules are in here: the same string is fine for SQLite and wrong for PostgreSQL, and the report says which part of it to change.

import db.sql

func report(title: str, query: str, d: sql.Dialect):
    mut r := sql.Lint(query, d)
    if r.ok:
        println title, "ok"
    else:
        println title, sql.ReportText(r)
    end
    sql.FreeReport(r)
end

func main:
    sqlite := sql.DialectOf(sql.SQLITE)
    mysql := sql.DialectOf(sql.MYSQL)
    postgres := sql.DialectOf(sql.POSTGRES)

    report("plain select: ", "SELECT id, name FROM users WHERE id = ?", sqlite)
    report("typo:         ", "SELECT id FROM users WHERE", sqlite)
    report("wrong marker: ", "SELECT id FROM users WHERE id = ?", postgres)
    report("mysql only:   ", "SELECT id FROM users WHERE name ILIKE 'a%'", mysql)
    report("sqlite only:  ", "CREATE TABLE t (id INT AUTO_INCREMENT)", sqlite)

    println "placeholders: ", sql.ParamCount("UPDATE users SET name = ?, age = ? WHERE id = ?", sqlite)
    ret 0
end

The compiler ships the same checks as a command, so a whole project can be checked without writing a program for it: salam sql check src --schema=db reads the .sql files under src, and the SQL string literals out of its .salam files, and reports them with the position in the Salam source. Without --dialect each Salam file is read with the engine it imports, so a tree that mixes db/sqlite and db/mysql is checked correctly with no flags at all. A query assembled from pieces - glued with + or .concat, or any literal whose only fault is that it stops in the middle - is a fragment rather than a statement, so it is counted and skipped instead of being reported as a syntax error. The count is in the summary, so nothing is checked silently; a fragment is also the reason to bind values as placeholders instead of pasting them in.

Every diagnostic carries a code. SQL0xx is lexical, SQL1xx is syntax, SQL2xx is a dialect rule, SQL3xx is a name or a count that does not match the schema. Match on the code when you want to allow one particular thing; never match on the message.

Your migrations are the schema

Apply runs DDL into a Catalog: CREATE TABLE adds a table with its columns, their types, their NOT NULL and PRIMARY KEY, ALTER TABLE adds, drops and renames, DROP removes, and CREATE VIEW records the view's columns. Feed it the same strings you give db.AddMigration and the catalog is the schema the program really has.

import db.sql

mut g_schema := sql.Catalog {}

func check(query: str):
    mut r := sql.Check(query, g_schema)
    if r.ok and r.warnings == 0:
        println "ok   ", query
    else:
        println "bad  ", query
        println sql.ReportText(r)
    end
    sql.FreeReport(r)
end

func main:
    g_schema = sql.NewCatalog(sql.DialectOf(sql.SQLITE))
    defer sql.FreeCatalog(g_schema)

    mut ddl := sql.Apply(g_schema, """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            name TEXT
        );
        CREATE TABLE orders (
            id INTEGER PRIMARY KEY,
            user_id INTEGER NOT NULL,
            total REAL NOT NULL
        );
    """)
    println "schema loaded:", ddl.ok, " tables:", sql.TableCount(g_schema)
    sql.FreeReport(ddl)

    check("SELECT u.email, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = ?")
    check("SELECT id, mail FROM users")
    check("SELECT id FROM user")
    check("INSERT INTO users (name) VALUES (?)")
    check("DELETE FROM orders")
    ret 0
end

With a catalog, Check resolves names: an unknown column or table is an error with the closest real name offered, a column that two joined tables both have is an error until you qualify it, an INSERT is checked against the column list and against the columns that are NOT NULL with no default, and an UPDATE or DELETE with no WHERE is a warning, because that is almost never what was meant.

It also reads the types it has: comparing a numeric column with a quoted value that is not a number is a warning on SQLite and MySQL, where it silently never matches or silently becomes zero, and an error on PostgreSQL, which refuses it; a column in the result list of a GROUP BY query that is neither grouped nor aggregated is an error on PostgreSQL and a warning elsewhere, with the same exception those engines make - grouping by a table's whole primary key determines the rest of that table's columns, so they are allowed unqualified; and USING (col) is checked on both sides of the join. A subquery that returns the wrong number of columns for the comparison it sits in, an ORDER BY 3 when the statement returns two columns, a LIMIT that is not a number, an aggregate in WHERE rather than HAVING, the same column assigned twice in one SET, and two result columns given the same alias are all reported too.

Putting the values in safely

The three SQL drivers do not all offer prepared statements - only db/sqlite has Prepare and Bind* - so code that has to run on MySQL or PostgreSQL ends up gluing values into the SQL by hand, which is where quoting mistakes become injections. Fill does that one job properly: it lexes the query, so it knows which ? is a placeholder and which one is a character inside a string, checks that the number of values matches, and writes each value the way the dialect wants it.

import db.sql

func show(query: str, a: sql.Args, d: sql.Dialect):
    mut r := sql.Report {}
    out := sql.Fill(query, a, d, r)
    if r.ok:
        println out
    else:
        println sql.ReportText(r)
    end
    sql.FreeReport(r)
end

func main:
    sqlite := sql.DialectOf(sql.SQLITE)
    postgres := sql.DialectOf(sql.POSTGRES)

    mut row := sql.NewArgs()
    sql.AddText(row, "O'Brien")
    sql.AddInt(row, 41 as i64)
    sql.AddBool(row, true)
    sql.AddNull(row)
    show("INSERT INTO users (name, age, paid, note) VALUES (?, ?, ?, ?)", row, sqlite)
    show("INSERT INTO users (name, age, paid, note) VALUES ($1, $2, $3, $4)", row, postgres)
    sql.FreeArgs(row)

    mut attack := sql.NewArgs()
    sql.AddText(attack, "x' OR '1'='1")
    show("SELECT id FROM users WHERE name = ?", attack, sqlite)
    sql.FreeArgs(attack)

    mut one := sql.NewArgs()
    sql.AddInt(one, 7 as i64)
    show("SELECT id FROM notes WHERE body = '?' AND id = ?", one, sqlite)
    show("SELECT id FROM users WHERE id = ? AND org = ?", one, sqlite)
    sql.FreeArgs(one)
    ret 0
end

A text value is wrapped in single quotes with every quote doubled, and on MySQL a backslash is doubled as well, because MySQL reads backslashes as escapes and the other two do not. A boolean is 1 or 0 on SQLite and MySQL and TRUE or FALSE on PostgreSQL; a null is NULL; numbers are written plainly. The '?' in the fourth query stays as it was, which a search-and-replace over the string would have got wrong.

This is substitution, not a prepared statement: the database still parses one complete statement, and there is no plan reuse. Where the driver can prepare and bind, as sqlite can, that is still the better tool. Where it cannot, this is the safe way to get the values in, and it is one audited function rather than a quoting rule repeated at every call site.

The type of every placeholder

Analyze does everything Check does and also hands back what the query takes and what it returns. Each Param says which column it was compared with, in which table, and the type that column has; each ResultCol says the name the column comes back under, its type, and whether it can be null. SalamType turns that into the Salam type to read it into, so a LEFT JOIN gives you option.Option<i64> where an inner join gives you i64.

import db.sql

func describe(query: str, schema: sql.Catalog):
    mut a := sql.Analyze(query, schema)
    println query
    println "  statement:", sql.StmtKindName(a.kind), "on", a.table
    mut i := 0
    until i < a.params.len():
        p := a.params.get(i)
        println "  param", p.ordinal, "is", p.column, "of type", sql.SalamType(p.kind, p.not_null)
        i = i + 1
    end
    mut j := 0
    until j < a.results.len():
        c := a.results.get(j)
        println "  column", c.name, "comes back as", sql.SalamType(c.kind, not c.nullable)
        j = j + 1
    end
    sql.FreeAnalysis(a)
end

func main:
    mut schema := sql.NewCatalog(sql.DialectOf(sql.SQLITE))
    defer sql.FreeCatalog(schema)
    mut ddl := sql.Apply(schema, """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            name TEXT,
            age INTEGER
        );
    """)
    sql.FreeReport(ddl)

    describe("SELECT id, name FROM users WHERE email = ? AND age > ?", schema)
    describe("INSERT INTO users (email, name) VALUES (?, ?) RETURNING id", schema)
    ret 0
end

That is enough to generate the binding code for a query, or to assert in a test that a query still takes and returns what the code around it believes. Placeholders are numbered in the order they appear, which is the order the driver binds them; for PostgreSQL the ordinal is the number in the $n itself, and a gap in those numbers is an error of its own.

Generating the query functions

With the schema and the analysis in place, the last step is mechanical, so the package does it: annotate a .sql file the way sqlc does and Generate writes the Salam for it - a row struct per query, a typed function per query, each one checked against the schema as it is generated.

import db.sql

func main:
    mut schema := sql.NewCatalog(sql.DialectOf(sql.SQLITE))
    defer sql.FreeCatalog(schema)
    mut ddl := sql.Apply(schema, """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            name TEXT
        );
    """)
    sql.FreeReport(ddl)

    queries := """
-- name: GetUser :one
SELECT id, email, name FROM users WHERE email = ?;

-- name: CreateUser :exec
INSERT INTO users (email, name) VALUES (?, ?);
"""
    mut r := sql.Report {}
    mut parsed := sql.ParseQueries(queries, r)
    println "queries:", parsed.len()
    code := sql.Generate("store", parsed, schema, r)
    if r.ok:
        println code
    else:
        println sql.ReportText(r)
    end
    sql.FreeQueries(parsed)
    sql.FreeReport(r)
    ret 0
end

The kinds are :one (the first row, with an ok &: bool out parameter), :many (a Vector of rows), :exec (a bool) and :execrows (the number of rows changed). Parameters are named after the column each placeholder is compared with and typed from it; a column that can be null comes back as option.Option<T> and is read through IsNull. A query that does not resolve against the schema stops the generation with the query's name in the message, so a typo never reaches the generated file.

The command is salam sql generate queries --schema=db --output=gen/store.salam --package=store. The generated file takes a dyn db.Connection, so one set of functions serves SQLite, MySQL and PostgreSQL, and it picks each dialect's quoting from c.Kind() at run time.

Types

struct Param

import db.sql

func main:
    sqlite := sql.DialectOf(sql.SQLITE)
    println sqlite.name, sql.DialectNamed("postgresql").name, sql.DialectOf(sql.MARIADB).name

    mut bad := sql.Lint("SELECT id FROM t WHERE", sqlite)
    println bad.ok, bad.errors, bad.warnings, bad.statements
    println sql.DiagText(bad.items.get(0))
    sql.FreeReport(bad)

    println sql.ParamCount("SELECT a FROM t WHERE b = ? AND c = ?", sqlite)
    mut marks := sql.Placeholders("SELECT a FROM t WHERE b = ? AND c = ?", sqlite)
    println marks.len(), marks.get(0)
    marks.free()

    mut cat := sql.NewCatalog(sqlite)
    mut ddl := sql.Apply(cat, """
        CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, age INTEGER);
    """)
    println "schema:", ddl.ok, sql.TableCount(cat), sql.TableName(cat, 0)
    sql.FreeReport(ddl)
    println sql.HasTable(cat, "users"), sql.HasColumn(cat, "users", "email"), sql.ColumnCount(cat, "users")
    col := sql.ColumnAt(cat, "users", 1)
    println col.name, col.type_name, sql.KindName(col.kind), col.not_null, col.primary

    mut miss := sql.Check("SELECT nope FROM users", cat)
    println sql.ReportText(miss)
    sql.FreeReport(miss)

    mut a := sql.Analyze("SELECT id, age FROM users WHERE email = ?", cat)
    println sql.StmtKindName(a.kind), a.table, a.params.len(), a.results.len()
    p := a.params.get(0)
    println p.marker, p.ordinal, p.table + "." + p.column, sql.SalamType(p.kind, p.not_null)
    r := a.results.get(1)
    println r.name, sql.KindName(r.kind), r.nullable
    sql.FreeAnalysis(a)

    mut args := sql.NewArgs()
    sql.AddInt(args, 3 as i64)
    sql.AddText(args, "a'b")
    sql.AddBool(args, false)
    sql.AddFloat(args, 2.5)
    sql.AddNull(args)
    println sql.ArgCount(args)
    mut err := sql.Report {}
    filled := sql.Fill("INSERT INTO t (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)", args, sqlite, err)
    println filled
    sql.FreeReport(err)
    sql.FreeArgs(args)

    sql.FreeCatalog(cat)
    ret 0
end

struct ResultCol

struct Analysis

struct Args

struct Dialect

struct Diag

struct Report

struct Column

struct Table

struct Catalog

struct Query

const STMT_UNKNOWN: i32 = 0

const STMT_SELECT: i32 = 1

const STMT_INSERT: i32 = 2

const STMT_UPDATE: i32 = 3

const STMT_DELETE: i32 = 4

const STMT_DDL: i32 = 5

const SQLITE: i32 = 0

const MYSQL: i32 = 1

const MARIADB: i32 = 2

const POSTGRES: i32 = 3

const ERROR: i32 = 0

const WARNING: i32 = 1

const NOTE: i32 = 2

const TYPE_NULL: i32 = 0

const TYPE_INT: i32 = 1

const TYPE_FLOAT: i32 = 2

const TYPE_TEXT: i32 = 3

const TYPE_BLOB: i32 = 4

const TYPE_BOOL: i32 = 5

const TYPE_TIME: i32 = 6

const TYPE_UNKNOWN: i32 = 7

const Q_ONE: i32 = 0

const Q_MANY: i32 = 1

const Q_EXEC: i32 = 2

const Q_EXECROWS: i32 = 3

func Check(sql: str, c: Catalog): Report

func Analyze(sql: str, c: Catalog): Analysis

func FreeAnalysis(a &: Analysis)

func StmtKindName(k: int): str

func NewArgs(): Args

func AddInt(a &: Args, v: i64)

func AddFloat(a &: Args, v: f64)

func AddText(a &: Args, v: str)

func AddBool(a &: Args, v: bool)

func AddNull(a &: Args)

func ArgCount(a: Args): int

func FreeArgs(a &: Args)

func Fill(query: str, a: Args, d: Dialect, err &: Report): str

func DialectOf(id: int): Dialect

func DialectNamed(name: str): Dialect

func FreeReport(r &: Report)

func DiagText(d: Diag): str

func ReportText(r: Report): str

func Lint(sql: str, d: Dialect): Report

func ParamCount(sql: str, d: Dialect): int

func Placeholders(sql: str, d: Dialect): Vector<str>

func NewCatalog(d: Dialect): Catalog

func Apply(c &: Catalog, ddl: str): Report

func FreeCatalog(c &: Catalog)

func TableCount(c: Catalog): int

func TableName(c: Catalog, i: int): str

func HasTable(c: Catalog, name: str): bool

func ColumnCount(c: Catalog, table: str): int

func ColumnAt(c: Catalog, table: str, i: int): Column

func HasColumn(c: Catalog, table: str, column: str): bool

func KindName(k: int): str

func SalamType(k: int, not_null: bool): str

func ParseQueries(src: str, err &: Report): Vector<Query>

func FreeQueries(v &: Vector<Query>)

func Generate(pkg: str, queries: Vector<Query>, c: Catalog, err &: Report): str