4 ms·

SQLite-loadable-rs: A framework for building SQLite Extensions in Rust

196 points by petercooper 4y ago

senthilnayagam 4y ago
I love sqlite and rust extension which author has given gets me excited. Last week saw a similar project in rust to built Postgres extensions https://github.com/tcdi/pgx https://github.com/tcdi/pgx Hope I can play something interesting during vacations
maxpert 4y ago
Just in time! I've been planning a SQLite extension written in Rust for Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) for evaluation purposes. I didn't wanted to touch C/C++ because I feel more comfortable with rust tooling. Thank you for doing this!
dfee 4y ago
Where can I get a good understanding of the limitations of SQLite for production use cases? I’ve used it frequently for prototyping, but the dialect is different enough from Postgres, that I usually switch over pretty early. But for production, it seems people are really advancing it as a solution for applications, which doesn’t fit with the “When To Use” guide on the website. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- dgb23 4y ago
  The listed use cases for applications in the link you provided are: - Application file format - Websites - Server-side database Under not recommended they essentially say, very high volume and highly concurrent access.
- simonw 4y ago
  The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point. But... actual guidance as to how to use it there is still pretty thin on the ground! Short version: use WAL mode (which is not the default). Only send writes from a single process (maybe via a queue). Run your own load tests before you go live. Don't use it if you're going to want to horizontally scale to handle more than 1,000 requests/second or so (though vertically scaling will probably work really well). I'd love to see more useful written material about this. I hope to provide more myself at some point. Here are some notes I wrote a few months ago: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/ https://simonwillison.net/2022/Oct/23/datasette-gunicorn/
  - Thaxll 4y ago
    > The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point I think it's still very relevant. I mean you have service XYZ replicated two times behind a load balancer, how do you use SQLite out of the box? You can't, you have to use one of those service that will proxy / queue queries, so then why even trying to use SQLite. The fact that it's just a file on disk make it a bad solution for a lot of simple use cases.
    - ok_dad 4y ago
      You could partition your data to 2 parts, and direct your users to whichever service serves their partition of that data. Every database turns out to be just a set of files on disk, but for using SQLite you have to think differently than most databases due to the extreme locality of the data.
    - emadda 4y ago
      You only need a pool of servers behind a load balancer for extreme reliability or for scaling out. Most small to medium web applications can handle: - Only being reliable for 99.99% of the time (using a single cloud VM where the cloud provides the hardware level failover). - Scaling up instead of out (adding CPU, RAM and faster disk).
    - ketralnis 4y ago
      Sure, if that's a requirement then it's not a great fit. But it might not be a requirement.
    - simonw 4y ago
      If you're worried about high availability should your instance fail (which is something that's worth worrying about, though many smaller web apps will likely work perfectly fine on a single instance with good scheduled backups) you should absolutely investigate https://litestream.io https://litestream.io - it's the missing piece of the SQLite-for-web-apps story.
  - tkhattra 4y ago
    > Only send writes from a single process depending on your use case, using multiple writer processes can be fine, e.g. multiple writer processes storing periodic metrics (once per minute) into a single sqlite database. the occasional "database is locked" error (it does happen a few times a day) is handled by simply returning and trying again at the next interval. that's fine for this particular use case - the metrics for the last interval are just delayed a bit.
Kinrany 4y ago
SQLite has a big advantage over Postgres here if installing an extension is trivial.
- michaelmior 4y ago
  I've never written an extension for Postgres, but for the ones I've installed, it's amounted to copying some files and running "CREATE EXTENSION" This feels pretty trivial to me already.
- giovannibonetti 4y ago
  You might want to have a look at Pgx: https://github.com/tcdi/pgx https://github.com/tcdi/pgx
