First Contact with SQLite
brandur.org
First Contact with SQLite
1–10 of 99 posts
Re: First Contact with SQLite
#2[deleted]
Re: First Contact with SQLite
#3>SQLite doesn’t have data types on columns. Data types (and there are only five) are on values only, so anything can go anywhere.
You can use CREATE TABLE STRICT[1] to get a typed table.
Re: First Contact with SQLite
#4> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is.
I find these 'different therefore wrong' takes to be immature.
Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other use cases than those other engines, and therefore has other design decisions.
Ultimately, all computer programs are solutions to problems, and the approach to solving a problem depends on the nature of the problem. A list of grievances and a Boolean judgment is useless without stating the problem that the author is trying to solve.
Re: First Contact with SQLite
#5He should read the docs and not try to transfer his previous knowledge, especially if it isn't really fitting. He would have an easier time and be less frustrated.
Re: First Contact with SQLite
#6I feel like the recent hype around SQLite made people use it for a lot of stuff that is not really suitable for SQLite. It has too many caveats especially around data types.
I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something more feature packed.
Re: First Contact with SQLite
#7>SQLite doesn’t have data types on columns. Data types (and there are only five) are on values only, so anything can go anywhere. You can use CREATE TABLE STRICT[1] to get a typed table. [1] https://www.sqlite.org/stricttables.html
The article mention that too.
Re: First Contact with SQLite
#8I feel like the recent hype around SQLite made people use it for a lot of stuff that is not really suitable for SQLite. It has too many caveats especially around data types. I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something mor…
> if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it
I had the option for a recent project. It's a niche forum-like application with around 2,000 users. Went with a monolithic design and vertical scaling (if needed), so SQLite was perfect. Every dynamic HTML page renders in under 1ms. Litestream for live-replication to a couple S3 buckets.
Running PostgreSQL for something like this would be a pain in the ass, and add a minimum 10ms of latency to every request. There would genuinely be more maintenance required for the PostgreSQL server/daemon than the application itself. It just makes no sense.
Re: First Contact with SQLite
#9Turso's SQLite fork libSQL[1] has an extension/improvement that adds the ability to alter columns and drop constraints (albeit not via a DROP CONSTRAINT clause). I'm not affiliated, but have been using libSQL recently and am finding it to be a very pleasant experience.
Although conceptually I agree that SQLite's limited type system is frustrating, if your usecase allows, an ORM might help with not having to think about it or touch it directly.
Re: First Contact with SQLite
#10SQLite's idiosyncrasies make more sense when you realize:
1. It started as a way for the author to access databases from TCL, in which everything is a string. Sounds kind of mad now, but that was the kind of thing you did back in the 90's.
2. SQLite is fanatically backwards compatible. That means that once you get a system that works, it will continue to work through all newer versions of SQLite. But that also means that you can't suddenly decide to enforce foreign keys or column types by default, because it will break loads of systems that worked just fine before.