Access sqlite from a remote server

Solution 1:

SQLite isn't a network database, so it doesn't have any network connection ability built into it.

You would need to either:

• Make the SQLite DB available on a network-accessible shared drive OR
• Write/find an existing network server/web service for it

A web application is essentially a web service. If you happen to be running a web application on top of this DB, just expose certain levels of DB access to admins-only.

It's not recommended you do this because multiple threads/clients/etc. accessing a SQLite DB simultaneously may lead to concurrency problems very quickly.

Solution 2:

While sqlite itself does not provide any network endpoints you can use a web gui such as sqlite-web.

Assuming you have python installed you can install it with

pip install sqlite-web

Once installed, start the web interface with:

sqlite_web my_database.db

By default it runs on http://127.0.0.1:8080/ ie is only accesible from the localhost.

To make it visible on your LAN/WAN start it with:

sqlite_web --host 0.0.0.0 my_database.db

While running with --host 0.0.0.0 is fine on your own home network, it's a really bad idea to do it on a public host (even though the app allows setting a password). For remote access on a public host it would be safer running it on 127.0.0.1 and then forward the port over ssh.

The UI has an option to export CSV or JSON and if you look at the request being made in the browser devtools you'll see that you can get the data directly as JSON by doing a POST request with an SQL query (very neat for playing and debugging in your intranet but worst nightmare ever if you expose that over the internet ❗️😱 ❌ — you have been warned❗️):

The POST to get JSON:

curl http://127.0.0.1:8080/mytable/query/ --data-raw 'sql=SELECT * FROM mytable limit 42;&export_json=' 

It depends on your use case but imo the best answer to the original question is "don't do it!" If you need to access the data programatically over the net then export the sqlite tables as JSON files and put them on a webserver.

^{Disclaimer: I am not affiliated with the author of the app. Just had the same need as the op and found the app on github.}

Solution 3:

SQLite is file-based only. There is no way to talk to it over TCP/IP.

Like an Access database file, you have to have the database file in a network shared folder. The path is usually going to be a UNC path, like "\server\sharename\folderpath\databasefile".

The problem is the connection string you built isn't a connection string. It's a partial string. You have this:

DataSource = @"\\\\" + txtipaddress.Text + @"\qscanDBAttacheds\test.s3db;Version=3;New=False;Compress=True;"

The "DataSource" part has to be part of the string you build. You don't have that. You need this:

string connString = @"Data Source=\\" + txtipaddress.Text + @"\qscanDBAttacheds\test.s3db;Version=3;New=False;Compress=True;"

Solution 4:

Navicat can connect via SSH to a remote sqlite database.

For smaller project im using phpliteadmin