We are Airsequel, a platform that hosts SQLite databases and automatically generates a GraphQL API, a spreadsheet UI, an SQL workbench, and a dashboard builder for each database. While working extensively with SQLite, we discovered that many of its great features were added only recently.

Despite its widespread usage, there is a common misconception that SQLite is a stagnant or outdated technology. Often overshadowed by newer and flashier database management systems, many overlook the continued innovation and evolution of SQLite. Granted, SQLite has been around for more than 20 years, but this just shows what a robust and valuable database it is. And as you can see in following chart, the development velocity hasnโ€™t slowed down a bit:

Bar chart of days since last SQLite release for each release

(Check out my blog post for a tutorial on how to create this bar chart.)

So let's take a look at some of the exciting improvements and refinements that SQLite has seen since 2020. This list focuses on changes related to the supported SQL instructions and the CLI.

  1. Version 3.31 (2020-01-22)
    1. Added support for generated columns.
    2. Added the #-N array notation for JSON function path arguments.
    3. New flags SQLITE_INNOCUOUS and SQLITE_DIRECTONLY for application-defined SQL functions.
    4. Added the uuid.c extension for processing UUIDs.
  2. Version 3.32 (2020-05-22)
    1. Added the iif() SQL function.
    2. Added options to the .import command: --csv, --ascii, --skip.
  3. Version 3.33 (2020-08-14)
    1. Support for UPDATE FROM.
    2. Increase the maximum size of database files to 281 TB.
    3. Added the decimal extension
      to the CLI for doing arbitrary-precision decimal arithmetic.
    4. Added four new output modes: box, json, markdown, and table
  4. Version 3.34 (2020-12-01)
    1. Added options --data-only and --nosys to the .dump dot-command.
    2. Added the generate_series(START,END,STEP) function to the CLI.
    3. Added the --tabs command-line option that sets .mode tabs.
  5. Version 3.35 (2021-03-12)
    1. Added built-in SQL math functions().
    2. Support for ALTER TABLE DROP COLUMN.
    3. Support RETURNING clause on DELETE, INSERT, and UPDATE statements.
    4. MATERIALIZED and NOT MATERIALIZED hints for common table expressions (CTEs).
  6. Version 3.36 (2021-06-18)
    1. REGEXP extension is now included in CLI
  7. Version 3.37 (2021-11-27)
    1. STRICT tables
    2. Added PRAGMA table_list statement
    3. .connection command, allowing the CLI to keep multiple database connections open.
    4. CLI option --safe disables dot-commands and SQL statements that might cause side-effects.
  8. Version 3.38 (2022-02-22)
    1. Full JSON support via built-in functions
      1. -> and ->> operators
    2. Added the unixepoch() function.
    3. Added the auto modifier and the julianday modifier.
  9. Version 3.39 (2022-06-25)
    1. Support for RIGHT and FULL OUTER JOIN.
  10. Version 3.40 (2022-11-16)
    1. Support for compiling SQLite to WASM and running it in web browsers.
    2. Added the recovery extension to recover content from corrupt databases.
  11. Version 3.41 (2023-02-21)

    1. Added function unhex()

    2. Added base64() and base85() functions in the CLI

    3. Enhancements to PRAGMA integrity_check

There were also tons of improvements made to the query planner to enhance performance and robustness. However, these improvements are beyond the scope of this post.

For a full list of all changes, please check out the official release history page.

This list should make it clear that SQLite is still as relevant as ever. Personally, it's now my database of choice for most projects. While it may not have all the bells and whistles of, for example, Postgres, the truth is that I mostly don't need them anyway. The ease of deployment, the ability to copy, move, and edit it like any other file, and the high performance more than make up for it.