/** * Tests for `npx scribase sql`: argument parsing, which project and token it * uses, the execute request, destructive-statement confirmation, errors, and * the first-table SQL that init suggests (no network, in-memory files). */ import { describe, it } from 'node:test'; import assert from 'node:assert/strict'; import { FIRST_TABLE_SQL, InitError } from '../src/cli/init.js'; import { formatSql, parseSqlArgs, runSql } from '../src/cli/sql.js'; import type { SqlIo } from '../src/cli/sql.js'; const ROOT = '/work/app'; const STATE = JSON.stringify({ organization_id: 'tmp-1', project_id: 'shop', environment_id: 'production', api_url: 'https://api.example.test', temporary: true, }); interface Seen { url: string; auth: string | null; body: Record; } function io(files: Record, answer: { status: number; body: unknown }, env: Record = {}, stored: { token?: string } = {}) { const seen: Seen[] = []; const value: SqlIo = { cwd: ROOT, env, fetch: (async (url: string | URL | Request, init?: RequestInit) => { seen.push({ url: String(url), auth: new Headers(init?.headers).get('authorization'), body: JSON.parse(String(init?.body)) as Record, }); return new Response(JSON.stringify(answer.body), { status: answer.status }); }) as typeof fetch, readFile: (path) => files[path] ?? null, credentials: () => stored, }; return { io: value, seen }; } const COMMITTED = { status: 'committed', duration_ms: 12, statements: [ { index: 0, command: 'CREATE TABLE', statement: 'create table notes (...)', kind: 'command', affected_rows: null }, { index: 1, command: 'SELECT', statement: 'select * from notes', kind: 'rows', row_count: 1, rows: [{ id: 1, body: 'hello from scribase' }] }, ], }; describe('scribase sql arguments', () => { it('joins the script, reads --file and takes --json and --confirm', () => { assert.deepEqual(parseSqlArgs(['select', '1'], () => null), { query: 'select 1', json: false, confirm: false }); const files: Record = { 'schema.sql': 'create table t (id int);' }; assert.deepEqual(parseSqlArgs(['--file', 'schema.sql', '--json', '--confirm'], (path) => files[path] ?? null), { query: 'create table t (id int);', json: true, confirm: true, }); }); it('rejects an empty script, a missing file and unknown flags', () => { assert.throws(() => parseSqlArgs([], () => null), (error: unknown) => error instanceof InitError && error.code === 'invalid_argument'); assert.throws(() => parseSqlArgs(['--file', 'nope.sql'], () => null), (error: unknown) => error instanceof InitError && error.code === 'file_not_found'); assert.throws(() => parseSqlArgs(['select 1', '--force'], () => null), /unknown option --force/); }); }); describe('scribase sql', () => { it('runs on this folder’s project with the temporary project’s token from the env file', async () => { const { io: value, seen } = io( { [`${ROOT}/.scribase/project.json`]: STATE, [`${ROOT}/.env.local`]: 'SCRIBASE_ACCESS_TOKEN=scb_agt_tmp\n' }, { status: 200, body: COMMITTED }, {}, { token: 'scb_stored' }, ); const result = await runSql({ query: FIRST_TABLE_SQL, json: false, confirm: false }, value); assert.equal(result.status, 'committed'); assert.equal(seen[0]?.url, 'https://api.example.test/v1/organizations/tmp-1/projects/shop/environments/production/database/execute'); assert.equal(seen[0]?.auth, 'Bearer scb_agt_tmp', 'the env file token wins over the stored login'); assert.deepEqual(seen[0]?.body, { query: FIRST_TABLE_SQL }); const text = formatSql(result); assert.match(text, /CREATE TABLE/); assert.match(text, /"body":"hello from scribase"/); assert.doesNotMatch(text, /scb_agt_tmp/, 'the token is never printed'); assert.match(text, /reloads its schema/, 'a schema change says when the REST API sees it'); }); it('prefers SCRIBASE_ACCESS_TOKEN and falls back to the stored login', async () => { const files = { [`${ROOT}/.scribase/project.json`]: STATE }; const fromEnv = io(files, { status: 200, body: COMMITTED }, { SCRIBASE_ACCESS_TOKEN: 'scb_env' }, { token: 'scb_stored' }); await runSql({ query: 'select 1', json: true, confirm: false }, fromEnv.io); assert.equal(fromEnv.seen[0]?.auth, 'Bearer scb_env'); const stored = io(files, { status: 200, body: COMMITTED }, {}, { token: 'scb_stored' }); await runSql({ query: 'select 1', json: true, confirm: false }, stored.io); assert.equal(stored.seen[0]?.auth, 'Bearer scb_stored'); }); it('passes --confirm and reports statements that need it', async () => { const pending = io( { [`${ROOT}/.scribase/project.json`]: STATE, [`${ROOT}/.env`]: 'SCRIBASE_ACCESS_TOKEN=t\n' }, { status: 200, body: { status: 'confirmation_required', statement_count: 1, destructive: [{ index: 0, command: 'DROP TABLE', statement: 'drop table notes', reason: 'drops a table' }] } }, ); const result = await runSql({ query: 'drop table notes', json: false, confirm: false }, pending.io); assert.equal(result.status, 'confirmation_required'); assert.match(formatSql(result), /Nothing ran.*\n.*DROP TABLE: drops a table[\s\S]*--confirm/); const confirmed = io({ [`${ROOT}/.scribase/project.json`]: STATE, [`${ROOT}/.env`]: 'SCRIBASE_ACCESS_TOKEN=t\n' }, { status: 200, body: COMMITTED }); await runSql({ query: 'drop table notes', json: false, confirm: true }, confirmed.io); assert.equal(confirmed.seen[0]?.body.confirm, true); }); it('says what to do when the folder is not initialized, has no token, or the token is refused', async () => { await assert.rejects( runSql({ query: 'select 1', json: false, confirm: false }, io({}, { status: 200, body: {} }).io), (error: unknown) => error instanceof InitError && error.code === 'not_initialized' && /scribase init/.test(error.next ?? ''), ); await assert.rejects( runSql({ query: 'select 1', json: false, confirm: false }, io({ [`${ROOT}/.scribase/project.json`]: STATE }, { status: 200, body: {} }).io), (error: unknown) => error instanceof InitError && error.code === 'not_signed_in', ); const refused = io( { [`${ROOT}/.scribase/project.json`]: STATE }, { status: 401, body: { code: 'unauthorized', detail: 'token expired' } }, { SCRIBASE_ACCESS_TOKEN: 'old' }, ); await assert.rejects( runSql({ query: 'select 1', json: false, confirm: false }, refused.io), (error: unknown) => error instanceof InitError && error.code === 'unauthorized' && /HTTP 401/.test(error.message) && /init/.test(error.next ?? ''), ); }); }); describe('the first table init suggests', () => { it('creates, fills and exposes read-only the table the next step queries', () => { assert.match(FIRST_TABLE_SQL, /^create table notes \(/); assert.match(FIRST_TABLE_SQL, /insert into notes/); assert.match(FIRST_TABLE_SQL, /enable row level security/); assert.match(FIRST_TABLE_SQL, /for select using \(true\)/); assert.match(FIRST_TABLE_SQL, /grant select on notes to anon, authenticated/); assert.doesNotMatch(FIRST_TABLE_SQL, /\b(drop|truncate|delete)\b/i, 'never needs --confirm'); assert.doesNotMatch(FIRST_TABLE_SQL, /grant (insert|update|delete|all)/i, 'the public key can only read'); }); it('pastes safely inside double quotes in any shell', () => { assert.doesNotMatch(FIRST_TABLE_SQL, /["`$!\\]/); }); });