Gipity Login

docs.gipity.ai › App development › app-database

App Database - Postgres for Functions

A deployed app reads and writes a per-app PostgreSQL database. You write the schema as migrations/, grant each function table access in gipity.yaml, and query from function code through the injected db helper. This is the database reference; for writing the functions themselves see app-development, and for the migration / deploy phase see deploy.

Setup

Use PostgreSQL syntax: SERIAL, BOOLEAN, TIMESTAMPTZ, JSONB, TEXT.

Primary keys: use a short_guid VARCHAR(20) PK (as the seeded tables do), not BIGSERIAL - don't expose raw integer ids. Mint the value with the injected guid(prefix) peer (second-arg service, no declaration): guid('adv') → adv_a7k2mq9pd3xe ({prefix}_ + 12 unambiguous crypto-random chars; prefix ≤ 7 chars so the result fits VARCHAR(20)). bigint/BIGSERIAL columns return from functions as JSON strings (node-pg behavior), so coerce/compare them as strings in tests and clients.

Limits: database count is per-plan (run credits_products to see), 500 rows / 128 KB per query, 50,000 chars per statement.

Destructive operations (DROP, TRUNCATE) are auto-confirmed by the platform - just call db_sql directly.

Schema lives in migrations/, not in one-off commands. Every table a function reads or writes must be created by a migrations/NNN-name.sql file (idempotent CREATE TABLE IF NOT EXISTS …) that runs via a database deploy phase in gipity.yaml (see deploy). Creating tables imperatively with a bare db_sql / gipity db query "CREATE TABLE …" works in the moment but the schema isn't captured anywhere: it doesn't ship with the app, a fresh deploy / prod / another user's install has no tables, and the signed-in features that write to them fail at runtime - which looks like broken auth, not a missing table.

Listing a table under a function's tables: in gipity.yaml (see app-development) is a permission - it lets the function touch that table. It does not create the table; the migration does. Both are needed: the migration to make searches exist, and tables: [searches] to let the function use it.

Database Helpers in Functions (via db)

// Raw SQL with parameters
const { rows } = await db.query('SELECT * FROM orders WHERE user_id = $1', [userId]);

// Convenience methods
const user  = await db.findOne('users', { id: userId });
const items = await db.findMany('orders', {
  where: { status: 'pending' },
  orderBy: 'created_at DESC',
  limit: 10,
  offset: 0,
});
const insertedRow  = await db.insert('orders', { user_id: 1, total: 99.99 });  // the new row
const updatedCount = await db.update('orders', { id: orderId }, { status: 'shipped' });  // a number
const deletedCount = await db.delete('orders', { id: orderId });  // a number

// Column metadata for a declared table or view (works on empty tables too)
const cols = await db.describe('orders');  // [{ name, type, nullable, default }, …]

db writes do NOT fire record. workflow triggers.* record.after_insert / after_update / after_delete workflows only fire on writes made through the Records API (the records_* tools, the /api/<appGuid>/records/<table> REST endpoints, workflow record steps). A raw db.query('INSERT ...') or db.insert(...) in a function changes the data but never triggers the workflow. If a write must trigger one (e.g. a contact form that emails the owner), route that write through the Records API - see workflow "Record triggers". For an anonymous public form, expose the table with a records phase in gipity.yaml (auth_level: public, after the database phase) - that's the native Records API and needs no kit. Do not gipity add records for this: the records kit is signed-in member CRUD and can't take anonymous writes.

Write-helper return shapes differ - don't guess. db.insert returns the inserted row (it appends RETURNING *), so read fields off it directly (insertedRow.id). db.update and db.delete return the affected row count as a plain number, not a row and not a result object - so updated.id is undefined and updated.rowCount is undefined. To get updated rows back, use db.query('UPDATE ... RETURNING *', [...]) and read rows.

db.query contract: returns { rows, rowCount }. rowCount follows node-pg semantics - rows affected for INSERT/UPDATE/DELETE, rows returned for SELECT - so a bare DELETE ... WHERE ... (no RETURNING) still reports the real count. There is no separate affectedRows field. To get back inserted/updated rows, add RETURNING and read rows.

