5 ms·

Honestly in that case you should just use Postgres locally. SQLite was never meant to be a stand-in for a true RDBMS. As they say, it's intended to be a replace

Honestly in that case you should just use Postgres locally. SQLite was never meant to be a stand-in for a true RDBMS. As they say, it's intended to be a replacement for fopen(). The fact that it works for local development is a fluke.

nicoburns 5y ago
Yeah, I've never understood people's aversion to running Postgres or MySQL locally. They're super easy to install, and they'll run in the background using minimal resources ready for you when you need them.
- xupybd 5y ago
  Yeah for development Postgres is easy to install. For deployment it comes at a complexity cost. I have small scripts that do things like send an email on a given business event. These scripts pull data from the main ERP. To keep the system upgradable we avoid changes to the ERP's database. So these scripts need their own DB to store state, such as when the email was sent. At first this involved one Postgres instance with databases for each script or application. If that Postgres server moves IP or has a version upgrade often every script or app needs a config change or library upgrade. Everything gets coupled together. By using an SQLite DB per script a range of problems go away. Of course anything that has multiple users or needs to be reported on has to go into a central DB but a surprising amount can happly run on it's own independent SQLITE DB. To move hosts or backup the application and DB you take a copy of the folder. It's so easy.
  - nicoburns 5y ago
    Oh sure. Nothing against SQLite. If your needs are simple then it's a great solution. It's the "Postgres/MySQL in production, SQLite for development/testing" pattern that puzzles me.
    - xupybd 5y ago
      Ah right yeah, I'm not sure that is a great idea. If you don't develop against the same system you want to deploy with you are asking to find bugs in production.
da_chicken 5y ago
You say that like a networked RDBMS isn't meant to be a replacement for fopen() at the end of the day. Yeah it does a lot more than fopen(). So does SQLite. It's still, bottom line, just secondary storage with a fancy API.
- zarzavat 5y ago
  Not many people are willing to make PostgreSQL a dependency of their program.
  - da_chicken 5y ago
    Not a dependency in the sense of not a statically linked binary? Sure. Not a dependency in the sense of not requiring a specific version of a Postgres db provider, or a specific version of a Postgres itself, or able to do any work without Postgres existing? Laughably no. DB agnostic software is very uncommon, even though ODBC is well-established and mature.
    - d4mi3n 5y ago
      I always found this very interesting. With the exception of enterprise software built to run in customer datacenters with whatever infrastructure that may entail, practice seems to dictate that the overhead of making something DB agnostic isn't a worthwhile trade off. I haven't spoken to too many folks who've had to make this decision, but off the top of my head I think there's a number of factors that aren't obvious at first glance that factor into this equation: 1. You need authors of queries to understand the least common denominator of supported SQL features and syntax (probably ANSI SQL, but there may be cases where it isn't) 2. Such projects would need to test or otherwise verify that all SQL queries execute in the ways they're expected to. This can be tricky when you have regressions in query performance from one database flavor to the next. 3. Your application can't have features that are made or broken by features specific to a particular database. I can see this being a very limiting constraint in a world where business intelligence and data analysis at scale has become a the norm. Are there other factors here that would compel projects to stick to something like ODBC (or your database abstraction layer of choice) rather than against it?
LAC-Tech 5y ago
With WAL2 mode, SSDs everywhere, and machines with gigabytes of RAM, SQLite makes sense in more situations than you might think.
justinclift 5y ago
No, that's specifically not the use case. A common use case is using SQLite as a local stand in replacement for "whatever production database" people are using. Commonly PostgreSQL, but not always. So, my idea was just a way to make that work better, nothing more.

nicoburns 5y ago

Yeah, I've never understood people's aversion to running Postgres or MySQL locally. They're super easy to install, and they'll run in the background using minimal resources ready for you when you need them.

xupybd 5y ago
Yeah for development Postgres is easy to install. For deployment it comes at a complexity cost. I have small scripts that do things like send an email on a given business event. These scripts pull data from the main ERP. To keep the system upgradable we avoid changes to the ERP's database. So these scripts need their own DB to store state, such as when the email was sent. At first this involved one Postgres instance with databases for each script or application. If that Postgres server moves IP or has a version upgrade often every script or app needs a config change or library upgrade. Everything gets coupled together. By using an SQLite DB per script a range of problems go away. Of course anything that has multiple users or needs to be reported on has to go into a central DB but a surprising amount can happly run on it's own independent SQLITE DB. To move hosts or backup the application and DB you take a copy of the folder. It's so easy.
- nicoburns 5y ago
  Oh sure. Nothing against SQLite. If your needs are simple then it's a great solution. It's the "Postgres/MySQL in production, SQLite for development/testing" pattern that puzzles me.
  - xupybd 5y ago
    Ah right yeah, I'm not sure that is a great idea. If you don't develop against the same system you want to deploy with you are asking to find bugs in production.

da_chicken 5y ago

You say that like a networked RDBMS isn't meant to be a replacement for fopen() at the end of the day. Yeah it does a lot more than fopen(). So does SQLite. It's still, bottom line, just secondary storage with a fancy API.

zarzavat 5y ago
Not many people are willing to make PostgreSQL a dependency of their program.
- da_chicken 5y ago
  Not a dependency in the sense of not a statically linked binary? Sure. Not a dependency in the sense of not requiring a specific version of a Postgres db provider, or a specific version of a Postgres itself, or able to do any work without Postgres existing? Laughably no. DB agnostic software is very uncommon, even though ODBC is well-established and mature.
  - d4mi3n 5y ago
    I always found this very interesting. With the exception of enterprise software built to run in customer datacenters with whatever infrastructure that may entail, practice seems to dictate that the overhead of making something DB agnostic isn't a worthwhile trade off. I haven't spoken to too many folks who've had to make this decision, but off the top of my head I think there's a number of factors that aren't obvious at first glance that factor into this equation: 1. You need authors of queries to understand the least common denominator of supported SQL features and syntax (probably ANSI SQL, but there may be cases where it isn't) 2. Such projects would need to test or otherwise verify that all SQL queries execute in the ways they're expected to. This can be tricky when you have regressions in query performance from one database flavor to the next. 3. Your application can't have features that are made or broken by features specific to a particular database. I can see this being a very limiting constraint in a world where business intelligence and data analysis at scale has become a the norm. Are there other factors here that would compel projects to stick to something like ODBC (or your database abstraction layer of choice) rather than against it?

LAC-Tech 5y ago

With WAL2 mode, SSDs everywhere, and machines with gigabytes of RAM, SQLite makes sense in more situations than you might think.

justinclift 5y ago

No, that's specifically not the use case. A common use case is using SQLite as a local stand in replacement for "whatever production database" people are using. Commonly PostgreSQL, but not always. So, my idea was just a way to make that work better, nothing more.