6 ms·
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 a
by deathclassic 4y ago
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.
- jrk 4y agohttps://www.sqlite.org/json1.html https://www.sqlite.org/json1.html ?
- massysett 4y agoI 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.
- gerdesj 4y agoThere are some right old noddies around here! You (masstsett) expressed a preference for something with some working shown and ended up in DV land. That's not fair on many levels and reflects harsher on the casual readership hereabouts than yourself. Your comment is probably rated stellar by the time I hit enter ...
- aeturnum 4y agoThat'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.
- deathclassic 4y agoIt'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.
- srcreigh 4y agohttps://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index
- fomine3 4y agoSo C is okay for everything
- aeturnum 4y agoI 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.
- Spivak 4y agoAlso 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.
- deathclassic 4y agoThere is https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite but i've never used it.
- oconnor663 4y agoSQLite has a bunch of JSON features though, doesn't it? https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html Are there gaps?
- deathclassic 4y agoOk JSON maybe wasn't the best example. I wasn't aware that SQLite had those json features though, thanks for the advice.
- briHass 4y agoThe 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.
- deathclassic 4y agoAlso valid. I just use ISO8601 and bite the bullet because storage is cheap.
- acuozzo 4y agoFWIW, storage is cheap, but caches are not.
- di456 4y agoIt's cheap if the cache is also on SQLite. Might not even need a separate db for some use cases. This runs on python and works with several backends including SQLite. https://requests-cache.readthedocs.io/en/stable/ https://requests-cache.readthedocs.io/en/stable/
- acuozzo 4y agoI 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.
- di456 4y agoI learned something new, thanks for sharing! What format works well for hardware caching?
- duskwuff 4y ago> Things like arrays, UUIDs, geometry stuff, JSON, etc. 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 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.
- paiute 4y agoadd 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:
- christophilus 4y agoLack of int64 is also a problem for some applications.
- yencabulator 4y agoOh my god how I want a uint64, including in Postgres. The limited datatypes are so silly, and the limits seem pretty pointless. There's all kinds of weird datatypes, but no unsigned integers? Of course, SQLite types are a special level of hell, where everything is stringly typed.