@dads-framework/sqlite-access
On this page
Check permissions, list accessible resources, resolve transitive subjects, and commit relationship changes alongside business data in SQLite.
Why SQLite
Sometimes the whole application fits on one machine or inside an embedded store, and adding a separate authorization cluster creates more moving parts than the product has. Keep relationship facts next to application rows, verify access inside the same BEGIN IMMEDIATE write transaction, and drop the outbox.
The adapter runs on Node.js built-in node:sqlite (DatabaseSync) with zero native addons. Callers can also wrap better-sqlite3, Drizzle SQLite, or libSQL behind the SqliteExecutor port.
Try it
Use Node.js 22.5 or later. Install the packages and run the script against an in-memory SQLite database.
npm install @dads-framework/access @dads-framework/sqlite-access zod
npm install -D tsx
Save as check-sqlite-access.mts and run npx tsx check-sqlite-access.mts.
import { DatabaseSync } from "node:sqlite";
import { access, accessModel, entityHandle } from "@dads-framework/access";
import { relationshipWrite } from "@dads-framework/access/runtime";
import {
createNodeSqliteExecutor,
SqliteAuthorization,
sqliteRelationshipSchemaMigration,
} from "@dads-framework/sqlite-access";
import { z } from "zod";
const user = entityHandle({ wireName: "user", idSchema: z.string() });
const team = entityHandle({
wireName: "team",
idSchema: z.string(),
kind: "scope",
});
const teamAccess = access(team, {
roles: { member: user },
permissions: (roles) => ({ view: [roles.member] }),
});
const model = accessModel([
access(user, { roles: {}, permissions: () => ({}) }),
teamAccess,
]);
const database = new DatabaseSync(":memory:");
try {
database.exec(sqliteRelationshipSchemaMigration.sql);
const authorization = new SqliteAuthorization({
model,
namespace: "app",
executor: createNodeSqliteExecutor(database),
});
const resource = { type: team.wireName, id: "team-1" };
const subject = { type: user.wireName, id: "alice" };
const relation = teamAccess.roles.member.roleName;
const query = {
resource,
subject,
permission: teamAccess.permissions.view.permissionName,
};
console.log(await authorization.check(query));
await authorization.writeRelationships([
relationshipWrite(resource, relation, subject),
]);
console.log(await authorization.check(query));
await authorization.deleteRelationships({
resourceType: resource.type,
resourceId: resource.id,
relation,
subject,
});
console.log(await authorization.check(query));
} finally {
database.close();
}
The script prints false, true, and false as membership is granted and revoked.
Protected writes
Use withProtectedSqliteTransaction({ executor, authorization, now }, work) when an authorization check guards a write. The helper runs BEGIN IMMEDIATE, pins one nowMs snapshot for the transaction, and passes { executor, authorization } bound to that transaction.
Check permissions again inside the callback and perform both business table writes and relationship updates through the scoped handles. Both handles expire as soon as the callback returns or throws. On error, the helper rolls back both application rows and relationship changes.
Use it
Apply sqliteRelationshipSchemaMigration.sql (or iterate sqliteRelationshipSchemaMigration.statements) during application startup or migrations. The schema creates dad_authorization_relationship along with its reverse subject lookup index and partial expiration index.
SqliteAuthorization compiles @dads-framework/access models into parameterized SQLite queries covering:
- Direct relations, nested and cyclic
group#membersubject sets, and wildcards (user:*). - Expiring relationships (
expires_atepoch milliseconds) evaluated consistently at a single timestamp across reads and updated via upsert. - Polymorphic arrows across multiple direct target entity types (
workspace | folder). - Recursive monotonic arrows (such as nested folders) with cycle termination on cyclic graph data.
- Transitive
lookupSubjectsreturning concrete subject IDs andhasWildcard. - Intersection arrows (
throughAll/.all()) requiring at least one active target and satisfaction across every active target.
What it refuses to do
Compilation rejects missing relation or permission targets, empty expressions, local permission self-cycles, non-monotonic recursive cycles through excluding or throughAll, and non-linear recursive intersections.
Permission checks and resource lookups require a concrete subject (id cannot be empty or *, and relation must be omitted). Relationship writes reject unknown relations, mismatched subject types, empty identifiers, and invalid expiresAt dates. Scoped transaction handles reject all calls after the transaction callback ends.
Contract
SqliteAuthorization extends Authorization and implements check, checkMany, checkEach, lookupResources, lookupSubjects, writeRelationships, deleteRelationships, relationshipExists, listDirectSubjects, applyChanges, and inTransaction.
Unknown permission names return false, [], or { subjects: [], hasWildcard: false }. Successful writes and deletes return an empty consistency token because SQLite reads observe committed writes directly. Database connection lifecycle and migration execution remain application-owned.