Skip to main content

Using pgdoc

Once pgdoc is enabled, clients keep reading and writing data the document database way, and PostgreSQL serves the requests. For the enabling steps and error codes, see Plugin overview.

This page follows the path “connect → read and write data → permissions → indexes”. Client code comes in two tabs, mini program and web; server-side variants are called out separately.

Connect​

Initialization only needs the environment ID, not a PostgreSQL connection string:

const db = wx.cloud.database()

For server-side code (cloud functions, Node.js), initialize with @cloudbase/node-sdk. See the transactions and aggregation examples.

SDK reference: mini program SDK, JS SDK database, and Node SDK database.

Read and write data​

Data stays a JSON document, with eight field types: String, Number, Object, Array, Bool, Date, GeoPoint, and Null. A Date created with new Date() is client time; db.serverDate() marks server time.

CRUD​

const todos = db.collection('todos')

// Create
await todos.add({ data: { title: 'Write the doc', done: false } })

// Query
const { data } = await todos
  .where({ done: false })
  .orderBy('createdAt', 'desc')
  .limit(20)
  .get()

// Update
await todos.doc('todo-id').update({ data: { done: true } })

// Delete
await todos.doc('todo-id').remove()

The mini program client wraps the payload in data for create and update, while the web client passes the data object directly. For deleting documents on the web client, see Delete data.

Geospatial queries​

// Write a location
await db.collection('stores').add({
  data: { name: 'Beijing store', location: db.Geo.Point(116.4, 39.9) },
})

// Query by distance
const _ = db.command
await db
  .collection('stores')
  .where({
    location: _.geoNear({
      geometry: db.Geo.Point(116.4, 39.9),
      minDistance: 1000,
      maxDistance: 5000,
    }),
  })
  .limit(10)
  .get()

geoNear accepts only geometry, minDistance, and maxDistance; limit the result count with a chained limit().

⚠️ Note: Create a geospatial index before querying a geospatial field, otherwise the query fails.

Transactions​

A transaction makes multiple steps succeed or fail together, and a session lasts at most 30 seconds. Control commit and rollback yourself with startTransaction plus commit / rollback; on the server, runTransaction can commit automatically:

const transaction = await db.startTransaction()

try {
  await transaction.collection('todos').doc('todo-id').update({
    data: { completed: true },
  })
  await transaction.collection('users').doc('user-id').update({
    data: { completedTodos: db.command.inc(1) },
  })
  await transaction.commit()
} catch (err) {
  await transaction.rollback()
  throw err
}

Aggregation​

Aggregation processes data as a pipeline of stages, which suits statistics and grouping:

const $ = db.command.aggregate

const res = await db
  .collection('books')
  .aggregate()
  .match({ onSale: true })
  .group({ _id: '$category', avgSales: $.avg('$sales') })
  .end()

💡 Tip: Aggregation cost grows with the data volume. Narrow the dataset with match before the group stage, or keep only the needed fields with project, to cut the runtime noticeably.

Permissions​

pgdoc supports basic permissions only, not security rules. Configure permissions on the collection management page in the console. Data ownership is determined by the _openid field:

PermissionUse case
Read all data, modify own dataPublic content such as articles and products
Read and modify own dataPrivate data such as user profiles
Read all data, modify no dataConfiguration data such as system settings
No accessSensitive data such as financial records

Collections that rely on security rules must switch to basic permissions before enabling, or move the checks into server-side logic such as a cloud function. Do not rely on the client alone.

When you read and write directly in PostgreSQL, permissions are judged by GRANT and RLS. See PostgreSQL data permissions.

Index management​

Maintain indexes on the collection index management page in the console. Single-field, composite, geospatial, and unique indexes are supported. Build indexes for the fields you actually filter on: if you often query by done and createdAt, create a done single-field index or a done + createdAt composite index.

  • A collection holds at most 64 indexes, including _id, and a composite index holds at most 32 keys; more indexes slow down writes
  • Regex queries cannot use an index
  • Geospatial queries require a geospatial index. See Geospatial queries

HTTP API and SDK compatibility​

pgdoc reuses the document database request entry points, so clients do not change the domain or the request body. Supported entry points:

TypeSupported entry point
HTTP APIDocument database HTTP API, with the same request domain and path
Mini programMini program SDK
WebJS SDK
Serverwx-server-sdk 2.x / 4.x, Node SDK v3 /admin, Node SDK v4 gateway /v1

⚠️ Note: Some capabilities are out of scope. See Limits.

Limits​

These are hard limits. Exceeding them fails the request:

ItemLimit
Single document (BSON)16 MiB
Records per batch write25,000
Indexes per collection64, including _id
Keys per composite index32
Records returned per request50 MiB by default, 10 MiB through the cloud API
Cursor pages per request4096
Transaction session30 seconds
Records per import batch1000

Compared with the document database, pgdoc does not support:

  • Cross-request cursors: a request reads everything it returns and hands back no resumable cursor, so large result sets must be fetched in one go
  • Special collections: timeseries, clustered, and change stream

💡 Tip: pgdoc supports both 16-digit and 24-digit _id. NaN and Infinity come back as standard EJSON, and a $oid query on a regular field matches both native ObjectIds and legacy embedded objects. These are patterns that the document database limits but that work directly after migration.