eatonphil 4y ago
I worked on a set of extensions for mattn/go-sqlite3, written in Go, inspired but a little further along (in functionality and test coverage) than SQLean. Useful if you want to see examples of this in Go or are using Go rather than Rust to embed SQLite. https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib
alexgarcia-xyz 4y ago
Author here, happy to answer any questions! Here's a few links that may be useful: Direct GitHub link: https://github.com/asg017/sqlite-loadable-rs https://github.com/asg017/sqlite-loadable-rs A soon-to-be-released sqlite-loadable extension that's the fasted CSV parser for SQLite, and rivals DuckDB's parser at non-analytical queries: https://github.com/asg017/sqlite-xsv https://github.com/asg017/sqlite-xsv A regular expression extension in Rust, the fastest SQLite regex implementation: https://github.com/asg017/sqlite-regex https://github.com/asg017/sqlite-regex And in the near future, expect more extensions for postgres, parquet, XML, S3, image manipulation, and more!
- CGamesPlay 4y ago
  The API for virtual tables is tricky, because you don't ever consume the API in Rust: SQLite does it. However, as it is presented the interface is unsafe, I think. In the sqlite-xsv VTab::open impl for XsvTable [0], you pass a &'vtab XsvTable to XsvCursor. But the means that if open is called again, to create a second cursor, then a `&mut XsvTable` and a `&XsvTable` reference exist at the same time, which is unsafe. Note that sqlite-xsv doesn't actually keep the `&XsvTable`, so it's fine, but it serves as an example where a safety problem could surface. The same problem also applies to VTabWriteable::update. The fix is to make both of these methods receive an immutable reference and force implementors to use interior mutability. Note this isn't hypothetical, it's actually unsafe, and would appear if you had a unit test that implemented a writable virtual table backed by a rust data structure, and attempted to iterate and update at the same time. I have an as-yet-unpublished code base that experiments with this. It's not published yet because it doesn't cover everything I want to and I'm trying to avoid publishing something where the API might have to break. My goals are explicitly safety first, performance second. https://github.com/CGamesPlay/sqlite3_ext https://github.com/CGamesPlay/sqlite3_ext [0] https://github.com/asg017/sqlite-xsv/blob/main/src/xsv.rs#L94 https://github.com/asg017/sqlite-xsv/blob/main/src/xsv.rs#L9...
  - CGamesPlay 4y ago
    I went ahead and published the crate so that it is browsable via docs.rs. Feel free to adopt anything from it; the code is public domain. https://docs.rs/sqlite3_ext/latest/sqlite3_ext/ https://docs.rs/sqlite3_ext/latest/sqlite3_ext/
josephg 4y ago
I'm quietly confident that you could compile sqlite with a rust extension into a single .wasm file. The first thing I'd try is to use crate-type = ["staticlib"] to compile the sqlite extension to a static .a file (cargo build --target wasm32-unknown-unknown), and compile sqlite to a staticlib separately then link them together into a .wasm file and run wasm-opt & friends on that. There might be some configuration needed to let the extension know the sqlite functions its calling will be linked together later, but that should all be possible. Maybe with some whack-a-mole of wading through compiler versions and build flags and whatnot.
faizshah 4y ago
This is really nice, just wanted to add to your list that you can extend SQLite in python with peewee orm: https://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-vtfunc https://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html... The workflow is basically prototype your virtual tables in python and then write some optimized code in a lower level language later. Just an idea for those interested.
matthewmueller 4y ago
Awesome project! Does SQLite's virtual table API support writes?
- panzerboiler 4y ago
  Yes, it does. https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html

196 points by petercooper 4y ago

I love sqlite and rust extension which author has given gets me excited. Last week saw a similar project in rust to built Postgres extensions https://github.com/tcdi/pgx https://github.com/tcdi/pgx Hope I can play something interesting during vacations

maxpert 4y ago

Just in time! I've been planning a SQLite extension written in Rust for Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) for evaluation purposes. I didn't wanted to touch C/C++ because I feel more comfortable with rust tooling. Thank you for doing this!

dfee 4y ago

Where can I get a good understanding of the limitations of SQLite for production use cases? I’ve used it frequently for prototyping, but the dialect is different enough from Postgres, that I usually switch over pretty early. But for production, it seems people are really advancing it as a solution for applications, which doesn’t fit with the “When To Use” guide on the website. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

dgb23 4y ago
The listed use cases for applications in the link you provided are: - Application file format - Websites - Server-side database Under not recommended they essentially say, very high volume and highly concurrent access.
simonw 4y ago
The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point. But... actual guidance as to how to use it there is still pretty thin on the ground! Short version: use WAL mode (which is not the default). Only send writes from a single process (maybe via a queue). Run your own load tests before you go live. Don't use it if you're going to want to horizontally scale to handle more than 1,000 requests/second or so (though vertically scaling will probably work really well). I'd love to see more useful written material about this. I hope to provide more myself at some point. Here are some notes I wrote a few months ago: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/ https://simonwillison.net/2022/Oct/23/datasette-gunicorn/
- Thaxll 4y ago
  > The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point I think it's still very relevant. I mean you have service XYZ replicated two times behind a load balancer, how do you use SQLite out of the box? You can't, you have to use one of those service that will proxy / queue queries, so then why even trying to use SQLite. The fact that it's just a file on disk make it a bad solution for a lot of simple use cases.
  - ok_dad 4y ago
    You could partition your data to 2 parts, and direct your users to whichever service serves their partition of that data. Every database turns out to be just a set of files on disk, but for using SQLite you have to think differently than most databases due to the extreme locality of the data.
  - emadda 4y ago
    You only need a pool of servers behind a load balancer for extreme reliability or for scaling out. Most small to medium web applications can handle: - Only being reliable for 99.99% of the time (using a single cloud VM where the cloud provides the hardware level failover). - Scaling up instead of out (adding CPU, RAM and faster disk).
  - ketralnis 4y ago
    Sure, if that's a requirement then it's not a great fit. But it might not be a requirement.
  - simonw 4y ago
    If you're worried about high availability should your instance fail (which is something that's worth worrying about, though many smaller web apps will likely work perfectly fine on a single instance with good scheduled backups) you should absolutely investigate https://litestream.io https://litestream.io - it's the missing piece of the SQLite-for-web-apps story.
