4 ms·

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

BuckRogers 5y ago
I'd say the team is using it wrong. SQLite is really intended for embedded use, not a Postgres replacement. The two shouldn't even be mentioned in the same sentence. SQLite is weakly typed, performing autoconversion from ints to strings. The value in SQLite is its light weight, and not it's SQL side. If you're building a mobile app and you're loading a lot of local data, it might be the right choice.
- pgwhalen 5y ago
  You may have misunderstood, we're not using it as a postgres replacement. I agree with this take, hence my original assertion that it isn't a drop in replacement for a DB server. We are using it as a replacement for RocksDB - we need a richer way to store data than a simple key value store. It still runs on a server though, and therefore it would be useful to be able to read data remotely, even if that isn't the primary purpose.
  - BuckRogers 5y ago
    My mistake then. I read it as "we tried it as a Postgres replacement, even though many here suggest it was going to work". I've toyed with SQLite as replacement for a client-server database for personal projects. While I stand by my overall dim assessment of SQLite, with a statically typed language and a diligently maintained data access layer (ie. one-man project), I would endorse its use on the server.
brunoluiz 5y ago
I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?
- pgwhalen 5y ago
  > I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Yeah. > Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file? We use "kubectl copy" currently when we want to inspect it, and we haven't actually had to write back to a production file yet. We've explored the "remote" option, but since it's just a file, everything seems to boil back down to "copy locally" then "copy back to remote prod". It's only a small part of our stack at the moment, so we haven't invested in tooling very much - but I'd be curious if others have solved similar problems.
  - brunoluiz 5y ago
    Ah I see! Yeah, I expected your team would have to copy it locally. I wonder tho, in times of data leaks and whatnot, couldn’t it be dangerous to have lots of PII (personal identifiable information) copied around many dev laptops? I mean, devs can do the same with Postgres, but it is more for backups instead of purely querying.
    - pgwhalen 5y ago
      That's true - our software does not deal with PII, which makes many of our DB practices sound strange to outsiders.
- tfehring 5y ago
  I think the usual approach would be to SSH into the server and run sqlite3 there. This issue [0] mentions some workarounds for connecting from DBeaver, which I assume would work for other graphical client software. I haven't tried those approaches, they seem pretty hacky and I imagine performance isn't great, but I guess that's to be expected given that SQLite isn't designed for that type of access. [0] https://github.com/dbeaver/dbeaver/issues/6876 https://github.com/dbeaver/dbeaver/issues/6876
aflag 5y ago
Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.
- pgwhalen 5y ago
  > Can't you run your queries in a copy of the data (eg.: a backup)? That's essentially what we do: copy the file locally if we need to inspect it. It's slightly more cumbersome though. > I think that'd be advisable even if you were running postgresql. Connecting to live prod servers is definitely not a 10/10 on the "best practices" scale, but it works well for our business (trading), where there are small developer teams that also operate, no PII in the database, and critical realtime functionality isn't directly involved with the database anyway.
  - aflag 5y ago
    With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data. I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have.
    - pgwhalen 5y ago
      > With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data. We do do this, and probably should be more disciplined about connecting to it when only reading. Of course that doesn't help if we need to run an update in production, but that isn't that often. > I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have. We do actually do this as well (nightly), and it is a huge productivity boost for testing and development. I would recommend to anyone that writes software dealing with persisted data to invest in an easy mechanism to clone from production.
tyingq 5y ago
>it's not easy to inspect or fix data in production the way we would with postgres. I assume because you're using a remote socket connection from the client? I haven't tried it in a serious setting yet, but I did play around with dqlite and was impressed. Canonical uses it as the backing data store for lxd. Basically sqlite with raft clustering and the ability for clients to connect remotely via a wire protocol. https://dqlite.io/ https://dqlite.io/
- pgwhalen 5y ago
  > I assume because you're using a remote socket connection from the client? Yeah, it's common for all developers to connect to and query against prod postgres DBs via DataGrip or similar. dqlite definitely looks interesting, but I worry it's a bit heavy given that our only use case for remote access is prod troubleshooting. I think I saw something recently where you could spin up a server on top of a sqlite file temporarily - that might be ideal for us.
srcreigh 5y ago
Did you try SSH and using SQLite command line tool?
- pgwhalen 5y ago
  That ought to work, but the experience we are trying to emulate is that of using DataGrip, rather than psql.

