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 conversion functions.
I meant hardware caches like L1, L2, and L3 on the CPU.
SQLite is used in some HPC work.
ISO-8601 datetime strings can easily wreck your L1 cache. Instead of filling an eighth of the cache with a 64-bit value, you wind up filling almost half with a 27-character-long string.
This is what we do. Have been storing 100% of our timestamps this way in SQLite for ~8 years now. Using .NET to handle the actual conversion to/from long.
var myTimeUtc = DateTime.UtcNow;
var myTimeUnix = new DateTimeOffset(myTimeUtc).ToUnixTimeSeconds();
var myTimeUtc2 = DateTimeOffset.FromUnixTimeSeconds(myTimeUnix).UtcDateTime;
No drama at all. No weird libraries or utility methods. It's all simple built-ins these days.
Agree, this is a giant PITA. I think this could be easily fixed. The main sticking point with unix epoch is the SQLite CLI interface, but there could easily (?) be added some kind of mark to columns that it's an epoch timestamp and a client feature which parses those and formats the date. Done, problem solved.
Any code (usually one codebase) which looks at dates in a SQLite DB can already easily do these conversions, even at the application-level.
That's true - but I think it goes back to "you will need." It's nice to query these things in the DB, but for most users you can just load everything based on associations and sort it out in memory. It's less efficient, but most of the time you will be ok.
It's ok until you have to deal with loading a bunch of point cloud or geometry data based on associations and sort it out in memory. Then PostGRES becomes your friend.
I mean, yes - "you only ever need C in most situations" is true. You might like to use something else. You might be really glad to use something instead of C. And also...you could generally get by with C. Just like you can mostly get by using SQLite.
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.
I agree. For my little applications I've looked at Postgres because it has much richer data types, but I can't justify the huge complexity increase of Postgres. So SQLite it is.
add partitioning and sharding, basic search in other DBs is pretty nice as well. I've come in on a project where the team did custom trig for distance queries that took 30 seconds to run once the data grew past their data set. All so they could use sqllite for local development. :facepalm:
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.
