DEV Community

Ryan Westlund
Ryan Westlund

Posted on • Updated on

Coming around to SQLite

A long time ago I wrote a critical opinion of SQLite. I had the opinion that SQLite basically sucks and I would rather use Postgres even for projects that didn't need most of Postgres's features. Lately SQLite's philosophy is growing on me.

Postgres has its whole giant built-in system of account and permission management, completely independent of the operating system or your app. When does this make sense? Permission management belongs in your app, not your database.

An SQLite database on the other hand is subject to filesystem permissions on the database file, and other than that, permissions are your application's job.

Postgres violates the self-containedness of your app. Different databases, even ones from different apps, live together in a centralization location on the host system. Postgres is run as a service your apps talk to, and so it has to have all these messy concepts like connection pooling... damn.

SQLite is just a library your process uses to access a file. It's so much more elegant.

I used to love PostgreSQL for having arrays. Not so much anymore. I had my epiphany about why arrays shouldn't be necessary in a relational database; foreign keys are the idiomatic solution, which allow querying members of the "array" without a slew of dedicated features and special syntax for arrays like Postgres has.

One of the biggest things I hated on about SQLite when I wrote that article was not enforcing foreign keys. What the hell, how could they do that? But I'm now starting to wonder if even foreign key handling isn't so useful, because maybe that too belongs in my app anyway. After all, if something can't be deleted because something else depends on it, I don't want to get an SQL error and have to tell the user "something went wrong". I want to be able to show them "can't delete that because ...".

I'm not trying to say none of the things I'm criticizing about Postgres are ever useful. But on the whole they seem like violations of the proper separation of responsibilities.

I just counted the source code lines with tokei. SQLite is 300k and Postgres is 1m. So those questionable features and design choices cost over a 3x increase in the weight of the database software itself. Maybe that stuff matters to me more than it should, but personally I loathe the feeling of millions of lines of code underneath me.

The major gripes I still have with SQLite are the lack of type enforcing and the lack of timestamp types. Those are the two I'm sure I'll never come around to agreeing with. But hey, at least it means SQLite doesn't have to care about timezones or bullshit like that. Postgres's timestamp and timestamp with time zone have certainly confused me before.

Discussion (3)

stereoplegic profile image
Mike Bybee

Maybe that stuff matters to me more than it should, but personally I loathe the feeling of millions of lines of code underneath me.

Do yourself a favor and don't check the size of the Linux kernel. 😉

I do enjoy the simplicity of SQLite. Not as much as I enjoy various cloud NoSQL DBaaS and not having to manage DB architecture myself, but it does mostly get out of the way.

yujiri8 profile image
Ryan Westlund Author

Do yourself a favor and don't check the size of the Linux kernel. 😉

Lol, I think I checked the FreeBSD repository once and don't remember, but I know it was millions. I understand operating systems are like that. I've also checked a few language implementations and browsers. I remember Firefox was something like 10 million, depending on how you count.

jrbrtsn profile image
John Robertson

I've been using SQLite on embedded systems for the last 10 years. Programming with it takes some getting used to, and I have wrapped it with an OO C api. I like how the SQL implementation strips the number of types down to a bare minimum for storage. There is essentially no maintenance needed for SQLite and the databases, and most of the bugs are beaten out of it at this point in time.