29:null 2a:["$","title",null,{"children":"Making SQLite extensions pip install-able | Alex Garcia | Observable"}] 2f:T4b3, Distributing these SQLite extensions in Python packages makes it easier to use them in Python applications, like [Datasette](https://datasette.io/). You can easily install some of these extensions in your Datasette instances with `datasette install`: ``` datasette install datasette-sqlite-regex ``` Or if you publish your Datasette instances with the [`datasette publish`](https://docs.datasette.io/en/stable/publish.html#datasette-publish) command, you can add these extension with the `--install` flag like so: ``` datasette publish vercel test.db --project=my-project \ --install datasette-sqlite-fastrand \ --install datasette-sqlite-regex \ --install datasette-sqlite-ulid \ --install datasette-sqlite-jsonschema ``` See [this live Datasette instance](https://sqlite-testing.vercel.app/test?sql=select+%0D%0A++regex_version%28%29%2C+%0D%0A++ulid_version%28%29%2C++%0D%0A++fastrand_version%28%29%2C%0D%0A++jsonschema_version%28%29%2C%0D%0A++regex_find%28%27%5Cw%27%2C+%27alex+garcia%27%29%2C%0D%0A++ulid%28%29%2C%0D%0A++fastrand_int%28%29%2C%0D%0A++jsonschema_matches%28%27%7B%22maxLength%22%3A+5%7D%27%2C+json_quote%28%27alex%27%29%29%0D%0A%3B) to see all these extensions in action!30:T568,## Distributing SQLite extensions is awkward Ever since SQLite extensions were release [17 years ago](https://www.sqlite.org/changes.html#version_3_3_7) (!!), actually *using* and *installing* them have been an awkward experience. Some extensions like the [JSON](https://www.sqlite.org/json1.html), [FTS5](https://www.sqlite.org/fts5.html), and [R*Tree](https://sqlite.org/rtree.html) extensions are automatically compiled into SQLite by default, but most community-built extensions have been copy+pasted C files that require manual compilation. Some popular extensions like [spatialite](https://www.gaia-gis.it/fossil/libspatialite/index) found success, but most were forgotten in old mailings lists and online forum posts. Then in 2021, the [sqlean](https://github.com/nalgeon/sqlean) project by Anton Zhiyanov brought in a new model for distributing SQLite extensions: [pre-compiling them on Github Actions](https://github.com/nalgeon/sqlean/blob/main/.github/workflows/build.yml) on Mac/Windows/Linux x86 runners, and [publishing them on Github Releases](https://github.com/nalgeon/sqlean/releases/). So if you wanted to try an extension and didn't want to manually compile raw C files (and you trust the authors), you could simply download the extension directly and get up-and-running in no time! ```sql .load ./stats select median(value) from generate_series(1, 10); ```31:T49e,## Downside: Only available for a few platforms Since these SQLite extensions are pre-compiled on Github Action runners, there are only a few operating systems and CPU architectures that are supported. The most common ones like Linux x86_64, MacOS (arm64 and x86_64), and Windows 64-bit x86_64 are available, but others like Linux arm, windows arm, freebsd, and more are not. Take a look at the [available wheels on PyPi for `sqlite-regex`](https://pypi.org/simple/sqlite-regex/) as an example, which only support the platforms described above: ``` sqlite_regex-0.1.1a5-py3-none-macosx_10_6_x86_64.whl sqlite_regex-0.1.1a5-py3-none-macosx_11_0_arm64.whl sqlite_regex-0.1.1a5-py3-none-manylinux_2_17_x86_64.manylinux2014_x86_64.manylinux1_x86_64.whl sqlite_regex-0.1.1a5-py3-none-win_amd64.whl ``` Consequently, the Python packages surrounding these SQLite extensions only support a subset of all available wheels on Python. Only pre-built wheels are supported, you can't install and build a source distribution from scratch. This is because these SQLite extension typically are hard to build and require large dependencies (C/Go/Rust compilers). This might change in the future!32:T55b,## Future work: More extensions, `npm`/`brew`/`apt-get` install-able I only initially added 5 of my extensions to `pip`, but I plan to also add 6 more of my extensions ( [`sqlite-lines`](https://github.com/asg017/sqlite-lines), [`sqlite-http`](https://github.com/asg017/sqlite-http), [`sqlite-html`](https://github.com/asg017/sqlite-html), [`sqlite-path`](https://github.com/asg017/sqlite-path), and [`sqlite-url`](https://github.com/asg017/sqlite-url)) to pip in the near future, as well as dozens more I plan to open source throughout this year! Additionally, `pip install` is my first attempt at making SQLite extension distribution easier, but certainly not my last! I also plan to add these SQLite packages to [`npm`](https://www.npmjs.com/) for JavaScript developers, for easy use with [better-sqlite3](https://github.com/WiseLibs/better-sqlite3) and [node-sqlite3](https://github.com/TryGhost/node-sqlite3). I also think there is an opportunity to add a [3rd party Deno module](https://deno.land/x), to more-easily interop with the blazingly-fast [Deno sqlite3 module](https://github.com/denodrivers/sqlite3). And later, adding these SQLite extension to [Homebrew](https://brew.sh/) and other system-level package managers like `apt-get` would also be fantastic. [Subscribe to learn](https://observablehq.com/d/daebec45deb4425d#cell-8) when these are released!33:Tbe9,## Deep dive: How it works Figuring out how to package a SQLite extension as a Python package was not easy, so if you want to know more about what it took, read on! Loadable SQLite extensions are [dynamically loadable files](https://en.wikipedia.org/wiki/Dynamic_loading) that are loaded at runtime. These files, ending in `.dylib`/`.so`/`.dll` depending on your operating system, are typically standalone files that get passed into the `load_extension()` method of your SQLite connection. So the idea is easy: We prebuild [Python wheels](https://pythonwheels.com/) for different operating systems/platforms, making sure to include the correct dynamic loadable file for the platform, upload it to pypi, and there we go! *How* to do that is a different story, however. The Python packaging ecosystem is *very* complicated, so deciding which tools to use and which to ignore was difficult. For example, why can't I just run [`python setup.py bdist`](https://docs.python.org/3/distutils/builtdist.html) and go along my merry way? Ah, but most people recommend [cibuildwheel](https://github.com/pypa/cibuildwheel) when cross-compiling Python wheels. But I'm not using CPython, I'm precompiling my library... Do I need [auditwheel](https://github.com/pypa/auditwheel)??? In the end, here's how I got it to work. This is the [`python/sqlite_regex`](https://github.com/asg017/sqlite-regex/tree/main/python/sqlite_regex) directory: ``` $ tree . . ├── README.md ├── noop.c ├── setup.py └── sqlite_regex ├── __init__.py └── regex0.dylib 1 directory, 5 files ``` That `regex0.dylib` file is the compiled SQLite extension, which is a different file extension when compiling on different platforms(`regex0.so` for Linux, `regex0.dll` for Windows). The [`__init__.py`](https://github.com/asg017/sqlite-regex/blob/main/python/sqlite_regex/sqlite_regex/__init__.py) is the Python API for this package, small enough to fit in a tweet: ```py import os import sqlite3 def loadable_path(): loadable_path = os.path.join(os.path.dirname(__file__), "regex0") return os.path.normpath(loadable_path) def load(conn: sqlite3.Connection) -> None: conn.load_extension(loadable_path()) ``` Then we make sure the compiled extension is included in our package with `package_data=` in our [`setup.py`](https://github.com/asg017/sqlite-regex/blob/c7b59de61a04b37be603ca0d09b24e04f676697f/python/sqlite_regex/setup.py#LL39): ```python # ... package_data={"sqlite_regex": ['*.so', '*.dylib', '*.dll']}, # ... ``` Now we can run [`pip wheel`](https://pip.pypa.io/en/stable/cli/pip_wheel/) to get our compiled wheel, which is literally just a ZIP file of all the source code + package metadata: ``` pip3 wheel python/sqlite_regex/ -w dist/ sqlite_regex-0.2.0-cp39-cp39-macosx_12_0_x86_64.whl ``` Now here's one problem: The name of the wheel file matters. In this case, building this on my make created the above filename, but it is "too narrow", only working with Python version 3.9 (my installed version): 34:T485,cp39-cp39 means "only for CPython version 3.9", which is really restrictive. This Python wheel does package platform specific code, but it can theoretically work for *any* Python version, and instead of just working on macosx_12_0_x86_64, it could work on *any* MacOSX x86_64 platforms, not just on Monterey. So after spending hours trying to make a Python wheels that work for *any* Python version, the solution was simple: just rename the file.

sqlite_regex-0.2.0-py3-none-macosx_10_6_x86_64.whl

... Well that's easy. So I just threw those renaming rules [into a python script](https://github.com/asg017/sqlite-regex/blob/main/.github/workflows/rename-wheels.py), upload them to [PyPi](https://pypi.org/simple/sqlite-regex/), and there you go, pip installable SQLite extensions! There are likely bugs and incorrect implementations in these Python wheels, but none I could find on my machines. if you do come across any issues, please [file an issue](https://github.com/asg017/sqlite-regex/issues/new)! 35:[{"id":"1401b9f5bf63f28388f06af27a2e545a793fab2a12fbc950d1fa02c7a68d2e0f0ee80e9f5bb01def299d26b7fa3af6fb289bc3b47e67687d20d2c8877964ef36","href":"https://static.observableusercontent.com/files/1401b9f5bf63f28388f06af27a2e545a793fab2a12fbc950d1fa02c7a68d2e0f0ee80e9f5bb01def299d26b7fa3af6fb289bc3b47e67687d20d2c8877964ef36","name":"1.jpeg","type":"image/jpeg","lastModified":1675386359131.678,"size":50994},{"id":"52295f58614d2e73429ce6879754eaea0437c7ddb8d8d1c93476b57e546027417192a2f61b4ba5d2c004f9ec319aa27cb38ffeed0f378ca80a9546970f772ab9","href":"https://static.observableusercontent.com/files/52295f58614d2e73429ce6879754eaea0437c7ddb8d8d1c93476b57e546027417192a2f61b4ba5d2c004f9ec319aa27cb38ffeed0f378ca80a9546970f772ab9","name":"2.jpeg","type":"image/jpeg","lastModified":1675386359038.508,"size":65214},{"id":"f289c043cdd1fc491458920b4a78f1008aac55256a51b169f73eb05dc451bced43498cef99130815f691ef153023ffd6cb2478c9002eeb43e06f87c077607662","href":"https://static.observableusercontent.com/files/f289c043cdd1fc491458920b4a78f1008aac55256a51b169f73eb05dc451bced43498cef99130815f691ef153023ffd6cb2478c9002eeb43e06f87c077607662","name":"3.jpeg","type":"image/jpeg","lastModified":1675386359031.438,"size":46459}] 2d:{"id":"daebec45deb4425d","slug":"making-sqlite-extensions-pip-install-able","title":"Making SQLite extensions pip install-able","thumbnail":"752da5162579e0416b9b2e97600b6306f00808125e12d7690a31f9b91e53d9c0","access_level":"public","create_time":"$D2023-02-02T01:38:31.059Z","update_time":"$D2023-02-06T17:55:25.372Z","view_time":null,"first_public_time":"$D2023-02-06T18:11:03.430Z","has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024"},"version":145,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null}],"members":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"ec34b19c7b7564c5","slug":"sqlite-blog","title":"SQLite Blog","owner":{"id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia"}},"type":"notebook","body":{"title":"Making SQLite extensions pip install-able","cells":[{"id":0,"value":"# Making SQLite extensions pip install-able","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"

\n ${(await Promise.all([\n FileAttachment(\"3.jpeg\").url(),\n FileAttachment(\"1.jpeg\").url(),\n FileAttachment(\"2.jpeg\").url(),\n \n])).map(d=>htl.html`

`)}\n

\n DALL·E attempts for this post's thumbnail\n ","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":83,"value":"You can now install several of my SQLite extensions with `pip`! Try it out with [`sqlite-regex`](https://github.com/asg017/sqlite-regex), to pull in the fastest and most fully-featured regular expression extension for SQLite:\n\n\n```bash\npip install sqlite-regex\n```\n\nThen, using `sqlite-regex` in Python is as easy as 🥧:\n\n```python\nimport sqlite3\nimport sqlite_regex\n\ndb = sqlite3.connect(':memory:')\n\ndb.enable_load_extension(True)\nsqlite_regex.load(db)\n\ndb.execute(\"select regex_version(), '[abc]' regexp 'a';\").fetchone()\n# ('v0.1.0', 1)\n```\n\nYou can also `pip install` [`sqlite-ulid`](https://github.com/asg017/sqlite-ulid), \n[`sqlite-jsonschema`](https://github.com/asg017/sqlite-jsonschema), \n[`sqlite-fastrand`](https://github.com/asg017/sqlite-fastrand), and \n[`sqlite-xsv`](https://github.com/asg017/sqlite-xsv)!\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":14,"value":"$2f","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":21,"value":"$30","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":73,"value":"While this is a massive step forward for usability, there are still some awkwardness around this. Users still have to manually install and update these extensions themselves, and in the age of package mangers like pip, npm, homebrew, and apt-get, managing packages yourself can be painful.\n\nSo, `pip install`'able SQLite extensions are my first attempt to modernize SQLite extension distribution! ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":10,"value":"$31","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":50,"value":"$32","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":23,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":97,"value":"\n
\nsqlite_regex-0.2.0-cp39-cp39-macosx_12_0_x86_64.whl\n
\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":136,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"## Stay in the loop!\n\nThis 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**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me) or [Mastodon](https://data-folks.masto.host/@alexgarciaxyz).\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":104,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":106,"value":"// courtesty d3.schemePastel1\ncolors = [\n \"#b3cde3\", // switchy switch\n \"#fbb4ae\",\n \"#ccebc5\",\n \"#decbe4\",\n \"#fed9a6\",\n \"#ffffcc\",\n \"#e5d8bd\",\n \"#fddaec\",\n \"#f2f2f2\"\n]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"