Raw SQL and migrations

Run any SQL and versioned migrations on Plexivo Postgres from your server or a Plexivo Function, with the secret key and parameters.

When the Data API is not enough, run SQL. Raw SQL has the database owner’s full power and ignores row-level security, so it needs the secret key and runs only from places you control: your servers, your CI and Plexivo Functions.

Never in a browser or appThe secret key bypasses every policy. A request that carries it from a web page is refused, and the client libraries refuse to start with it in a browser. For joins a browser needs, use a view or a database function.

Run SQL#

Values go in as parameters, never pasted into the text, so they cannot change what the statement does:

import { createClient } from '@plexivo/client';

const server = createClient(process.env.PLEXIVO_DATA_URL, process.env.PLEXIVO_SECRET_KEY, { persistSession: false });

await server.exec('create table invoices (id bigint generated always as identity primary key, customer text not null, total numeric(12, 2) not null, issued_on date not null default current_date)');
await server.exec('insert into invoices (customer, total) values ($1, $2), ($3, $4)', ['Asha', 1299.5, 'Ravi', 480]);

// A tagged template: each ${value} becomes a parameter
const minimum = 500;
const large = await server.sql`select customer, total from invoices where total > ${minimum} order by total desc`;

const byCustomer = await server.raw('select customer, sum(total)::text as total from invoices group by customer');

dart

import 'package:plexivo_client/plexivo_client.dart';

final server = PlexivoClient(dataUrl, secretKey);

final large = await server.raw(r'select customer, total from invoices where total > $1', [500]);
final done = await server.exec(r'update invoices set total = total + $1 where customer = $2', [1, 'Ravi']);
print(done.rowCount);

Numbers, text, booleans, dates as ISO text, JSON objects and lists all work as parameters; PostgreSQL converts each to the column’s type. Cast exact numbers such as numeric and bigint to ::text in the select when you need every digit.

Several statements, one transaction#

Every statement in one call commits together. If one fails, none is saved, and the error names it:

await server.sqlTransaction([
  { sql: 'insert into invoices (customer, total) values ($1, $2)', params: ['Meera', 99] },
  { sql: 'update invoices set total = total * 1.18 where customer = $1', params: ['Meera'] }
]);

dart

await server.sql.transaction([
  {'sql': r'insert into invoices (customer, total) values ($1, $2)', 'params': ['Kabir', 99]},
  {'sql': r'update invoices set total = total * 1.18 where customer = $1', 'params': ['Kabir']},
]);

Migrations#

List your migrations in order. Each runs once, in its own transaction, and is recorded in a plexivo_migrations table, so you can run the list on every deploy:

const migrations = [
  { id: '2026_11_01_customers', up: 'create table customers (id bigint generated always as identity primary key, name text not null)', down: 'drop table customers' },
  { id: '2026_11_02_customer_email', up: 'alter table customers add column email text unique', down: 'alter table customers drop column email' }
];

const applied = await server.migrate(migrations);       // ids applied this time
await server.rollback(migrations, { steps: 1 });         // undo the newest

dart

final migrations = [
  PlexivoMigration.sql('2026_11_01_suppliers', 'create table suppliers (id bigint generated always as identity primary key, name text not null)', 'drop table suppliers'),
];
await server.migrate(migrations);

A new table or column reaches the Data API within 30 seconds, usually at once, as soon as it has row-level security and a policy.

Over HTTPS#

bash

curl "$PLEXIVO_DATA_URL/sql/v1" \
  -H "apikey: $PLEXIVO_SECRET_KEY" \
  -H "content-type: application/json" \
  -d '{"statements": [{"sql": "select count(*) as n from invoices where total > $1", "params": [100]}]}'

The answer is {"results": [{"command": "SELECT", "columns": ["n"], "rows": [{"n": 2}], "row_count": 1, "truncated": false}]}, one result per statement.

What is refused#

Only the secret key, from a server, reaches raw SQL. The publishable key and signed-in users are refused whatever they send:

const app = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await app.raw('select 1');

And the secret key from a web page, which sends an Origin header, is refused before anything runs:

bash

curl "$PLEXIVO_DATA_URL/sql/v1" \
  -H "Origin: https://app.example.com" \
  -H "apikey: $PLEXIVO_SECRET_KEY" \
  -H "content-type: application/json" \
  -d '{"sql": "select 1"}'

Limits#

Up to 50 statements in one call, each up to 200,000 bytes of SQL.
Each statement has 30 seconds to run.
Up to 10,000 rows or 5 MB come back per statement; beyond that, truncated is true. Page through larger results with limit and a key.

Next steps#

Connect an ORMGORM, Prisma and others through the connection pooler. Plexivo FunctionsServer code beside your data.

Updated 28 October 2026