Skip to main content

Relational Database (PostgreSQL)

version tip

Since @cloudbase/manager-node@5.4.0, this module has been added. Accessed via app.database, it provides the ability to execute SQL statements and manage data on PostgreSQL-based CloudBase environments.


Initialize​

import CloudBase from '@cloudbase/manager-node'

const app = CloudBase.init({
secretId: 'Your SecretId',
secretKey: 'Your SecretKey',
envId: 'Your envId'
})

const { database } = app

executePGSql​

1. API Description​

Function: Execute any SQL statement (DDL / DML / DQL, etc.) on a PostgreSQL environment, returning the result set and affected row count.

Declaration: app.database.executePGSql(options): Promise<IExecutePGSqlResult>

2. Input Parameters​

IExecutePGSqlOptions

FieldRequiredTypeDescription
SqlYesStringThe SQL statement to execute
RoleNoStringSpecify a role to execute the SQL. Accepts any valid role, including user-defined roles. For read-only execution, pass an application role (anon / authenticated) or a read-only role you created yourself. Platform-reserved roles are not exposed to users; passing them directly may fail with 42501
EnvIdNoStringCloudBase environment ID. If not provided, the EnvId used during manager instance initialization will be used

3. Response​

IExecutePGSqlResult

FieldTypeDescription
RequestIdStringUnique request identifier
AffectedRowsNumberNumber of affected rows (effective for DML statements)
ColumnsString[] / nullList of column names (returned for SELECT statements); null when there is no result set
RowsString[] / nullList of data rows, each item is a JSON string that deserializes to (string | null)[], aligned with Columns order; null when there is no result set
ExecutionTimeMsNumberSQL execution time (milliseconds)

4. Code Example​

// Create a table
await database.executePGSql({
Sql: 'CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)'
})

// Insert data
const insertRes = await database.executePGSql({
Sql: "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"
})
console.log('Affected rows:', insertRes.AffectedRows) // 1

// Query and parse results
const res = await database.executePGSql({
Sql: 'SELECT id, name, email FROM users WHERE id = 1'
})
console.log(res.Columns) // ['id', 'name', 'email']
const rows = (res.Rows || []).map(s => JSON.parse(s))
// rows: [['1', 'Alice', 'alice@example.com']]

// Run a query with a read-only role you created yourself, preventing unintended write operations
const readonlyRes = await database.executePGSql({
Role: 'report_reader',
Sql: 'SELECT id, name, email FROM users'
})
console.log(readonlyRes.Rows)

// Execute on a different environment ID
await database.executePGSql({
EnvId: 'other-env-id',
Sql: 'SELECT NOW()'
})

Database Migrations​

version tip

Since @cloudbase/manager-node@5.6.5, the following database migration methods have been added, allowing you to manage schema and data changes on PostgreSQL-based CloudBase environments using versioned SQL scripts.

Concepts​

  • Migration: A versioned SQL change consisting of Version, Name, Query.
    • Version: A 14-digit numeric timestamp such as 20260526000000. Using an actual timestamp (YYYYMMDDHHMMSS) is recommended to guarantee global ordering and uniqueness.
    • Name: Lowercase letters and underscores only, e.g. create_users_table.
    • Query: The SQL to execute.
  • cloudbase_migrations.schema_migrations: The migration history table maintained by CloudBase on the target PG environment, recording all successfully applied migrations.
  • Typical workflow:
    1. Preview the plan and check checksum conflicts with previewPGUserMigrations.
    2. If there are no conflicts, call pushPGUserMigrations and get a TaskId.
    3. Poll the task status via describeTaskResult until it reaches Succeed / Failed.
    4. Query applied records via listPGUserMigrations / listAllPGUserMigrations / describePGUserMigration when needed.
    5. When the history is out of sync with the database, use repairPGUserMigrationHistory to fix the history records only (no SQL is executed).

previewPGUserMigrations​

1. API Description​

Function: Preview the remote execution plan for a batch of user migrations (no SQL is actually executed). Returns Pending, Applied, Conflicts (checksum conflicts) and Executable.

Declaration: app.database.previewPGUserMigrations(options): Promise<IPreviewPGUserMigrationsResult>

2. Input Parameters​

IPreviewPGUserMigrationsOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the EnvId used during manager initialization
MigrationsYesIPGUserMigrationInput[]Migration list, at least 1 item; each item must pass the validations below
IncludeAllNoBooleanWhether to allow out-of-order local migrations (inserting a new version before an already applied one). Default false

IPGUserMigrationInput

FieldRequiredTypeDescription
VersionYesStringMigration version, a 14-digit numeric timestamp (e.g. 20260526000000)
NameYesStringMigration name, lowercase letters and underscores only
QueryYesStringThe SQL to execute; must not be empty

