Expose SQLite db to external ORM connections

1

I use Prisma as my ORM and it has this really great feature called “Prisma Studio” for exploring the database. I’d love to be able to explore my staging (or prod :scream:) database from my local machine from time to time. A wireguard requirement is acceptable for my use case. Is there any way to expose a connection URL to a SQLite database? I don’t know what I’m doing, but maybe serving my sqlite.db file via a static file server would be sufficient? I have no idea. Suggestions are welcome.

2 Likes
2

This is one of the weaknesses of using SQLite since it’s just a file on some disk and not a database server you can connect to externally.

With that said we’ve found some success running NocoDB on the same container as our app server and using fly proxy to connect to the admin web console from dev machines.

1 Like
3

@pheuter’s suggestion of NocoDB is a good idea. Datasette may work as well. You might be able to expose an NFS mount as long as you’re running a relatively recent version of NFS (v4+).

Ricardo Ander-Egg also did an interesting proof-of-concept a while back of making a remote SQLite GUI that communicates over SSH & the sqlite3 CLI. However, I can’t seem to find the link for that right now.

I wrote a SQLite proxy over the Postgres protocol a while back called Postlite although it’s definitely still “alpha”.

If you just need a read-only copy of your database and your database is relatively small, you can run a backup using the .backup PATH command in the sqlite3 CLI. You can combine that and cat in SSH to back up and download at the same time:

$ ssh $HOST 'sqlite3 /path/to/db ".backup /tmp/db" && cat /tmp/db' > db

Then you can open it locally at db. SQLite databases compress really well too so you could throw a gzip in there as well if you’re concerned about bandwidth.

2 Likes
4

I’d like to point out that for a couple of weeks now flyctl has sftp : flyctl ssh sftp · Fly Docs

3 Likes
5

I’m thinking about adding an endpoint to my app that will startup prisma studio on my server and serve as a proxy for prisma studio itself. Any immediate concerns about this? Feels like it should just work™️

6

Yeah, that seems like it should work. I haven’t used Prisma Studio before. Is it just a web app that runs on your server?

7

Yep. Unfortunately it’s coded in such a way that it expects to be served on / rather than a /subroute, so several fetch requests it makes don’t work. They asked whether running it on a subdomain would work and I don’t think it would because I don’t think Fly would let me have a single machine handle multiple domains. Or does it? :thinking: Would there be a way to have two servers running in my VM and have them each be served under a different domain?

8

@kentcdodds I’m not exactly sure what Prisma studio expects, but I think you can use WunderBase and wire it up with Prisma Studio. All it does is expose the HTTP API from the Prisma Rust Engine.

9

After a lot of search I stumbled upon this post which achieved pretty much the same results that you were looking for.

Make sure you are locally in the correct app folders when running the flyctl commands. Also make sure once you connect to the fly ssh console to change the directory to your app folder before running the prisma studio command.

Did you manage to find a more elegant way of achieving this outcome?

2 Likes
10

I used a few tricks to achieve this