Skip to content

Ratchet — Database Connections

Ratchet allows you to connect external databases to the Protokol platform and execute pre-defined, parameterized queries via the SDK or platform apps.

Supported Databases

Database Connection Type TLS Required
MySQL DSN Recommended
PostgreSQL DSN Recommended
MongoDB Connection URI Recommended
BigQuery Service Account JSON N/A

Connecting an External Database

Step 1: Whitelist Protokol's IP Addresses

Ratchet connects to your database from Protokol's servers. You must allow inbound connections from the following IPs in your database firewall or security group settings.

Required — Whitelist This IP

All Ratchet database connections egress through a dedicated NAT gateway with a fixed Reserved IP.

IP Address Description
129.212.253.213 Protokol egress (all environments)

Whitelist this single IP in your database firewall. This IP is permanent and does not change.

Provider-Specific Instructions
  1. Go to DigitalOcean Dashboard → Databases → your cluster
  2. Click Settings tab
  3. Under Trusted Sources, click Edit
  4. Add 129.212.253.213
  5. Click Save
  1. Go to AWS Console → RDS → your instance
  2. Click the Security Group under "Connectivity & security"
  3. Edit Inbound Rules
  4. Add a rule: Type = MySQL/Aurora (port 3306), Source = 129.212.253.213/32
  5. Save rules
  1. Go to GCP Console → SQL → your instance
  2. Click Connections → Networking
  3. Under Authorized networks, click Add a network
  4. Name: Protokol, Network: 129.212.253.213/32
  5. Save

Add a firewall rule, for example with ufw:

ufw allow from 129.212.253.213 to any port 3306

Or with iptables:

iptables -A INPUT -p tcp -s 129.212.253.213 --dport 3306 -j ACCEPT

For security, create a dedicated database user for Ratchet with limited permissions:

-- Read-only user (recommended for most use cases)
CREATE USER 'protokol_ratchet'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT ON your_database.* TO 'protokol_ratchet'@'%';

-- Read-write user (if you need INSERT/UPDATE/DELETE queries)
CREATE USER 'protokol_ratchet'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'protokol_ratchet'@'%';

FLUSH PRIVILEGES;

db.createUser({
  user: "protokol_ratchet",
  pwd: "<strong-password>",
  roles: [
    { role: "read", db: "your_database" }    // read-only
    // { role: "readWrite", db: "your_database" }  // read-write
  ]
});

Step 3: Configure the Connection DSN

Important: DSN Format

MySQL connections require a specific DSN format. A common mistake is using the generic mysql:// URI format, which will not work.

Correct format: 

user:password@tcp(hostname:port)/database_name?tls=true&parseTime=true

Examples: 

# DigitalOcean Managed MySQL
doadmin:password@tcp(db-mysql-xxx.ondigitalocean.com:25060)/mydb?tls=true&parseTime=true

# AWS RDS
admin:password@tcp(mydb.xxx.us-east-1.rds.amazonaws.com:3306)/mydb?tls=true&parseTime=true

# Self-hosted (no TLS)
myuser:password@tcp(192.168.1.100:3306)/mydb?parseTime=true

# If TLS certificate verification fails, use:
user:password@tcp(hostname:port)/database?tls=skip-verify&parseTime=true

Common mistakes:

Wrong ❌ Right ✅
mysql://user:pass@host:3306/db user:pass@tcp(host:3306)/db
user:pass@host:3306/db user:pass@tcp(host:3306)/db
user:pass@(host:3306)/db user:pass@tcp(host:3306)/db
...?ssl-mode=REQUIRED ...?tls=true

Format: 

mongodb://user:password@hostname:port/database?options

Examples: 

# DigitalOcean Managed MongoDB
mongodb+srv://doadmin:password@db-mongodb-xxx.mongo.ondigitalocean.com/mydb?tls=true&authSource=admin

# Standard MongoDB
mongodb://myuser:password@hostname:27017/mydb?authSource=admin

# Replica set
mongodb://user:pass@host1:27017,host2:27017/mydb?replicaSet=rs0&authSource=admin

BigQuery uses a Service Account JSON key instead of a DSN:

  1. Go to GCP Console → IAM → Service Accounts
  2. Create a service account with BigQuery Data Viewer role
  3. Generate a JSON key
  4. Paste the JSON key contents in the Service Account JSON field
  5. Enter your GCP Project ID

Step 4: Add Queries

Queries are pre-defined expressions stored on a connection and referenced by name at execution time. How parameters work depends on the database driver — SQL and MongoDB behave very differently.

Query Expressions & Parameters

SQL (MySQL / PostgreSQL)

SQL expressions use ? as a positional placeholder. Parameters are bound in order at execution time.

-- Single parameter
SELECT * FROM users WHERE id = ?

