5 ms·

There are things that different databases are best for. This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a gu

There are things that different databases are best for. This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week): * Using postgreSQL to store data in an iPhone app * Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and extend it (send the database, and also instructions for installing postreSQL, and getting everything set up. Make sure it works on linux, mac and windows).

piva00 2y ago
The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications. Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor a stable network stack he might appreciate SQLite for what it is since the comparison to Postgres is non-sensical when approaching from that viewpoint.
- rezonant 2y ago
  I didn't take this post to be overly negative, just a reflection of their experience in working with the tech. For anyone coming from a heavyweight SQL background, it is indeed pretty easy to be caught off guard with SQLite. That doesn't reduce its value of course.
  - bruce511 2y ago
    the clue to the difference is, perhaps, however, right there in the name....
- hobofan 2y ago
  None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here. If want to have more of an apples-to-apples comparison you could swap out Postgres for DuckDB (which aims to follow Postgres in SQL dialect), and all of the mentioned points should still hold.
  - piva00 2y ago
    > None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here. Which is why I mention "history and philosophy" on the same line of my comment. The `ALTER TABLE` documentation on SQLite [0] has a very clear reasoning on why it's implemented the way it is, and gives steps on how to reproduce more common usage of `ALTER TABLE` in other SQL engines. To directly quote from their docs: > Why ALTER TABLE is such a problem for SQLite > Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables. > SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain "creative" schema designs. > The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact. > Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite. > Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data. > The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify. [0] https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html
- arkh 2y ago
  > embedding a lightweight SQL-ish database into applications Current personal project is a game with crafting. I'm tired of games with limited inventory and no search / filters so I've been enjoying SQLite to manage that. The fact the db is a simple file is awesome to manage backups and you don't have to start a server to tinker with it: DB Browser, open file and you're done. Also kudos to the team behind the Godot SQLite wrapper.

piva00 2y ago

The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications. Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor a stable network stack he might appreciate SQLite for what it is since the comparison to Postgres is non-sensical when approaching from that viewpoint.

rezonant 2y ago
I didn't take this post to be overly negative, just a reflection of their experience in working with the tech. For anyone coming from a heavyweight SQL background, it is indeed pretty easy to be caught off guard with SQLite. That doesn't reduce its value of course.
- bruce511 2y ago
  the clue to the difference is, perhaps, however, right there in the name....
hobofan 2y ago
None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here. If want to have more of an apples-to-apples comparison you could swap out Postgres for DuckDB (which aims to follow Postgres in SQL dialect), and all of the mentioned points should still hold.
- piva00 2y ago
  > None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here. Which is why I mention "history and philosophy" on the same line of my comment. The `ALTER TABLE` documentation on SQLite [0] has a very clear reasoning on why it's implemented the way it is, and gives steps on how to reproduce more common usage of `ALTER TABLE` in other SQL engines. To directly quote from their docs: > Why ALTER TABLE is such a problem for SQLite > Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables. > SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain "creative" schema designs. > The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact. > Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite. > Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data. > The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify. [0] https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html
arkh 2y ago
> embedding a lightweight SQL-ish database into applications Current personal project is a game with crafting. I'm tired of games with limited inventory and no search / filters so I've been enjoying SQLite to manage that. The fact the db is a simple file is awesome to manage backups and you don't have to start a server to tinker with it: DB Browser, open file and you're done. Also kudos to the team behind the Godot SQLite wrapper.