3. Response​

IPreviewPGUserMigrationsResult

FieldTypeDescription
PendingIPGUserMigrationPlanItem[] | nullMigrations that will be executed
AppliedIPGUserMigrationPlanItem[] | nullMigrations that have already been applied
ConflictsIPGUserMigrationConflict[] | nullConflicts: migrations with the same version but a different checksum. Non-empty means local SQL differs from applied records
ExecutableBooleanWhether the plan can be executed directly (currently means no checksum conflicts)
RequestIdStringUnique request identifier

Main fields of IPGUserMigrationPlanItem: Version, Name, Status (e.g. applied / pending), Reason (e.g. checksum_matched), Checksum, Source.

Main fields of IPGUserMigrationConflict: Version, Name, RemoteName, LocalChecksum, RemoteChecksum, Reason (e.g. checksum_mismatch), Message.

4. Code Example​

const preview = await database.previewPGUserMigrations({
Migrations: [
{
Version: '20260526000000',
Name: 'create_users_table',
Query:
'CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);'
}
]
})

if (!preview.Executable || (preview.Conflicts || []).length > 0) {
console.error('Checksum conflicts must be resolved first:', preview.Conflicts)
return
}

console.log('Pending:', preview.Pending)
console.log('Applied:', preview.Applied)

pushPGUserMigrations​

1. API Description​

Function: Apply a batch of user migrations. This is an asynchronous task: it returns a TaskId on success. Use describeTaskResult to query the final result.

Declaration: app.database.pushPGUserMigrations(options): Promise<IPushPGUserMigrationsResult>

2. Input Parameters​

IPushPGUserMigrationsOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the EnvId used during manager initialization
MigrationsYesIPGUserMigrationInput[]Migration list, at least 1 item; same field validations as previewPGUserMigrations
LockTimeoutMsNoNumberMaximum wait time (ms) to acquire the database lock; must be >= 0. Default 5000
StatementTimeoutMsNoNumberMaximum execution time (ms) for a single SQL statement; must be >= 0. Default 300000
IncludeAllNoBooleanWhether to allow out-of-order local migrations. Default false

3. Response​

IPushPGUserMigrationsResult

FieldTypeDescription
RequestIdStringUnique request identifier
TaskIdStringAsync task ID, used to query task progress and status

4. Code Example​

// 1. Submit the async task
const { TaskId } = await database.pushPGUserMigrations({
Migrations: [
{
Version: '20260526000000',
Name: 'create_users_table',
Query:
'CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);'
}
],
LockTimeoutMs: 5000,
StatementTimeoutMs: 60000
})

// 2. Poll the task status
async function waitTask(taskId) {
while (true) {
const task = await database.describeTaskResult({ TaskId: taskId })
if (task.Status === 'Succeed' || task.Status === 'Failed') {
return task
}
await new Promise(r => setTimeout(r, 1000))
}
}

const result = await waitTask(TaskId)
console.log('Task finished:', result.Status, result.Phase, result.Reason)

repairPGUserMigrationHistory​

1. API Description​

Function: Only maintain records in the cloudbase_migrations.schema_migrations history table. No SQL is executed. Typical use cases:

  • SQL was applied out-of-band and the history table is missing the corresponding record (use applied to insert it).
  • A migration recorded in history needs to be withdrawn (use reverted to delete the record).

Declaration: app.database.repairPGUserMigrationHistory(options): Promise<IResponseInfo>

2. Input Parameters​

IRepairPGUserMigrationHistoryOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the EnvId used during manager initialization
MigrationVersionYesStringMigration version, a 14-digit numeric timestamp
NameYesStringMigration name, lowercase letters and underscores only
StatusYes'applied' | 'reverted'applied inserts into history; reverted deletes from history
ReasonYesStringReason for the repair; cannot be empty
QueryNoStringRequired when Status=applied, should be the corresponding SQL; can be omitted when Status=reverted

3. Response​

IResponseInfo: contains common fields such as RequestId.

4. Code Example​

// Insert a migration that was applied out-of-band
await database.repairPGUserMigrationHistory({
MigrationVersion: '20260526000000',
Name: 'create_users_table',
Status: 'applied',
Reason: 'Applied manually out-of-band, backfill history',
Query: 'CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);'
})

// Remove an incorrectly recorded migration from history
await database.repairPGUserMigrationHistory({
MigrationVersion: '20260526000000',
Name: 'create_users_table',
Status: 'reverted',
Reason: 'Recorded by mistake; remove from history'
})

listPGUserMigrations​

1. API Description​

Function: Paginated query of applied migrations on the target environment.

