Build your own SQLite, Part 1: Listing tables | Hacker News Reader

Build your own SQLite, Part 1: Listing tables

blog.sylver.dev

179 pointsupmind12 comments

An interesting idea just struck me. If this is all in native Rust then you could do something interesting with macro defined SQL queries, where at compile time, you could output direct bindings from the SQL to the internal DB api. This would skip parsing and building query plans at compile time (for static queries).

Anyway, cool project.

That's an interesting idea. One problem I see is that conventional query planners use statistics to choose the optimal plan, however the data presumably wouldn't be available at compile time. But if you built the database with this approach in mind you might find a different approach to planning.

Pretty sure this is why sql engines normally jit compile to either bytecode or straight to machine code, so that they can easily recompile and replace the query if the statistics or other factors change. So long as you don't regularly recompile the query you pay the cost once then reuse the results possibly thousands or even millions of times, but without being forced to keep using the exact same plan until you rebuild the application.

Pretty sure Sqlite's bytecode is generated after choosing the optimal plan. So you can't change it after that point. I had never considered this but you probably do want to prepare or expire statements after a while if your DB changes significantly

It never reconsiders? The one I have the most experience with (SQL server) certainly will, sometimes at weird times in ways that are non-obvious leading to performance degradation heh.

Poking at the documentation, they're only recompiled when the schema changes or ANALYZE has been run[1]. So, if you want them recompiled, you can use the optimize PRAGMA[2].

They raise a good point in the docs that in the contexts SQLite works in, if there's a regression caused by changing the plan, there won't be a DBA around to fix it.

[1] https://sqlite.org/queryplanner-ng.html

[2] https://sqlite.org/lang_analyze.html#req

SQLx seems to do some form of this. though what you're suggesting may remove the build time dependency on "connecting" to a SQLite database.

"SQLx supports compile-time checked queries. It does not, however, do this by providing a Rust API or DSL (domain-specific language) for building queries. Instead, it provides macros that take regular SQL as input and ensure that it is valid for your database. The way this works is that SQLx connects to your development DB at compile time to have the database itself verify (and return some info on) your SQL queries."

https://github.com/launchbadge/sqlx

Query plans may depend on runtime information about a table’s statistics, though

> If this is all in native Rust then you could do something interesting with macro defined SQL queries, where at compile time, you could output direct bindings from the SQL to the internal DB api.

Aren't table references resolved at runtime?

This is essentially a prepared statement, right?

https://www.sqlite.org/c3ref/stmt.html

I came across this blog as well because I got interested in writing a compiler that converts SQL into source code of a type-checked language.

Are there any blog series about the details of query planning, optimization etc?

Not sure I understand, but SQLite itself sort of does that. You can take a look at the architecture here: https://www.sqlite.org/arch.html

Reply on news.ycombinator.com