News AI
July 11th, 2024

First Contact with SQLite

The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.

Read original articleLink Icon
First Contact with SQLite

The article discusses the author's recent exploration of SQLite, highlighting surprising aspects of the database. Some key points include the lack of support for altering columns or dropping constraints directly, the absence of data types on columns (only on values), and the potential for SQLite to behave unexpectedly when dealing with unsupported types. The author notes the recent addition of jsonb support in SQLite, clarifying that it functions as a format rather than a distinct data type. Additionally, the absence of certain critical types like timestamptz is mentioned, with suggestions on how to handle date/time values in SQLite. Despite some initial challenges, the author acknowledges SQLite's impressive streaming features while reaffirming their belief in PostgreSQL as a top-tier database.

Related

At 50 Years Old, Is SQL Becoming a Niche Skill?

At 50 Years Old, Is SQL Becoming a Niche Skill?

SQL, a foundational technology, faces scrutiny in today's IT world. Evolving roles like data scientists challenge its centrality. Debates persist on SQL's relevance against newer technologies like JSON queries, impacting its future role.

Graph-Based Ceramics

Graph-Based Ceramics

The article explores managing ceramic glazes in a kiln and developing an app. It compares Firebase, Supabase, and Instant databases, highlighting Instant's efficiency in handling complex relational data for ceramic management.

Optimizing Large-Scale OpenStreetMap Data with SQLite

Optimizing Large-Scale OpenStreetMap Data with SQLite

The article discusses optimizing large-scale OpenStreetMap data with SQLite. Converting OSMPBF to SQLite enhanced search functionalities. Indexing, full-text search, and compression improved query performance, despite some false positives.

Graph-Based Ceramics

Graph-Based Ceramics

The article explores managing ceramic glazes in a kiln and creating an app for this purpose. It compares Firebase and Instant databases, Supabase, Postgres, and InstaQL for efficient data handling.

Graph-Based Ceramics

Graph-Based Ceramics

The article discusses managing ceramic glazes, introducing a custom app. It compares Firebase and Supabase databases, opting for Supabase's better data modeling. Instant database is explored for a hybrid solution.

Link Icon 34 comments
By @cjs_ac - 4 months
> 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.

By @simonw - 4 months
Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create the new table, copy the data across and then atomically swap the table names before you commit: https://www.sqlite.org/lang_altertable.html#otheralter

My favourite feature of my sqlite-utils CLI tool is the "transform" command which implements this pattern for you (also available as a Python library method): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

By @CJefferson - 4 months
There are things that different databases are best for.

This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week):

* Using postgreSQL to store data in an iPhone app

* Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and extend it (send the database, and also instructions for installing postreSQL, and getting everything set up. Make sure it works on linux, mac and windows).

By @eknkc - 4 months
I 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.

By @silvestrov - 4 months
I think it is time for a real version/generation 4 of SQLite which drops some backwards compatibility (e.g. file format) and has

    1) strict always enforced.
    2) full datatypes (ints, floats, datetime, jsonb)
    3) all "ALTER TABLE" functionality, even if it has to rewrite the table
By @trustno2 - 4 months
Don't hate on the author.

He heard a hype (sqlite), decided to try it to see what is it about, found out it's not for him really, wrote a bit blurb about it on his blog.

By @simonw - 4 months
Worth noting that this is from Brandur's "atoms" series: https://brandur.org/atoms - "Multimedia particles in the style of a tweet"

So it's more a short set of notes (like a TIL) than a full-fleshed blog post. Brandur's long-form writing has a different tone: https://brandur.org/articles

By @stavros - 4 months
I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far.

I don't understand the comparison here at all.

By @sudb - 4 months
Turso'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.

[1] https://github.com/tursodatabase/libsql/tree/main

By @harvie - 4 months
My mind was blown when i've realized i can write bash command that pipes CSV file into the SQLITE, runs SQL query on it and spits out results. (all completely in ram, without need for temporary sqlite file on disk)
By @42lux - 4 months
He 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.
By @alberth - 4 months
I wish SQLite would officially fork itself and:

- So make breaking changes as needed. (it was first released in 2000 and has fantastic backwards compatibility, but hardware & OS have radically changes over the last 24 years - as well as use cases).

- put more focus on client/server use cases

- make things more 'strict' (types, checks, etc)

Note: I say this with tremendous love for SQLite. There's just so many attempts for companies & projects to morph SQLite what it's not designed for, that you might as well embarrass these use cases and make an official fork to support them.

