4 ms·

> Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite Not sure exactly

> Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite Not sure exactly what you mean by 'remote access', but if it's to debug something on a remote DB from an SQL IDE, I do this quite often. I use sshfs to mount the remote filesystem and open the SQLITE file in DBeaver. Even in case of Postgres, you would have to connect to a remote DB via SSH or via VPN. It's just that you will be using sshfs to connect to a remote SQLITE file.

justsomeuser 6y ago
I just scp that son of a b down and run all the queries on the SQLite file locally.
- spicybright 6y ago
  Sounds like a pain for larger databases.
  - justsomeuser 6y ago
    Well it depends how large you are talking about. With todays internet speeds, anything under 10GB is a less than a few minutes. I once accidentally locked a production MySQL database with some kind of recursive sub query. Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.
    - spicybright 6y ago
      I guess there's a lot of cases I just want to peek into a table live without pulling it all down. I've only used postgres so it's weird to me you can't talk to any MySql style DB like it does. Anything that needs to be coded I'll pull it down though, it's nice to have options.
nh2 6y ago
Note that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking. According to: https://www.fossil-scm.org/forum/forumpost/8749496886 https://www.fossil-scm.org/forum/forumpost/8749496886 > There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.
- hannofcart 6y ago
  I wasn't aware of this. Thanks for the information. Is this true even when you use a read-only sshfs mount?
  - loeg 6y ago
    No. Read-only cannot mutate the database. I don't know if Sqlite ensures you won't observe inconsistent results if there is a concurrent (single) writer.
    - nh2 6y ago
      The docs [1] and this [2] suggest that you'll probably observe inconsistent results. WAL mode supports it ("reading and writing can proceed concurrently"), but "does not work over a network filesystem" like SSHFS [3] because it either requires shared memory on the same machine, or exclusive locking [4] (which SSHFS does not provide, and which again does not allow readers concurrent with a writer). Thus overall, you can only rely on your read queries over SSHFS if you know that there is no concurrent writer. [1]: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html [2]: https://stackoverflow.com/questions/1005206/does-sqlite-lock-the-database-file-on-reads/1005218#1005218 https://stackoverflow.com/questions/1005206/does-sqlite-lock... [3]: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html [4]: https://www.sqlite.org/wal.html#noshm https://www.sqlite.org/wal.html#noshm
- loeg 6y ago
  If you're just using sshfs for RO queries for debugging, it should be harmless.

justsomeuser 6y ago

I just scp that son of a b down and run all the queries on the SQLite file locally.

spicybright 6y ago
Sounds like a pain for larger databases.
- justsomeuser 6y ago
  Well it depends how large you are talking about. With todays internet speeds, anything under 10GB is a less than a few minutes. I once accidentally locked a production MySQL database with some kind of recursive sub query. Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.
  - spicybright 6y ago
    I guess there's a lot of cases I just want to peek into a table live without pulling it all down. I've only used postgres so it's weird to me you can't talk to any MySql style DB like it does. Anything that needs to be coded I'll pull it down though, it's nice to have options.

nh2 6y ago

Note that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking. According to: https://www.fossil-scm.org/forum/forumpost/8749496886 https://www.fossil-scm.org/forum/forumpost/8749496886 > There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.

hannofcart 6y ago
I wasn't aware of this. Thanks for the information. Is this true even when you use a read-only sshfs mount?
- loeg 6y ago
  No. Read-only cannot mutate the database. I don't know if Sqlite ensures you won't observe inconsistent results if there is a concurrent (single) writer.
  - nh2 6y ago
    The docs [1] and this [2] suggest that you'll probably observe inconsistent results. WAL mode supports it ("reading and writing can proceed concurrently"), but "does not work over a network filesystem" like SSHFS [3] because it either requires shared memory on the same machine, or exclusive locking [4] (which SSHFS does not provide, and which again does not allow readers concurrent with a writer). Thus overall, you can only rely on your read queries over SSHFS if you know that there is no concurrent writer. [1]: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html [2]: https://stackoverflow.com/questions/1005206/does-sqlite-lock-the-database-file-on-reads/1005218#1005218 https://stackoverflow.com/questions/1005206/does-sqlite-lock... [3]: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html [4]: https://www.sqlite.org/wal.html#noshm https://www.sqlite.org/wal.html#noshm
loeg 6y ago
If you're just using sshfs for RO queries for debugging, it should be harmless.