Declaration: app.database.listPGUserMigrations(options?): Promise<IListPGUserMigrationsResult>

2. Input Parameters​

IListPGUserMigrationsOptions (all optional)

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the initialization EnvId
LimitNoNumberPage size, range [1, 500], default 100
OffsetNoNumberPagination offset, >= 0, default 0

3. Response​

IListPGUserMigrationsResult

FieldTypeDescription
TotalNumberTotal count
LatestVersionStringLatest applied version (14-digit numeric timestamp)
MigrationsIPGUserMigrationSummary[]Applied migrations, each item contains Version and Name
RequestIdStringUnique request identifier

4. Code Example​

const page = await database.listPGUserMigrations({ Limit: 100, Offset: 0 })
console.log('Total:', page.Total, 'Latest version:', page.LatestVersion)
page.Migrations.forEach(m => {
console.log(m.Version, m.Name)
})

listAllPGUserMigrations​

1. API Description​

Function: Auto-paginate and return all applied migrations on the target environment. Internally repeats listPGUserMigrations with PageSize until finished.

Declaration: app.database.listAllPGUserMigrations(options?): Promise<IListPGUserMigrationsResult>

2. Input Parameters​

IListAllPGUserMigrationsOptions (all optional)

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the initialization EnvId
PageSizeNoNumberInternal page size, range [1, 500], default 500

3. Response​

Same as listPGUserMigrations: IListPGUserMigrationsResult. The returned Migrations is the full list.

4. Code Example​

const all = await database.listAllPGUserMigrations()
console.log('Total applied:', all.Migrations.length)
console.log('Latest version:', all.LatestVersion)

describePGUserMigration​

1. API Description​

Function: Get the details (including the SQL body) of a specific migration on the target environment.

Declaration: app.database.describePGUserMigration(options): Promise<IDescribePGUserMigrationResult>

2. Input Parameters​

IDescribePGUserMigrationOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the initialization EnvId
MigrationVersionYesStringMigration version, a 14-digit numeric timestamp

3. Response​

IDescribePGUserMigrationResult

FieldTypeDescription
VersionStringMigration version
NameStringMigration name
QueryStringThe SQL body of the migration
RequestIdStringUnique request identifier

4. Code Example​

const detail = await database.describePGUserMigration({
MigrationVersion: '20260526000000'
})
console.log(detail.Version, detail.Name)
console.log(detail.Query)

describeTaskResult​

1. API Description​

Function: Query the execution status of an asynchronous task (such as the task returned by pushPGUserMigrations). Can be used to poll until the task reaches a terminal state.

Declaration: app.database.describeTaskResult(options): Promise<IDescribeTaskResultResult>

2. Input Parameters​

IDescribeTaskResultOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Falls back to the initialization EnvId
TaskIdYesStringTask ID; cannot be empty

3. Response​

IDescribeTaskResultResult

FieldTypeDescription
TaskIdStringTask ID
TaskTypeStringTask type (e.g. PGUserMigration)
StatusStringTask status: Accepted / Running / Succeed / Failed
PhaseStringCurrent phase
ReasonStringFailure reason (may be empty on success)
CreatedAtStringCreated time (ISO 8601)
UpdatedAtStringLast updated time (ISO 8601)
RequestIdStringUnique request identifier

4. Code Example​

const task = await database.describeTaskResult({ TaskId: 'task-xxxxxx' })
if (task.Status === 'Succeed') {
console.log('Task succeeded, updated at', task.UpdatedAt)
} else if (task.Status === 'Failed') {
console.error('Task failed:', task.Phase, task.Reason)
} else {
console.log('Task in progress:', task.Status, task.Phase)
}

Realtime settings​

Version

Available since @cloudbase/manager-node@5.10.0. Realtime provides Broadcast, Presence, and PostgreSQL CDC (postgres_changes); see Realtime quick start for the client side.

The three methods enable, query, and update the service. Updates are incremental: only the fields you pass are sent, and everything else keeps its server-side value. Call describePGRealtimeSettings first to read the current configuration.

enablePGRealtime​

1. Description​

Enables the Realtime service for a PG mode environment. It is idempotent: calling it on an enabled environment does not fail, and calling it on a suspended environment resumes the service. The call only changes the service state; rate limits, channel policies, and other settings are left untouched.

Declaration: app.database.enablePGRealtime(options): Promise<IEnablePGRealtimeResult>

2. Input parameters​

IEnablePGRealtimeOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Defaults to the EnvId of the manager instance

3. Return value​

IEnablePGRealtimeResult

FieldTypeDescription
EnabledBooleanEnablement state
RequestIdStringUnique request ID

4. Code example​

