31:T421,Now, run `cargo build`, and your new compiled loadable extension will be available in `target/debug/libxyz.*` ! You can run it with the sqlite3 CLI like so: ```sql $ sqlite3 sqlite> .load target/debug/libxyz sqlite> select hello('world'); hello, world! sqlite> select hello(upper('alex')); hello, ALEX! ``` `sqlite-loadable-rs` handles more than just simple custom scalar functions: there's also support for [virtual tables](https://www.sqlite.org/vtab.html) and [table functions](https://www.sqlite.org/vtab.html#eponymous_virtual_tables). See the [documentation](https://docs.rs/sqlite-loadable/latest/sqlite_loadable/) for a full reference, or the [`examples/`](https://github.com/asg017/sqlite-loadable-rs/tree/main/examples) directory for full examples. I hope to also support custom [aggregate window functions](https://github.com/asg017/sqlite-loadable-rs/issues/1), [collating sequences](https://github.com/asg017/sqlite-loadable-rs/issues/2), and [virtual file systems](https://github.com/asg017/sqlite-loadable-rs/issues/3) in the near future. 32:T9ce,## Why Rust? Looking past the overzealous fanboys and the language wars that online discussions of Rust brings, it's a fantastic choice for SQLite extensions. It has builtin-support for [dynamic libraries](https://doc.rust-lang.org/cargo/reference/cargo-targets.html#library) (which SQLite requires for loadable extensions), runs nearly as fast as extensions written in C (see [the benchmarks](https://github.com/asg017/sqlite-loadable-rs/tree/main/benchmarks)), and has thousands of easily-importable and easy-to-manage crates. To build _loadable_ SQLite extensions, you have four options: - **With C/C++**, the "traditional" way. The [SQLite website](https://www.sqlite.org/loadext.html#programming_loadable_extensions) has a comprehensive guide on how to do this, and [sqlean](https://github.com/nalgeon/sqlean) has great examples and how to build and distribute these extensions on Github. - **With Go**, using [riyaz-ali/sqlite](https://github.com/riyaz-ali/sqlite) or other go-sqlite library. That repository has [a few example extensions](https://github.com/riyaz-ali/sqlite/tree/master/_examples) to get you started, or take a look at my [sqlite-http](https://github.com/asg017/sqlite-http) or [sqlite-html](https://github.com/asg017/sqlite-html) libraries for building and distributing these extensions on Github. - **With Rust**, using sqlite-loadable. - **With Zig**, using [zig-sqlite](https://github.com/vrischmann/zig-sqlite). Each options has their own advantages and disadvantages, and personal preference matters a lot. My own experience and my preferences lead me to the following conclusions: | | Performance | Binary Size | Developer Experience | 3rd party libraries |-|-|-|-|- |C/C++ | ✅ Extremely fast | ✅ Extremely small | ❌ Poor| ❌ Hard to integrate | Go, with `riyaz-ali/sqlite` | ❌ Slow |❌ Large | ✅ Fantastic| ✅ Readily available | Rust, with `sqlite-loadable` | ✅ Extremely fast | ❌ Medium | ✅ Fantastic| ✅ Readily available Note: I never tried zig-sqlite So, in my opinion, I don't mind sacrificing the larger binary sizes and longer compile times with `sqlite-loadable-rs`, since the performance is still incredible and my experience developing the extensions is fantastic. It also becomes a practical question: Which programming language do I want to write, maintain, and support for the long term? Everyone will have a different answer, but for me personally, Rust will be the best option for me and the projects I make. 33:T44e,## Prior art Building this framework wasn't easy - but [rusqlite](https://github.com/rusqlite/rusqlite) and [xlite](https://github.com/x2bool/xlite) certainly made it easier! Rusqlite is the most widely-used SQLite crate for Rust, and offers the ability to create custom _application-defined_ functions and virtual tables in Rust. However, for making _loadable_ extensions, there have been several pull requests that have attempted to include this functionality, but nothing has been merged in. Some [recent developments](https://github.com/rusqlite/rusqlite/pull/910#issuecomment-1330827807) indicate this might happen soon, however. I referenced rusqlite _heavily_ while making sqlite-loadable, so I do hope rusqlite includes this functionality soon! I referenced xlite heavily as well - which implemented the SQLite extension API by hand. `sqlite-loadable-rs` provides much more safety guarantees and a cleaner API than what xlite implements, but I do appreciate Sergey's work! I also found great inspiration from [pgx](https://github.com/tcdi/pgx)'s and [pyO3](https://pyo3.rs/)'s API designs. 34:T594,## Do SQLite extensions even have a future? Asking someone who makes SQLite extensions "Do SQLite extensions have a future?" is like asking a trombone oil salesman "Does trombone oil have a future?" - of course it does! But in the age where new data tools are made every month (like [DataFusion](https://github.com/apache/arrow-datafusion) and [DuckDB](https://duckdb.org/)), why try to beef up a 20-year-old technology? My answer can be its own entire blog post, but to keep it short: SQLite has only grown in popularity, features, and [deployments](https://www.sqlite.org/mostdeployed.html) for more than two decades, and there's no reason to believe that will change anytime soon. Many software engineers, data engineers, and data journalists use SQLite in their day-to-day job to wangle and analyze datasets of varying sizes. Getting data inside a SQLite database has historical been a tough issue, which many people have tackled with tools like [sqlite-utils](https://sqlite-utils.datasette.io/) or ad-hoc tools on top of Pandas/Spark/thousands of other tools. SQLite extensions can be a perfect solution to various SQLite shortcomings, but it's historical been difficult to make one. I hope that the combination of [riyaz-ali/sqlite](https://github.com/riyaz-ali/sqlite) for Go and `sqlite-loadable-rs` for Rust will enable more people to write fast, performant, and delightful SQL APIs to their favorite datasets! 35:T5c9,## This ain't the end In addition to [sqlite-xsv](https://github.com/asg017/sqlite-xsv), [sqlite-base64](https://github.com/asg017/sqlite-base64), and [sqlite-regex](https://github.com/asg017/sqlite-regex), I plan to release _several_ more SQLite extensions written with `sqlite-loadable-rs` in the near future, including: - **sqlite-jsonschema**: A SQLite extension for [JSON Schema](https://json-schema.org/) validation for SQLite expressions and columns - **sqlite-parquet**: A SQLite extension for reading Parquet files - **sqlite-postgres**: A SQLite extension for reading (and writing!) to Postgres databases - **sqlite-img**: A SQLite extension for manipulating, resizing, encoding, and decoding images in pure SQL - **sqlite-xml**: A SQLite extension for querying/manipulating/generating XML - **sqlite-s3**: A SQLite extension for querying, get'ing, and put'ing objects in S3 buckets Designing a proper SQL API is challenging and takes time, but I'm aiming to get drafts of all these up on Github soon! This is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**. Alternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me). 36:[{"id":"ea9545a835aae56a593557c63faa5dca28cd924da57f03bf8126910b4844cbac4a4d53cb40027e3cbd6da5acae6e0b4facc50187ff16ef55fc266e6662d33e50","href":"https://static.observableusercontent.com/files/ea9545a835aae56a593557c63faa5dca28cd924da57f03bf8126910b4844cbac4a4d53cb40027e3cbd6da5acae6e0b4facc50187ff16ef55fc266e6662d33e50","name":"DALL·E 2022-12-02 13.57.14 - red crab in an office working on a spreadsheet, realistic photograph, dramatic lighting.png","type":"image/png","lastModified":1670018351260.736,"size":1638505},{"id":"4d79db2dea2ac750cb26bb7d0fed7487683ce3cf7a36871f7673aaacff5b403bf5983b9250f3365a4d0e70880e4d433689c23ee56d0c8c1a564adbcd4c3b90a5","href":"https://static.observableusercontent.com/files/4d79db2dea2ac750cb26bb7d0fed7487683ce3cf7a36871f7673aaacff5b403bf5983b9250f3365a4d0e70880e4d433689c23ee56d0c8c1a564adbcd4c3b90a5","name":"count@1.png","type":"image/png","lastModified":1670048897813.763,"size":72626},{"id":"e4ba7a9b418b14bab97062bbbfdcc249763a62deb667cd7a9acb8d448ef088128dc66d04f3ba6fc96be1f939949e97fdafa8afc6bc34d45ff8834f4004a9f164","href":"https://static.observableusercontent.com/files/e4ba7a9b418b14bab97062bbbfdcc249763a62deb667cd7a9acb8d448ef088128dc66d04f3ba6fc96be1f939949e97fdafa8afc6bc34d45ff8834f4004a9f164","name":"DALL·E 2022-12-02 13.58.26 - red crab in an office working on a spreadsheet, digital art, dramatic lighting.png","type":"image/png","lastModified":1670018354511.571,"size":1446832},{"id":"1bb07699af027216e8446b6f6c0996bb8e67798dbbd7edae15ec0a3db57558a7efb75f1b4cd79f2010abce8c439fbb8b3650b50157562c0eda0eaef1fccb545d","href":"https://static.observableusercontent.com/files/1bb07699af027216e8446b6f6c0996bb8e67798dbbd7edae15ec0a3db57558a7efb75f1b4cd79f2010abce8c439fbb8b3650b50157562c0eda0eaef1fccb545d","name":"DALL·E 2022-12-02 14.01.07 - red crab in an office working on a spreadsheet, disney cartoon, dramatic lighting.png","type":"image/png","lastModified":1670018474885.66,"size":1395837}] 2f:{"title":"Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in Rust","cells":[{"id":0,"value":"# Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in Rust\n\n","pinned":false,"hidden":false,"mode":"md"},{"id":6,"value":"[sqlite-loadable-rs](https://github.com/asg017/sqlite-loadable-rs) is a new framework for writing fast, performant, and mostly-safe SQLite extensions in Rust. These extensions are nearly as fast those written in C, with the added benefit of Rust's memory safety features, easy access to thousands of 3rd party crates and packages on [crates.io](https://crates.io/), all while maintaining a splendid developer experience. \n\n","pinned":false,"hidden":false,"mode":"md"},{"id":109,"value":"

\n ${imgs.map(d=>htl.html`

`)}\n

\n ","pinned":false,"hidden":false,"mode":"html"},{"id":124,"value":" DALL·E attempts for this post's thumbnail","pinned":false,"hidden":false,"mode":"html"},{"id":41,"value":"## How it works\n\nTo try out `sqlite-loadable-rs`, run `cargo init --library` in a new directory to create a new Rust project, and update your `Cargo.toml` to the following:\n\n\n```toml\n[package]\nname = \"xyz\"\nversion = \"0.1.0\"\nedition = \"2021\"\n\n[dependencies]\nsqlite-loadable = \"0.0.2\"\n\n[lib]\ncrate-type=[\"cdylib\"]\n```","pinned":false,"hidden":false,"mode":"md"},{"id":45,"value":"In `src/lib.rs`, put in this \"hello world\" scalar function example:\n\n```rs\nuse sqlite_loadable::prelude::*;\nuse sqlite_loadable::{api, define_scalar_function, Result};\n\npub fn hello(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {\n let name = api::value_text(values.get(0).expect(\"1st argument as name\"))?;\n api::result_text(context, format!(\"hello, {}!\", name))?;\n Ok(())\n}\n\n#[sqlite_entrypoint]\npub fn sqlite3_xyz_init(db: *mut sqlite3) -> Result<()> {\n define_scalar_function(db, \"hello\", 1, hello, FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC)?;\n Ok(())\n}\n\n```","pinned":false,"hidden":false,"mode":"md"},{"id":49,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":66,"value":"## Real word examples and performance\n\nIn short: \"hello world\" SQLite extensions written with `sqlite-loadable-rs` are extremely fast, but are still 10-60% slower than the same extensions written in C. But, \"real world\" extensions like CSV parsing and regular expressions can be much faster than C SQLite extensions. \n\n[`sqlite-xsv`](https://github.com/asg017/sqlite-xsv) is a SQLite extension for parsing CSVs written in Rust with `sqlite-loadable`. It's not 100% complete yet, but on my Macbook it's 1.5 times faster than the official [CSV Virtual Table](https://www.sqlite.org/csv.html) extension written in C, 1.7 times faster the [vsv virtual table](https://github.com/nalgeon/sqlean/blob/main/docs/vsv.md) from sqlean, and 20 times faster than the [`.import`](https://www.sqlite.org/cli.html#csv) command in the SQLite command line interface! \n\n","pinned":false,"hidden":false,"mode":"md"},{"id":140,"value":"count = FileAttachment(\"count@1.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":141,"value":"\n\nSimilarly, [`sqlite-regex`](https://github.com/asg017/sqlite-regex) is nearly twice as fast as the official [regexp](https://github.com/sqlite/sqlite/blob/master/ext/misc/regexp.c) extension, and is several times faster than the [sqlean re](https://github.com/nalgeon/sqlean/blob/main/docs/re.md) extension - all while containing a more fully-developed regular expression engine and more utility functions.\n\n\nI'll be putting out an official \"release\" of `sqlite-xsv` and `sqlite-regex` in the next week or so — including accompanying [blog posts](https://buttondown.email/alexgarcia/). ","pinned":false,"hidden":false,"mode":"md"},{"id":9,"value":"$32","pinned":false,"hidden":false,"mode":"md"},{"id":91,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":168,"value":"## Drawback: No WASM support\n\n[SQL.js](https://github.com/sql-js/sql.js/) of the official [sqlite3 WASM project](https://sqlite.org/wasm/doc/tip/about.md) demonstrate how you can run SQLite in the browser by compiling it to WASM. You can also staticly include extensions written in C into a WASM SQLite build, which has been done for [sqlite-lines](https://github.com/asg017/sqlite-lines) and [sqlite-path](https://github.com/asg017/sqlite-path).\n\nHowever, this is extremely difficult to do. Bill Mill wrote [a very comprehensive guide](https://github.com/llimllib/wasm_sqlite_with_stats) on how to do this, but it only works with extensions written in C. As of now, I don't think you compile `sqlite-loadable-rs` extensions into WASM. As far as I can tell, you can't easily compile a Rust project to WASM if there's a C dependency. \n\nIf you think this is possible, [please file an issue!](https://github.com/asg017/sqlite-loadable-rs/issues/new)\n","pinned":false,"hidden":false,"mode":"md"},{"id":27,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":81,"value":"$35","pinned":false,"hidden":false,"mode":"md"},{"id":130,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":112,"value":"imgs = Promise.all([\n FileAttachment(\n \"DALL·E 2022-12-02 13.57.14 - red crab in an office working on a spreadsheet, realistic photograph, dramatic lighting.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-12-02 14.01.07 - red crab in an office working on a spreadsheet, disney cartoon, dramatic lighting.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-12-02 13.58.26 - red crab in an office working on a spreadsheet, digital art, dramatic lighting.png\"\n ).url()\n])","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W36","stdlib":"1","titling":"auto"}