Browse documentation

@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.

Shell
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.

TypeScript
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#member subject sets, and wildcards (user:*).
  • Expiring relationships (expires_at epoch 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 lookupSubjects returning concrete subject IDs and hasWildcard.
  • 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.