- tkhattra 4y ago
  > Only send writes from a single process depending on your use case, using multiple writer processes can be fine, e.g. multiple writer processes storing periodic metrics (once per minute) into a single sqlite database. the occasional "database is locked" error (it does happen a few times a day) is handled by simply returning and trying again at the next interval. that's fine for this particular use case - the metrics for the last interval are just delayed a bit.

Kinrany 4y ago

SQLite has a big advantage over Postgres here if installing an extension is trivial.

michaelmior 4y ago
I've never written an extension for Postgres, but for the ones I've installed, it's amounted to copying some files and running "CREATE EXTENSION" This feels pretty trivial to me already.
giovannibonetti 4y ago
You might want to have a look at Pgx: https://github.com/tcdi/pgx https://github.com/tcdi/pgx

eatonphil 4y ago

I worked on a set of extensions for mattn/go-sqlite3, written in Go, inspired but a little further along (in functionality and test coverage) than SQLean. Useful if you want to see examples of this in Go or are using Go rather than Rust to embed SQLite. https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib

alexgarcia-xyz 4y ago

Author here, happy to answer any questions! Here's a few links that may be useful: Direct GitHub link: https://github.com/asg017/sqlite-loadable-rs https://github.com/asg017/sqlite-loadable-rs A soon-to-be-released sqlite-loadable extension that's the fasted CSV parser for SQLite, and rivals DuckDB's parser at non-analytical queries: https://github.com/asg017/sqlite-xsv https://github.com/asg017/sqlite-xsv A regular expression extension in Rust, the fastest SQLite regex implementation: https://github.com/asg017/sqlite-regex https://github.com/asg017/sqlite-regex And in the near future, expect more extensions for postgres, parquet, XML, S3, image manipulation, and more!

CGamesPlay 4y ago
The API for virtual tables is tricky, because you don't ever consume the API in Rust: SQLite does it. However, as it is presented the interface is unsafe, I think. In the sqlite-xsv VTab::open impl for XsvTable [0], you pass a &'vtab XsvTable to XsvCursor. But the means that if open is called again, to create a second cursor, then a `&mut XsvTable` and a `&XsvTable` reference exist at the same time, which is unsafe. Note that sqlite-xsv doesn't actually keep the `&XsvTable`, so it's fine, but it serves as an example where a safety problem could surface. The same problem also applies to VTabWriteable::update. The fix is to make both of these methods receive an immutable reference and force implementors to use interior mutability. Note this isn't hypothetical, it's actually unsafe, and would appear if you had a unit test that implemented a writable virtual table backed by a rust data structure, and attempted to iterate and update at the same time. I have an as-yet-unpublished code base that experiments with this. It's not published yet because it doesn't cover everything I want to and I'm trying to avoid publishing something where the API might have to break. My goals are explicitly safety first, performance second. https://github.com/CGamesPlay/sqlite3_ext https://github.com/CGamesPlay/sqlite3_ext [0] https://github.com/asg017/sqlite-xsv/blob/main/src/xsv.rs#L94 https://github.com/asg017/sqlite-xsv/blob/main/src/xsv.rs#L9...
- CGamesPlay 4y ago
  I went ahead and published the crate so that it is browsable via docs.rs. Feel free to adopt anything from it; the code is public domain. https://docs.rs/sqlite3_ext/latest/sqlite3_ext/ https://docs.rs/sqlite3_ext/latest/sqlite3_ext/

josephg 4y ago

I'm quietly confident that you could compile sqlite with a rust extension into a single .wasm file. The first thing I'd try is to use crate-type = ["staticlib"] to compile the sqlite extension to a static .a file (cargo build --target wasm32-unknown-unknown), and compile sqlite to a staticlib separately then link them together into a .wasm file and run wasm-opt & friends on that. There might be some configuration needed to let the extension know the sqlite functions its calling will be linked together later, but that should all be possible. Maybe with some whack-a-mole of wading through compiler versions and build flags and whatnot.

faizshah 4y ago

This is really nice, just wanted to add to your list that you can extend SQLite in python with peewee orm: https://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-vtfunc https://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html... The workflow is basically prototype your virtual tables in python and then write some optimized code in a lower level language later. Just an idea for those interested.

matthewmueller 4y ago

Awesome project! Does SQLite's virtual table API support writes?

panzerboiler 4y ago
Yes, it does. https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html