What I learned trying to make something useful with IndexedDB
12 Mar 2017I started working on a little project that mainly involves writing a user script for a website that stores some stuff locally and makes a new page with it. When I started thinking about trying this, I remembered vaguely reading about WebSQL and something else like that being in browsers. When I started trying to actually use them, I discovered some rather bare-bones poorly documented stuff, so I thought I’d write about what I figured out to maybe save somebody else some headaches.
First off, WebSQL
It seems that some people thought of making a thing that gives a website what is essentially a local SQLite database a while ago, and poked around at it for a bit, then got tired of it. Chrome has some basic stuff implemented, but Firefox seems to have refused to implement it at all, citing how “It’s basically SQLite” doesn’t really cut it for a documented standard. With that, the Chrome team seems to have given up on it.
What you do have, at least in Chrome, is a thing that will let you declare the existence of a SQLite database and run some queries against it, probably. It seems to have transactions and an executeSql command, and the executeSql command does actually support parameterized queries. The transactions don’t have any explicit commit or rollback commands, though, and I’m still not sure how to get the results of a statement that you executed. It seems that there’s a callback function passed to executeSql that gets it, according to this website, not that there’s any real documentation anywhere.
I think it was the lack of methods on transaction objects that tilted me towards giving up on WebSQL. Not that I needed it, but it sure implies that the whole API is half-baked and hasn’t been touched in years, and there’s no plans to improve or document it. Still not sure if it actually returns the number of rows affected by write queries, or if there’s anything for schema version management, or any hope of having named parameters in parameterized queries, or what level of advanced SQL is actually supported, or how errors are handled, or how to see what the current schema actually is, or any number of other things.
IndexedDB to the rescue?
IndexedDB seems to be the way that web standards are going, and it actually has support in multiple browsers and at least some real documentation on MDN and a nice little tutorial. Let’s give that a try, then! It’s basically a pretty standard, if bare-bones, NoSQL store, storing JSON objects by keys.
I’m not really a fan of NoSQL databases in general, but they’re at least useful for some purposes and have known standard limitations, so I’ll refrain from criticizing IndexedDB based on the lack of ability to do joins or otherwise relate tables to each other. This leaves plenty still to criticize though.
Schema Management
First off, the IndexedDB system does come with schema management. You give it a schema version when you open the DB, and if the database doesn’t already exist or isn’t at that version, then it fires an onversionchange event. This callback is the only place where you are allowed to change the schema. Backing up a bit, everything that happens to an IndexedDB happens in the context of a transaction, and transactions can have one of three security levels: read, readwrite and schemachange. You can create read and readwrite transactions yourself, but the schemachange transaction is created for you, and you can only get it in the onversionchange callback.
It’s in the docs, if not exactly prominent, how all this works, and seems pretty reasonable. Once you have your schemachange transaction, you can call various functions to add or remove tables and indexes. Oh, and you can also change the name of a table and index - that’s a headline feature in IndexedDB 2.0, which tells you a lot about the state of the infrastructure.
Querying
Querying is quite limited compared to SQL databases. You can retrieve objects by the primary key they were stored by. That’s about all you can do unless you add an index to one or more fields.
If you have an index, you can query it by it’s key by exact value, or you can get a cursor with a key range with a minimum and/or maximum value and a direction. Notably, the direction parameter isn’t documented anywhere that I can find. The only way I could find to figure out what you’re supposed to pass into the relevant functions was to actually read the code - you pass in one of the strings in the IDBCursorDirection list as strings to openCursor.
If you want to query on multiple fields, even multiple indexed fields, or use more complex search conditions, or sort by more than one field, or a field other than the one you queried by, well too bad. You just have to do a supported cursor range query that you know will get you all of the data you want, and then do any other filtering and sorting in JavaScript after you retrieve it.
Add, Update, and Delete
I grouped these together, because they all work pretty similarly with the same limitations. Specifically, they all only work on a single record at once. For add and put, you pass the single object affected, and for delete, you pass the primary key. That’s about it, works as expected, no bulk operations. It’s simple enough to do, say, a bulk add in a loop though. Don’t even bother asking about conditions - you just have to use the querying functions to get the data, filter manually to find the ones you want to delete or update, and submit those one at a time manually.
Other Tips
On the good side, every change to an IndexedDB database table happens in the context of a transaction, which you open with db.transaction('tablename', 'readwrite'). This returns a transaction object which you call various methods on to read and write things. All of the reading and writing is async, and it doesn’t use any of the nice new Promise stuff either - instead, you use onsuccess, oncomplete, and onerror to handle the results. Note that if you are trying to do something like write to the database right before a page load happens, you don’t know how fast the old page will clear out or how fast the transaction will complete, so it will be unpredictable whether your data will actually write. If you’re doing something like this, you’d better make sure the page load is only triggered in the transaction oncomplete method if you want your data to be written reliably.
It isn’t immediately clear exactly what causes a transaction to either commit or rollback. Might have to do with time or the object being GC’ed? In any case, it seems to work fine to keep on doing reads and writes on a transaction as long as you need to in the same code block. I wouldn’t try to hold onto one while waiting for a network call to return or a user action. I have no idea how, or if, it’s possible to deliberately commit one after completing one atomic set of changes and then opening another one to do something else. I suppose you would start your second set of things on the first transaction’s oncomplete, but I don’t know how long it waits to run that after the changes finish or the transaction object goes out of scope.