Shared Stores

Store Reference

Reference for every @axiljs/stores class: options, methods, table schemas, atomicity on PostgreSQL, MySQL and SQLite, cleanup, and caveats such as clock skew and lease expiry.

10 min readUpdated DocumentationEdit this page
On this page18

Every class on this page takes an SqlExecutor as its first argument and an options object as its second. createSqlStores() builds all of them at once.

Common options

Every store except PgAdvisoryLockProvider accepts:

OptionDefaultDescription
tablesee each storeTable name. Must match /^[A-Za-z_][A-Za-z0-9_]{0,62}$/, since it is interpolated into SQL
cleanupIntervalMsoffDelete expired rows every N ms on an unref'd timer. 0 or unset disables it
nowDate.nowClock in epoch ms

And exposes:

MemberDescription
tableThe table name in use
ensureSchema()Create the table and indexes. Idempotent; called automatically on first use
cleanup()Delete expired rows now. Resolves with the number of rows removed when the driver reports it
close()Stop the cleanup timer. Idempotent. Never closes the database connection

Storage format

  • Times are BIGINT epoch milliseconds computed in JavaScript, so every dialect compares the same numbers.
  • Key columns are VARCHAR(255); on MySQL with utf8mb4_bin collation, so keys are case-sensitive on every dialect.
  • Long keys: a key longer than 200 characters is stored as its first 120 characters, #, and its SHA-256 hex digest, so it stays unique and indexable.
  • Values in the idempotency and cache stores go through JSON.stringify.

SqlRateLimitStore

Fixed-window counters. Same shape as @axiljs/security's RateLimitStore and @axiljs/runtime's rate-limit counter store, so one instance can back both rateLimit({ store }) and setRateLimitStore().

Default table: axil_rate_limits.

MethodDescription
increment(key, windowMs)Count one hit and resolve with { count, resetAt } (resetAt in epoch ms)
reset(key)Delete the counter for key
SQL
SQL
CREATE TABLE axil_rate_limits (id VARCHAR(255) PRIMARY KEY, hits INTEGER NOT NULL, reset_at BIGINT NOT NULL);
CREATE INDEX axil_rate_limits_reset_at_idx ON axil_rate_limits (reset_at);

Atomicity. PostgreSQL and SQLite use one statement, INSERT ... ON CONFLICT (id) DO UPDATE ... RETURNING hits, reset_at: a window that has expired restarts at 1, otherwise the count is incremented. Concurrent hits get distinct counts; 50 parallel increments return exactly 1 to 50. MySQL runs INSERT ... ON DUPLICATE KEY UPDATE and then SELECT in one transaction (see MySQL).

Cleanup deletes rows whose reset_at has passed. An expired row is also overwritten the next time its key is hit.

SqlIdempotencyStore