-- Multiple positional parameters
SELECT * FROM users
WHERE name LIKE ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?

Parameters are passed as a flat array. Order must match the ? positions in the expression:

// Executes: SELECT * FROM users WHERE name LIKE 'john%' LIMIT 20 OFFSET 0
ratchet.query('conn-id.query-id', ['john%', 20, 0]);

MongoDB

MongoDB works fundamentally differently from SQL. There are no placeholders in the expression — because MongoDB expressions are not parameterized strings, they are BSON command documents.

How MongoDB params work

The expression string is a JSON-encoded MongoDB command document — it is parsed into a bson.D (an ordered list of key-value fields). Each item in the params array must be a key-value object. Its keys are then appended as additional fields onto the command document before it is sent to MongoDB.

Final command = expression fields + param fields (appended in order)

Params append, they do not replace

If the expression already contains a key that a param also provides (e.g. filter), the command document will contain the key twice. MongoDB evaluates the first occurrence, making the param ineffective. Always leave parameterized fields absent from the expression.

Correct pattern — leave parameterized fields out of the expression

find — query a collection

The find command retrieves documents from a collection. The expression names the collection; everything else is injected via params.

Expression fields:

Field Type Description
find string Required. Collection name
filter object Match condition. Leave out to inject at runtime
projection object Fields to include/exclude
sort object Sort order. 1 = ascending, -1 = descending
limit number Max documents to return
skip number Documents to skip (for pagination)

Fixed query — no params needed: 

{
  "expression": "{\"find\": \"users\", \"filter\": {\"status\": \"active\"}, \"sort\": {\"createdAt\": -1}, \"limit\": 100}"
}

Parameterized query — filter and pagination at runtime: 

{ "expression": "{\"find\": \"orders\", \"projection\": {\"_id\": 1, \"total\": 1, \"status\": 1}}" }

ratchet.query('conn-id.q-orders', [
  { "filter":  { "customerId": "abc123", "status": "pending" } },
  { "sort":    { "createdAt": -1 } },
  { "limit":   50 },
  { "skip":    0 }
]);
// MongoDB receives:
// {
//   "find": "orders",
//   "projection": { "_id": 1, "total": 1, "status": 1 },
//   "filter": { "customerId": "abc123", "status": "pending" },
//   "sort": { "createdAt": -1 },
//   "limit": 50,
//   "skip": 0
// }

Useful filter operators in params:

// Range query
{ "filter": { "total": { "$gte": 100, "$lte": 500 } } }

// Match any of a list
{ "filter": { "status": { "$in": ["pending", "processing"] } } }

// Regex search
{ "filter": { "email": { "$regex": "^john", "$options": "i" } } }

// Nested field
{ "filter": { "address.country": "US" } }

// Multiple conditions (implicit AND)
{ "filter": { "status": "active", "role": "admin", "age": { "$gte": 18 } } }

aggregate — run a pipeline

The aggregate command runs a multi-stage pipeline. Always include "cursor": {} in the expression — MongoDB requires it.

Expression fields:

Field Type Description
aggregate string Required. Collection name
cursor object Required. Always {}
pipeline array Aggregation stages. Leave out to inject at runtime
allowDiskUse boolean Allow spilling to disk for large datasets

Fixed pipeline: 

{
  "expression": "{\"aggregate\": \"orders\", \"cursor\": {}, \"pipeline\": [{\"$group\": {\"_id\": \"$status\", \"count\": {\"$sum\": 1}}}]}"
}

Parameterized pipeline — fully dynamic stages: 

{ "expression": "{\"aggregate\": \"orders\", \"cursor\": {}}" }

ratchet.query('conn-id.q-aggregate-orders', [
  {
    "pipeline": [
      { "$match":  { "createdAt": { "$gte": { "$date": "2024-01-01T00:00:00Z" } } } },
      { "$group":  { "_id": "$customerId", "totalSpent": { "$sum": "$total" }, "orderCount": { "$sum": 1 } } },
      { "$sort":   { "totalSpent": -1 } },
      { "$limit":  10 }
    ]
  }
]);

Mix of fixed and dynamic fields — fix the collection and allow-disk-use in the expression, inject the pipeline: 

{ "expression": "{\"aggregate\": \"events\", \"cursor\": {}, \"allowDiskUse\": true}" }

ratchet.query('conn-id.q-events', [
  {
    "pipeline": [
      { "$match":   { "type": "purchase" } },
      { "$unwind":  "$items" },
      { "$group":   { "_id": "$items.sku", "sold": { "$sum": "$items.qty" } } },
      { "$sort":    { "sold": -1 } },
      { "$limit":   20 }
    ]
  }
]);

count — count matching documents

Expression fields:

Field Type Description
count string Required. Collection name
query object Filter condition (note: field is query, not filter)
limit number Max documents to count
skip number Documents to skip before counting 

