Appendix B: The 21 Methods — Quick Reference

CRUD — The Core Nine

doc_insert(collection, document) → document

Insert a single document. Auto-creates the collection (table + GIN index with jsonb_path_ops) if it does not exist.

• Parameters: collection (str), document (dict)
• Returns: the inserted document, including the assigned _id and created_at
• Generated SQL: INSERT INTO collection (_id, data) VALUES (gen_random_uuid(), $1::jsonb) RETURNING _id, data, created_at
• Chapter: 9, 11

doc_insert_many(collection, documents) → [document]

Insert many documents in a single round-trip.

• Parameters: collection (str), documents (list of dict)
• Returns: the list of inserted documents
• Generated SQL: Multi-row INSERT INTO collection (_id, data) VALUES (...), (...), ... RETURNING ...
• Chapter: 9, 11

doc_find(collection, filter=None, sort=None, limit=None, skip=None) → [document]

Find all documents matching the filter.

• Parameters: collection, MongoDB-style filter, optional sort ({field: 1|-1}), limit, skip
• Returns: list of matching documents
• Generated SQL: SELECT _id, data, created_at FROM collection WHERE <compiled filter> [ORDER BY ...] [LIMIT ...] [OFFSET ...]
• Chapter: 9, 10

doc_find_one(collection, filter=None) → document | None

Return the first matching document, or None.

• Generated SQL: SELECT ... FROM collection WHERE <compiled filter> LIMIT 1
• Chapter: 9, 10

doc_count(collection, filter=None) → int

Count documents matching the filter.

• Generated SQL: SELECT count(*) FROM collection WHERE <compiled filter>
• Chapter: 9, 10

doc_update(collection, filter, update, upsert=False) → {`{matched, modified}`}

Update every document matching the filter.

• Parameters: filter (MongoDB-style), update (plain merge or update operators: $set, $inc, $unset, $push, $pull, $addToSet, $mul, $rename)
• Returns: {"matched_count": N, "modified_count": N}
• Generated SQL: UPDATE collection SET data = <jsonb_set / jsonb_concat / ...> WHERE <compiled filter>
• Chapter: 9, 11

doc_update_one(collection, filter, update, upsert=False) → {`{matched, modified}`}

Update one document. With upsert=True, becomes INSERT ... ON CONFLICT (_id) DO UPDATE.

• Generated SQL: UPDATE ... WHERE _id = (SELECT _id FROM ... LIMIT 1) or INSERT ... ON CONFLICT (_id) DO UPDATE
• Chapter: 9, 11

doc_delete(collection, filter) → {`{deleted_count}`}

Delete every document matching the filter.

• Generated SQL: DELETE FROM collection WHERE <compiled filter>
• Chapter: 9, 11

doc_delete_one(collection, filter) → {`{deleted_count}`}

Delete one document.

• Generated SQL: DELETE FROM collection WHERE _id = (SELECT _id FROM ... LIMIT 1)
• Chapter: 9, 11

Queries and Indexing — Five Methods

doc_find_cursor(collection, filter=None, batch_size=500) → iterator

Server-side cursor for large result sets. Memory bounded by batch size on both sides.

• Generated SQL: BEGIN; DECLARE c CURSOR FOR SELECT ... ; FETCH N FROM c; ... CLOSE c; COMMIT;
• Chapter: 9, 11

doc_distinct(collection, field, filter=None) → [value]

Distinct values of a field, with optional filter. Dot notation supported for nested fields.

• Generated SQL: SELECT DISTINCT data->>'field' FROM collection WHERE <compiled filter>
• Chapter: 9, 11

doc_create_index(collection, keys, unique=False, partial_filter=None, name=None) → str

Manual index creation for the cases auto-indexing does not cover. Returns the index name.

• Parameters: keys ({field: 1 | -1 | "text" | "2dsphere"})
• Generated SQL: CREATE [UNIQUE] INDEX [CONCURRENTLY] name ON collection (...) [WHERE ...]
• Chapter: 6, 9

