2c:null 2d:["$","title",null,{"children":"Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno | Alex Garcia | Observable"}] 32:[{"id":"dd01f14e182f197b9a6710f8190c43574cd4ce9fb102d8da64d45edcbc5f5487c879c71b3d584506141b5ae96cf8c5d14ae99d453651227cc7fc27aba7520757","href":"https://static.observableusercontent.com/files/dd01f14e182f197b9a6710f8190c43574cd4ce9fb102d8da64d45edcbc5f5487c879c71b3d584506141b5ae96cf8c5d14ae99d453651227cc7fc27aba7520757","name":"1.jpg","type":"image/jpeg","lastModified":1680112306610.21,"size":16779},{"id":"e8450029b14792810b9d0c73ac9d8934319bfa4b06e4610fb07b3da122faea318ccc0ffed15b10fc8862c1d80ada16e0c970460b31506b1e2612ae6cb117d473","href":"https://static.observableusercontent.com/files/e8450029b14792810b9d0c73ac9d8934319bfa4b06e4610fb07b3da122faea318ccc0ffed15b10fc8862c1d80ada16e0c970460b31506b1e2612ae6cb117d473","name":"3.jpg","type":"image/jpeg","lastModified":1680112306359.248,"size":23689},{"id":"a2cfe94eafc5810847a39d040557b177d0e1ff55d80702fad130ed79438a0cb62a7cafdfd0114486072fc79c7959983672405bb564107f4bcc9301eef3c8c6c3","href":"https://static.observableusercontent.com/files/a2cfe94eafc5810847a39d040557b177d0e1ff55d80702fad130ed79438a0cb62a7cafdfd0114486072fc79c7959983672405bb564107f4bcc9301eef3c8c6c3","name":"2.jpg","type":"image/jpeg","lastModified":1680112306610.117,"size":26241}] 30:{"id":"c4cf8c70c8ac0034","slug":"making-sqlite-extensions-npm-installable-and-deno","title":"Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno","thumbnail":"3230a079e88d057d4c8b5b3d2a3bed85aefe3ffa12e7c6bfad5d5524382ca7db","access_level":"public","create_time":"$D2023-03-07T01:44:25.133Z","update_time":"$D2023-03-29T17:59:52.564Z","view_time":null,"first_public_time":"$D2023-03-29T18:01:36.909Z","has_unpublished_edits":null,"fork":false,"stars":5,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024","bio":"Software engineer, available for freelance work! • he/him/his"},"version":192,"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,"bio":"Software engineer, available for freelance work! • he/him/his"}],"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":{"titling":"auto","title":"Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno","cells":[{"id":178,"value":"# Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":6,"value":"\nLast month I made [several of my SQLite extensions pip install-able](https://observablehq.com/@asg017/making-sqlite-extensions-pip-install-able), for easier use in Python. A natural followup question: Can you do the same for Node.js and npm?\n\nYes!\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":180,"value":"

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

`)}\n

\n DALL·E attempts for this post's thumbnail\n ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":0,"value":"\nTry it out with [`sqlite-regex`](https://github.com/asg017/sqlite-regex), to bring in a fast and fully-featured regular expression extension for SQLite:\n\n\n```bash\nnpm install sqlite-regex\n```\n\nThen in Node.js, the `sqlite-regex` extension can then be loaded into a [`better-sqlite3`](https://github.com/WiseLibs/better-sqlite3) or [`node-sqlite3`](https://github.com/TryGhost/node-sqlite3) connection.\n\n```javascript\nimport Database from \"better-sqlite3\";\nimport * as sqlite_regex from \"sqlite-regex\";\n\nconst db = new Database(\":memory:\");\n\ndb.loadExtension(sqlite_regex.getLoadablePath());\n\nconst version = db.prepare(\"select regex_version()\").pluck().get();\nconsole.log(version); // \"v0.2.0\"\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"\nYou can also `npm install` all of these other extensions:\n\n- [`sqlite-xsv`](https://github.com/asg017/sqlite-xsv), for reading large CSVs/TSVs/whatever-SVs\n- [`sqlite-http`](https://github.com/asg017/sqlite-http), for making HTTP requests in SQL\n- [`sqlite-html`](https://github.com/asg017/sqlite-html), for parsing and querying HTML documents\n- [`sqlite-lines`](https://github.com/asg017/sqlite-lines), for reading large files line-by-line\n- [`sqlite-path`](https://github.com/asg017/sqlite-path), for parsing file paths\n- [`sqlite-jsonschema`](https://github.com/asg017/sqlite-jsonschema), for validating JSON documents\n- [`sqlite-url`](https://github.com/asg017/sqlite-url), for parsing URLs\n- [`sqlite-ulid`](https://github.com/asg017/sqlite-ulid), for working with [ULID](https://github.com/ulid/spec)'s (UUID alternative)\n- [`sqlite-fastrand`](https://github.com/asg017/sqlite-fastrand), for generating fast numbers/blobs quickly\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"## For Deno too!\n\n[Deno](https://deno.land/), the new JavaScript runtime that's a popular alternative to Node.js, already has [some form of npm/Node.js package support](https://deno.land/manual@v1.31.2/node). So technically you could use the above NPM packages with Deno and `better-sqlite3`. But it can be slow and clunky to run npm packages with Deno, and `better-sqlite3` brings in many dependencies. \n\nPlus, in the Deno ecosystem, the recent [`x/sqlite3`](https://deno.land/x/sqlite3@0.9.1) module uses Deno's unique FFI API to make an extremely fast and performance SQLite client library, and claims to be the fastest SQLite client outside of C! Can we bundle SQLite extensions specifically for Deno and `x/sqlite3`?\n\n\nYes! Try it out with [`x/sqlite-regex`](https://deno.land/x/sqlite_regex), a Deno module that packages the `sqlite-regex` extension for use inside Deno:\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":37,"value":"\n```typescript\nimport { Database } from \"https://deno.land/x/sqlite3@0.8.0/mod.ts\";\nimport * as sqlite_regex from \"https://deno.land/x/sqlite_regex@v0.2.3-alpha.2/mod.ts\";\n\nconst db = new Database(\":memory:\");\n\ndb.enableLoadExtension = true;\nsqlite_regex.load(db);\n\nconst [version] = db\n .prepare(\"select regex_version()\")\n .value<[string]>()!;\n\nconsole.log(version); // \"v0.2.3-alpha.2\"\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":44,"value":"Copy+paste this sample into a `script.ts`, and run it on the command line with:\n\n```bash\ndeno run -A --unstable scripts.ts\n```\n\n\nThe underlying `x/sqlite3` module requires the `--unstable` flag to enable the Deno FFI API, and since FFI essentially grants all permissions, the `-A/--allow-all` flag is included.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":165,"value":"All of these extensions are also distributed on `deno.land/x`:\n\n- [`deno.land/x/sqlite-xsv`](https://deno.land/x/sqlite-xsv), for reading large CSVs/TSVs/whatever-SVs\n- [`deno.land/x/sqlite-http`](https://deno.land/x/sqlite-http), for making HTTP requests in SQL\n- [`deno.land/x/sqlite-html`](https://deno.land/x/sqlite-html), for parsing and querying HTML documents\n- [`deno.land/x/sqlite-lines`](https://deno.land/x/sqlite-lines), for reading large files line-by-line\n- [`deno.land/x/sqlite-path`](https://deno.land/x/sqlite-path), for parsing file paths\n- [`deno.land/x/sqlite-jsonschema`](https://deno.land/x/sqlite-jsonschema), for validating JSON documents\n- [`deno.land/x/sqlite-url`](https://deno.land/x/sqlite-url), for parsing URLs\n- [`deno.land/x/sqlite-ulid`](https://deno.land/x/sqlite-ulid), for working with ULID's (UUID alternative)\n- [`deno.land/x/sqlite-fastrand`](https://deno.land/x/sqlite-fastrand), for generating fast numbers/blobs quickly","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":185,"value":"## Caveat: Not for client-side JavaScript\n\nIt's possible to compile SQLite to WebAssembly for use in the browser, like with [sql.js](https://sql.js.org/#/) or the official [SQLite WASM project](https://sqlite.org/wasm/doc/trunk/index.md). However, these SQLite extensions are _server-side_ only.\n\nIn some cases, it's possible to add a SQLite extension to a SQLite WASM build, but require static compilation. I've only been able to make it work with extensions written in pure C, which you can see with [`sqlite-lines`](https://observablehq.com/@asg017/introducing-sqlite-lines) and [`sqlite-path`](https://observablehq.com/@asg017/introducing-sqlite-path). But in general, you can't use _loadable_ SQLite extension in WASM. \n\nThese extensions distributed on `npm` and `deno.land/x` only work in server-side JavaScript environments in Node and Deno.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":11,"value":"## How SQLite extension on NPM work\n\n\nOrhun Parmaksız's blog post [_\"Packaging Rust Applications for the NPM Registry\"_](https://blog.orhun.dev/packaging-rust-for-npm/) was a great guide to figuring this out! \n\nAs a recap: _loadable_ SQLite extensions are [dynamically loadable files](https://en.wikipedia.org/wiki/Dynamic_loading) that are platform dependent, depending on your operating system and CPU architecture. \n\nThe plan is to make some sort of NPM package that includes these loadable SQLite extensions. Since they are platform dependent, we can use the [`os`](https://docs.npmjs.com/cli/v9/configuring-npm/package-json#os) and [`cpu`](https://docs.npmjs.com/cli/v9/configuring-npm/package-json#cpu) fields inside of `package.json` to create platform-specific NPM packages of our extension. \n\nHere's an example of what that looks like for `sqlite-regex-darwin-x64`, the MacOS flavor of the `sqlite-regex` NPM package (for `x86_64` Macs, pre-M1). \n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":68,"value":"```\n$ tree .\n.\n├── README.md\n├── lib\n│ └── regex0.dylib\n└── package.json\n```\n_Source:_ [_sqlite-regex-darwin-x64-0.2.3-alpha.2.tgz_](https://registry.npmjs.org/sqlite-regex-darwin-x64/-/sqlite-regex-darwin-x64-0.2.3-alpha.2.tgz)\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":100,"value":"As you can see, this NPM package is very bare-bones. It doesn't even have any JavaScript files! But it does include the loadable SQLite extension under `lib/regex0.dylib`. The `package.json` for this file looks like:\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":91,"value":"\n```json\n{\n \"name\": \"sqlite-regex-darwin-x64\",\n \"version\": \"0.2.3-alpha.3\",\n ...\n \"os\": [\n \"darwin\"\n ],\n \"cpu\": [\n \"x64\"\n ]\n}\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":93,"value":"The `os` and `cpu` fields are most important here: that ensures only users who are on a Mac with x86 an CPU can download this package. \n\nLet's take a look at the sibling `sqlite-regex-linux-x64` package structure: ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":70,"value":"\n```\n tree .\n.\n├── README.md\n├── lib\n│ └── regex0.so\n└── package.json\n```\n_Source: [sqlite-regex-linux-x64](https://registry.npmjs.org/sqlite-regex-linux-x64/-/sqlite-regex-linux-x64-0.2.3-alpha.2.tgz)_\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":98,"value":"It's similar! But instead of `regex0.dylib`, which is MacOS specific, it's a `regex0.so` file, fit for Linux x86 platforms. Here's what that `package.json` looks like:\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":108,"value":"\n```json\n{\n \"name\": \"sqlite-regex-linux-x64\",\n ...\n \"os\": [\n \"linux\"\n ],\n \"cpu\": [\n \"x64\"\n ]\n}\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":111,"value":"Now, installing these platform-specific packages manually is annoying, and prone to errors. So, we'll create a generic `sqlite-regex` NPM package, that'll download the correct package for a user's platform. Here's what it looks like:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":112,"value":"\n```json\n{\n \"name\": \"sqlite-regex\",\n \"version\": \"0.2.3-alpha.3\",\n ...\n \"optionalDependencies\": {\n \"sqlite-regex-darwin-arm64\": \"0.2.3-alpha.3\",\n \"sqlite-regex-darwin-x64\": \"0.2.3-alpha.3\",\n \"sqlite-regex-linux-x64\": \"0.2.3-alpha.3\",\n \"sqlite-regex-windows-x64\": \"0.2.3-alpha.3\"\n },\n}\n\n```\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":116,"value":"The [`\"optionalDependencies\"`](https://docs.npmjs.com/cli/v9/configuring-npm/package-json#optionaldependencies) key does all the heavy lifting here: When you `npm install sqlite-regex`, it'll try to download all the packages in `\"optionalDependencies\"`. But since only one will \"match\" your platform, then only the correct package is downloaded. \n\nAs an example, when running `npm install sqlite-regex` on my x86 Mac, my `node_modules` directory looks like:\n\n```\n$ tree -L 1 node_modules/\nnode_modules/\n├── sqlite-regex\n└── sqlite-regex-darwin-x64\n```\n\nOnly the `sqlite-regex` and `sqlite-regex-darwin-x64` packages, nice! Let's take a look at the `node_modules` on a Linux computer:\n\n```\n$ tree -L 1 node_modules/\nnode_modules/\n├── sqlite-regex\n└── sqlite-regex-linux-x64\n\n```\n\nOnly the `sqlite-regex` and `sqlite-regex-linux-x64` packages, awesome!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":121,"value":"So with this strategy, we have top-level NPM packages like `sqlite-regex`, `sqlite-xsv`, and `sqlite-ulid`, which each download platform-specific packages like `sqlite-regex-darwin-x64` or `sqlite-xsv-windows-64` at install-time.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":47,"value":"### How SQLite Extensions in Deno works","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":49,"value":"Most of the heavy lifting is done by [`x/plug`](https://deno.land/x/plug@1.0.1), a Deno library for managing dynamic libraries in Deno.\n\nThe SQLite extensions for each platform are compiled on Github Actions and uploaded to [a Github release](https://github.com/asg017/sqlite-regex/releases/tag/v0.2.3-alpha.3). Then in the SQLite extension Deno modules, we [reference those uploaded releases assets](https://github.com/asg017/sqlite-regex/blob/main/deno/mod.ts#L4-L31) with `x/plug`, which will download the required library at runtime and load it into the database connection.\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":175,"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","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"}],"files":"$W32","stdlib":"1"}} 31:"$30:body"