Compile-time query syntax/type verification

[Wukuyon]

Are there plans for Turso Limbo to support compile-time syntactic and semantic verification of queries, like how SQLx does it? This would be big for applications in Rust and maybe other statically typed languages. It would let the database's query API leverage the host language's type checker in its queries' input and output data, while guaranteeing that there is no SQL syntactic error at compile time.

SQLx in Rust supports compile-time checked queries. It does not do this by providing a Rust API or DSL for building queries (#580). Instead, it provides macros like sqlx::query! that take regular SQL as input and ensure that its syntax is valid by connecting to and using a development database at compile time. (Rusqlite does not support this: rusqlite/rusqlite#801.)

SQLx even does compile-time query typing. sqlx::query! outputs an anonymous record type, where each SQL column is a Rust field. (SQLite uses dynamic typing, but it also now has STRICT tables, and type verification could be possible for those.)

// Example from SQLx
let countries = sqlx::query!(
        "
SELECT country, COUNT(*) as count
FROM users
GROUP BY country
WHERE organization = ?
        ",
        organization
    )
    .fetch_all(&pool) // -> Vec<{ country: String, count: i64 }>
    .await?;

// countries[0].country
// countries[0].count

It would be a huge deal if Turso Limbo did this compile-time syntax and type verification. Because Turso Limbo is a rewrite in Rust, it might even be able to do compile-time verification without connecting to an actual database, because its parser is built into the library. That would make compile-time query typing harder, but it could still be possible, maybe by making the user create a schema of the database's tables and views at compile time.

(There was a part here about an AST builder, but I moved this to #580 (reply in thread).)

[penberg]

Hey @Wukuyon, I think this question came up before. I am open to having a AST API, but this is more of a question to folks like @jussisaurio how feasible this is. I do like the idea of using Limbo without the overhead of SQL parsing for some use cases.

[Wukuyon]

Thanks for your reply. I didn't see #580 when I searched for a query builder. That's my fault.

I'm actually more interested in compile-time query verification than the AST API. I’ve refocused this issue's original post on compile-time query verification, and I moved the request for an AST API to #580 (reply in thread). I think there's huge potential in harnessing Rust's macro and type system to enhance the safety of SQLite queries for those using Rust, rather than relying on dynamic typing. Hopefully @jussisaurio might have insight into how feasible that is.