doc_find_one_and_update(collection, filter, update, return_document="before"|"after", upsert=False) → document | None

Atomic find-and-modify. No read-then-write race.

• Generated SQL: WITH picked AS (SELECT _id FROM ... LIMIT 1 FOR UPDATE SKIP LOCKED) UPDATE ... FROM picked WHERE ... RETURNING data
• Chapter: 9, 11

doc_find_one_and_delete(collection, filter) → document | None

Atomic find-and-delete.

• Generated SQL: WITH picked AS (SELECT _id FROM ... LIMIT 1 FOR UPDATE SKIP LOCKED) DELETE FROM ... USING picked WHERE ... RETURNING data
• Chapter: 9, 11

Aggregation and Operations — Seven Methods

doc_aggregate(collection, pipeline, refresh="on_write"|"ttl"|"manual", ttl=None) → [document]

Run a MongoDB-style aggregation pipeline. The result is cached behind a materialized view keyed by the canonicalized pipeline hash; warm-cache calls are an index scan.

• Parameters: pipeline (list of stage dicts), refresh (cache mode), ttl (seconds, when refresh="ttl")
• Generated SQL: A CTE chain with one stage per CTE, materialized into gl_matview_<hash> and indexed on the primary key
• Chapter: 8, 12

doc_watch(collection, callback) → subscription

Subscribe to a change stream. Callback receives {operationType, _id, fullDocument, updateDescription}.

• Generated SQL/DDL: Installs an AFTER INSERT OR UPDATE OR DELETE trigger on the collection that calls pg_notify('doc_<collection>', payload::text). The SDK opens a LISTEN doc_<collection> connection.
• Chapter: 9, 13

doc_unwatch(collection, subscription=None) → None

Stop watching. Drops the trigger when no subscribers remain.

• Chapter: 9, 13

doc_create_ttl_index(collection, expire_after_seconds, field="created_at") → str

Create a TTL index. Documents older than expire_after_seconds are deleted automatically.

• Generated SQL/DDL: B-tree expression index on the TTL field plus a sweep trigger (or scheduled pg_cron job, depending on configuration)
• Chapter: 9, 13

doc_remove_ttl_index(collection) → None

Remove the TTL index and its sweep mechanism.

• Chapter: 9, 13

doc_create_capped(collection, max_documents=None, max_bytes=None) → None

Cap a collection at a document count or byte size. Older documents are deleted when new ones arrive.

• Generated SQL/DDL: AFTER INSERT trigger that counts rows and deletes the oldest when the cap is exceeded
• Chapter: 9, 13

doc_remove_cap(collection) → None

Remove the cap and its trigger.

• Chapter: 9, 13

The Twenty-Second Method (Convenience)

doc_create_collection(collection, unlogged=False, validator=None) → None

Manual collection creation, for the case where you want options before any insert.

• Why it isn't in the count of 21: Collection creation is implicit — the first doc_insert creates the table automatically. doc_create_collection is a convenience for setting unlogged=True (high-throughput temporary collections) or attaching a $jsonSchema validator at creation time. The parity claim is twenty-one methods; this one is hospitality, not capability.

Method Index by Category

CRUD (9): doc_insert, doc_insert_many, doc_find, doc_find_one, doc_count, doc_update, doc_update_one, doc_delete, doc_delete_one

Queries and Indexing (5): doc_find_cursor, doc_distinct, doc_create_index, doc_find_one_and_update, doc_find_one_and_delete

Aggregation and Operations (7): doc_aggregate, doc_watch, doc_unwatch, doc_create_ttl_index, doc_remove_ttl_index, doc_create_capped, doc_remove_cap

MongoDB-to-Method Mapping

For readers porting code call by call:

The filter syntax, the update operators, and the pipeline stages port unchanged. The diff is one import statement and a connection string.