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.
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:
| Option | Default | Description |
|---|---|---|
table | see each store | Table name. Must match /^[A-Za-z_][A-Za-z0-9_]{0,62}$/, since it is interpolated into SQL |
cleanupIntervalMs | off | Delete expired rows every N ms on an unref'd timer. 0 or unset disables it |
now | Date.now | Clock in epoch ms |
And exposes:
| Member | Description |
|---|---|
table | The 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
BIGINTepoch milliseconds computed in JavaScript, so every dialect compares the same numbers. - Key columns are
VARCHAR(255); on MySQL withutf8mb4_bincollation, 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.
| Method | Description |
|---|---|
increment(key, windowMs) | Count one hit and resolve with { count, resetAt } (resetAt in epoch ms) |
reset(key) | Delete the counter for key |
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.
| Method | Description |
|---|---|
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 |
-- 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.
| Method | Description |
|---|---|
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 |
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.
| Method | Description |
|---|---|
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 |
CREATE TABLE axil_locks (id VARCHAR(255) PRIMARY KEY, owner VARCHAR(64) NOT NULL, expires_at BIGINT NOT NULL);How it works:
- Each acquire generates a random owner id and inserts a row for the key.
- If the row exists, it takes the lock over only when the lease has lapsed (
UPDATE ... WHERE id = ? AND expires_at <= now). - Otherwise it retries with jittered exponential backoff, from 10 ms doubling up to 250 ms, until
waitMshas passed, then rejects. 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).
| Option | Default | Description |
|---|---|---|
namespace | 'axiljs' | Mixed into the lock id hash so separate apps on one database don't collide |
| Method | Description |
|---|---|
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 |
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.
| Method | Description |
|---|---|
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.
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 aSELECTinside one transaction. The upsert holds the row lock until commit, so the read sees exactly this change. - This requires an executor with
transaction();fromConnectionprovides 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 IGNOREand checksaffectedRows, 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. Concurrentsets of the same key can leave the union of their tags. - SQLite has no exact decimal type, so
cost_usdis 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 nextcleanup()removes it.
Exports
| Export | Kind |
|---|---|
fromConnection, toDialect, createSqlStores | functions |
SqlRateLimitStore, SqlIdempotencyStore, SqlCacheStore, SqlLockProvider, PgAdvisoryLockProvider, SqlBudgetStore | classes |
SqlDialect, SqlExecutor, SqlQueryResult, StoreOptions, SqlStores, SqlStoresOptions, SqlLockKind | types |
SqlRateLimitStoreOptions, SqlIdempotencyStoreOptions, SqlCacheStoreOptions, SqlLockProviderOptions, PgAdvisoryLockProviderOptions, LockAcquireOptions, LockRelease, SqlBudgetStoreOptions, SqlBudgetAmounts, SqlBudgetWindow | types |
Related
Was this page helpful?