{ "expression": "{\"count\": \"users\"}" }

// Count active admins
ratchet.query('conn-id.q-count-users', [
  { "query": { "status": "active", "role": "admin" } }
]);

// Count with a limit (useful for "are there more than N?" checks)
ratchet.query('conn-id.q-count-users', [
  { "query": { "status": "pending" } },
  { "limit": 1000 }
]);

distinct — get unique values for a field

Expression fields:

Field Type Description
distinct string Required. Collection name
key string Required. Field to get distinct values of
query object Optional filter 

{ "expression": "{\"distinct\": \"orders\", \"key\": \"status\"}" }

// Distinct statuses — no params needed for this one
ratchet.query('conn-id.q-distinct-status', []);

// With a filter — distinct cities for active users only
// Expression:
// { "expression": "{\"distinct\": \"users\", \"key\": \"address.city\"}" }
ratchet.query('conn-id.q-distinct-cities', [
  { "query": { "status": "active" } }
]);

What can go into params — full reference

Params are an array of objects. Each object's keys are appended to the command document. Any valid MongoDB command field can be passed as a param — as long as it is absent from the expression.

// Every field shown here can be a param key
ratchet.query('conn-id.q-find', [

  // --- Filter ---
  { "filter": {
      "status":    "active",                          // exact match
      "age":       { "$gte": 18, "$lte": 65 },        // range
      "role":      { "$in": ["admin", "moderator"] }, // match any
      "name":      { "$regex": "^john", "$options": "i" }, // regex
      "deletedAt": { "$exists": false },              // field absence
      "tags":      { "$all": ["featured", "sale"] }  // array contains all
  }},

  // --- Projection (include/exclude fields) ---
  { "projection": { "password": 0, "internalNotes": 0 } },  // exclude
  // OR
  { "projection": { "name": 1, "email": 1, "_id": 0 } },    // include only

  // --- Sorting ---
  { "sort": { "createdAt": -1, "name": 1 } },  // createdAt desc, name asc

  // --- Pagination ---
  { "limit": 25 },
  { "skip":  50 },

]);

One key per object, or multiple keys in one object — both work

These are equivalent:

// One key per object
[{ "limit": 25 }, { "skip": 50 }, { "sort": { "createdAt": -1 } }]

// All keys in one object
[{ "limit": 25, "skip": 50, "sort": { "createdAt": -1 } }]

Scalar params are silently ignored

MongoDB params must be objects. Anything that is not a map[string]interface{} is silently skipped with no error:

// ❌ All of these are ignored silently
["active"]
[42]
[true]
[null]
[["a", "b"]]

// ✅ Must always be an object
[{ "filter": { "status": "active" } }]

Qualified Name

Every query is identified by a qualified name in the format connectionID.queryID. This is used in all SDK calls and the direct API:

conn-abc123.q-find-users
└──────────┘ └──────────┘
 connection      query
    ID             ID

Executing Queries via SDK

import { Platform } from '@ptkl/sdk';

const ratchet = new Platform().ratchet();

// --- SQL example ---
// SELECT * FROM users WHERE email = ?
const users = await ratchet.query('conn-id.q-by-email', ['user@example.com']);
console.log(users.data.data);

// --- MongoDB example ---
// Stored expression: {"find": "orders"}  (no filter — parameterized)
const orders = await ratchet.query('conn-id.q-orders', [
  { "filter": { "status": "pending" } },
  { "limit": 100 }
]);
console.log(orders.data.data);

Ad-hoc / Inspect queries

Use inspectQuery to run a query expression inline without saving it first. Useful for exploration or testing:

// SQL ad-hoc
const test = await ratchet.inspectQuery({
  name: 'conn-id.any',
  query: {
    id: 'test',
    expression: 'SELECT * FROM users WHERE email = ?'
  },
  params: ['user@example.com']
});

// MongoDB ad-hoc
const mongoTest = await ratchet.inspectQuery({
  name: 'conn-id.any',
  query: {
    id: 'test',
    expression: '{"aggregate": "orders", "cursor": {}}'
  },
  params: [
    { "pipeline": [{ "$match": { "total": { "$gt": 100 } } }, { "$limit": 10 }] }
  ]
});

Troubleshooting

Error Cause Solution
default addr for network '...' unknown Missing tcp() wrapper in DSN Use user:pass@tcp(host:port)/db format
Access denied for user Wrong credentials or IP not whitelisted Check credentials and whitelist Protokol IPs
Connection timeout / hangs Firewall blocking, or wrong TLS setting Whitelist IPs and try tls=skip-verify
x509: certificate signed by unknown authority TLS CA not trusted Use tls=skip-verify
Error 1045 (28000) Authentication failure Verify username, password, and that user is allowed from Protokol's IP