ICU (Unicode) support?

[mindplay-dk]

Is there any particular reason you ship a binary without Unicode (ICU) support?

You ship with the full-text search extension, which makes sense - but how is full-text search any use without Unicode support? Literally all the text on the internet is in Unicode format.

I can understand shipping SQLite without Unicode support for things like internal databases in apps, or the system registry in your computer or phone - but (I would assume) a primary reason people are interested in SQLite in Deno, is to build microservices, or smaller/simpler web apps, and I can't imagine what those services or apps would even be doing that doesn't require Unicode support?

Maybe this is a cultural divide of some sort? But I swear, I have researched this topic everywhere, and the only explanation I can find is, the ICU extension is "big" and makes some things "slow" - and I can understand this position from SQLite itself, being designed and optimized for use as an embedded database.

But shipping this as a Deno package is very different from the SQLite default use-case, I think? Are we more concerned about being "small" and "fast", or being actually useful?

I understand it's possible to build the extension from source and load it myself, but I'm just a humble web developer, and Unicode support seems very much like something basically everyone would need?

I can't understand how people are building anything with SQLite without Unicode?

Or are they all going out on their own to solve this problem first? am I just dumb or lazy? 😅

[mindplay-dk]

To the best of my understanding, SQLite should at least support storing Unicode UTF-8 strings?

So I attempted to run a simple query SELECT upper('abc æøå') just to see what happens, and:

I also tried running PRAGMA encoding='UTF-8' first, same result.

I then swapped to npm:libsql, which runs the query, but of course return ABC æøå, since there's no ICU support there either.

I found this article, which sums up 5 ways to handle Unicode, all of them slow or impractical in various ways. So is custom collations and bad performance what people put up with in JS? Or does everyone build their own binaries from source?

[michaelmanus]

I was able to reproduce your issue with a straight sql string - db.prepare("select * from table where somerow = '🦄'").run();

However, if it is parameterized, there is no problem. db.prepare("select * from table where somerow = ?").run(["🦄"]);

So this means it is practically not a big deal.

sqlite's CLI does allow unicode literals, though, so I suppose it should be supported.

[mindplay-dk]

Can confirm, it works with parameter binding. 👍

sqlite's CLI does allow unicode literals, though, so I suppose it should be supported.

I think most clients allow it? I guess queries containing unicode literals might be rare - I just did it for testing. 🙂

I guess I've kind of mixed two different issues into one though.

Really the main thing that baffles me is why no client ships with something as basic and essential as Unicode support?

I know ICU is "big" and makes things "slower". I also understand there are some issues with OS kernel differences, leading to practical subtleties around DB file portability. Yet, the internet runs on Unicode - everything right down to something as trivial as JSON requires Unicode.

So I just spent another half day on this, and finally came up with a tolerable solution - this relies on a lot of fragile, user-supported tooling though.

Do you think it would make sense to bundle some loadable extensions like nalgeon/text with the package? (I read that the official icu extension is "big", but this one is just ~64KB.)

Alternatively, would it make sense to integrate sqlpkg somehow?

Perhaps providing a function like e.g. getExtension("nalgeon/text") which would download and install sqlpkg locally in the project, run sqlpkg init and sqlpkg install nalgeon/text, and then return a Promise<string> with the resolved path, e.g. .sqlpkg/nalgeon/text/text.so.

Alternatively some maybe some way to literally import from e.g. @sqlpkg/nalgeon/text to get an installed package and path? Obviously that's way outside the scope of this project, I'm just airing the idea in case you find this approach preferable to including a loadable extension.

I'm just wondering how we can save the next person from a day or two of adventuring through a long list of half solutions and bad ideas before discovering how to do something as basic as just searching and sorting by Unicode strings. 😅

[DjDeveloperr]

I don't understand how it is a big deal - if you need to use ICU extension it is possible to load extensions with the library. And as for unicode encoding support in statements (not parameters - which was already there), I added that in latest commit and tests cases supporting it. It seems that it is possible to bake in ICU support to the binary but most SQLite3 clients do not seem to do that, at least by default.

A separate thing for making the extension easier to load would be good.

[mindplay-dk]

I don't understand how it is a big deal

I don't understand how it's not 😄

literally every computer and the entire internet is unicode - I don't think I've seen an ASCII file in 10 years.

how does it make sense to ship anything without unicode support.

It seems that it is possible to bake in ICU support to the binary but most SQLite3 clients do not seem to do that, at least by default.

because SQLite was designed to be small enough to embed on extremely low spec phones and devices

that's just not the scenario if you're building something with Node

there is no JS runtime that even supports ASCII without workarounds, it's all natively unicode

yet for some reason no one ships an SQLite binary for Node that you can actually just use for anything useful out of the box

this isn't by any means a critique of your or your package, but I have long since given up on SQLite

it's just not worth the hassle 😌

