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 (e.g. postgres), used for statements requiring higher privileges (e.g. CREATE POLICY / DROP POLICY)
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 a specific role to create an RLS policy
await database.executePGSql({
  Role: 'postgres',
  Sql: `CREATE POLICY avatars_public_read ON storage.objects
        FOR SELECT TO public USING (bucket_id = 'avatars')`
})

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