Hacker News Reader

Comment by deathclassic | Hacker News Reader

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.

View on HN

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.

Also valid. I just use ISO8601 and bite the bullet because storage is cheap.

FWIW, storage is cheap, but caches are not.

It'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/

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.

I learned something new, thanks for sharing!

What format works well for hardware caching?

yw :-)

As small as you can tolerate tbh. The game is played by keeping data as small as possible and ensuring that related data has good locality in memory.

The gap between how fast your CPU works and how fast data can be retrieved from RAM is enormous.

> Store it as Unix epoch seconds

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.

Are you maybe hiring? I have been reading you posts last couple years on HN.

I am doing something very similar with .NET stack (single file deployments), SQLite and offline scenario...

Please contact me on my username's email (gmail).

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.

SQLite has a bunch of JSON features though, doesn't it? https://www.sqlite.org/json1.html Are there gaps?

Ok JSON maybe wasn't the best example. I wasn't aware that SQLite had those json features though, thanks for the advice.

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.

https://www.gaia-gis.it/fossil/libspatialite/index

So C is okay for everything

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.

> 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

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.

There 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 ...

https://www.sqlite.org/json1.html ?

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:

Lack of int64 is also a problem for some applications.

Oh 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.

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.

There is https://github.com/rqlite/rqlite but i've never used it.

Reply on news.ycombinator.com