Hacker News Reader

Comment by ezekg | Hacker News Reader

After using SQLite in production for a little while, I don't think "SQLite is all you need." Migrations are a pain, so any long-lived app will eventually experience pain. As an example, SQLite doesn't have a way to add a NOT NULL constraint to an existing column; you have to rebuild the entire table using a temp table.

View on HN

> As an example, SQLite doesn't have a way to add a NOT NULL constraint to an existing column; you have to rebuild the entire table using a temp table.

Or any other constraint for that matter, every few months I look for ADD CONSTRAINT again, only to rediscover there isn't anything.

The lack of any sort of PL language (or even just trivial stored procs aliasing queries) also makes migrations more annoying, CTE can handle some of the load but the verbosity and limitations of basic SQL make that awkward, and needing to round-trip through the host language for everything is frustrating (even more so when the host language is statically typed and thus requires adaptation out).

They will have to pry Postgres from my cold, dead hands.

That said, I do think it's really positive that sqlite3 is now a first class option in the framework. Tons of high quality work by very smart people has been invested in recent years.

This isn't what rails advocates, is it? I thought SQLite in rails is a replacement for redis, not for postgres. I've been out of the loop though.

EDIT: this comment is more about rails itself than the actual blog. I take the point being, that sqlite is fine for starting out with a tiny app if you have very little users, and I think that is true.

However, migrating later on to a different database is a always a pain, so I wouldn't recommend starting out with sqlite if you intend to do that later on (with your production data).

Huh. TIL.

But in the rails context couldn’t you mostly manage with an ActiveRecord validation? I know it wouldn’t be ideal.

This is perhaps what you were hinting at when you said “wouldn’t be ideal”, but Active Record validations on their own are subject to race conditions with concurrent requests. The only truly safe way to ensure integrity of your data is to enforce validations/constraints at the database level.

IME you usually want _both_ the Active Record validations, and the database-level validations, because you get better error messages from the former, and the latter is just a safeguard.

No I agree. I always want a belt and suspenders with my data validations

Reply on news.ycombinator.com