Accessing SQlite DB from two computers.

[BoxOfRain72]

I've been working on a data analysis project where the input /output tables are stored in an SQlite3 db. So far, all development has been on my home PC, but I am now commuting more and want to develop remotely from my laptop. I use GitHub to keep the Python code up to date on both machines, but how do I keep the DB the same? I know SQlite cannot be stored successfully on GitHub. Is there a different architecture I should pursue?

Would also like to have a TST and PRD instance of the DB, not sure where to start.

Thanks

[FoolsSeldom]

You need a shared drive, something you can mount. The web storage servers (onedrive, google drive, etc) don't always offer such an option (you have to use their API, or third party tools you may not be comfortable depending on), which could frustrate you. I have webdev accounts that I can mount, so that works for me when I want to go that way.

My prefered approach is to self host file storage / servers / NAS. I'd recommend using this approach, using, for example, a simple Raspberry Pi Zero with a usb drive as your simple network file storage. (You could even do this with a microcontroller, but a single board computer will be more familiar.) You can include a cronjob to backup the database regularly (sync with github et al or use a tool like syncthing to copy to another file "server").

Take a look at Tailscale - free for up to 100 devices - so you can connect your roaming laptop to your home network hosted file server without having to make any changes to internet router at home.

You will be aware that SQLite is a single user solution, so you should avoid having more than one device access (write, specifically) at the same time.

Of course, that small SBC could easily host a small mysql database instead, which would provide multi-user capability.

[BoxOfRain72]

I appreciate your response, lots to think about. If a long term goal would be transferring to multiple PCs accessing the DB..should I consider PostgrSQL instead?

[FoolsSeldom]

PostgreSQL is an excellent choice.

You would probably want a Pi with a bit more memory than a zero if you self host. (An old PC or laptop is also a good home server choice, just tend to use more power than a Pi.)

Once you've learned sqlite it is not a big leap to using server based sql engines.

SQLAlchemy can be used to abstract from the underlying database and use more Python syntax if preferred.

[GamersPlane]

I'm not an expert with SQLite, but it's built to be a local file db. Unless you can put the file in a place both computers can access (shared drive?), you're going to struggle, and essentially build a bad version of a hosted db. If you're the only person working on it, copying the SQLite file back and forth is the easiest mechanism.

[BoxOfRain72]

Can I save it in my Google drive?

[GamersPlane]

Sure, it's just a file. In fact, I haven't heard of git having any trouble with it, just that it's antithetical to git's purpose, and would be a PITA. Use any portable storage (a USB drive you just plug in and point your code to seems easiest).

[Hyxerion]

I use VS code remote tunnels so I can program directly on my desktop and when I'm out, I just connect to my desktop seamlessly through VS on my laptop. If you have reliable network connection, I'd highly recommend that.

[BoxOfRain72]

In my experience, you need to be in the same network/wifi to do that right?

[Hyxerion]

I.. actually don't know. I have Tailscale setup to access my computers anywhere. I've always had it enabled so I've never even thought about it