4 ms·

Non-IT guy here, can someone explain to me how good/bad of an idea using this simple setup of SQLite + this interface as the core DBMS in a commercial setting w

Non-IT guy here, can someone explain to me how good/bad of an idea using this simple setup of SQLite + this interface as the core DBMS in a commercial setting would be? The interfaces I have used in my experience were built in-house over Apache Spark and Microsoft SQL, and always felt overengineered and more complicated than it needed to be. For the record I have learnt data retrieval and analysis only on the job and not from any formal training, apologies if I am missing something fundamental.

virtue3 3y ago
if it's built in house it's probably got some business logic / rules built into it. This tool wont necessarily know about any specifics about how the database relates to your business.
ocdtrekkie 3y ago
SQLite can start to show weaknesses when you need a bunch of people or processes writing to it at the same time. For small applications or applications where you can tightly control what is talking to it at any given time, SQLite is great. Enterprise products tend to target larger organizations where a more overengineered solution is likely commonly available anyways. Generally though, I want developers to justify why SQLite isn't good enough for their app before they build on something else. In a lot of cases SQLite absolutely is good enough, and it removes an insane amount of complexity, especially from things like database backups.
- willsmith72 3y ago
  i tend to lean the other way. postgres is just so easy. with managed db services, you dont have to think about backups, version upgrades, all the rest. you never have to worry about running into a rough edge or missing feature. the maturity and huge community make it hard to go wrong
  - tracker1 3y ago
    Agreed... As soon as you need shared access to multiple systems, PG it is, usually in Docker though if it doesn't need a dedicated instance. Though I have used Firebird when I needed to scale it support local and remote data before. I don't think Figured gets nearly enough attention.
- tracker1 3y ago
  I remember really pushing for SQLite for a specific application that used MS SQL... The main data in practice has requirements to be archived by event. And didn't need more than a few dozen users with low transactions in practice. Easy backup, archive etc. In the end, there was a complex solution with a all the business logic in stored procedures and functions. With a lot of variability and difficulty supporting the application. This was for a petition verification system. So each petition could have easily fit into a SQLite database. Been portable and easy to archive, backup, transport etc. But no,. We had to put all the logic in the database... Because integritai...
input_sh 3y ago
They have a page for this: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- alex_suzuki 3y ago
  From the referenced page: > SQLite does not compete with client/server databases. SQLite competes with fopen().
  - bux93 3y ago
    That page also doesn't mention that SQLite can act as a gateway drug. Store stuff in SQLite now, and use a RDBMS later when it makes sense.
    - HPsquared 3y ago
      Similar to MS Access.
tracker1 3y ago
Well, there's Turso and Cloudflare D2 as examples of how you can scale SQLite in practice. In terms of a simple API over SQLite, it will depend on the number of users, the overhead and the cache ability of tall data in practice. If you have a heavy transactional load, it may not go so well. Similarly, any service may be better implemented in a single connection with seriously requests, which will limit this but reduce complexity. Realistically, PostgreSQL in Docker is pretty much my baseline for a shared database server. If other systems really need to access it, it's going to be the easier path. Even Firebird would likely be a better option for many lower end use cases where a shared service is needed.
c16 3y ago
From my experience on a fun project, it's fine for running your website, but if you need to edit data as values update, pulling down the db, making changes then uploading the DB will obviously not do. You'll need some sort of web interface like phpliteadmin; and if you're not running php well now you are. Unless you want to use a docker image, and now you're using docker. I wish there was a real simple way to connect to a remote db like you do with postgres.
- radiator 3y ago
  if you can SSH into the remote server where the .sqlite database is saved, then you can do anything can't you? Including editing the data without down/uploading the whole DB. Perhaps also make use of sshfs, if you want to use a GUI installed on your workstation.