Passing arrays: pass the array as a single parameter and match it in SQL with ANY($1) (or = ANY($1::text[])) - do not expand it into IN (...) yourself:

const ids = ['id_a', 'id_b', 'id_c'];
const { rows } = await db.query('SELECT * FROM orders WHERE id = ANY($1)', [ids]);
const del = await db.query('DELETE FROM orders WHERE id = ANY($1::text[])', [ids]);
// del.rowCount === number of rows deleted

Upserts work normally - INSERT ... ON CONFLICT (id) DO UPDATE SET ... is fine; declare only the real table in gipity.yaml permissions.

Transactions - db.tx. Each db.query runs as its own statement on its own connection; there is no implicit transaction per invocation. When two or more writes must succeed or fail together (a data row plus its audit/outbox/ledger event), wrap them in db.tx - the callback's queries run on one pinned connection inside BEGIN/COMMIT; it commits when the callback resolves and rolls back (and rethrows) when it throws:

export default async function (ctx, { db }) {
  const record = await db.tx(async (tx) => {
    const row = await tx.insert('assets', { name: ctx.body.name, owner: ctx.auth.userGuid });
    await tx.insert('events', { entity_id: row.id, kind: 'asset.created', actor: ctx.auth.userGuid });
    return row;  // db.tx returns the callback's return value
  });
  return { data: record };
}

The tx handle has the same query/findOne/findMany/insert/update/delete helpers as db. Table permissions and query/row limits apply unchanged inside the transaction. One transaction at a time - db.tx cannot be nested. Raw db.query('BEGIN')/'COMMIT' is rejected (it never worked - each query ran on its own connection); db.tx is the only transaction API. If the function errors or times out mid-transaction, the runtime rolls it back automatically.

Limits (per invocation): max_queries 50, max_rows_read 10,000, max_rows_affected 10,000. These are governor defaults, not Postgres limits - Postgres handles far larger statements fine. Bulk writes must be set-based (one UPDATE … WHERE id = ANY($1)), because a row-by-row loop hits max_queries (100) first; a set statement counts as 1 query and its rowCount counts against max_rows_affected.

Raising a limit. Add a limits: block to the function's gipity.yaml entry to lift any governor up to the platform ceiling - so you don't have to chunk or page prematurely:

function_definitions:
  - name: bulk-move
    auth: user
    tables: [nodes]
    limits:
      max_rows_affected: 50000   # default 10,000, platform max 100,000

Ceilings: max_queries 100, max_rows_read 50,000, max_rows_affected 100,000 (other governors have their own caps). A value over the max fails the deploy with a clear message; an unknown/typo'd key is rejected, not silently dropped. For genuinely huge bulk (>100k rows in one shot) use a Job - it runs on a longer envelope built for batch work, not the 30s function window. Security: Only declared tables are accessible. DDL (CREATE, ALTER, DROP) is blocked inside functions - schema changes always go through migrations/ (the pattern above). If your app generates its schema (e.g. a metadata-driven data plane), have the agent generate migrations/NNN-*.sql files from your schema source and deploy; don't try to issue DDL from a function.

SQL views work in tables:. A view created in a migration (CREATE OR REPLACE VIEW report_summary AS …) can be listed in a function's tables: and queried with db.query like any table - useful for report/aggregation endpoints. Caveat: schema introspection tooling lists base tables only, so views won't appear in schema listings; the permission and queries still work.

Globs in tables:. A tables: entry may contain * (e.g. tables: ['kit_*']) to allow every matching table/view - opt-in per entry; entries without * stay exact matches. Use it for registry-driven functions that legitimately operate over a family of same-prefix tables, so each new object doesn't require editing every function's tables: list. Keep write-path functions on explicit lists unless they truly are generic.

Schema introspection - db.describe. await db.describe('orders') returns column metadata (shape shown in the helpers block above) for one declared table or view. It respects the function's tables: permission (globs included) and works on empty tables - use it instead of inferring columns from the first result row. information_schema itself is not queryable through db.query.

Testing database code - the two isolation facts that bite

gipity test runs your functions against an isolated, throwaway copy of your database - see app-testing for the harness. Two lifetimes trip up naturally-written tests, so know them before writing your first assertion: