Repository navigation
Found and fixed: a decade of AlaSQL
Andrey Gershun is one of the most brilliant people I have ever collaborated with.
Not because of what he creates. Because of how he creates it.
Most of us write code to find out what we think. We type, we squint, we investigate, we reflect, we delete, we try again. When Andrey is eagerly chasing a goal, the code seems to be merely a derivative of something that, to him, already exists. Already finished, somewhere out there in the ether, loading into his mind a hundred steps ahead of the caret.
That makes the keyboard the slowest part of the machine. More than once I have watched hundreds of lines arrive where nearly every variable was one or two characters long. Not out of laziness. Out of bandwidth. When the whole building already stands in your mind, you do not spend keystrokes naming the bricks. He just needed time to squeeze it out through his fingertips.
I counted, for the sake of this scroll, in the source of AlaSQL v0.1.10 from June 2015. 36% of all var declarations have a name of one or two characters. The favourites are s, i, db, r and j. Easy to type. Fast to edit.
I met Andrey for the first time in #71.
It was a coincidence. I was part of an indoor CO2 measurement device startup, and AlaSQL looked like just the right tool to let people slice and dice their data. Looking back, I should probably have focused on IoT security and product-market fit. But I was young (well, let's call it early in my startup journey), and preparing an interface for an imagined future customer with hundreds of sensors - allowing parents at a school to see which class subjects exposed their kids to consistently high CO2 levels - felt like the most pressing challenge on the roadmap.
Oh, back to the main story.
On 2 March 2015 I filed a limp little report: AVG lost its values when you used GROUP BY (something you really want if you are trying to show data per building, per teacher or per class subject). That same evening Andrey answered.
Got it. This is bug, I will find the reason tomorrow. Thank you for the issue!
He fixed it the next day. In the same thread I mentioned a test suite called sqllogictest, and pasted one of its queries as an example of how far things could go.
SELECT DISTINCT + COUNT ( * ) * - + ( + 40 ) + - 0 + - 59 / - 46 AS col1 FROM tab1 cor0Andrey replied,
:) Looks like Brainf*ck language.
The next day I filed #72. Six hours later he wrote, "Sorry, this is a bug in toString() function." Ninety seconds later came the reply I still remember.
Found and fixed. Thank you!
I think it was the theatrical exclamation mark that did it. Here was a person - well, a genius - who had built this sophisticated chunk of logic, and he was happy that a stranger found holes in it. I felt more than welcome. Across 2015 and 2016 we met in 266 issue threads. He wrote over a thousand comments in that time, and in 136 of them he says "Thank you". Generous with gratitude and stingy with keystrokes.
"Now we can release 0.1!!!"
My third issue, #75, was about UNION with CORRESPONDING. It turned out to be hard. AlaSQL kept rows as objects, so it had no idea of column order. Andrey worked on it for two months, quoted Molière along the way ("These forty years now, I've been speaking in prose without knowing it!"), and on 3 May 2015 he wrote.
I FIXED THIS!!! See test254.js. Now we can release 0.1!!!
My bug report was the last thing standing before AlaSQL's first real release. Version 0.1.0 went to npm that same day, codenamed "Venice". Eight weeks after my first bug report I was committing straight to his repository. My first self-merged commit added "Community generated feature requests" to the README. I like to think it was trust. I suspect he was also tired of reviewing my stream of README-tweaking pull requests.
We found a nice balance. I tried to find out what was wrong, and he fixed it as fast as his fingers could type.
A different era
AlaSQL was born in a different time. Connections were slow and every KB counted. Web Workers were exotic and slightly scary for most developers. ES5 was the cool JavaScript, and its adoption was fuelled by the Node 0.10 era. Node.js and io.js had just split up, and it felt like the ecosystem was fragmenting faster than ever. AlaSQL 0.1 shipped six weeks before ES2015 was even approved as a standard.
Writing a library meant writing for a zoo: IE8 was effectively an ES3 browser with Object.defineProperty, but only for DOM objects. IE9 was mostly ES5 but ignored 'use strict', while IE10 enforced it, so the same file could run under two different sets of rules. Older Safari and Firefox had partial ES5 depending on the feature and the version. A shim could give you Array.prototype.map, but it could not make an old parser accept new syntax.
The bugs were wonderful. In #73 someone's IE9 turned out to be quietly running in compatibility mode, which made it IE7. Saving files failed because IE9 had no Blob. Andrey's verdict, "As usual: good news and bad news" - CSV and XLS could be saved from IE9, while XLSX could not.
And through all of that, AlaSQL just worked. "Batteries included" does not cover it. It came with the batteries, a generator and a spare can of fuel. Open the npm package of v0.1.10 from June 2015 and you find builds for both browser and Node.js, preconfigured Web Workers you could switch on by changing one line, an SQL console for the browser, connectors for localStorage, IndexedDB, SQLite and Excel, a command-line tool, a tiny server, TypeScript typings (in 2015), a Meteor package and, for reasons I have never fully understood, a Prolog plugin.
Andrey kept asking one question: how can this tool be as helpful as possible for the person using it? AlaSQL abstracted away the hard parts and let people connect to their data via a browser in a way that was rarely seen at the time. That is why more and more people embraced it. In all of 2015, AlaSQL was downloaded 25,563 times from npm. In the month of September 2026, it was downloaded almost 1.2 million times from npm, and jsDelivr served the code another 1.6 million times.
Fourteen hours of hope
I was eager to get a complete picture of how well we were actually doing compared to the established players. SQLite uses a regression test suite called sqllogictest: about 6 million SQL queries of increasing complexity, each with a known answer that every new SQLite release must reproduce exactly. Unzipped, the suite is 1.5 GB, so it got its own repository.
I spent night after night writing a parser for the format of their test files. My first run in March 2015 only checked whether AlaSQL could parse the queries. Out of the 2,504,249 tests my script could understand, 36% passed.
A year later, by January 2016, we could parse and correctly process 87.47% of 5,945,362 tests, and a run took 5.9 hours. After each round of fixes from Andrey I would start it, go to bed, and look forward to hopefully seeing the percentage creep up a little the next morning.
But one of the test files kept me awake. One file that flatly refused to finish. It was excluded from the nightly runs. The line in my test runner that left it out still carries the comment I wrote next to it, typos and all.
/select5\.test/ // get all exept select5.test - as its taking days to completeDays. Every now and then I would run it on its own, hoping the latest load of fixes would bring the time down to something manageable. Six to eight hours, maybe, so it could brew overnight. I was chasing that complete glimpse of our actual coverage more than I am proud to admit. Little did I know that even the universe would not have survived the wait.
The hideous select5.test
select5.test was the file excluded from the nightly runs. It sets up 64 tables with 10 rows in each, then works through 732 queries of increasingly obscure select combinations, gradually moving from joining a few tables to joining all 64.
Joining across many tables was never AlaSQL's strong suit. A test log from January 2016 demonstrates this very well: a single query joining 6 tables took 0.7 seconds, 7 took 6.3 seconds, 8 took 67 seconds, 9 took 12 minutes, and 10 took 2.1 hours. After 14.7 hours I admitted defeat and stopped it at step 783 of 1,436. It is the only test log that survived those murky hours. I remember trying again and again, leaving it running for days, fiddling with the test script and tuning Node in different ways trying to understand what stood between us and a complete picture of our compatibility coverage.
Ten years later
Last week I ran the suite again. Same test runner, same configuration, every file except select5.test.
In 2016 we passed 87.47%. Ten years and 1,610 commits later we pass 89.54% of the same 5,945,362 tests. I must say, two percentage points in a decade felt a little thin.
But a win is a win. I sat down with an AI on my lap to look at what the improvements consisted of. We did not get far. "The test runner ignores the result types the tests declare, so it compares the values differently from SQLite. Would you like me to fix it for you?" Ehh, sure, let's see what you found. A faint memory of an ambitious plan to have both the test suite and AlaSQL adapt to different SQL dialects started to shimmer at the back of my mind, like hot air above a pan you forgot on the stove.
It turned out that about 220,000 of the failed tests had the right values but were checked the wrong way. Aww. Not impressed with my own work back then. A minute and a half later it was fixed, and the next full run passed 93.38% with AlaSQL v4.21.0. Nice jump! Even if the credit goes to the test execution and not to AlaSQL expanding its capabilities.
The old feeling of chasing a complete picture of our test coverage started welling up again. I was hooked now.
For ten years I had not run select5.test, mostly out of fear of reviving the feeling of defeat. Slightly traumatised, but too curious to leave it alone, I geared up for a deep dive with an AI now firmly holding my hand. We dug up the corpses of old logs to compare and ran the setup again.
The 9-table query that took 12 minutes in 2016 took only 88 seconds. Now, NOW, is the time we will see the end of this test execution, I felt.
After a bit of extrapolation of the numbers, reality hit me. 8x speed-up still left us with an expected execution time of 8.4 x 10^58 seconds. Thanks to the beauty that is the beast of exponential growth, the wait time would be roughly two hundred thousand billion billion billion billion times the age of the universe. So much for "taking days to complete".
I wanted to understand those nasty numbers better, and almost before I had finished asking, the answer plopped out of the AI.
Bare column names like
b1 = a2are not recognised as a join condition, onlyt1.b1 = t2.a2is. Every extra table makes the query ten times slower.
select5.test uses bare names all the way through and lists its tables in shuffled order. SQLite follows the keys from one table to the next, so a 64-table join query becomes roughly 64 small lookups. AlaSQL built every combination of rows before WHERE got to filter them. That is 10^64 combinations.
At 3:37 in the morning I typed "Please make a PR for #2910". Twelve minutes later #2911 was ready. Found and fixed - as someone I know would have put it.
AlaSQL v4.22.0 gets through all 732 queries in 5.1 seconds with every answer correct. For the first time the suite could run with select5.test included, and the score went from 93.38% to 93.39%. Outside select5.test and its slow cousin select4.test, the suite gained just three passing tests, so the fix bought speed, not coverage.
Run time for all 732 queries in
select5.test:
- 2016: 6.4 x 10^59 seconds
- 2026, before the fix: 8.4 x 10^58 seconds
- 2026, after the fix: 5.1 seconds
- 2026, SQLite: 0.44 seconds
The fix itself is no great piece of science, and I will admit those twelve minutes shook me a little. The bug had sat there for a decade, not because nobody looked, but because finding it meant holding the whole engine in your head at once. For ten years only Andrey and God had the guts to do that. Now, apparently, AI joined the club. But a codebase that only geniuses and machines can navigate keeps everyone else out. A good codebase allows itself to be understood by people piece by piece - and the aim for such a transition is almost as old as our collaboration.
The vision from #94
In March 2015, two weeks after we met, Andrey opened #94, "Alasql 2". Requirement number two said it plainly.
Modular structure: I want to separate parser and database backends from SQL core.
It turned out to be much tougher than expected to separate the compartments across the codebase. A library built to survive IE8, IE9 in disguise and a dozen half-ES5 browsers had grown into one tightly woven piece, and every thread you pulled was attached to three others. The topic kept coming back, both from us as active maintainers and from others who saw the horrors of trying to pull the codebase apart.
- 2015-05 in #192 proposed to "Rewrite parser and lexer", keeping the grammar rules for plugins separate.
- 2015-06 we tried to "Test with Browserify and ES6" in #280.
- 2015-11 @nickdeis wrote in #445, "I think the main goal here is to write ECMA6 modules of ./src", and on New Year's Eve Andrey replied, "I will join you on this topic after New Year! )))".
- 2015-12 @nickdeis delivered the first ES6 modules of the source with #491 "ecma6 and rollup, start of slim build", followed by #496 "plugin and ecma6: Added orderby and join".
Then the core team parked it. Not because the problem was solved, but because we had walked into the same wall often enough to know exactly where it was. Over the following years people kept bumping into it, each time from a different direction.
- 2016-05 in #676 @Daniel15 asked for a browser build without references to Node's
fsandpath, because bundlers that analyse dependencies statically fail on them even when the code never runs. - 2016-06 in #701 @weilitao asked for support for Meteor 1.3 and its ES2015
importsyntax. - 2017-01 in #809 @rosie9431's React build warned that the "require function is used in a way in which dependencies cannot be statically extracted".
- 2017-07 in #913 @iamvitali's Vue build failed on React Native modules, and @fmnxl wished the in-memory part could be imported without the file handling.
- 2018-08 in #445 @elipalmer asked "Has there been any recent movement on this ecma 6 refactor?" In my answer I wrote that I was moulding v0.4.6 "into typescript and ES6". The first goal was to untangle the "access everything via global scope" style, and I pushed it as the feature/rollup branch, which was finally merged today.
- 2022-12 in #1558 and #1561 @jimmywarting proposed esbuild with ESM to replace the concatenated build or at least to transition to ES6 syntax.
- 2022-12 in #1592 @AjManoj asked for AlaSQL to be updated so it could be imported as an ES6 library.
- 2023-01 in #1611 @jimmywarting proposed loading plugins with
import(), which had to wait until "all of alasql can also use the import syntax". - 2024-02 in #1877 @rohitspujari's Next.js build failed on
react-native, a module it never uses. - 2026-07 in #2516 @tps-flint complained that SheetJS takes 7.4 MB of the 9.3 MB install, whether you use spreadsheets or not.
So thank you to @Daniel15, @weilitao, @rosie9431, @iamvitali, @fmnxl, @elipalmer, @jimmywarting, @AjManoj, @rohitspujari and @tps-flint for stating the obvious: the code structure was inadequate for what AlaSQL should be. And special thanks to @nickdeis for pointing out the right solution all the way back in #445, and for providing pull request #491 to actually do something about it. And a special special thank you to @jimmywarting for converting Transaction, the expressions and Select into ES6 classes (#1575, #1589, #1607, #1609), and removing lodash, jQuery and IE8 support (#1569, #1599, #1566). That work is the reason we could focus on modules and not syntax in this release.
With the release today of v4.23.2, AlaSQL is finally built from a modular ES6 source all the way through. It is the first significant step towards a wave of improvements that Andrey's vision has been waiting a decade to ride: a real plugin system, builds that contain only what you need, and a core you can understand one file at a time. The variables might finally deserve longer names, once the code has room to breathe and bundlers take care of the tree-shaking and the minification.
A decade down the line, I still feel like I am carrying out Andrey's vision. The code is soaked in his feel for data and his grip on the quirks of JavaScript. What changes now is the shape around it. Yes, an AI helped carry the migration over the line, just as it found the holes in my test runner and the slow path in select5.test. But AI is not the point here.
It seems to me we had long since reached the limit of what the community could lift without Andrey's mind to navigate by. A modular source lets more people in, and their AI-assisted pull requests with them, to lift both the quality and the speed of AlaSQL.
Serendipity
I grew up in Denmark, and despite Scandinavians feeling very confident in the English language (not always rightfully so, as I came to realise after moving to Australia), I used to feel limited in my ability to express myself. I remember feeling outside the open-source community. Not because anyone kept me out, but because the language barrier scared me off a little. I remember reading about Aaron Swartz, who was helping shape RSS at the age of fourteen, and wondering what I could have influenced if I had felt comfortable engaging with people across the globe from a young age.
Instead I wandered in sideways, by chance and for unrelated reasons. Chasing the wrong priority in a startup, I stumbled into an open-source project at just the right moment, run by someone who was building something extraordinary and wanted to know where it broke. Nobody cared about my grammar. Andrey cared about my bug finding skills.
Ever since, AlaSQL has been my strikketøj. In Danish, strikketøj is your knitting project: the thing you pick up in a quiet moment and put down again. The jumper is almost beside the point. It is the repetition, the connection, the feeling that something is growing a few stitches at a time. You create something, and something shapes you back.
The branch that finally carried the modular source code into main had its first commit in June 2018, with the message "Better progress than perfect". It was more of a note to self, or maybe an encouragement to self, in the dark hours of hand-porting the code file by file, trying to find the best code structure for the future of AlaSQL. I do not think any code is left from those early stages of the port, but the principles and the vision for the structure have stuck ever since. The last rows went a good deal faster with a machine holding the other end of the yarn. But the pattern was knitted by hand, by a lot of hands, over a lot of years.
To every developer who has contributed over the years, poked holes, and figured out where the issue was: Thank you! (With a theatrical but sincere exclamation mark.) Special thanks and honourable mentions go to @nickdeis for your webpack work, @n-a-m-e for your early work on casing and empty strings, @bopjesvla for improving Postgres compatibility, arrays and NULL handling, @kanghj for wrangling Excel files and exporting them, @seb-ster for your local storage work, @akhaneev for fixing chains of OUTER JOINs and supporting String and Number objects, @forestfang-stripe for making MEDIAN, MIN and MAX work on mixed types, @sljohnsondev for helping with timestamp and BETWEEN work, @alsundukov for your work on EXISTS for subqueries and NULL handling in COALESCE and GROUP BY with CASE, @jimmywarting for your huge contribution to modernising the code base, @ambujsahu81 for fixing UNION results and INSERT with a subquery, @Julias0 for your work with async execution and raw CSV strings, @vishal6557 for your work with IndexedDB and error messages, @paulrutter for performance work, beating regressions of SQL logic and avoiding dirty caching, and @Anikesh0001 for cache invalidation and index creation.
And thank you so much to everyone who contributed during the last month: @20vision, @AaryavGangrade, @abhi478jeetur-rgb, @ammadakram, @dajiaohuang, @fixfon, @hgjoni20, @imsanupm, @joshuablac, @jsingh0026, @komal0000, @Lay-quq, @MirandaCavalie, @pratyushsinghal7, @shaktimaan00, @TheeCoderAhmed, @thelastbackspace, @ZainnQureshii, @zzoraemon043 and @mateuscmtropical who have really picked up the ball on pushing SQL compatibility towards 100%.
We still need all the help we can get to bring Andrey's vision to life: a helpful, fast and friendly library. select5.test now finishes well before the end of the universe, but SQLite still does it about twelve times faster. Somewhere in the source, the next fix awaits. We appreciate any and all contributions we can get. Come join the fun.
And if you feel outside this world of code, because of your language, your background, or a feeling that everyone else knows something you don't: step in anyway. Submerge yourself in a crew of people you do not know, who you have never met and probably never will. You do not need to be confident. You do not need perfect English. Better progress than perfect. You just need one bug report. Mine was about AVG(...).
Here's to the next decade - may it include fewer cosmic-scale queries, and perhaps finally a use for that Prolog plugin.
/ Mathias Wulff
Full Changelog: v4.23.1...v4.23.2