adius 3y ago
We think SQLite in a commercial setting is perfect and that’s why we built https://airsequel.com https://airsequel.com. A full fledged SQLite hosting platform with an automatic GraphQL API, cloud functions, dashboards, … We totally agree that most enterprise software is too complicated for “normal” businesses. Those tools were often developed by the biggest fish for their outsized problems.
- toadlytoad 3y ago
  I think you think wrong.

virtue3 3y ago

if it's built in house it's probably got some business logic / rules built into it. This tool wont necessarily know about any specifics about how the database relates to your business.

ocdtrekkie 3y ago

SQLite can start to show weaknesses when you need a bunch of people or processes writing to it at the same time. For small applications or applications where you can tightly control what is talking to it at any given time, SQLite is great. Enterprise products tend to target larger organizations where a more overengineered solution is likely commonly available anyways. Generally though, I want developers to justify why SQLite isn't good enough for their app before they build on something else. In a lot of cases SQLite absolutely is good enough, and it removes an insane amount of complexity, especially from things like database backups.

willsmith72 3y ago
i tend to lean the other way. postgres is just so easy. with managed db services, you dont have to think about backups, version upgrades, all the rest. you never have to worry about running into a rough edge or missing feature. the maturity and huge community make it hard to go wrong
- tracker1 3y ago
  Agreed... As soon as you need shared access to multiple systems, PG it is, usually in Docker though if it doesn't need a dedicated instance. Though I have used Firebird when I needed to scale it support local and remote data before. I don't think Figured gets nearly enough attention.
tracker1 3y ago
I remember really pushing for SQLite for a specific application that used MS SQL... The main data in practice has requirements to be archived by event. And didn't need more than a few dozen users with low transactions in practice. Easy backup, archive etc. In the end, there was a complex solution with a all the business logic in stored procedures and functions. With a lot of variability and difficulty supporting the application. This was for a petition verification system. So each petition could have easily fit into a SQLite database. Been portable and easy to archive, backup, transport etc. But no,. We had to put all the logic in the database... Because integritai...

input_sh 3y ago

They have a page for this: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

alex_suzuki 3y ago
From the referenced page: > SQLite does not compete with client/server databases. SQLite competes with fopen().
- bux93 3y ago
  That page also doesn't mention that SQLite can act as a gateway drug. Store stuff in SQLite now, and use a RDBMS later when it makes sense.
  - HPsquared 3y ago
    Similar to MS Access.

tracker1 3y ago

Well, there's Turso and Cloudflare D2 as examples of how you can scale SQLite in practice. In terms of a simple API over SQLite, it will depend on the number of users, the overhead and the cache ability of tall data in practice. If you have a heavy transactional load, it may not go so well. Similarly, any service may be better implemented in a single connection with seriously requests, which will limit this but reduce complexity. Realistically, PostgreSQL in Docker is pretty much my baseline for a shared database server. If other systems really need to access it, it's going to be the easier path. Even Firebird would likely be a better option for many lower end use cases where a shared service is needed.

c16 3y ago

From my experience on a fun project, it's fine for running your website, but if you need to edit data as values update, pulling down the db, making changes then uploading the DB will obviously not do. You'll need some sort of web interface like phpliteadmin; and if you're not running php well now you are. Unless you want to use a docker image, and now you're using docker. I wish there was a real simple way to connect to a remote db like you do with postgres.

radiator 3y ago
if you can SSH into the remote server where the .sqlite database is saved, then you can do anything can't you? Including editing the data without down/uploading the whole DB. Perhaps also make use of sshfs, if you want to use a GUI installed on your workstation.

adius 3y ago

We think SQLite in a commercial setting is perfect and that’s why we built https://airsequel.com https://airsequel.com. A full fledged SQLite hosting platform with an automatic GraphQL API, cloud functions, dashboards, … We totally agree that most enterprise software is too complicated for “normal” businesses. Those tools were often developed by the biggest fish for their outsized problems.

toadlytoad 3y ago
I think you think wrong.