Skip to content
Firstly

Module - SQL Admin

A drop-in SQL console to run raw queries against your database, gated by a role. By default a query runs with ['read']: a READ ONLY transaction, one statement at a time (Postgres extended protocol), so the database itself rejects writes. The “allow writes” box adds 'write'. Comes with a companion <SqlAdmin /> Svelte component styled with raw Tailwind utilities (no daisyUI / shadcn plugin needed).

Once you have it setup, assign you the role "SqlAdmin.Admin" (You can get it via Roles_SqlAdmin.SqlAdmin_Admin), or rely on the global FF_Role.FF_Role_Admin.

Results of every query are also logged to the browser console as for AI: <json rows> - handy for chrome-devtools / AI agents inspecting the page via list_console_messages.

Installation

Section titled “Installation”
Terminal window
npm add firstly@latest -D

Setup

Section titled “Setup”
src/server/api.ts
import { remultApi } from 'remult/remult-sveltekit'
import { sqlAdmin } from 'firstly/sqlAdmin/server'


export const api = remultApi({
  modules: [
    sqlAdmin({
      // OPTIONAL - the route where you mount <SqlAdmin />, used in the AI hint logged on boot.
      // path: '/sql/admin',
      // OPTIONAL - override the SqlDatabase used to execute queries.
      // Defaults to `SqlDatabase.getDb()` (the active Remult data provider).
      // dp: async () => myCustomSqlDatabase,
      // OPTIONAL - reads run as an auto-created read-only role, see below.
      // readPool: 'auto', // or false, or your own pg.Pool
    }),
  ],
})

Usage

Section titled “Usage”

Mount the component on a protected route. The component is styled with raw Tailwind utilities, so your project needs Tailwind set up - nothing else.

<!-- src/routes/(admin)/sql/admin/+page.svelte -->
<script lang="ts">
  import { SqlAdmin } from 'firstly/sqlAdmin'
</script>


<SqlAdmin />

Then assign the role to your user:

import { Roles_SqlAdmin } from 'firstly/sqlAdmin'


// somewhere where you grant roles
user.roles = [Roles_SqlAdmin.SqlAdmin_Admin]

That’s it! 🎉

Read-only role

Section titled “Read-only role”

READ ONLY is a transaction mode, not a permission: your app role can still write. So by default (readPool: 'auto'), with nothing to configure, reads run as a dedicated role instead.

At boot it checks the role in one query and only (re)creates it when something is off. It upserts ff_readonly_<db> through the app’s own connection: LOGIN, SELECT on every schema of this database (and on tables created later), default_transaction_read_only = on, statement_timeout = '30s'. Its password is derived from the app’s, so every instance agrees and nothing is stored. The app role needs CREATEROLE (a Coolify POSTGRES_USER is a superuser); without it boot logs an error and reads stay on the app role. readPool: false turns it off; on a database other than Postgres it does nothing. A pre-existing role of that name with more than read rights is refused.

Prefer to manage the role yourself? Pass a pool: readPool: new pg.Pool({ connectionString: env.DATABASE_URL_READONLY, max: 2 }).

The READ ONLY transaction and one-statement rule stay on top. write always uses the app role.

Behind a WAF

Section titled “Behind a WAF”

Some firewalls (Cloudflare, ModSecurity rule sets) block requests containing SELECT ... FROM, or responses containing Postgres errors. <SqlAdmin /> and ff-sql send the SQL as ffsql1:<base64>, and a request sent that way gets its result (or error message) back encoded the same way, as a string. It is a disguise, not security. Plain SQL still works and gets plain JSON (ff-sql --raw sends it that way, for a server older than the encoding). encodeSqlWire / decodeSqlWire are exported from firstly/sqlAdmin.

SQL tokens - run SQL from outside the browser

Section titled “SQL tokens - run SQL from outside the browser”

Bearer tokens for a script or an AI on a dev machine that needs a fact only the production database holds. Nothing is registered unless you opt in - no entities, no endpoints.

src/server/api.ts
sqlAdmin({
  // `sqlAdmin: false` registers no controller at all; combined with `tokens` it throws,
  // because tokens run through that same `exec` endpoint.
  tokens: {
    // Which capabilities may be minted. `read` runs inside a READ ONLY transaction,
    // one statement at a time (extended query protocol). `write` runs SQL as is.
    capabilities: ['read'],
    // OPTIONAL - the token then acts as its minter with their LIVE roles
    // (lose admin, tokens die). Without it the token is its own authority.
    userFromId: (id) => loadUser(id),
    // OPTIONAL - prefix: 'ffsql_', apiPath: '/api', callLogRetentionDays: 30
  },
})
<script lang="ts">
  import { SqlTokens } from 'firstly/sqlAdmin'
</script>


