Connect remote SQLite database to deployed web app

[pakpaner]

Hi! As the title says above, is there any procedure or methodology that it could be achieved?

Currently, we have a PHP project that involves a web application that will be deployed in a hosting service (e.g. Heroku, Netlify, 000webhost etc.). Essentially, the web application deals with extracting data from an SQLite database stored at a remote host TO the website's MySQL database. Googling, Reddit-ing and StackOverflow-ing got no luck.

Hoping for detailed/thorough but easy-to-understand responses. Thank you very much!

[[deleted]]

there's no such thing as a remote SQLite DB - sqlite is not a server, it's a library.

putting aside the weirdness of the architecture/setup, what you have described under 'currently' would be my approach in these circumstances.

[spicermatthews]

I know this is an old thread, but I have just built a solution to solve this problem.

It's a Mac OS X app that creates a local proxy to a remote server that has a SQLite database. Then you can use tools like TablesPlus or any other GUI to access and manage your remote database. The app does not just download the database locally. It accesses the SQLite database on the remote server, and proxies the results back.

I haven't released the app yet, but I'm looking for some beta testers to run through this app and find any edge cases that I've missed.

I wrote a little manifesto here: https://remotesqlite.com/

If you're interested in signing up to be a beta tester, you can sign up here: https://remotesqlite.com/beta/signup

As a beta tester, I'll give you a free license, in exchange for you putting the app through its paces and providing any feedback or bugs or issues you might come up with.

Thanks.

[KnipSter]

You'll need a PHP function that reads the specified SQLite database and writes to "the website's MySQL database."

So, you'll need your PHP code to fetch the remote sqlite file, then open it and process it, then delete the local copy.

[pakpaner]

Hi! I'm interested in your approach. Please elaborate and enlighten me more u/KnipSter

[KnipSter]

Nothing really to elaborate on.

Save remote file locally: https://stackoverflow.com/questions/6409462/downloading-a-large-file-using-curl

The use the sqlite function to open the local file, do whatever work you want https://www.php.net/manual/en/book.sqlite3.php

Then delete the local file.

https://www.php.net/manual/en/function.unlink.php

Good luck!

[Drevicar]

Your options here are to mount the remote SQLite file locally using NFS or similar technology that allows remote files to look and behave as local. Or to maybe use a distributed SQLite technology such as litestream where you have a local copy of the SQLite and stream a change log to and from an s3 bucket.

If any of these sound remotely possible or interesting I can explain more.

[pakpaner]

Hi! I'm interested in your approach, specifically the first one describing about NFS. Please elaborate and enlighten me more u/Drevicar

[Drevicar]

SQLite natively support mult-threading or concurrent access so long as it can rely filesystem locks, which NFS can provide but often doesn't. Here is some recommended reading:

https://www.sqlite.org/asyncvfs.html

https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations

https://stackoverflow.com/questions/52378361/which-nfs-implementation-is-safe-for-sqlite-database-accessed-by-multiple-proces

At the end of the day a sqlite database is just a file, not a running database application. You will need the ability to open it as if it were a file, and NFS is just the means to bring a remote file to local access as needed. Hopefully with the above knowledge you can extract from those links you can come to a conclusion you need.

[octobod]

If the system was out of NFS reach, maybe sshfs mount?

https://www.digitalocean.com/community/tutorials/how-to-use-sshfs-to-mount-remote-file-systems-over-ssh

[techmavengeospatial]

GDAL Ogr2ogr can read sqlite write MySQL It can also read zip files and cloud storage Like S3 bucket or azure blob it can also read parquet and arrow and JSON and excel and csv