The Future of SQLite

ghenne · July 10, 2019, 2:08pm

Apple has announced that the next version of Safari will no longer support SQLite. This has been confirmed in the beta of iOS 13 - it’s gone.

There’s no need to panic. There are workarounds, which I’ll get to later in this post. Also, there are no indications that Google will do the same with Chrome.

Some History

SQLite was introduced into browsers many years ago as WebSQL. It was a great idea: SQLite is small, powerful and solid. You can make great apps with it. The problem came from the standards committee. W3C prefers a clear standard with alternative implementations: saying “Just use SQLite” isn’t good enough. They decided, in 2009, to deprecate WebSQL and replace it with IndexedDB.

(Behind the scenes, I hear there were some heavy politics going on. Microsoft and Oracle were both very unhappy with WebSQL using SQLite - they each had their own established database software and did not want an upstart competitor to become standard.)

Regardless, Safari and Google continued to include WebSQL in their browsers. FireFox and IE did not. https://caniuse.com/#search=websql

The suggested replacement, IndexedDB, is not a SQL database. It’s more of an indexed file system. It’s been criticized for being slow, clumsy and inconsistently implemented. For more information, see this article: https://developers.google.com/web/ilt/pwa/working-with-indexeddb

Alternatives

Web Apps on Android: No problem - Chrome continues to support WebSQL.

Web Apps on iOS: Either use Chrome to run your app, or make your app a native app.

Native Apps for iOS and Android: Use the Cordova plugin for SQLite (as you probably have been). Just as this to your config.xml - no changes to your code.

<plugin name="cordova-sqlite-evcore-extbuild-free" source="npm" />

Desktop Apps: Another reason to use Electron. Use the SQLite plugin https://www.npmjs.com/package/sqlite

edgari · July 11, 2019, 7:41pm

This GREATLY concerns me as one of my problems is that I have no control when users update their iOS. But If I am reading this right, if a device where one of my app runs, which is a bunch – because I am talking of about 23 apps – gets the next iOS I will be out of luck as far as running my web apps right? You say make it a Native app, is that a 100% for sure solution? Apple can’t block SQLite from running that way?

ghenne · July 11, 2019, 8:40pm

Correct - you can safely use SQLite, but you have to run as a native app. Apple doesn’t have anything against SQLite - they are removing it from Safari, since it isn’t part of the spec.

Remember too, that iOS 13 is currently in Beta 2. Apple could change their mind, but I would not bank on it.

You could add this code your app to test for this happening:

if (typeof(openDatabase) !== "function") {
	NSB.MsgBox("Please run using Google Chrome");
}

ghenne · July 11, 2019, 8:42pm

If you feel like reporting the lack of SQLite support in iOS 13 as a bug, you can do so here:

pedau · July 11, 2019, 11:55pm

Just to confirm, adding

if (typeof(openDatabase) !== "function") {
	NSB.MsgBox("Please run using Google Chrome");
}

will check whether SQLite is supported on the browser?

Thanks

ghenne · July 12, 2019, 11:57am

Correct. It checks if ‘openDatabase’ is a valid function - if not, it suggests using a different browser.

edgari · July 12, 2019, 1:39pm

If I go the native way, can I still deploy from my server?

ghenne · July 12, 2019, 5:27pm

For Android, yes. For iOS, you need to go through the app store or use enterprise distribution.

kevbob · July 12, 2019, 7:51pm

One problem I see is that with a PWA on iOS if you run it in Chrome (to get access to WebSQL) you can’t save your app to the Home Screen. (Chrome on iOS doesn’t allow “Save to Home Screen”).

ghenne · July 12, 2019, 8:18pm

@kevbob is correct. If you want your app to be on the home screen and it uses SQLite, you will need to go native.

lotsofcows · July 15, 2019, 12:22am

What is the speed difference in SQLite getting say 200 records vs JSON.parsing in a stringifyed array of 200 records? Is there a number of records where SQLite becomes essential?

ghenne · July 15, 2019, 11:54am

It really depends on what you are doing. SQLite’s advantage comes in complex queries that draw data from multiple tables. Here’s an example of an SQLite query which would be several pages of code if you’re working with JSON data:


        SELECT 'Died' AS event, death_date AS event_date, 
          '' AS desc_1, '' AS desc_2, '' AS desc_3,
          ${allFields}
        FROM animal_main
        WHERE death_date IS NOT NULL AND animal_main.deleted_at IS NULL
        UNION ALL
        SELECT 'Sold' AS event, depart_date AS event_date, 
         '' AS desc_1, '' AS desc_2, '' AS desc_3,
          ${allFields}
        FROM animal_main
        WHERE depart_date IS NOT NULL AND death_date IS NULL AND animal_main.deleted_at IS NULL
        UNION ALL 
        SELECT 'Bought' AS event, arrive_date AS event_date,
         '' AS desc_1, '' AS desc_2, '' AS desc_3,
          ${allFields}
        FROM animal_main
        WHERE arrive_date IS NOT NULL AND arrive_date <> birth_date AND animal_main.deleted_at IS NULL
        UNION ALL 
        SELECT 'Sale Group' AS event, farm_salegroup.farm_salegroup_date AS event_date,
          sale_group.farm_salegroup_id as desc_1,
          sale_group.sale_group_permit || sale_group_errors || case when length(permit_valid_from) > 0 then
          ' From: ' || replace(sale_group.permit_valid_from,'00:00:00','') || ' To: '
          || replace(sale_group.permit_valid_to,'00:00:00','') else '' end as desc_2,
          farm_salegroup.farm_salegroup_name AS desc_3,
          ${allFields}
        FROM animal_main
          INNER JOIN sale_group ON sale_group.animal_id = animal_main.animal_id
          INNER JOIN farm_salegroup ON sale_group.farm_salegroup_id = farm_salegroup.farm_salegroup_id
        WHERE sale_group.deleted_at IS NULL AND animal_main.deleted_at IS NULL 
        ORDER BY event_date DESC, event

pedau · July 16, 2019, 1:49am

This is also a big problem for me. I’m not keen on going native for ios.

I had a look online and found the following:

I found from this site that the Shortcuts app can create a shortcut on the home page for Chrome apps. This might solve the problem of not saving it to home screen.
Someone said in a forum that you can save a webpage on the home page using Safari and then change the default browser to Chrome. When the go to open the shortcut, the app will open in Chrome.

I’m not sure if either of these actually work as i don’t have an Apple or know anyone with one.
If anyone can please clarify this, it will be much appreciated.

kevbob · July 23, 2019, 3:12pm

This works to create a shortcut on the home page, but it looks like it is running in the browser with an address bar etc. - your app doesn’t have access to the full screen.

ghenne · September 20, 2019, 12:19pm

It turns out there is a setting in iOS 13 to turn WebSQL back on:

Settings
Safari
Advanced
Experimental Features
Disable Web SQL (at the bottom)

pedau · September 22, 2019, 12:04pm

Is there a way to know if WebSQL is enabled from within the app?

Thanks

ghenne · September 23, 2019, 12:27pm

Yes.

If (openDatabase == undefined)

ghenne · September 23, 2019, 11:05pm

It’s also possible to turn SQLite back on in Desktop Safari:

Turn on the Developer’s menu. (Safari Prefs… Advanced)
On the Developer’s menu, select Experimental Features. Uncheck ‘Disable Web SQL’

MaxPeck · September 26, 2019, 2:04pm

Have turned off the “Diable Web SQL” option. Still getting the “SQLite not supported” error. Even restarted the phone.

Any other ideas?

Thanks …

ghenne · September 26, 2019, 2:08pm

Does it work with Chrome on the phone?