The Future of SQLite

If you feel like reporting the lack of SQLite support in iOS 13 as a bug, you can do so here:
https://developer.apple.com/bug-reporting/

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

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

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

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

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”).

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

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?

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   

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:

  1. 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.
  2. 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.

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.

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)

2 Likes

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

Thanks

Yes.

If (openDatabase == undefined)

1 Like

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

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

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

Any other ideas?

Thanks …

Does it work with Chrome on the phone?

No. I tried both Safari and Chrome. Same result.

There must really be some politics regarding this one … as a developer I can’t even imagine why you would want to remove support for something as ubiquitous as SQLLite from the platform!

The politics are discussed in this blog post:
https://blog.appstudio.dev/2019/09/the-future-of-sqlite/

I just tried running SQLSample1 on an iOS 13.1 iPhone 8 and on the Mac Desktop, with the changes to the settings. It worked on both platforms.

Give my sample a try:
https://www.appstudio.dev/i/SQLSample1/