Results for tIdempotent (@axiljs/runtime's IdempotencyStore).

Default table: axil_idempotency.

MethodDescription
get(key){ result }, or undefined when missing or expired
set(key, { result }, ttlMs)Store or replace the result for ttlMs
delete(key)Remove the record
SQL
SQL
-- payload is LONGTEXT on MySQL
CREATE TABLE axil_idempotency (id VARCHAR(255) PRIMARY KEY, payload TEXT NOT NULL, expires_at BIGINT NOT NULL);
CREATE INDEX axil_idempotency_expires_at_idx ON axil_idempotency (expires_at);

set is an upsert of { result } as JSON. Expired rows are ignored on read and removed by cleanup().

SqlCacheStore

Entries for tCache, tCachePut, tCacheEvict and tCacheInvalidate (@axiljs/runtime's CacheStore). Entries live in one table, tags in a second one.

Default tables: axil_cache and axil_cache_tags. The extra option tagTable (default <table>_tags) names the tag table.

MethodDescription
get(key){ value }, or undefined when missing or expired
set(key, value, ttlMs, tags?)Upsert the entry and replace its tags
delete(key)Remove the entry and its tag rows
deleteByTag(tag)Remove every entry with that tag, and all of those entries' tag rows (not only this tag's)
clear()Remove every entry and tag row
SQL
SQL
1CREATE TABLE axil_cache (id VARCHAR(255) PRIMARY KEY, payload TEXT NOT NULL, expires_at BIGINT NOT NULL);
2CREATE INDEX axil_cache_expires_at_idx ON axil_cache (expires_at);
3CREATE TABLE axil_cache_tags (tag VARCHAR(255) NOT NULL, id VARCHAR(255) NOT NULL, PRIMARY KEY (tag, id));
4CREATE INDEX axil_cache_tags_id_idx ON axil_cache_tags (id);

Values are stored as { v: value } JSON. cleanup() deletes expired entries together with their tag rows.

SqlLockProvider

Portable lease locks for tLock (@axiljs/runtime's LockProvider) on every dialect.

Default table: axil_locks.

MethodDescription
acquire(key, { ttlMs, waitMs })Resolve with a release() function once the lock is held, or reject with lock '<key>' unavailable after <waitMs>ms
cleanup()Delete lapsed leases (they are also taken over on demand)
close()Stop the cleanup timer. Held leases are not released
SQL
SQL
CREATE TABLE axil_locks (id VARCHAR(255) PRIMARY KEY, owner VARCHAR(64) NOT NULL, expires_at BIGINT NOT NULL);

How it works:

  1. Each acquire generates a random owner id and inserts a row for the key.
  2. If the row exists, it takes the lock over only when the lease has lapsed (UPDATE ... WHERE id = ? AND expires_at <= now).
  3. Otherwise it retries with jittered exponential backoff, from 10 ms doubling up to 250 ms, until waitMs has passed, then rejects.
  4. release() deletes the row only if the owner still matches, so a holder whose lease lapsed cannot release the new holder's lock. Calling it twice is harmless.

Warning

Lease locks are leases, and there is no renewal. If the holder runs longer than ttlMs, another server can take the lock over while the first is still working. Choose ttlMs above the worst-case run time.

PgAdvisoryLockProvider

PostgreSQL transaction-level advisory locks for tLock. The constructor throws unless the executor's dialect is postgres and it has transaction() (use fromConnection).

OptionDefaultDescription
namespace'axiljs'Mixed into the lock id hash so separate apps on one database don't collide
MethodDescription
acquire(key, { ttlMs, waitMs })Resolve with release() once held, or reject after waitMs (same backoff as SqlLockProvider)
lockId(key)The BIGINT advisory lock id for key, as a decimal string
TypeScript
TS
1import { setLockProvider } from '@axiljs/runtime'
2import { PgAdvisoryLockProvider } from '@axiljs/stores'
3
4setLockProvider(new PgAdvisoryLockProvider(exec, { namespace: 'billing' }))

How it works: the lock id is the first 8 bytes of SHA-256(namespace + key), read as a signed 64-bit integer. Each attempt opens a transaction on one pooled connection and calls pg_try_advisory_xact_lock(id). On success the transaction stays open until release() is called or ttlMs passes, and COMMIT frees the lock. If the process dies, PostgreSQL frees the lock when the connection drops. No table is used.

Warning

Each held lock keeps one pooled connection busy. Size the pool for it: a method that holds a lock and also runs its own queries needs at least two connections. The lock is also lost if idle_in_transaction_session_timeout ends the session. Lock ids are 64-bit hashes, so a collision between distinct keys is possible in principle but negligible.

SqlBudgetStore

Counters for withBudget() from @axiljs/ai (its BudgetStore). One row per scope, window name and window start holds the running token, cost and request totals.

Default table: axil_ai_budget. The extra option retainMs (default 0) keeps rows that long after their window ends before cleanup() removes them, for example for usage reports.

MethodDescription
add(scope, window, { tokens, costUsd, requests })Atomically add the amounts (which may be negative) and resolve with the window's new totals
get(scope, window)The window's current totals; zeros when nothing was recorded
cleanup()Delete rows whose window_end is more than retainMs in the past

window is { name, start, end }: name is the window name ('minute', 'hour', 'day' or 'month' from @axiljs/ai; letters, digits, _ and -, up to 32 characters), start is inclusive and end exclusive, both in epoch ms. Amounts must be finite numbers; anything else throws.

SQL
SQL
1-- cost_usd is DECIMAL(30, 12) on MySQL and SQLite
2CREATE TABLE axil_ai_budget (
3  scope VARCHAR(255) NOT NULL, window_name VARCHAR(32) NOT NULL, window_start BIGINT NOT NULL,
4  tokens BIGINT NOT NULL, cost_usd NUMERIC(30, 12) NOT NULL, requests BIGINT NOT NULL, window_end BIGINT NOT NULL,
5  PRIMARY KEY (scope, window_name, window_start)
6);
7CREATE INDEX axil_ai_budget_window_end_idx ON axil_ai_budget (window_end);

Atomicity. withBudget reserves an estimate before each call, then adds the difference to the actual usage, or the negated estimate if the call fails. The store applies each of these as one atomic increment, so concurrent requests on different servers cannot jointly overrun a limit. PostgreSQL and SQLite use one INSERT ... ON CONFLICT (scope, window_name, window_start) DO UPDATE SET tokens = tokens + excluded.tokens, ... RETURNING tokens, cost_usd, requests; MySQL uses ON DUPLICATE KEY UPDATE plus SELECT in one transaction, like the rate-limit store.

Precision. Costs are stored as an exact decimal with 12 fractional digits (pico-dollars), so a reservation and its release cancel exactly. Tokens and requests are BIGINT and are rounded to integers. All totals come back as JavaScript numbers.

Consistency and caveats

Clocks

Windows, expiries and leases use each server's Date.now(). Keep server clocks in sync (NTP); skew between servers shifts window boundaries and lease expiry by the amount of the skew.

JSON values

Cached and idempotent results go through JSON.stringify: a Date becomes a string, Map, Set and class instances lose their type, and undefined inside objects is dropped.

Expired rows

Expired rows are skipped on read but stay in the table until cleanup() runs (manually or through cleanupIntervalMs), or, for locks and rate limits, until the key is used again.

PostgreSQL

All operations are single atomic statements or short transactions, safe under any number of servers. CREATE TABLE IF NOT EXISTS can race when several servers start at once; schema creation retries once.

MySQL

  • MySQL has no RETURNING, so the rate-limit and budget stores run the upsert and then a SELECT inside one transaction. The upsert holds the row lock until commit, so the read sees exactly this change.
  • This requires an executor with transaction(); fromConnection provides one. With a custom executor that lacks it, the two statements run separately and a concurrent hit can make two callers see the same count.
  • Upserts use VALUES(col), which MySQL 8.0.20+ deprecates but still accepts, and which MariaDB supports.
  • Lock acquisition uses INSERT IGNORE and checks affectedRows, so the executor must report it.
  • Secondary indexes are declared inline in CREATE TABLE.

SQLite

  • Suitable for one host: several processes sharing one database file, not several servers.
  • The stores never open transactions on SQLite, because its connection is shared by the whole app; every correctness-critical step is a single statement.
  • Cache set (entry plus tags) is therefore not atomic as a whole. Concurrent sets of the same key can leave the union of their tags.
  • SQLite has no exact decimal type, so cost_usd is kept as a REAL. Totals are rounded to 12 decimal places on read, which is exact for practical sums.

Budget edge cases

  • A cost below 0.5e-12 USD per add rounds to zero.
  • A settle or release that arrives after cleanup() removed its window's row creates a short-lived row with negative totals for that already-ended window; the next cleanup() removes it.

Exports

ExportKind
fromConnection, toDialect, createSqlStoresfunctions
SqlRateLimitStore, SqlIdempotencyStore, SqlCacheStore, SqlLockProvider, PgAdvisoryLockProvider, SqlBudgetStoreclasses
SqlDialect, SqlExecutor, SqlQueryResult, StoreOptions, SqlStores, SqlStoresOptions, SqlLockKindtypes
SqlRateLimitStoreOptions, SqlIdempotencyStoreOptions, SqlCacheStoreOptions, SqlLockProviderOptions, PgAdvisoryLockProviderOptions, LockAcquireOptions, LockRelease, SqlBudgetStoreOptions, SqlBudgetAmounts, SqlBudgetWindowtypes

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY