Hacker News Reader

Comment by bambax | Hacker News Reader

There is no tool that I love more than SQLite. It's perfect. It does exactly what it says it will do, it never breaks (NEVER), is so small as to be invisible, is lightning fast, and packs many features a lot of the big guys still lack.

It is incredible.

View on HN

SQLite is incredible but it is sometimes quirky and the new GENERATED ALWAYS syntax unexpectedly disappointed me when I read about it with the 3.31.0 release.

GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS is IBM DB2 syntax that became part of the SQL standard. It is meant to be the unifying syntax for all the various flavors of AUTOINCREMENT that exist (GENERATED ALWAYS AS IDENTITY is now the standard way). It also makes a distinction between ALWAYS generated and BY DEFAULT which can be bypassed with a user supplied value.

SQLITE's GENERATED ALWAYS doesn't apply to keys/indexes so it doesn't work in the main use case in the SQL standard; primary key generation. Hopefully the AS IDENTITY syntax will eventually be added to SQLite to fix the goofy INT PRIMARY KEY vs. INTEGER PRIMARY KEY (long-form INTEGER is a synonym for IDENTITY/AUTOINCREMENT) syntax.

The JSON Document use case is useful but nowhere near as useful as supporting a clean and standard GENERATED ALWAYS AS IDENTITY for primary keys, in my opinion. Maybe I'm missing something.

Yes, I managed to get tripped up on the INT/INTEGER primary key distinction as a new sqlite3 user, and the idea of using the SQL standard to replace it would be great.

I’ve never used SQLite and this would almost certainly have exploded my brain if I encountered it in dev before reading it here.

I will admit to being less well traveled with the SQL family, mainly sticking to news on sqlite and mysql with some curiosity of postgres, but I'd only known of the GENERATED AS syntax being primarily for virtual columns that use a computed value.

It’s definitely the preferred/recommended PK solution for versions of postgres supporting it.

I love it. Moore's Law has made SQLite pretty much the only database I ever use. It's always been relatively fast, but with today's CPUs and SSDs, it's fast enough for anything I need.

A decade or so ago, I might have installed MySQL or Postgres to do some log processing or something. I never bother any more. SQLite is just the perfect data tool. You can load it up with gigs of data and it flies.

I still discover little tweaks and features I didn't realize it had as well. It's just a really well done project.

SQLite is awesome.

I've always been a bit confused by the design decision for SQLite to allow you to store any value in any column despite the column's type, but I've never had that been a problem since my applications don't try to store strings in integer columns.

It's a similar situation for storing date types as strings. Seems like an odd choice, but I've never had a problem because I keep my date types consistent.

It just goes to show that seemingly odd design choices might never end up being a problem.

I'd be fascinated to hear from those who may have had problems with SQLite's "flexible" type system.

I mean countless critical systems run on Excel spreadsheets and they're the definition of dynamically typed values, so there's some precedent for that working in the real world

There's also a cottage industry predicated on unbundling and (for the most part) statically typing processes defined in excel spreadsheets.

So, I've thought about this a lot, especially since there are sometimes some challenges with things such as datetimes in SQLite if you're not very careful and specific (thinking about if you actually want to use in query for more than basic comparisons).

However, on the OTHER hand... if we think about SQLite as a general purpose data storage tool, it's actually a really handy feature, letting the in datatypes almost every case be more of an additional type of label.

For example, if I required a high-precision timestamp, I could declare the column type something like 'UTCEpochNanos', providing a reminder to myself and other consumers of the data how it should be interpreted.

SQLite is awesome. I use it for my blog backend. It works really well.

I use it as a backend for markdown tables in my markdown notes. I have a script that runs on every commit that exports the tables as markdown tables. It's amazing!

It needs a transpose/pivot function

What has been your cases about SQLite ? would love to understand never breaks part.

Reply on news.ycombinator.com