sqlk — SQL-backed keyed lists for Tcl

sqlk provides persistent, hierarchical Tcl keyed lists backed by SQL. It keeps the familiar command-oriented Tcl API while storing values, attributes, hierarchy, and sibling order in SQLite or MySQL/InnoDB.

The library is designed for long-running Tcl applications: database instances keep only their state and lightweight command ensembles, while shared implementation procedures remain centralized and hot-reloadable.

Highlights

• Tcl 8.6, with no object system and no dict-based data model
• SQLite is the default backend and remains compatible with existing sqlk 0.8 databases
• MySQL 8.4/InnoDB backend through mysqltcl 3.052
• Persistent root and nested keys with automatic intermediate-node creation
• Stable sibling ordering, subtree deletion, and kmove
• Per-key attributes
• Tcl and human-readable serialization
• Optional AES encryption for names, values, and attributes
• Lazy database-driver loading: requiring sqlk alone loads neither SQLite nor mysqltcl

Requirements

• Tcl 8.6
• For SQLite: the Tcl sqlite3 package
• For MySQL: MySQL 8.4 with InnoDB and mysqltcl 3.052
• For encrypted databases: the Tcl aes package

Place sqlk.tcl and pkgIndex.tcl in a directory on Tcl's package path, then load it with:

package require sqlk 0.8

Quick start with SQLite

SQLite is selected when -backend is omitted:

package require sqlk

# kinit creates a database ensemble named "db".
sqlk::kinit app.sqlite -procname db

# Create a keyed-list variable and expose its ensemble as "config".
db varadd config config

config kset user.name "Raúl"
config kset user.id 1001
config kset app.theme dark app.language es

puts [config kget user.name]  ;# Raúl
puts [config keys user]       ;# name id
puts [config tree]            ;# user user.name user.id app ...

config attrset user.name type string source profile
puts [config attrget user.name type]

db closefile

For a temporary SQLite database:

set db [sqlk::kinit :memory:]
$db varadd scratch
$db kset scratch answer 42
puts [$db kget scratch answer]
$db closefile

:memory: is exclusively a SQLite target and cannot be used with encryption.

MySQL/InnoDB

Select MySQL explicitly and pass its connection target as an alternating option/value Tcl list. The target belongs to the backend; the common keyed-list layer does not interpret its connection fields.

set target [list \
    -host 127.0.0.1 \
    -port 3306 \
    -user sqlk_app \
    -password $password \
    -db application]

sqlk::kinit $target -backend mysql -procname db

db varadd config
db kset config service.name sqlk service.enabled 1
puts [db kget config service.name]
db closefile

Accepted target options are -host, -port, -socket, -user, -password, -db, -ssl, and -compress. -db is required; use either -host or -socket, not both. Transparent mysqltcl reconnect is intentionally unsupported.

sqlk fixes mysqltcl communication to UTF-8 internally and stores MySQL names and paths as utf8mb4. Connection encoding is not a configurable target option.

Passwords are used only while opening the connection. They are removed from the target stored in the instance namespace and are never exposed by sqlk::klist.

On an empty database, sqlk creates and validates its InnoDB schema. Existing schemas are validated strictly and are never silently migrated or repaired.

Core model

A database can contain multiple named keyed-list variables. Keys form a dot-separated hierarchy:

db varadd settings

# Missing "server" and "server.tls" nodes are created automatically.
db kset settings server.tls.enabled 1

puts [db keys settings]             ;# server
puts [db keys settings server]      ;# tls
puts [db kexist settings server.tls.enabled] ;# 1

Values and attributes are Tcl values. Unencrypted MySQL values use UTF-8 text transport to preserve Tcl semantic values; encrypted values and attributes use a binary-safe transport that preserves ciphertext exactly.

Sibling order is persistent:

db kset settings first 1 second 2 third 3

# Move "third" to the first position.
db kmove settings third

# Move it after "second".
db kmove settings third second

kdel removes the selected key and its complete subtree:

db kdel settings server.tls

Public API

Package commands

sqlk::kinit target ?-backend sqlite|mysql? ?-procname command? ?-enckey key?
sqlk::klist
sqlk::kclose instance
sqlk::kcloseall