BuckRogers 5y ago

I'd say the team is using it wrong. SQLite is really intended for embedded use, not a Postgres replacement. The two shouldn't even be mentioned in the same sentence. SQLite is weakly typed, performing autoconversion from ints to strings. The value in SQLite is its light weight, and not it's SQL side. If you're building a mobile app and you're loading a lot of local data, it might be the right choice.

pgwhalen 5y ago
You may have misunderstood, we're not using it as a postgres replacement. I agree with this take, hence my original assertion that it isn't a drop in replacement for a DB server. We are using it as a replacement for RocksDB - we need a richer way to store data than a simple key value store. It still runs on a server though, and therefore it would be useful to be able to read data remotely, even if that isn't the primary purpose.
- BuckRogers 5y ago
  My mistake then. I read it as "we tried it as a Postgres replacement, even though many here suggest it was going to work". I've toyed with SQLite as replacement for a client-server database for personal projects. While I stand by my overall dim assessment of SQLite, with a statically typed language and a diligently maintained data access layer (ie. one-man project), I would endorse its use on the server.

brunoluiz 5y ago

I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?

pgwhalen 5y ago
> I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Yeah. > Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file? We use "kubectl copy" currently when we want to inspect it, and we haven't actually had to write back to a production file yet. We've explored the "remote" option, but since it's just a file, everything seems to boil back down to "copy locally" then "copy back to remote prod". It's only a small part of our stack at the moment, so we haven't invested in tooling very much - but I'd be curious if others have solved similar problems.
- brunoluiz 5y ago
  Ah I see! Yeah, I expected your team would have to copy it locally. I wonder tho, in times of data leaks and whatnot, couldn’t it be dangerous to have lots of PII (personal identifiable information) copied around many dev laptops? I mean, devs can do the same with Postgres, but it is more for backups instead of purely querying.
  - pgwhalen 5y ago
    That's true - our software does not deal with PII, which makes many of our DB practices sound strange to outsiders.
tfehring 5y ago
I think the usual approach would be to SSH into the server and run sqlite3 there. This issue [0] mentions some workarounds for connecting from DBeaver, which I assume would work for other graphical client software. I haven't tried those approaches, they seem pretty hacky and I imagine performance isn't great, but I guess that's to be expected given that SQLite isn't designed for that type of access. [0] https://github.com/dbeaver/dbeaver/issues/6876 https://github.com/dbeaver/dbeaver/issues/6876

aflag 5y ago

Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.

pgwhalen 5y ago
> Can't you run your queries in a copy of the data (eg.: a backup)? That's essentially what we do: copy the file locally if we need to inspect it. It's slightly more cumbersome though. > I think that'd be advisable even if you were running postgresql. Connecting to live prod servers is definitely not a 10/10 on the "best practices" scale, but it works well for our business (trading), where there are small developer teams that also operate, no PII in the database, and critical realtime functionality isn't directly involved with the database anyway.
- aflag 5y ago
  With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data. I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have.
  - pgwhalen 5y ago
    > With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data. We do do this, and probably should be more disciplined about connecting to it when only reading. Of course that doesn't help if we need to run an update in production, but that isn't that often. > I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have. We do actually do this as well (nightly), and it is a huge productivity boost for testing and development. I would recommend to anyone that writes software dealing with persisted data to invest in an easy mechanism to clone from production.

tyingq 5y ago

>it's not easy to inspect or fix data in production the way we would with postgres. I assume because you're using a remote socket connection from the client? I haven't tried it in a serious setting yet, but I did play around with dqlite and was impressed. Canonical uses it as the backing data store for lxd. Basically sqlite with raft clustering and the ability for clients to connect remotely via a wire protocol. https://dqlite.io/ https://dqlite.io/

pgwhalen 5y ago
> I assume because you're using a remote socket connection from the client? Yeah, it's common for all developers to connect to and query against prod postgres DBs via DataGrip or similar. dqlite definitely looks interesting, but I worry it's a bit heavy given that our only use case for remote access is prod troubleshooting. I think I saw something recently where you could spin up a server on top of a sqlite file temporarily - that might be ideal for us.

srcreigh 5y ago

Did you try SSH and using SQLite command line tool?

pgwhalen 5y ago
That ought to work, but the experience we are trying to emulate is that of using DataGrip, rather than psql.