> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ohmyho.st/llms.txt
> Use this file to discover all available pages before exploring further.

> ## Agent Instructions
> For account actions, read https://ohmyho.st/skills/ohmyhost-get-started/SKILL.md and use the authenticated ohmyho.st CLI or local product MCP. Mintlify search only reads documentation. Preserve the customer’s selected project, environment and authentication provider.

# Postgres queries, writes and compute

> Call Postgres from your app, query or update Dev/Prod data, open time-bound psql access and inspect compute.

Read provider metadata without executing SQL:

```sh theme={null}
ohmyhost database compute get --project "$PROJECT_ID" --environment dev --json
ohmyhost database compute get --project "$PROJECT_ID" --environment prod --json
```

Install the exact npm alias from `runtime.packages.customerRuntime` in `ohmyhost init --dry-run --json`, under the import name `@ohmyhost/customer-runtime`: `npm:@amerged/ohmyhost-runtime@<version>`. This applies to every framework; update/commit the lockfile. Use the current CLI/runtime release together. For nested JSON results, depth is measured from each row, while the response envelope and all rows still share the same total budgets.

Application requests use `createPrivateDatabaseClient(env.OHMYHOST_DATABASE)` from `@ohmyhost/customer-runtime/database`. `query()` executes one statement, `transaction()` atomically runs up to 25 statements chosen in advance, and `withConnection()` holds a real connection for read-decide-write; one scope permits 100 statements, 30 seconds total and five seconds idle, with two active transaction/auth connections per physical data area. Shared Dev/Prod use the same lanes. Standalone statements commit before their response; use explicit `BEGIN` and `COMMIT` for an atomic sequence. Closing rolls back an unfinished transaction, and transaction-local server timeouts release its slot if the caller disappears.

Inside a `withConnection` callback that performs an atomic transaction, pass its current connection and SQL executor to every participating repository and identity lookup. Do not open another database connection for those reads. Complete the short transaction and close the connection before a file transfer, email or AI call, then open a new short transaction to store the result.

JSON/JSONB object parameters work with the current runtime. Pass ordinary JavaScript objects, including nested objects and arrays, as parameters:

```ts theme={null}
const { rows } = await database.query({
  text: "SELECT $1::jsonb AS settings",
  values: [{ notifications: { channels: ["email"] } }],
});
```

The client creates validated plain objects for Workers RPC. JSON keys may include dates, ULIDs, dots, @ and non-ASCII characters, at most 128 UTF-8 bytes without control characters; `__proto__`, `constructor` and `prototype` are refused. Upgrade the alias/lockfile together if an older client rejects valid JSON. For an application that stores JSON, verify a JSON write/read through the hosted application, since a scalar-only health query does not exercise that path.