const res = await database.enablePGRealtime()
console.log(res.Enabled)

// Specify the environment
await database.enablePGRealtime({ EnvId: 'your-env-id' })

describePGRealtimeSettings​

1. Description​

Returns the current Realtime configuration: suspend state, channel access policy, Presence switch, rate limits, connection pool sizes, and creation/update time.

Declaration: app.database.describePGRealtimeSettings(options): Promise<IDescribePGRealtimeSettingsResult>

2. Input parameters​

IDescribePGRealtimeSettingsOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Defaults to the EnvId of the manager instance

3. Return value​

IDescribePGRealtimeSettingsResult

FieldTypeDescription
SuspendBooleanWhether the service is suspended, applied globally. When true, all online connections are dropped, new connections are rejected, and server-side broadcast APIs are disabled
PrivateOnlyBooleanWhether only private channels are allowed. When true, clients cannot join channels prefixed with public:
PresenceEnabledBooleanMaster switch for Presence
MaxConcurrentUsersNumberMaximum concurrent users, counted per user (multiple connections of the same user count once)
MaxEventsPerSecondNumberMaximum broadcast events per second, summed across all clients
MaxJoinsPerSecondNumberMaximum channel joins per second
MaxBytesPerSecondNumberMaximum broadcast payload bytes per second
MaxChannelsPerClientNumberMaximum channels per client
MaxPresenceEventsPerSecondNumberMaximum presence events per second, summed across all track / untrack calls
MaxPayloadSizeInKBNumberMaximum payload size per message (KB)
ConnectionPoolNumberDatabase connection pool size, used for RLS authorization queries
PostgresChangesPoolNumberDedicated pool size for CDC (postgres_changes), isolated from the authorization pool
CreatedTimeStringCreation time (ISO 8601)
UpdatedTimeStringLast update time (ISO 8601)
AdminSuspendedTimeStringPlatform freeze time (ISO 8601). Empty when not frozen; returned only for planned calls
RequestIdStringUnique request ID

4. Code example​

const settings = await database.describePGRealtimeSettings()
console.log(settings.Suspend, settings.MaxConcurrentUsers, settings.PostgresChangesPool)

modifyPGRealtimeSettings​

1. Description​

Updates the Realtime configuration. Updates are incremental: only the fields you pass are sent. Changing Suspend in either direction disconnects all existing WebSocket connections, so clients must reconnect.

Declaration: app.database.modifyPGRealtimeSettings(options): Promise<IModifyPGRealtimeSettingsResult>

2. Input parameters​

IModifyPGRealtimeSettingsOptions

FieldRequiredTypeDescription
EnvIdNoStringCloudBase environment ID. Defaults to the EnvId of the manager instance
SuspendNoBooleanSuspend the service. true drops all online connections and rejects new ones; false resumes it (false on an unenabled environment enables the service)
PrivateOnlyNoBooleanAllow private channels only. Once true, clients cannot join channels prefixed with public:
PresenceEnabledNoBooleanMaster switch for Presence. When false, presence join requests are rejected
MaxConcurrentUsersNoNumberMaximum concurrent users. The server accepts [0, 15000]; new connections are rejected beyond it
MaxEventsPerSecondNoNumberMaximum broadcast events per second; exceeding it returns a rate limit error
MaxJoinsPerSecondNoNumberMaximum joins per second; new joins are throttled beyond it
MaxBytesPerSecondNoNumberMaximum payload bytes per second; messages beyond it are rejected
MaxChannelsPerClientNoNumberMaximum channels per client; beyond it the client cannot join more channels
MaxPresenceEventsPerSecondNoNumberMaximum presence events per second; beyond it presence changes are rejected
MaxPayloadSizeInKBNoNumberMaximum payload size per message (KB); larger messages are rejected
ConnectionPoolNoNumberDatabase connection pool size, used for RLS authorization queries
PostgresChangesPoolNoNumberDedicated pool size for CDC (postgres_changes), isolated from broadcast and authorization pools

3. Return value​

IModifyPGRealtimeSettingsResult

FieldTypeDescription
RequestIdStringUnique request ID

4. Code example​

// Adjust concurrency and rate limits
await database.modifyPGRealtimeSettings({
MaxConcurrentUsers: 500,
MaxEventsPerSecond: 200,
})

// Allow private channels only and disable Presence
await database.modifyPGRealtimeSettings({
PrivateOnly: true,
PresenceEnabled: false,
})

// Suspend the service and drop all connections
await database.modifyPGRealtimeSettings({ Suspend: true })
Suspending drops existing connections

When Suspend is true, every online WebSocket connection is closed and server-side broadcast APIs are disabled. After resuming, clients must call connect() again and re-subscribe to channels.