SQLite 3.38.0 is now generally available

On the 22nd of this month, SQLite 3.38.0 was officially released.

SQLite is a small, fast, self-contained, highly reliable, full-featured embedded SQL database engine, the most used database engine in the world.

Major updates

• Added -> and ->\> operators for easier handling of JSON. The new operator is compatible with MySQL and PostgreSQL.
• JSON functions are now built-in. The -DSQLITE\_ENABLE\_JSON1 compile-time option is no longer required to enable JSON support. JSON is enabled by default. Use the new -DSQLITE\_OMIT\_JSON compile-time option to disable the JSON interface.

• Enhancements to date and time functionality:

  • Added unixepoch() function.
  • Added auto modifier and julianday modifier.
• Renamed printf() SQL function to format() for better compatibility. The original printf() name is preserved as an alias for backward compatibility.
• Added the sqlite3\_error\_offset() interface, which can sometimes help localize SQL errors to specific characters in the input SQL text so that applications can provide better error messages.

• The interface of virtual tables is enhanced as follows:

  • Added sqlite3\_vtab\_distinct() interface.
  • Added sqlite3\_vtab\_rhs_value() interface.
  • Added new operator types SQLITE\_INDEX\_CONSTRAINT_LIMIT and SQLITE\_INDEX\_CONSTRAINT_OFFSET.
  • The sqlite3\_vtab\_in() interface (and related interfaces) was added to enable virtual tables to handle all IN operator constraints at once, rather than handling each value to the right of the IN operator individually.

• CLI enhancements:

  • Columnar output modes have been enhanced to properly handle tabs and newlines embedded in text.
  • Added options like "--wrap N", "--wordwrap on", and "--quote" to Columnar output modes.
  • Added .mode qbox alias.
  • The .import command automatically disambiguates column names.
  • Provide better error messages using the new sqlite3\_error\_offset() interface.

• Query planner enhancements:​

  • Use Bloom filters to speed up large analytical queries.
  • Use a balanced merge tree to evaluate a UNION or UNION ALL compound SELECT statement with an ORDER BY clause.
• Changed the ALTER TABLE statement to silently ignore unparsed entries in the sqlite_schema table when PRAGMA writable_schema=ON.