<!-- Defaults to a ready-to-run `ff-sql` line; override `command` for your own. -->
<SqlTokens capabilities={['read']} />

The rules the module enforces:

  • A token is a bag of capabilities that acts as its minter, for a fixed lifetime (1h, 24h, 7d). Only the sha256 is stored; the raw value is shown once. Leave the name empty to get an adjective-animal-3f9 one.
  • Minting and revoking need a live session - a token can never mint, extend or revoke a token.
  • A token answers exactly one path: the console’s own /api/ff/sqlAdmin/exec. Anywhere else it authenticates nobody. Revoking is a plain repo(SqlToken).update(id, { revokedAt }) - the only writable field, and final.
  • Revoke and delete are different gestures: revoking is the kill switch and keeps the row and its calls (the audit trail); deleting is cleanup and takes the calls with it. A live token can only be revoked - deleting one throws. SqlAdminController.purgeTokens() deletes every expired or revoked token at once (the “Delete N dead” button).
  • Every call is logged in _ff_sql_token_calls (SQL text, row count, ms, error - never the rows) and pruned after callLogRetentionDays.
  • remult.context.sqlTokenBearer is set whenever a bearer with the prefix is seen, valid or not - your own initRequest can return on it so a token request never falls back to a cookie.

Calling it

Section titled “Calling it”

firstly ships an ff-sql bin, and the mint screen hands over the whole command - token, origin and api path inlined - so there is nothing to configure, no env file to point at:

Terminal window
FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app "select count(*) from users"
FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app --json "select * from users limit 3"
FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app << 'SQL'
  select handle from "users" where "createdAt" > now() - interval '7 days'
SQL

The token sits in the command line, so paste it with a leading space (or keep it in an env file) to keep it out of your shell history. Multi-line SQL goes through stdin (the heredoc above); as an argument, anything that is not flag-shaped is SQL, and -- ends the flags - so a leading -- comment is fine.

--origin also reads FF_SQL_ORIGIN, so an app that queries the same place all day can wrap it: "sql:prod": "FF_SQL_ORIGIN=https://my.app ff-sql" (package scripts have ff-sql on their PATH).

Rows go to stdout (a table, or --json), the N row(s) · X ms summary to stderr. ff-sql --help lists the flags. Or skip the bin entirely:

Terminal window
curl -X POST https://my.app/api/ff/sqlAdmin/exec \
  -H "authorization: Bearer ffsql_..." -H "content-type: application/json" \
  -d '{"args":["select count(*) from users"]}'

Errors point at the right identifier

Section titled “Errors point at the right identifier”

Callers writing SQL against a schema they cannot see mostly fail on names - entity columns are quoted camelCase, so created_at and key_value do not exist. Any failed query comes back with Postgres’ own HINT when there is one, or the closest names from the catalog, plus the SQLSTATE:

column a.analysisversion does not exist · Did you mean "activities"."analysisVersion"? · [42703 at 8]
relation "key_value" does not exist · Did you mean "keyValues"? · [42P01 at 15]

That enriched message is also what lands in the call log.

Schema drift - <SqlDrift />

Section titled “Schema drift - <SqlDrift />”

Remult creates tables and adds columns, but never revisits them: no FK index, a PK that ignores a later id change, not null default '' kept after a field turns allowNull, columns left behind by a removed field. Each check is opt-in, one flag at a time - nothing is registered without one.

src/server/api.ts
import { collectEntities, sqlAdmin } from 'firstly/sqlAdmin/server'


const modules = [
  sqlAdmin({
    drift: {
      // A getter: the module list holds sqlAdmin() itself.
      entities: () => collectEntities(modules),
      relationIndexes: true, // index toOne FK columns not covered by an index (the PK counts)
      primaryKeys: true, // re-align live PRIMARY KEYs with each entity `id`
      nullable: true, // relax drifted `allowNull` columns, text '' -> NULL (you pick which)
      orphanColumns: true, // DROP columns no field declares (you pick which)
    },
  }),
]


export const api = remultApi({ modules })
<script lang="ts">
  import { SqlDrift } from 'firstly/sqlAdmin'
</script>


<SqlDrift />

Every check is a dry run first; applying runs in the BackendMethod transaction, so one failure rolls everything back. The destructive ones (nullable, orphanColumns) tick nothing by default and re-check the selection server-side. Results are logged as for AI: <json>.

To create an index from code instead (e.g. a composite one Remult can’t infer), build the SQL from typed fields - real db names, FF_IX_<table>_<cols> by default:

import { SqlDatabase } from 'remult'
import { sqlCreateIndex } from 'firstly/sqlAdmin/server'


const sql = await sqlCreateIndex(Task, ['userId', 'createdAt'], { ifNotExists: true })
await SqlDatabase.getDb().execute(sql)