on top of that, I discovered pglite which is literally postgres in a 3 MB binary

if we can have a proper database in 3 MB why would you even consider SQLite for anything that touches the web? 🤷‍♂️

[DjDeveloperr]

Storing/retrieving unicode is there, it is actually supporting it fully in the query engine that is missing, am I getting it right? And latter can be loaded up as an extension for cases it is really needed. I'm not fully understanding the need but I'd like to learn. Could you provide some example use case that doesn't otherwise work that is commonly used by web apps for instance?

[mindplay-dk]

Mainly case-insensitive search, and locale-aware sorting. I don't know that most apps can do without this.

Lack of unicode normalization also means that e.g. "é" (U+00E9) and "e" + "´" (U+0065 U+0301) can't be compared, so:

SELECT 'é' = 'é';  -- returns 0

The LIKE operator isn't aware and can't search by character classes, so:

SELECT * FROM users WHERE name LIKE '%n%';  -- works, but doesn't find strings with 'ñ'

Operators like UPPER and LOWER don't really work either for words with accented letters, and so on.

There are practical reasons why every single database product ships with unicode support - and every single digital product in general, for that matter. Literally the entire internet and every file on your computer and most your devices use unicode.

I can't even think of a use case for a database with no unicode support, at least not in a web context.

I'd say the only place SQLite makes sense without a unicode extension is in the context of things like microcontrollers or maybe game engines or other real time, extremely performance intensive or super low resource scenarios.

It's no use in any setting involving things like people's names or text content where any kind of searching or sorting is required - which is practically any application I can think of.

may I ask what you're using it for? 🙂

[DjDeveloperr]

I did some testing, static linking with ICU on macOS causes +33.41 MiB increase in sqlite3 binary size. It's not acceptable to ship that as a default. I can add a separate entrypoint that downloads alternate binary (_icu, baked in ICU support)

[tracker1]

Just my own $.02, been working on an mssql driver (rust + ffi from Deno/TS) with various hiccups along the way... the unicode support is the most significant portion of my rust driver. So I can understand not wanting to add like 1.5mb to a payload for Unicode support. Just curious, have you checked the node:sqlite implementation? Bun is the only odd one out there, I'd been thinking of creating a shim/overlay to get the devex that I want (using statements/dispose for connections/transactions, dapper-like methods).

completely aside: my bigger issue is with my desire to support windows auth with the current user.

[DjDeveloperr]

What I ended up going with is dynamic linking ICU on linux/macOS and making it opt-in. On Windows since it's more complicated to link to ICU, I added static linking, so not a 1.5mb increase but 30mb. 1.5mb is if you dynamic link I think, and that adds a system library dependency.

I'll be shipping both non-ICU and ICU binaries in next version (which I plan on stablizing with better-sqlite3 API and lots of performance work)

I'm curious about the ICU related work you're doing in the Rust driver. I have been tackling improving performance especially in string-heavy tables and I've gotten promising results. I'm going to publish more about the tricks used to make all the driver code hot and JIT friendly, the same tricks could be used by other native drivers.

[tracker1]

Wild... the driver I'm working on is for MSSQL specifically, and the Unicode support is mostly just sending UTF16 to the server (NVARCHAR) since SQL Server can handle translation to/from UTF-8 collated VARCHAR fields internally... so I'm not really supporting all of Unicode... I was just trying to reduce the binary size a bit, and what Unicode support I did have was a fair amount of the payload... I'll probably shift out the other side to JS if I can, reducing the binary a bit more.

So not directly or indirectly related to sqlite.

[mindplay-dk]

I've been having the same discussion with someone from the PGLite team, and they came up with an (unfinished) approach that might work for PostgreSQL, where only the ICU library itself is linked, and the ICU data gets loaded on demand.

I understand if this isn't possible due to file size constraints. 😌

I still don't understand who actually builds products where sorting and searching doesn't need to work? Except maybe products for US and certain western markets only, or products where (you hope) you'll never need text based sorting/searching. YMMV, I guess. 😊

[tracker1]

@mindplay-dk I can't speak for a anyone else, but I'm in the US, and have mostly worked on localized product categories, so it's rarely been an issue for me in practice. Most of my work could be limited to just US-ASCII 0-127 relatively easily. If not for a desire to use emojii and the like in text comments.

Even then, for searching/sorting a lot of the time, even then you can collate to levels that are insensitive to diacritics and case... this is where sorting for different cultures tends to break, a lot in practice. I think people just get used to it. As long as it's correct-ish. There are lots of articles out there about how sorting is broken for different cultural norms (like character position in a given localized alphabet).

For that matter, the biggest issues I have with sorting often come down to casing, not matching prefixed number sets inside strings, or a desire to treat characters outside letters and numbers effectively the same, or at least interchangeable... This is for file/directory sorting mind you, but still.