SQL `DATE` values and `date[]` elements are `YYYY-MM-DD` strings. Keep calendar days free of timezone conversion; timestamp values retain their existing decoding. [Application database examples](https://ohmyho.st/skills/ohmyhost-build-portable-app/references/database-runtime.md).

Verified statement failures expose their five-character PostgreSQL `SQLSTATE` on `error.code`, so applications can distinguish duplicate (`23505`) and exclusion (`23P01`) conflicts. SQL, row values and provider messages stay private. Only serialization failures (`40001`) and deadlocks (`40P01`) are marked retryable; retry the whole transaction within a bound. Transport failures remain `database_unavailable` and do not prove that a write was rejected.

The offline init scans all source: `database_binding_private` marks `DATABASE_URL`, `HYPERDRIVE`, connectionString or postgres URLs; `database_driver_unsupported` marks pg, postgres, pg-native or `mysql2`. Tests/bootstrap can also be reported. Source planning rejects these in reachable hosted routes, middleware, Worker/companion modules as `framework_conversion_required` naming the file. Inspect each reported file and the actual hosted entrypoint before changing it: offline tools may use temporary direct credentials, while hosted requests must use the private binding. A local development adapter needs an explicit local-only mode and validated loopback configuration. An RPC import alone does not establish that another driver is unreachable; verify the built artifact and the real hosted request. Do not remove required bootstrap or tests merely to silence the scan. An unreachable offline script can remain, but init still reports it and cannot write a new config while blocked; document that exception without claiming clean init.

## Application connection and result limits

Standalone queries have one connection per physical data identity; transaction() and held scopes share two further connections. Shared Dev/Prod consume the same capacity. Each lane admits 32 waiting acquisitions with a ten-second connection/wait bound. PostgreSQL statements time out after ten seconds (`SQLSTATE` 57014); a cold database may need about five seconds to wake.

A call has one SQL statement up to 64 KiB, 100 parameters and 1 MiB aggregate parameter data; a JSON parameter is at most 64 KiB. JSON depth is at most eight; a result is at most 1,000 rows, 10,000 values/nodes and 1 MiB. Multiple statements, SET/RESET/DISCARD and oversized input fail before RPC as `database_query_invalid` (not retryable). PREPARE is refused outside `withConnection`, and chained COMMIT/ROLLBACK ... \[NO] CHAIN inside it. Use BEGIN/COMMIT/ROLLBACK/savepoints only through a held scope; for transaction-local state use `SELECT set_config('app.user_id', $1, true)`.

Oversized results and the 101st held statement throw `CustomerDatabaseError` (root package export), `error.code === "54000"`, `retryable: false`. Page reads/split writes or open a new held scope. An idle 5-second or expired 30-second scope closes and rolls back uncommitted work as `database_unavailable`; open a new scope. A write with RETURNING, or a WITH statement, rolls back on an oversized result through query(), transaction() or a held autocommit statement. Inside your own BEGIN, those effects remain until you ROLLBACK or end the callback without COMMIT; do not catch 54000 and commit or retry that write inside the same transaction.

A missing database binding is `database_binding_missing`, not a retryable query failure. Read it from Worker env or Next.js `getCloudflareContext({ async: true }).env`, only in source declaring `database.enabled:true`; it is neither a `process.env` string nor a browser capability.

## Optional Kysely dialect

The optional `@ohmyhost/customer-runtime/database-kysely` adapter targets Kysely `0.29.5` over the private binding, including its leased transaction protocol:

```ts theme={null}
import { Kysely } from "kysely";
import { createPrivateDatabaseDialect } from "@ohmyhost/customer-runtime/database-kysely";

const db = new Kysely<AppDatabase>({
  dialect: createPrivateDatabaseDialect(env.OHMYHOST_DATABASE),
});
await db.transaction().execute(async (trx) => {
  const row = await trx
    .selectFrom("items")
    .select("id")
    .where("id", "=", itemId)
    .executeTakeFirst();
  if (row)
    await trx
      .updateTable("items")
      .set({ title: nextTitle })
      .where("id", "=", row.id)
      .execute();
});
```

`AppDatabase`, table names and values are your application's types/data. The adapter implements BEGIN/COMMIT or ROLLBACK through the held-connection API and releases that scope; the same limits apply. Library tests cover that protocol, but do not establish real hosted RPC/PostgreSQL behavior or signup/mail. Verify the application on its deployed private binding. It is not a Drizzle support claim or a pg-proxy transaction substitute. Keep network calls outside the transaction.

## Compute profiles

| Profile | Compute | Memory | Idle suspension |
| - | - | - | - |
| Free standard | 0.25 CU | 1 GB | 60 seconds |
| Paid standard | 0.5 CU | 2 GB | 60 seconds |
| Paid performance | 1 CU | 4 GB | 300 seconds |

Performance costs 2.5 times Paid standard database-compute credits for equal active duration. Its longer idle window can also add active minutes. Storage and retained history consume credits independently of suspended compute.

## Change the profile

```sh theme={null}
ohmyhost database compute set --project "$PROJECT_ID" --environment dev --profile performance --idempotency-key "$COMPUTE_REQUEST_KEY" --yes --json
```

Explain the price and possible brief connection interruption first. Use `--profile standard` to return to standard, and choose `prod` only when that is the intended target. Poll the returned operation, then read actual compute again. Resizing preserves SQL data. A shared placement affects both logical environments.

## Query Dev or Prod

Use your existing CLI login or load your user API token from a private environment file. The token must have the selected organization's Owner project permissions. `OHMYHOST_ENVIRONMENT` chooses the independent hosting platform; `--environment dev|prod` chooses this project's database.

```sh theme={null}
ohmyhost database query --project "$PROJECT_ID" --environment dev --statement 'SELECT current_database() AS database_name' --json
```

Choose `--environment prod` for the project's production database. For application records, discover the actual schema and use parameters rather than interpolating values into SQL:

```sh theme={null}
ohmyhost database query --project "$PROJECT_ID" --environment prod --statement 'SELECT id, title FROM public.items WHERE id = $1' --parameters-json '["item-123"]' --json
```

`items`, its columns and `item-123` are examples: use the application's actual table and record ID. Reads accept one read-only SELECT/WITH query, at most 32 scalar parameters, 100 rows and five seconds. Both queries and writes wake suspended compute and use ordinary database metering.

## Update application data

Review the intended rows and environment before an update. Put one parameterized INSERT, UPDATE or DELETE in a UTF-8 file of at most 64 KiB starting with that keyword, no leading comment or WITH; ordinary `INSERT … ON CONFLICT …` upserts are supported. For example, `approved-update.sql`:

```sql theme={null}
UPDATE public.items SET title = $1 WHERE id = $2
```

A bad file fails locally as `statement_file_invalid` (exit 2, not retryable), without sending; fix it and reuse the same key. Execute only the chosen update:

```sh theme={null}
ohmyhost database write --project "$PROJECT_ID" --environment prod --statement-file approved-update.sql --parameters-json '["Updated title", "item-123"]' --idempotency-key "$WRITE_REQUEST_KEY" --yes --json
```

The write returns `operation_id`, `environment`, `state`, `command`, `affected_rows` and `error`. It does not return rows, including rows produced by a SQL RETURNING clause. Fetch records with a separate query. One write has a five-second SQL timeout and at most 1,000 directly affected rows; triggers and cascades can affect additional rows. Existing credit grace and project Stop budgets apply.

Check the receipt's `state`: HTTP 200 alone is not success. `succeeded` confirms the reported command and count; `running` means inspect the original operation; `failed` carries the error and next action.

```sh theme={null}
ohmyhost operation get "$OPERATION_ID" --json
```

Retain the exact request and key after a lost response. Repeating a key that already has a receipt observes its existing attempt. For `database_write_outcome_unknown`, first inspect the data and keep the operation ID; never automatically choose a new key or execute a second write. Even an error can mean the original commit result was not received.

## Connect with psql or a SQL client

For interactive work you can issue a direct PostgreSQL login for your own project database. Each credential is time-bound, revocable and returned **exactly once**:

```sh theme={null}
ohmyhost database access create --project "$PROJECT_ID" --environment dev --mode read --ttl 1h --label laptop --yes --json
```

The response contains `connection_uri` and `psql_command` one single time; they are never retrievable again. Use them immediately, and never store a connection string or password in files, notes, source or commit messages. Later reads show metadata only:

```sh theme={null}
ohmyhost database access list --project "$PROJECT_ID" --json
ohmyhost database access revoke --project "$PROJECT_ID" --access "$ACCESS_ID" --yes --json
```

To open a session without handling the URI yourself, let the CLI start your local `psql`. It issues the credential, passes the password to `psql` through its private environment and revokes the credential when you exit:

```sh theme={null}
ohmyhost database psql --project "$PROJECT_ID" --environment dev --mode write
```

MCP exposes `database_access_create` (`mode: "write"` requires `confirmed: true`), `database_access_list` and `database_access_revoke`; REST uses `POST`/`GET /v1/projects/{project_id}/database/access` and `DELETE /v1/projects/{project_id}/database/access/{access_id}`.

| Setting | Values |
| - | - |
| Mode | `read` (default, read-only session) or `write` (the application's own INSERT/UPDATE/DELETE rights) |
| Lifetime | `--ttl` 5 minutes to 24 hours, one hour by default |
| Active credentials | at most three per project environment |
| Database | `neondb`, TLS required (`sslmode=require`) |

No mode can change schema or roles: schema changes remain versioned [GitHub migrations](/migrations), and application row-level security applies to these sessions exactly as it does to `database query` and `database write`. After expiry PostgreSQL refuses new logins and the platform ends remaining sessions and removes the role within 15 minutes. An open session wakes compute and is metered like any other database use, under the same credit grace and project Stop budgets. Revoke as soon as you are finished instead of waiting for expiry.

## Use MCP or an automation platform

MCP `database_query` requires `project_id`, `environment`, `statement` and optional scalar `parameters`. `database_write` uses the same project/environment with JSON `parameters`, the saved `idempotency_key` and `confirmed: true` for an authorized write.

REST uses your token in the Authorization header:

```sh theme={null}
curl --fail-with-body "https://app.ohmyho.st/v1/projects/$PROJECT_ID/database/query" \
  --header "Authorization: Bearer $OHMYHOST_TOKEN" \
  --header 'Content-Type: application/json' \
  --data-binary '{"environment":"dev","statement":"SELECT current_database() AS database_name","parameters":[]}'
```

The write endpoint is `POST /v1/projects/{project_id}/database/write`; supply the `Idempotency-Key` header and JSON `environment`, `statement` and `parameters`. An automation should keep the same key for retries of one event and use a new key only for a deliberately new write. [Download the exact OpenAPI schema](/openapi.json).

## Permissions and shared data

Application row-level security (RLS) applies to reads and writes. Hosting Owner permission does not bypass those policies or sign you in as an application user. A filtered or empty query does not prove the database is empty. Keep the policies; use the separate authorized [SQL export](/backups) when a complete archive is required.

Shared Dev/Prod data resolves to one physical database, so a selected-environment write can affect both apps. In isolated mode each environment resolves its own data. Schema changes remain versioned [GitHub migrations](/migrations); roles/grants are unsupported. [Assignment changes/reset](/environments#change-data-assignments) keep files with their data identity and never copy records.

Polling is an explicit query, not a row-change subscription. Keep returned records, SQL parameters, passwords and signed URLs out of project notes and feedback.

[Usage](/usage) · [API tokens](/login-tokens) · [SQL exports](/backups).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.