By @_dain_ - 4 months
>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

By @intellectronica - 4 months
I think what the author misses is that SQLite's choices make a lot of sense in a world where working with the DB is super easy and you can move a lot of the complexity to the code around it. With traditional databases it used to be the case that you manage everything in the DB, because it's expensive to call it, and because you don't always know who's going to call it, but that's clearly not a typical scenario for SQLite.
By @postepowanieadm - 4 months
All of the issues are well documented. It would make much sense to make ones first contact with the documentation to decide if given piece of software is suitable for ones needs. SQLite is like a bicycle, postgresql is like a freight train. Each has it's use.
By @gwd - 4 months
SQLite'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.

By @1vuio0pswjnm7 - 4 months
As long as one can convert database dumps between the two without too much trouble, this tired "debate" seems silly.

Perhaps the debate might be over how much trouble is too much. AFAICT automated "find and replace" can cure just about every "incompatibility".

By @yread - 4 months
Funny! Next do a "First contact with c++ as a Python developer without reading a manual"
By @renegade-otter - 4 months
Is there really an SQLite hype going on? I am getting a whiplash because of this pivot. From extreme complexity (Microservices! Kubernetes!) to extreme simplicity (Server-side rendering! Zero build! EC2!)
By @prirun - 4 months
"First Contact" with anything new is usually jarring. It'd be much more useful to hear a review after using the thing every day for a year.
By @wigster - 4 months
https://www.sqlite.org/quirks.html does list a few of these
By @endisneigh - 4 months
Fact of the matter is that SQLite shouldn’t be used in a non embedded context. There’s a reason people use Postgres.

There are some neat things you can do with it like using HTTP range queries to query it directly from object store or Litestream, but my point stands.

By @LVB - 4 months
My decision on SQLite vs Postgres is usually something like fixed and variable costs. For each little thing, Postgres is typically nicer to use. But the fixed cost of standing up and maintaining a dedicated database is high enough that I rarely need to go that route. (Hosted services just make that fixed cost into explicit $) And now with litestream, the server-side story has gotten nicer for SQLite, so I expect the percentage of time I need pg to continue to drop.
By @VagabundoP - 4 months
I use SQLite with my flask app - it has 5/6 users connected at once max - and its excellent. I've run into issues with alembic when running migrations occasionally. I had intended to move to something "beefier" but I see no point, unless I have to really scale up.

Its straightforward, fast and easy for a casual DB programmer. Everything is wrapped in SQLAlchemy anyway, so all the complicated logic is in my Python code.

Which have been around ALTER_TABLE and CONSTRAINTS

By @OliverJones - 4 months
I needed to use SQLite from php to develop a WordPress plugin. (Yeah, yeah, I have heard most if not all the jokes about that particular stack.)

Here are some notes about my experience, offered to anybody who might be able to use them. https://www.plumislandmedia.net/reference/sqlite3-in-php-som...

By @ysw0145 - 4 months
SQLite has its own decisions, to keep itself embeddable and low-footprint. There are many choices on the server but little on embedded devices like mobile phones. I have faced many problems that arise when using SQLite in a cross-platform mobile app, such as corruption and deadlock. However, there are almost no proven alternatives to SQLite.
By @qazxcvbnm - 4 months
How do you typically deal with "missing features" from SQLite (e.g. stored procedures)? Do you use extensions like https://github.com/nalgeon/sqlean in production?
By @ivanb - 4 months
Indeed. In practice, SQLite is the only embedded SQL OLTP DB. It doesn't have a rival. What we need is more competition in this space. I for one would love to have something with a feature set and strictness comparable to Postgres. A drop-in replacement would be extra nice.
By @NuSkooler - 4 months
"I completely misunderstand the purpose of SQLite, so here are some rants"
By @mbforbes - 4 months
It’s interesting to read a lot of push back to the points here.

I recently built a product with the backend using SQLite as the data store and ran into all these issues and many more. It is frustrating. I use SQLAlchemy and Alembic. It seemed everywhere I turned, the docs said “it works this way in all databases, except SQLite where X isn’t supported or you have to do Y differently.”

I think with litestream and D1 and other web SQLite tech emerging, you see the sentiment: “if you don’t have Google-scale, you can easily serve using disk-backed SQLite, plus enjoy skipping RTT network latency to DB.” Then, when someone does that and has a bad time, the comments instead go: “SQLite is only for embedded data stores.”

Personally, if I had to do it again, I would stick to the most boring tech for the target stack: Postgres and Django’s ORM.