sql-storage-adapter v0.7.0

Added

• Segments. A segment option on createDatabase(), resolveStorageAdapter() and each adapter keeps one user's tables apart from another's in a database they share. On PostgreSQL a segment is a schema of its own: every connection of the adapter's pool starts with search_path set to the segment, then public, as a startup option (-c search_path=<segment>,public), so a connection the pool opens to replace one the server ended starts in the segment too, RESET search_path and DISCARD ALL return to it, and the types and operators of extensions in public (pgvector's vector and <->) resolve. open() creates the schema when it is missing, and PGOPTIONS is kept beside the segment's option. transaction() and a withConnection() connection run in the segment. On better-sqlite3, sql.js and the Electron main adapter a segment is a file of its own (app.sqlite3 becomes app-agentos.sqlite3; :memory: stays as it is), and on IndexedDB and Capacitor a database name of its own (app-db becomes app-db-agentos). A segment name is lower-case letters, digits and underscores, at most 63 characters, and not public, information_schema or a name beginning with pg_; another is refused before any adapter is tried, and so is a segment given with a pool of the caller's (postgres.pool), which a resolution without strict would otherwise have passed over for a local file in the segment. segmentName(), segmentFilePath() and validateSegment() are exported. A segmented Electron main adapter leaves the renderer's IPC channels, one set per process, to the adapter without a segment.
• On a segmented PostgreSQL adapter, a CREATE EXTENSION that names no schema installs the extension into public, where every segment and the host find it, instead of into the segment, where only that segment would: through the adapter, inside a transaction (the rest of the transaction stays in the segment) and on a withConnection() connection. When an extension a segment asks for is already installed in a schema its search_path does not reach, the adapter warns once, through the host logger, and names the ALTER EXTENSION ... SET SCHEMA public that moves it. A connection whose BEGIN or ROLLBACK fails around such a statement, or whose search_path cannot be set back, is closed instead of returned to the pool.
• The Postgres adapter runs on a pool its caller made: createPostgresAdapter({ pool }) or new PostgresAdapter({ pool }), from the start, with no open() needed. close() leaves that pool open unless ownsPool: true is given, in which case close() ends it once, however often it is called; open() after close() takes the pool up again. onError receives the errors an idle connection of the pool reports, getPool() returns the pool the adapter runs on, and getPoolStats() counts a pool of the caller's as it counts one the adapter made. The settings that configure a pool when one is made (allowExitOnIdle and typeParsers among them), and segment, are refused with pool: set them on the pool (typeParsers as its types). createDatabase({ postgres: { pool } }) opens PostgreSQL on it. A host that wrote its Pool into the adapter's pool field before open(), as hosts on 0.6 did, keeps working: the adapter runs on it and ends it on close(), as 0.6 did. PostgresPoolLike, PostgresPoolClientLike and PostgresQueryResultLike describe the part of pg's Pool the adapter uses.
• IndexedDB adapter: durability: 'commit'. run(), exec(), a write through all() or get() (INSERT ... RETURNING) and transaction() resolve once their write is committed in IndexedDB, on the IndexedDB transaction's complete event, and reject when IndexedDB refuses or aborts it; the write stays in the database and the next save stores it. While a transaction begun with BEGIN is open, its statements resolve at once and its rows are stored when its COMMIT resolves. 'interval', the timer, stays the default.
• IndexedDB adapter: flush() stores every write made so far: it waits for an open transaction(), resolves at once when nothing is waiting, and rejects while a transaction begun with BEGIN is open, which sql.js's export would end.
• IndexedDB adapter: crossTab, so two tabs of a site on one database keep each other's writes. Every statement and transaction runs under a Web Lock on the database, on the newest stored image, and is stored before the lock is let go; a version stamp stored beside the image tells a tab that another has saved, and the tab reloads before it reads or writes. Where the browser has no Web Locks (Safari before 15.4, an insecure origin, WebKit's Lockdown Mode), the stamp alone keeps both tabs' writes: a statement that finds another tab saved first runs again on that tab's image, and a transaction() another tab saved over is refused with IndexedDbConflictError, storing nothing of it. crossTab: { locks } takes a lock manager of the caller's (IndexedDbLockManager), and crossTab: { locks: null } the stamp alone. Every save writes the stamp, with crossTab or without.
• @framers/sql-storage-adapter/kv, a module that imports no adapter: the KeyValueStore contract (get, set, delete, keys(prefix), deletePrefix(prefix), memoryOnly), namespaced(), MemoryKeyValueStore, and openIndexedDbStore() and IndexedDbKeyValueStore with idb() and their IndexedDB shapes, moved unchanged from @framers/agentos src/core/storage/browser, so a page's database, object store, version and keys stay where they are. createSqlKeyValueStore(adapter, { table }) keeps the same contract in a SQL table on any adapter: keys in the order IndexedDB gives them, prefixes read literally, and each value kept as text that comes back as the structured clone gives it back (undefined, -0, NaN, bigints, dates, regular expressions, maps, sets, buffers and views, boxed primitives, errors, shared references and cycles); a value the structured clone refuses, or a host object text cannot hold, is refused with a DataCloneError before anything is written.

Changed

• createDatabase() in a browser keeps the database in IndexedDB: it chooses ['indexeddb', 'sqljs'], as the resolver does, where it chose ['sqljs'], a database in memory that a reload lost. createDatabase({ file: ':memory:' }) and createMemoryDatabase() keep sql.js in memory, and in a Capacitor app on a phone createDatabase() chooses ['capacitor', 'indexeddb', 'sqljs'], as the resolver does. The indexedDb options reach the IndexedDB adapter whole, through the resolver too: durability, crossTab, segment and the rest.
• The Postgres adapter listens for the errors idle connections of a pool it makes report, and logs a warning through the host logger (setStorageLogger()) when no onError was given. pg raises such an error (the server ended the connection, the network dropped) as an event that ended the process when nothing listened.
• A Postgres open() that cannot connect ends the pool it made and holds none, so a later open() starts again; two open() calls at once open once, and two close() calls at once close once.
• CI's PostgreSQL service is the pgvector image (pgvector/pgvector:0.8.7-pg16), and a browser job runs the IndexedDB adapter's and the key-value module's specs in Chromium and WebKit through Playwright (@vitest/browser and playwright are dev dependencies). The NodeNext consumer check type-checks a pg Pool against the pool option and imports @framers/sql-storage-adapter/kv.

Fixed

• exportData() on PostgreSQL exports the tables of the current schema, the segment's on a segmented adapter, and reads each table's columns from that schema alone, in their order. It listed public's tables, and read the columns of every table of that name in any schema.
• IndexedDB adapter: a save keeps the connection settings a caller made (PRAGMA foreign_keys = ON and the rest). sql.js's export(), which every save runs, closes the connection and opens it again, which reset them. A write made through all() or get() outside a transaction is saved, and importDatabase() refuses bytes that are not a SQLite database, keeping the database it had.
• Electron main adapter: run() resolves with its result. It threw a TypeError after every write while multi-window broadcasting was on, the default: three of the patterns that find the statement's tables for the broadcast lacked the global flag matchAll() needs.
• sql.js adapter: open({ filePath: ':memory:' }) keeps the database in memory. It wrote it to a file named :memory: in the working directory, which every such adapter then shared.