SQLite makes control characters explicit in SQL dumps

Text stored in a database can contain characters that a terminal interprets as instructions. Dumping those bytes directly confuses two jobs, preserving a value and displaying it safely. SQLite 3.50.0, released on 29 May, adds unistr() and unistr_quote() alongside changes to command-line output. The functions supply a readable SQL representation for values that should not be emitted as raw control characters.

The connection between the SQL functions and the shell is more interesting than a pair of new names in the function catalog. A dump must remain executable SQL while representing the stored text. Escaping a control character for display is useful only if the receiving engine can reconstruct its value. This release changes both sides of that representation.

I appreciate SQLite as a file format an application can inspect and move without a separate server. Textual dumps are another way to inspect that data. Making their representation explicit improves that workflow, particularly when the contents are not ordinary printable prose.

Unicode escapes are function input

The function implementation at the release tag describes unistr() as a work-alike of PostgreSQL's function. It recognizes four-digit hexadecimal escapes, six-digit escapes introduced by a plus sign, and the forms beginning with lowercase or uppercase u. A doubled backslash represents a literal backslash. Other characters pass through literally.

This is a function interpreting the contents of its argument. It does not redefine every string literal in SQLite as a language with backslash escapes. The SQL parser first supplies a string value, then unistr() processes the escape sequences inside that value. Keeping those stages separate prevents confusion when an application constructs SQL text through another programming language.

For example, these are SQL statements for the new release. The first reconstructs a line-feed character between two pieces of text. The second reconstructs a printable character using its Unicode value.

SELECT unistr('first\u000asecond');
SELECT unistr('\u0041');

The second result is A. The first result contains an actual line feed, which is different from retaining the visible characters backslash, u, and four digits. That difference is what permits a dump to show the escape form without losing the character's meaning when SQL restores it.

The implementation also rejects invalid escape syntax with an error. An unrecognized backslash sequence is not silently accepted as an alternative spelling. Code generating these expressions needs to use one of the supported forms and escape a literal backslash correctly. This is especially relevant when the generator's own language also processes backslashes before SQLite receives the SQL.

Quoting a value requires a reconstructible expression

The existing quote() function produces the text of an SQL literal. Its tagged implementation comments explain how strings and blobs are represented. unistr_quote() uses the same quoting machinery with an additional mode for escaping control characters through unistr(). Its result is SQL text representing the argument, rather than simply a transformed version of the argument for direct display.

That means two expressions can return different text while referring to the same underlying value after evaluation. One representation can contain a literal control character. Another can contain an escape within a function call. A consumer that treats every quoted value as a single string-literal token needs to understand that the new representation can be an expression.

The release makes this relevant beyond direct calls to unistr_quote(). The alternate-form flag # now changes the %Q and %q conversions in SQLite's formatting routines so that control characters receive escapes suitable for unistr(). This applies to the built-in formatting implementation used by C APIs and SQL formatting functions.

The CLI also avoids direct output of most control characters. Its .dump command uses the new function to encode special characters unless escape mode is off. A textual export can therefore become dependent on the new function even when the original schema did not contain calls to it. The engine that imports such an export needs to recognize the generated SQL.

Export compatibility differs from file compatibility

The immediate compatibility question concerns the text generated by .dump. A restore program cannot assume that an older engine understands every expression emitted by a newer shell. unistr() is new in this release, so its appearance in an export gives that export a version requirement distinct from the database file being exported.

This distinction is useful when choosing the tools that accompany an application. A database file and a SQL script are different interchange formats. The file's format rules do not promise that every future shell will restrict its generated SQL to functions available in every older engine. A workflow that transports dumps must account for the exporter and importer together.

The release notes describe a separate compatibility change in the OPFS SAHPool VFS for JavaScript and WebAssembly. They say databases created by its corrected filename-digest implementation cannot be read by older versions of that VFS, while the new implementation can read existing databases. That is a VFS-specific change, and should not be conflated with the new dump expressions or generalized to every SQLite file.

Blocking locks get an independent timeout setting

Another change in 3.50.0 concerns configuration rather than representation. The new sqlite3_setlk_timeout() interface separates the blocking-lock timeout from the busy timeout. The tagged public header explains the interaction and the order in which callers must configure them.

sqlite3_busy_timeout() sets both stored timeout values. Calling sqlite3_setlk_timeout() afterward changes only the blocking-lock value. Reversing the order allows the busy-timeout call to overwrite the separate setting. This is an observable configuration dependency even though the API names suggest two independent operations.

The new interface is useful only in builds with SQLITE_ENABLE_SETLK_TIMEOUT and a VFS that supports blocking locks. It applies to eligible locks on WAL-mode databases. With an unsupported build or VFS, the function does nothing. A nonzero setting alone therefore cannot establish that an application's operating system is performing blocking lock waits.

I prefer APIs that expose this distinction because waiting policy belongs to the application's operational behavior. A busy handler's sleep-and-retry policy and an eligible blocking-lock wait have different mechanisms. The new call lets supported integrations choose different values, while the header documents the configuration order needed to retain them.

Together, these changes make two existing boundaries more explicit. Text exports can represent control characters through reconstructible SQL expressions. Supported WAL integrations can configure blocking-lock waits separately after setting their busy timeout. Both require attention to the actual tool or build in use, because the new behavior belongs to particular interfaces rather than a universal change in how every SQLite operation executes.