also in Dutch here
Being hired as a java consultant (which is what happens to me a lot) you don’t often encounter SQLite. I never worked with it in an assignment, and my colleagues looked slightly hazy when I asked. Sure, there’s a JDBC driver so no technical hurdles there, but as it typically runs embedded, there’s no connections to open, and just a single user. Although, there are ways around that.
On the other hand: it’s the most used database in the world! It’s their claim, but I believe them! It’s free, powerful ánd simple to use and has minimal footprint. So you’ll find it on your phone, an IOT device or bundled in some app (the license is very permissive). So if your desktop app has a lot of data to store locally, you can just use SQLite and execute queries using SQL.
Last summer I saw a presentation by Steven Sanderson. Also not well know among java developers. He works at Microsoft on .Net and blazor. Check out his github or his blog.
He showed off this new feature in blazor, enabling users to download up to like 100.000 records in a single request and then easily and lightning fast filter and sort them in a UI, using SQLite as a WASM compiled runtime in the browser. Imagine: just last year, I had discussions at a client, about the maximum amount of rows a user was allowed to see in one go, maybe 500…‘we have to think about performance!’.
So…
I went to see if something similar exists for java. That is, of course you can download 100.000 table rows in JSON and put them in your local SQLite using inserts, right? But that doesn’t sound very efficient! Binary would be better, but wait, why not in the actual format that SQLite uses? !
Enter SQLighter…
I took a really good look at the docs, held my breath for a month or two, and created a library that outputs the binary format that SQLite uses. It’s not feature complete in that it doesn’t support views, just a single table but other than that, you can create databases of any size, using any types for the values.
Below you can see how you would create a database with values (can be any source, has to be tabular).
- Create a instance of
DatabaseBuilder, - create new records with
new LtRecord(...), - add values (
LtValue.of(...)) - and ‘insert’ them with
addRecord. - Finally invoke DatabaseBuilder.build().
Your database now exists in memory. Be aware, this is not SQLite, it’s its binary file format, that you can now write to a local file, or an OutputStream, for instance one belonging to HttpServletResponse.
Also included, a little util (ResulSet2SQLite) that uses the JdbcMetaData to transform a row in java.sql.ResultSet into an LtRecord.
The (serverside) performance is comparable to that of jackson, so nothing will stop you using it in a REST api.
Complete example with dummy values (from the demo):
public Database getAllCustomersAsSQLite() {
DatabaseBuilder databaseBuilder = new DatabaseBuilder();
databaseBuilder.addSchema("customers",
"create table customers (name varchar(100), email varchar(100), streetname varchar(100), housenumber integer, city varchar(100), country varchar(100))");
final RandomStuffGenerator generator = new RandomStuffGenerator();
long rowid = 1;
for (int i = 0; i < 100_000; i++) {
LtRecord record = new LtRecord(rowid++);
record.addValues();
String firstName = generator.generateFirstName();
String lastName = generator.generateLastName();
record.addValues(LtValue.of(firstName + " " + lastName),
LtValue.of(firstName + "." + lastName + "@icemail.com"),
LtValue.of(generator.generateStreetName()),
LtValue.of(generator.generateSomeNumber()),
LtValue.of(generator.generateSomeCityInIceland()),
LtValue.of(generator.generateIceland()));
databaseBuilder.addRecord(record);
}
return databaseBuilder.build();
}
Run it on your machine?
git clone https://gitlab.com/sander-hautvast/sqlighter.git
cd sqlighter/demo
bash start_api.sh
bash start_ui.sh
Or in some other terminal (cmd):
cd sqlighter/demo/api
mvn -f api/pom.xml -DskipTests clean spring-boot:run
cd ../ui
npm run dev
Go to https://localhost:5173/ NB. https and Chrome only…
The demo UI is built using Lit. This javascript/typescript framework is the current flavour of the month where I work right now, but I really like it’s simplicity.
So I did also test in Safari, but it wouldn’t start OPFS and Firefox is sadly still implementing that. It’s still an upcoming standard for working with files in the browser. Haven’t tried Edge, but in theory it should work (just like Safari).
For those that don’t want to come near Chrome within 100 ft, you can always try the branch where I used sql.js. (But beware that that also depends on postgres, which I included to show how you can combine SQLighter with other databases).
So I switched from sql.js to the official WASM release of SQLite. In the process I also needed to add webworkers, OPFS, https-only (OPFS wants it), and extra headers. Took me a while to get it all working, but great fun in the end.
I borrowed some Lit code for enabling virtual scrolling. It came with Parcel, but I had to switch to Vite, because I wanted to use the ESM wrapper for SQLite/WASM https://github.com/overtone-app/sqlite-wasm-esm/) which only works out-of-the-box in Vite. This small wrapper let’s you import SQLite in your project, the way you’d want nowadays. I found both Parcel and Vite a joy to work with compared to webpack.
∞