> ## Content Index
> Fetch the complete content index at: https://blog.protokol.io/llms.txt
> Use this file to discover other available public pages before exploring further.

# Ratchet: query your external databases from Protokol
- URL: https://blog.protokol.io/ratchet/
- Published: 2026-11-17T17:33:00.000Z
- Updated: 2026-11-17T18:57:48.000Z
- Description: Ratchet connects MySQL, PostgreSQL, MongoDB and BigQuery to your project and runs saved, parameterized queries from workflows, functions or the SDK.
- Author: Strahinja Djuric
- Tags: Engineering

Not all of your data lives in Protokol. A MySQL database run by another team, a MongoDB cluster behind an older product, a BigQuery dataset used for reporting: you often need to read from them, or write to them, without moving the data first.

Ratchet connects external databases to your project and lets you run predefined, parameterized queries against them. You find it in the dashboard under Developer › Ratchet. You call the same queries from workflows, functions or the SDK.

## What Ratchet supports

| Database   | Connection type      | TLS         |
| ---------- | -------------------- | ----------- |
| MySQL      | DSN                  | Recommended |
| PostgreSQL | DSN                  | Recommended |
| MongoDB    | Connection URI       | Recommended |
| BigQuery   | Service Account JSON | N/A         |

A **connection** holds the credentials for one database. Each connection holds a set of **queries**: saved expressions that you reference by name when you run them.

## Connecting a database

### 1\. Allow Protokol through your firewall

Ratchet connects to your database from Protokol's servers. All Ratchet connections leave from a single fixed IP address that does not change, and it is the same in every environment. The [Ratchet documentation](https://docs.protokol.io/platform/ratchet/?ref=blog.protokol.io) lists the address and shows how to allow it on DigitalOcean, AWS RDS, Google Cloud SQL and self-hosted servers (`ufw` and `iptables`).

### 2\. Create a dedicated database user

Create a separate user for Ratchet with limited permissions. For most uses, make it read-only:

```sql
CREATE USER 'protokol_ratchet'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT ON your_database.* TO 'protokol_ratchet'@'%';

```

If your queries need to write, grant `INSERT`, `UPDATE` and `DELETE` as well. MongoDB works the same way, with the `read` or `readWrite` role.

### 3\. Configure the connection

MySQL expects a specific DSN format, not a `mysql://` URI:

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

```

![The Ratchet connection form with a MySQL connection filled in using example values](https://blog.protokol.io/content/images/2026/11/01-connection-form.png)  
*Creating a connection: pick the database type, then enter the credentials.*

MongoDB takes a standard connection URI, for example `mongodb://myuser:password@hostname:27017/mydb?authSource=admin`. For BigQuery you create a service account with the BigQuery Data Viewer role, generate a JSON key, and paste it in with your GCP project ID.

If a MySQL connection fails, the [Ratchet documentation](https://docs.protokol.io/platform/ratchet/?ref=blog.protokol.io) has a troubleshooting table that maps the common errors to fixes. `Access denied` usually means wrong credentials or an address that isn't allowed. A certificate error means you can try `tls=skip-verify`.

## Parameterized queries

Queries are stored on the connection and run by their **qualified name**: `connectionID.queryID`, for example `conn-abc123.q-find-users`. You pass parameters when you run the query. How parameters work depends on the database.

### SQL

SQL expressions use `?` as a positional placeholder. You pass the values as a flat array, in the same order:

```sql
SELECT * FROM users
WHERE name LIKE ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?

```

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

```

### MongoDB

MongoDB expressions have no placeholders. The stored expression is a JSON-encoded command document, and each item in `params` is an object whose keys are appended to that command. Supported commands include `find`, `aggregate`, `count` and `distinct`.

Leave out of the stored expression anything you want to set at run time:

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

```

```js
ratchet.query('conn-id.q-orders', [
  { "filter": { "customerId": "abc123", "status": "pending" } },
  { "sort":   { "createdAt": -1 } },
  { "limit":  50 },
  { "skip":   0 }
]);

```

Two details matter here:

- **Params append, they don't replace.** If the stored expression already contains `filter` and a param supplies `filter` too, the command contains the key twice and MongoDB uses the first one. Keep parameterized fields out of the expression.
- **Params must be objects.** A scalar such as `["active"]` is silently ignored, with no error.

For `aggregate`, always include `"cursor": {}` in the expression. You can fix the collection and options there and inject the `pipeline` as a param.

## Running queries

### From the SDK

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

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

const users = await ratchet.query('conn-id.q-by-email', ['user@example.com']);
console.log(users.data.data);

```

While you are developing a query, `inspectQuery` runs an expression inline without saving it:

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

```

### From workflows and functions

Saved queries can be executed from workflows, functions or the SDK. The SDK call shown above is the one to use in code.

### From the command line

The `ptkl ratchet` commands cover the full lifecycle:

```bash
# 1. Validate credentials before saving
ptkl ratchet test --data '{"id":"my-postgres","type":"postgres","credentials":{...}}'

# 2. Create the connection
ptkl ratchet connection create --data '{"id":"my-postgres","type":"postgres","credentials":{...}}'

# 3. Develop a query ad hoc
ptkl ratchet inspect --data '{"name":"my-postgres","query":"SELECT 1","params":[]}'

# 4. Run a saved query
ptkl ratchet query --name my-postgres.ping

```

`ptkl ratchet test` checks a connection configuration without saving it, which is handy before `connection create`.

## Safety notes

Ratchet's safety comes from a few documented choices:

- **A fixed egress address.** You allow one address in your firewall, not a range.
- **A least-privilege database user.** A read-only user can't change data, whatever query is saved.
- **Pre-defined queries.** Callers run a saved query by name and supply parameters. Ad-hoc `inspect` calls are the explicit exception, meant for development.
- **TLS.** It is recommended for MySQL, PostgreSQL and MongoDB.

## Where to use it

Use Ratchet when the data should stay where it is: reporting against a business-intelligence database, reading from a system you can't migrate yet, or looking up a value during a workflow run. If the data belongs to your product, a component is the better home.

## A worked example

You want a workflow to list the twenty best-selling items from purchase events stored in MongoDB:

1. Create a read-only MongoDB user and allow Protokol's address in the firewall.
2. Add a connection and save a query `q-events` with the expression `{"aggregate": "events", "cursor": {}, "allowDiskUse": true}`.
3. Test the pipeline with `inspectQuery`.
4. Call the saved query with the pipeline as a param:

```js
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 }
    ]
  }
]);

```

## Learn more

The [Ratchet documentation](https://docs.protokol.io/platform/ratchet/?ref=blog.protokol.io) covers firewall setup per provider, every MongoDB command and the troubleshooting table. The [ptkl ratchet reference](https://docs.protokol.io/toolkit/ratchet/?ref=blog.protokol.io) covers the CLI. The [TypeScript SDK](https://docs.protokol.io/sdk/typescript/?ref=blog.protokol.io) page covers the client you call it from.