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

Field	Required	Type	Description
Sql	Yes	String	The SQL statement to execute
Role	No	String	Specify a role to execute the SQL. Accepts any valid role, including user-defined roles. CloudBase provides a built-in read-only role cloudbase_read_only_user; using it prevents unintended write operations from succeeding
EnvId	No	String	CloudBase environment ID. If not provided, the EnvId used during manager instance initialization will be used

3. Response

IExecutePGSqlResult

Field	Type	Description
RequestId	String	Unique request identifier
AffectedRows	Number	Number of affected rows (effective for DML statements)
Columns	String[] / null	List of column names (returned for SELECT statements); null when there is no result set
Rows	String[] / null	List 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
ExecutionTimeMs	Number	SQL 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']]

// Use the built-in read-only role to run a query, preventing unintended write operations from succeeding
const readonlyRes = await database.executePGSql({
  Role: 'cloudbase_read_only_user',
  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

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the EnvId used during manager initialization
Migrations	Yes	IPGUserMigrationInput[]	Migration list, at least 1 item; each item must pass the validations below
IncludeAll	No	Boolean	Whether to allow out-of-order local migrations (inserting a new version before an already applied one). Default false

IPGUserMigrationInput

Field	Required	Type	Description
Version	Yes	String	Migration version, a 14-digit numeric timestamp (e.g. 20260526000000)
Name	Yes	String	Migration name, lowercase letters and underscores only
Query	Yes	String	The SQL to execute; must not be empty

3. Response

IPreviewPGUserMigrationsResult

Field	Type	Description
Pending	IPGUserMigrationPlanItem[] | null	Migrations that will be executed
Applied	IPGUserMigrationPlanItem[] | null	Migrations that have already been applied
Conflicts	IPGUserMigrationConflict[] | null	Conflicts: migrations with the same version but a different checksum. Non-empty means local SQL differs from applied records
Executable	Boolean	Whether the plan can be executed directly (currently means no checksum conflicts)
RequestId	String	Unique 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

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the EnvId used during manager initialization
Migrations	Yes	IPGUserMigrationInput[]	Migration list, at least 1 item; same field validations as previewPGUserMigrations
LockTimeoutMs	No	Number	Maximum wait time (ms) to acquire the database lock; must be >= 0. Default 5000
StatementTimeoutMs	No	Number	Maximum execution time (ms) for a single SQL statement; must be >= 0. Default 300000
IncludeAll	No	Boolean	Whether to allow out-of-order local migrations. Default false

3. Response

IPushPGUserMigrationsResult

Field	Type	Description
RequestId	String	Unique request identifier
TaskId	String	Async 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

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the EnvId used during manager initialization
MigrationVersion	Yes	String	Migration version, a 14-digit numeric timestamp
Name	Yes	String	Migration name, lowercase letters and underscores only
Status	Yes	'applied' | 'reverted'	applied inserts into history; reverted deletes from history
Reason	Yes	String	Reason for the repair; cannot be empty
Query	No	String	Required 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)

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the initialization EnvId
Limit	No	Number	Page size, range [1, 500], default 100
Offset	No	Number	Pagination offset, >= 0, default 0

3. Response

IListPGUserMigrationsResult

Field	Type	Description
Total	Number	Total count
LatestVersion	String	Latest applied version (14-digit numeric timestamp)
Migrations	IPGUserMigrationSummary[]	Applied migrations, each item contains Version and Name
RequestId	String	Unique 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)

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the initialization EnvId
PageSize	No	Number	Internal 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

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the initialization EnvId
MigrationVersion	Yes	String	Migration version, a 14-digit numeric timestamp

3. Response

IDescribePGUserMigrationResult

Field	Type	Description
Version	String	Migration version
Name	String	Migration name
Query	String	The SQL body of the migration
RequestId	String	Unique 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

Field	Required	Type	Description
EnvId	No	String	CloudBase environment ID. Falls back to the initialization EnvId
TaskId	Yes	String	Task ID; cannot be empty

3. Response

IDescribeTaskResultResult

Field	Type	Description
TaskId	String	Task ID
TaskType	String	Task type (e.g. PGUserMigration)
Status	String	Task status: Accepted / Running / Succeed / Failed
Phase	String	Current phase
Reason	String	Failure reason (may be empty on success)
CreatedAt	String	Created time (ISO 8601)
UpdatedAt	String	Last updated time (ISO 8601)
RequestId	String	Unique 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)
}