41–50 of 378 posts
Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?
It’s not about logic or practicality. It’s that they feel some kind of mental pain using Postgres as it is too “bloated”.
I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
The biggest one missing is date and/or time. The workarounds all suck: - Store the date as a huge, wasteful string in ISO8601 format - Store it as Unix epoch seconds - Store it as a fractional Julian day Besides the first one, you have to remember how the date is stored and ensure all client libraries handle the conversion. If you want to view or manipulate the latter 2 formats in SQL, you need to chain a bunch of co…
I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
As others have mentioned, SQLite has fairly comprehensive support for JSON. Arrays can be represented as JSON arrays.
Some geometry features are supported through the R*Tree module: https://www.sqlite.org/rtree.html
And I'm not sure what sort of support you'd expect for a UUID type. Depending on how you represent the UUID, it's either a string or a blob -- I can't think of any meaningful operations to perform on a UUID which go beyond basic comparisons.
This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…
It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.
How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data.
Using a PAAS solution for a database, you get all that functionality.
Sure it’s all insane overkill - but I use Linux for the same reasons - I want one API that I can use everywhere, from my toaster to my spaceship, from hobby to enterprise.
The simplicity is not the API. The simplicity is having one API.
I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
Also the moment you need two databases for high availability or access to it over the network you end up inventing mysql but using sqlite rather than innodb.
This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…
I have thought about jacking inotify into sqlite_busy_handler() to get the exclusive writers doing better than random waits, but this full api isn't exposed to PHP (where I need it), and doing it at C would suggest alternate approaches, maybe even with xargs at the shell.
Oracle has a DBWR process that manages itself, but a write-heavy app on sqlite must explicitly declare one, and there are many further traps down this path that will trip the unwary.