SQLite Uses ByteCode (And For Good Reason)

Recorded live on twitch, GET IN

### Article

### My Stream

### Best Way To Support Me
Become a backend engineer. Its my favorite site

This is also the best way to support me is to support yourself becoming a better backend engineer.

MY MAIN YT CHANNEL: Has well edited engineering videos

Discord

Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))

[vcankeklik]

SQLite documentation is a gem. If you want to write a SQL parser you should definitely see their railroad diagrams (don't ask me why I know this).

[squisherderheld]

Fun fact, jq - the json processor also uses bytecode and some kind of virtual machine to execute queries

[Ptolemusa]

sqlite is one of a tiny list of genuinely good pieces of software. probably #1. almost guaranteed to be the most used piece of software ever in the history of forever. everything else pales in comparison, quality, testing, reliability, documentation, usage, portability. truly the mvp of all time. most valuable program.

[yapet]

I’m in the middle of the academic research into bytecode vs ast compilation/execution and this is right up in my valley. Sweet

[panstromek]

You should read on how they do testing (Richard Hipp also done some talks on it), that's some next level stuff.

[Jwareness]

Weird timing to see this video, since I just discovered that it used byte code myself while reading some of the SQLite documentation last week.

Hope to see Prime do a video on DuckDB sometime, since it's in-process like SQLite but made for big data and insanely impressive both in features and performance. I've evaluated many databases over the years, and DuckDB is the first one ever that checks every box I have. Was cool to see the creator of SpacetimeDB shout out DuckDB during his presentation on this channel.

[kuhluhOG]

The reason why SQLite is quite close for contribution si because they want to make sure it stays in the Public Domain.
But since in a lot of countries you can't just put something into PD (except by dying and then waiting for Copyright to run out) and in others it's a lot of bureaucratic mess, they decided it's easier to just restrict who can contribute.

[hkejhkm]

SQLite devs: yeah so prepared statements are really just another table in your db

[complexity]

Good video.
I remember reading about SQLite dungeon code in college during a compiler class. We had to make a SQL parser. We also had to do a matlab like parser and other stuff. I remember, that I did it in perl. It was like 60 lines of perl (?(DEFINE))xi . I was sophomore and I remember everybody else in the class was a senior. Today I guess most people use ANTLR or LLVM. This was a good clip (refresher).

[jmickeyd]

I would heavily challenge the statement that no tree-of-objects database provides the level of detail as sqlite. MS SQL and Oracle both have amazingly powerful query inspection tools. In many places it doesn't just explain what it would do or has done but why it chose that physical implementation over another based on stored statistics of the tables and indices. They also allow you to override those choices in the case where you know something the database engine doesn't.

[personalaccount]

THX! dude, i really enjoyed every mitine of the video

[Burgo]

I think I'm going to have learn more about this it sounds a lot more interesting than I would have expected

[jaysistar]

Emulator devs that write in Rust may already know, but enums in Rust can be both an AST and a bytecode since the discriminator can have a repr of u8. My implementation does use "unsafe" code to move and dereference the Program Counter pointer, but enums are actually enums in Rust (a way to represent a finite number of states). You had said something about not liking Rust enums, but Haxe and OCAML have pretty much the same thing. They are very useful for making AST representations.

[romankoshchei]

Database creation is a really interesting thing, you are right

[carljacobs]

Lol, falling in love with SQLite when I first started using it over 20 years ago. I used it when writing my own client server database system. The server ran SQLite and managed multiple clients by making sure the accesses where put into a queue to run one at a time thereby not trying to do concurrent access on the database. It was for a point of sale system, so custom-ish hardware and at most about 10 clients all on a local network. The real advantage of it was that both the client and server where single exe files, with no complex installation process required. Ever since then I've stored local config in SQLite databases, and just about every application I've ever written consist of only a single exe file. Installation and usage instructions - run the file from wherever you want to. Uninstall instructions - delete the exe file, if you can be bothered.

[kieserel]

man with 20 years experience just learned about sql, nice

[hamednz]

Not sure, but I guess one of the reason that other dbs don't use this approach is that each engine can optimize a query in a different manner, so it's more appealing to rely on a implementation agnostic AST.

[mjvtoyr]

You calling SQL as "squeal" made me smile. Not gonna lie. I've always heard it referred to as "sequel". Nice.

[dominiquefortin]

Most RDB (relational database) that do optimisation will transform the SQL statement into a formal mathematical representation that can be manipulated for optimisation before execution.

[remrevo]

11:24 Rusts sql library sqlx actually connects to it's database at compile time and generates those prepared statements already then.
That way prepared statements can be used all the time.

Edit: Turns out I was wrong and misremembered that part of the documentation. Though having compile-time validation of your queries and per connection caching of your prepared statements is still really cool!