SQLite Database Sharing

Hey All,

I now, I’ve dug through the forums, and I know that “database on iCould Drive” is a bad idea - but this may be different…?

I have an app using a local SQLite database stored o the device. What I REALLY would like is for that database to be able to share its data between other devices within (for example) an associated iCloud (drive?) account.

Don’t want to reveal too much, so let’s call the app “Bob’s Car Stuff” - geared to companies with 1-20 employees. Employee goes on location and creates an “entry” to the database (Job Name, Location, car model, date, etc. - (my current table count is 8 - all text, and all less than 100 characters). For that “job”, various options are filled (ex. Tinted windows, pin-striping, leather steering wheel cover, and a description of the work done, etc. - on my database, 12 columns)

Point being, this isn’t a big database with a boatload of data - it’s small without any “large” data. Works great on a “local” SQLite database, but I need the data to be shared across all devices associated with that “account” (iCloud Drive access…?)

I know that “storing a database on an iCloud Drive” is a bad idea - but this is a database that won’t be “hit” very often, and “new” data from other users doesn’t really need to be available to other users while using the app - is there a way to check to see if the database is “busy” - or is there a better solution (I would REALLY like to avoid 3rd party plug-ins or subscription-based solutions)

Is there a way to connect to the database, do whatever, and disconnect - and, if so, is there a way to check to see if “someone else” is connected - and try again after XYZ time…?

Thanks in advance!

Marc

1 Like

It’s a bad idea but it sounds like you are set on it. Traditionally this is what you’d use a database server for.

But if you’re intent on going through with this idea, take a page from the way other databases do it, and create a file alongside the database while it’s in use. Like if you have MyData.db create a file called MyData.lck to indicate that the database is in use. Before another instance of the app opens the database, check for the existence of that file, and if it’s there, show an error message that the database is already in use. Once the first instance of the app finishes with the db, delete the .lck file.

If you do this, you’re in for a lot of pain as @Christian_Wheel says. You could have a device that is the “DB Server” that implemented API services to update a central SQLite database using a Web Project.

2 Likes

It is called „bad idea“ because it is.
Multi-User or Multi-Device access to a single user database is calling for trouble. It can go wrong and it will, sooner or later.

Get a copy of CubeSqlServer and let it manage access to the SQLite file.

Check out SQLabs

2 Likes

CubeSQL is free for up to two users.

“cubeSQL with up to 2 concurrent connections is now Freeware. Request your free key today!”

Setting up a PostgreSQL database is free and pretty easy with minimal research. On a Mac, the PostgresApp makes it particularly so.

1 Like

I am new to SQL, and have heard all good things about Postgress.
Was curious anyway to know if an app could open a SQLite database only for reading, so remotes could safely retrieve data?
and
The version of the app running on the same computer could perform the writes?

Thanks all for the feedback and suggestions. Y’all have convinced me - lol - bad idea. I’ll check out the suggestions y’all gave and let you know.

Thanks!

Marc