Using SQLite on Mac OS

1

Hi,
is it possible to run an AppStudio app in the desktop browser (Safari) on Mac OS 15 (I have 15.0.1)? SQLite is obviously installed on my machine, I can open SQLite locally and run commands without problem. But it doesn’t work from AppStudio. I can use WASM and run the app in the desktop browser without problem, but that stores the data in localStorage obviously.
By the way, I found that it doesn’t matter what name you put in the SQLOpenDatabase statement - “localStorage” or anything else - it always looks at localStorage it seems.
Kind regards
Thomas

2

Support for WebSQL was dropped in Safari 12.1. How are you trying it out?

You are correct: SQLite saves its data in localStorage. Browser apps are not allowed to save data outside of the browser’s sandbox - allowing that would introduce huge security problems.

3

Hi George,
I ran SQLite directly in Terminal, which worked without any problem. I then ran AppStudio, and tried one of the examples, first without activating WASM which failed, then after activating WASM which worked. I also tried the NorthWind sample file, which also worked (Start in Desktop Browser - Safari).
Sorry, I didn’t use AppStudio for quite a while and lost touch a bit. Now I have more time and intend to dig into all the new things a little more.
My understanding regarding SQLite, current status, on Mac using Safari:

  1. SQLite databases are no longer stored in the file system directly (which they used to be, possibly a long time ago), but stored in localStorage. This applies to use via Safari (possibly other browsers which I haven’t tried) - direct use via Terminal works.
  2. You can have separate SQLite databases, with their own names, like Northwind.db. But you can’t open(attach) a 2nd database in a running application, at least for the moment. They are still all stored in localStorage, so the size restrictions for localStorage apply.

Took me a while and some investigating to find this out - I had been searching for the database file names using Finder, without success.

Different subject:
I suspect the guy who wanted to find out the tables existing in a DB still hasn’t quite understood what my solution was and that it requires a loop to print out the names of the tables. At least his latest comment sounds like some misunderstanding.

And 1 more thing: MSGbox still breaks a success/error function that’s triggered from an SQLight query, in the sense that anything behind a MSGBox statement isn’t executed. But having the database statements executed synchronously when using WASM makes life a bit easier even if there’s a performance penalty.

Kind regards
Thomas

4

Running in Terminal is completely different that running in the browser. Browsers have carefully restricted ability to interact with the computer they are running on - you can imagine what could go wrong if a web page is allowed to read or write data anywhere on your computer.

Updating your understanding:

  1. SQLite databases are indeed no longer stored in the file system directly. This closes off a security loophole. (It was already very restricted before)
  2. Yes, the storage restrictions for localStorage do apply.

This is still an evolving area - lots of people use SQLite with web pages. It’s a great tool to have.

MsgBox will always behave like this. Async programming is tricky, but necessary for web pages.