Without -procname, kinit returns a generated command such as ::sqlk::00000001. With -procname, it creates the requested command and retains the historical empty return value.

Database ensemble

Command	Purpose
db varlist	List variables visible to the instance
db varadd name ?procname?	Create a variable and optional command alias
db vardel name	Delete a variable and its stored subtree
db varcmd name ?procname?	Return or assign its per-variable ensemble
db kset name key value ?key value ...?	Set one or more values
db kget name key	Read a value
db kexist name key	Test key existence
db keys name ?key?	List direct children in persistent order
db tree name ?key?	Return a depth-first flat path list
db kdel name key	Delete a key and its subtree
db kmove name key ?afterkey?	Move a sibling first or after another sibling
db attrset name key attr value ?attr value ...?	Set attributes
db attrget name key ?attr?	List attributes or read one value
db attrdel name key attr	Delete an attribute
db serialize name ?options?	Serialize a variable
db parse name ?options? data	Parse Tcl serialization into a variable
db backup filename	SQLite online backup; unsupported by MySQL
db restore filename	SQLite online restore; unsupported by MySQL
db closefile	Close the connection and remove the instance

Variable ensemble

varadd creates a variable ensemble, so the variable name can be omitted from data operations:

db varadd settings settings

settings kset app.name sqlk
settings kget app.name
settings kexist app.name
settings keys app
settings tree
settings kmove app.name
settings attrset app.name type string
settings attrget app.name type
settings attrdel app.name type
settings kdel app
settings serialize -format TCL
settings parse $serialized

Serialization

Tcl serialization is the lossless import/export format implemented by the public API. It preserves hierarchy, values, attributes, and sibling order.

set serialized [settings serialize -format TCL]
set indented   [settings serialize -format TCL -indent 1]

db varadd restored restored
restored parse $serialized

serialize -format TEXT produces a human-readable dump. XML serialization and parsing are not implemented.

Encryption

Encryption is selected when the database instance is opened and requires the aes Tcl package:

sqlk::kinit secrets.sqlite -procname secretsdb -enckey $secret
secretsdb varadd credentials
secretsdb kset credentials service.token $token
secretsdb closefile

The same key must be supplied when reopening the database. Names use deterministic encryption so they remain searchable; values and attributes use a random IV stored with the ciphertext.

Encrypted sqlk 0.7 databases used RC4 and are not directly compatible with sqlk 0.8 AES storage. Export them with sqlk 0.7 before importing with sqlk 0.8.

Backup and restore

SQLite supports its online backup and restore operations:

db backup snapshot.sqlite
db restore snapshot.sqlite

MySQL intentionally does not emulate SQLite file backup. These commands return controlled unsupported-operation errors; use your normal MySQL administration and backup tooling outside sqlk.

Architecture

Each live database has an instance namespace containing its connection handle, selected backend, sanitized target, encryption state, and thin wrapper commands. Shared common procedures exist once under ::sqlk and deliberately use uplevel 1 to operate on instance state.

Storage and lifecycle operations dispatch directly to matching procedures under:

::sqlk::backend::sqlite
::sqlk::backend::mysql

This preserves sqlk's hot-reload-friendly design: shared procedures can be redefined in a long-running daemon and existing instances use the updated implementation without losing their namespace state.

Compatibility notes

• Existing sqlk 0.8 SQLite databases open without migration.
• SQLite remains the implicit default backend.
• SQLite :memory: behavior is unchanged.
• Historical API behavior and known anomalies are intentionally preserved for compatibility.
• MySQL uses InnoDB and serializes sqlk writers with a singleton row lock to reproduce the mutation assumptions of SQLite BEGIN IMMEDIATE.
• MySQL AUTO_INCREMENT may leave gaps after rolled-back inserts; public hierarchy and ordering semantics do not depend on contiguous IDs.
• Physical MySQL UTF-8 bytes can differ from SQLite's Tcl storage representation while returning the same Tcl value.
• MySQL backup and restore are deliberately unsupported.

License

MIT

Acknowledgements

Originally created in 2008 from real production needs for sharing structured state between Tcl processes. It remains intentionally small, command-oriented, and compatible with long-running Tcl applications.

☕ Support my work

If this project has been helpful to you or saved you some development time, consider buying me a coffee!