# Universal Database Gateway (`solutionssmart/universal-database-gateway`) Actor

Move data from PostgreSQL, MySQL, MariaDB, SQLite, or MongoDB into Apify Datasets. Export tables, run parameterized read-only queries, and sync new rows incrementally with bounded batches and secure credential handling.

- **URL**: https://apify.com/solutionssmart/universal-database-gateway.md
- **Developed by:** [Solutions Smart](https://apify.com/solutionssmart) (community)
- **Categories:**
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.10 / 1,000 database rows exporteds

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.
Actors are written with capital "A".

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## Universal Database Gateway

Connect PostgreSQL, MySQL, MariaDB, SQLite, or MongoDB to an Apify Dataset. Universal Database Gateway reads a table, runs one safe read-only query, discovers schema metadata, or syncs new rows on a schedule.

The result is ordinary Dataset data that you can send to another Actor, Google Sheets workflow, forecasting pipeline, AI task, or human review process.

### What this Actor does

- Tests the database connection before reading data.
- Exports SQL tables or MongoDB collections in bounded batches.
- Runs parameterized SELECT queries with a read-only safety check.
- Selects columns and excludes sensitive columns from table output.
- Saves incremental cursor state in a named Apify Key-Value Store.
- Normalizes timestamps, big integers, decimals, UUIDs, JSON, and binary values.
- Keeps credentials out of Dataset records and normal logs.
- Works from the Apify Console, API, CLI, schedules, and integrations.

This Actor does not host the DBX web application, provide a public SQL console, modify source databases, or require an AI service.

### Supported databases

The current build has native provider implementations for these databases:

| Status | Database | Available modes |
| --- | --- | --- |
| Native and tested | PostgreSQL | table, query, incremental, discover |
| Native and tested | MySQL and MariaDB | table, query, discover |
| Native and tested | SQLite | table, query, discover |
| Native and tested | MongoDB | collection export, discover |
| Planned | SQL Server, ClickHouse, DuckDB, Redis, Elasticsearch, Oracle, Snowflake, BigQuery, Databricks, Neo4j, Qdrant | Not available yet |

Do not select a planned database type. The Actor rejects database types that are not implemented.

### Quick start in Apify Console

1. Open the Actor and select **Start**.
2. Choose a database type.
3. Paste a connection string into **Connection string**.
4. Choose **table** as the mode.
5. Enter the schema and table name.
6. Set **Maximum rows** and start the run.
7. Open the run's **Dataset** tab to inspect the exported records.

Use a database account with read-only permissions. Do not use `localhost` or `127.0.0.1` for a cloud run. The database hostname must resolve and accept connections from Apify's compute environment.

### PostgreSQL table extraction

Example input:

```json
{
  "databaseType": "postgresql",
  "connectionString": "postgresql://readonly_user:PASSWORD@db.example.com:5432/app?sslmode=verify-full",
  "mode": "table",
  "schema": "public",
  "table": "customers",
  "columns": ["id", "name", "email"],
  "maxRows": 1000
}
```

Use the direct database connection string when your provider supplies pooled and direct URLs. For Neon, this is usually the unpooled connection URL because the Actor applies a PostgreSQL statement timeout on the connection.

### MySQL or MariaDB table extraction

```json
{
  "databaseType": "mysql",
  "connectionString": "mysql://readonly_user:PASSWORD@db.example.com:3306/shop",
  "mode": "table",
  "table": "customers",
  "columns": ["id", "name", "email"],
  "excludeColumns": ["password_hash"],
  "maxRows": 1000
}
```

MariaDB uses the same input shape with `databaseType: "mariadb"`.

### SQLite table extraction

For a SQLite database available inside the Actor container, set the connection string to the file path:

```json
{
  "databaseType": "sqlite",
  "connectionString": "/data/app.sqlite",
  "mode": "table",
  "table": "events",
  "maxRows": 5000
}
```

The SQLite file must already be available inside the Actor environment. A cloud Actor cannot read a file that exists only on your computer, and this build does not download local files automatically.

### MongoDB collection export

MongoDB uses collection terminology instead of SQL tables:

```json
{
  "databaseType": "mongodb",
  "connectionString": "mongodb+srv://readonly_user:PASSWORD@cluster.example.mongodb.net/app",
  "mode": "table",
  "table": "customers",
  "maxRows": 1000
}
```

Documents remain nested JSON objects. MongoDB SQL query mode is not available in this build.

### Read-only query mode

Query mode is useful when table mode is not enough:

```json
{
  "databaseType": "postgresql",
  "connectionString": "YOUR_CONNECTION_STRING",
  "mode": "query",
  "query": "SELECT id, customer_id, total FROM orders WHERE updated_at >= $1",
  "parameters": ["2026-09-01T00:00:00Z"],
  "maxRows": 10000,
  "batchSize": 1000
}
```

The Actor accepts one SELECT-style statement. It rejects INSERT, UPDATE, DELETE, MERGE, DDL, transaction control, procedure calls, provider commands, and multiple statements. Parameters are passed through the database driver and are never inserted into the SQL string.

The read-only check is an application safeguard. It cannot replace database permissions. Use a database role that can only read the required schema and tables.

### Incremental sync

Incremental mode reads rows whose cursor value is greater than the saved value:

```json
{
  "databaseType": "postgresql",
  "connectionString": "YOUR_CONNECTION_STRING",
  "mode": "incremental",
  "schema": "public",
  "table": "orders",
  "cursor": {
    "field": "updated_at",
    "type": "timestamp"
  },
  "stateStoreName": "orders-sync-state",
  "maxRows": 100000,
  "batchSize": 1000
}
```

Supported cursor types are `timestamp`, `integer`, and lexicographically ordered `string`. Use a field that is populated, indexed where appropriate, and moves forward reliably. The current MVP uses a single cursor field. If several rows can share the same maximum cursor value across runs, use a cursor design that avoids ties or expect composite cursor support in a later version.

The Actor writes each Dataset batch first, then updates the cursor. A failed Dataset write does not advance the saved cursor. The state store and `stateKey` must stay the same between scheduled runs.

### Schema discovery

Set `mode` to `discover` to inspect database metadata without exporting table rows:

```json
{
  "databaseType": "postgresql",
  "connectionString": "YOUR_CONNECTION_STRING",
  "mode": "discover",
  "schema": "public"
}
```

The output includes schemas and tables. Provider metadata methods also expose table columns, types, nullability, and primary-key information for future integrations.

### Input fields

The Console form groups the common settings first. Most users only need these fields:

| Field | What to enter |
| --- | --- |
| Database type | `postgresql`, `mysql`, `mariadb`, `sqlite`, or `mongodb` |
| Connection string | The complete URL from your database provider, marked secret in the Console |
| Mode | `table`, `query`, `incremental`, or `discover` |
| Schema | Usually `public` for PostgreSQL; omit for MongoDB and SQLite defaults |
| Table | SQL table or MongoDB collection name |
| Columns | Optional list of columns to export |
| Query | Required only for query mode |
| Parameters | Values matching `$1`, `$2` or `?` placeholders |
| Maximum rows | Hard per-run limit; default `100000` |
| Batch size | Rows written per Dataset batch; default `1000` |

Advanced settings control timeouts, binary values, serialization failures, cursor record names, and the persistent state store. Leave them at their defaults until the basic extraction works.

### Connection options and networking

You can use a connection string or the advanced `connection` object with `host`, `port`, `database`, `username`, `password`, `ssl`, and provider-specific fields. Connection strings are usually the simplest option.

Use the advanced object when your database provider gives you separate connection fields:

```json
{
  "databaseType": "postgresql",
  "connection": {
    "type": "postgresql",
    "host": "db.example.com",
    "port": 5432,
    "database": "app",
    "username": "readonly_user",
    "password": "YOUR_PASSWORD",
    "ssl": true
  },
  "mode": "table",
  "schema": "public",
  "table": "customers"
}
```

For cloud runs, the database needs a reachable DNS hostname and open database port. A database running on your laptop, inside a private VPC, or behind a firewall will not be reachable automatically. Use an Apify-supported private networking, VPN, proxy, or secure tunnel arrangement when required. This Actor does not create a tunnel or act as a generic network proxy.

Use TLS when your provider supports it. Do not disable certificate verification to work around a TLS error. For PostgreSQL, `sslmode=verify-full` is an explicit connection-string setting.

### Data types and sensitive values

The Dataset preserves source column names where possible. The serializer uses these rules:

| Source value | Dataset value |
| --- | --- |
| Timestamp or date | ISO-8601 string |
| Safe integer or bigint | JSON number |
| Unsafe bigint | Decimal string, preserving exact digits |
| Decimal or NUMERIC | Driver string, preserving precision |
| UUID | String |
| JSON or nested document | Nested JSON |
| Binary value | Rejected by default; base64 only with `binaryPolicy: "base64"` |
| NULL | JSON `null` |

Use `excludeColumns` for known secrets such as password hashes or API keys. The Actor does not perform automatic PII detection.

### FAQ

#### Does the Actor modify my database?

No. The current modes are for reading and exporting data. Query mode accepts one read-only SQL statement. Use a database account with read-only permissions as an additional safeguard.

#### Can I connect to a database on my laptop?

Not directly. Apify must be able to resolve the hostname and reach the database port from the Actor runtime. Use a reachable cloud endpoint or an Apify-supported private networking arrangement.

#### Does incremental sync replace the Dataset?

Each run writes its rows to the run's default Dataset. The cursor is stored separately in a Key-Value Store, so later scheduled runs can request rows newer than the previous cursor. The current MVP does not deduplicate or update earlier Dataset items.

#### How do I start with the simplest form input?

Choose the database type, paste the provider's connection string, select `table`, and enter the schema and table name. Start with a small `maxRows` value, confirm the output, then increase the limit or schedule an incremental run.

### Output

Rows go to the run's default Dataset. The `OUTPUT` record in the default Key-Value Store contains a summary like this:

```json
{
  "databaseType": "postgresql",
  "mode": "incremental",
  "source": "public.gateway_test",
  "rowsRead": 2,
  "rowsWritten": 2,
  "rowsSkipped": 0,
  "batches": 1,
  "cursor": {
    "field": "updated_at",
    "previous": null,
    "current": "2026-09-08T20:41:02.496Z"
  },
  "completed": true
}
```

Apify gives the run a Dataset, Key-Value Store, logs, API access, scheduling, monitoring, and integrations. Use the Dataset API or connect the Dataset to downstream Actors and workflows after the run finishes.

### Limits, retries, and failure behavior

The Actor checks the connection before extraction and applies connection and query timeouts where the provider supports them. `maxRows` and `batchSize` prevent an omitted limit from becoming an accidental full-table export.

The default `rowErrorPolicy` is `fail`. Set it to `skip` only when losing an individual serialization-failing row is acceptable; the output reports `rowsSkipped`. Connection failures, invalid SQL, and permission errors still fail the run. Query replay after a partial network failure is intentionally not automatic because replay can duplicate Dataset rows.

### Pricing

The Actor supports an optional pay-per-event event named `rows-exported-1000`. One event represents up to 1,000 successfully written Dataset rows. The Actor charges after Dataset pushes succeed, never for failed connections or failed queries. The billing count is based on cumulative exported rows, not the configurable `batchSize`; a final partial export counts as one event. Local and non-PPE runs skip this charge and report `chargedBatches: 0`.

For an initial beta configuration, set `rows-exported-1000` to `$0.10` in the Actor's Pricing tab. This is a starting price, not a final cost recommendation. Benchmark 1,000, 10,000, and 100,000 row exports before scheduling large jobs, and include Apify compute and storage costs in the review. See [`docs/benchmark.md`](docs/benchmark.md).

Configure the event in Apify Console under **Development > My Actors > your Actor > Monetization > Pay per event**. Add the event name exactly as `rows-exported-1000`, give it the description `Export up to 1,000 database rows to the default Dataset`, and set the price for each subscription tier. Disable the automatic `apify-default-dataset-item` event, or users may be charged both per Dataset item and per export unit. The source code triggers the custom event after successful Dataset persistence.

### Scheduling and integrations

Use an Apify Schedule for recurring table exports or incremental syncs. For incremental runs, keep the same named `stateStoreName` and `stateKey`. Each run writes new records to its own default Dataset, so downstream workflows should consume the Dataset linked from that run or use an explicit storage workflow when you need a shared destination.

The output is intentionally generic. No special integration code is needed for AI processing, human review, forecasting, Sheets, or another Actor. Pass the Dataset ID or Dataset URL to the next step in your workflow.

### Troubleshooting

#### `getaddrinfo ENOTFOUND`

The hostname cannot be resolved. Replace placeholder values such as `HOST` with the real database hostname from your provider. Do not use `localhost` for a cloud run.

#### Authentication failed

Check username, password, database name, port, TLS settings, and the database role's remote-login permissions. Use a read-only role with access to the selected schema and table.

#### Connected but no rows returned

Check the schema, table or collection name, cursor value, and permissions. In incremental mode, an empty Dataset can be correct when no row is newer than the saved cursor.

#### PostgreSQL SSL warning

Use `sslmode=verify-full` in the PostgreSQL connection string. The warning is emitted by the PostgreSQL Node driver and does not by itself mean the connection failed.

### Security and privacy

Do not put production credentials in source files, README examples, or logs. Use secret input fields in Apify Console and a least-privilege source account. The Actor never writes credentials to Dataset output, but source column values remain your responsibility. Treat exported data as sensitive if the source database contains personal or confidential information.

Read the full [security model](docs/security.md) for SQL safety, network access, resource limits, credential handling, and known limitations.

### Roadmap

Planned work includes SQL Server, ClickHouse, DuckDB, Redis, Elasticsearch, Oracle, Snowflake, BigQuery, Databricks, Neo4j, and Qdrant providers; composite cursors; provider-native cancellation; a separate error Dataset; and a read-only MCP Gateway. Write-back and natural-language SQL remain outside the MVP.

### Development

```bash
npm install
npm run build
npm test
apify validate-schema
apify run
```

DBX was used as an architectural reference. This Actor does not bundle DBX source, binaries, web UI, or agents. See [DBX reference analysis](docs/dbx-reference-analysis.md), [architecture](docs/architecture.md), and [third-party licenses](THIRD_PARTY_LICENSES.md).

# Actor input Schema

## `databaseType` (type: `string`):

Choose the database that owns the source table or collection.

## `connectionString` (type: `string`):

Paste the complete provider URL. This field is secret and is never shown in Actor output.

## `connection` (type: `object`):

Optional JSON object with host, port, database, username, password, ssl, or filename fields. Leave blank when using a connection string.

## `mode` (type: `string`):

Choose table for the simplest export. Use query, incremental, or discover for other workflows.

## `schema` (type: `string`):

SQL schema; defaults to public for PostgreSQL.

## `table` (type: `string`):

Source table or MongoDB collection.

## `columns` (type: `array`):

Optional explicit projection.

## `excludeColumns` (type: `array`):

Columns removed before Dataset output.

## `query` (type: `string`):

One parameterized SELECT-style query.

## `parameters` (type: `array`):

Driver parameters in query placeholder order. Do not insert values into the SQL text.

## `cursor` (type: `object`):

Cursor field and type for incremental mode.

## `batchSize` (type: `integer`):

Rows pushed to Dataset per batch. The default works for most runs.

## `maxRows` (type: `integer`):

Hard maximum rows read in this run.

## `connectionTimeoutSeconds` (type: `integer`):

Maximum connection establishment time.

## `queryTimeoutSeconds` (type: `integer`):

Maximum provider query time where supported.

## `binaryPolicy` (type: `string`):

Reject binary values or encode them as base64.

## `rowErrorPolicy` (type: `string`):

Fail the run or skip serialization failures.

## `stateKey` (type: `string`):

Record name for the incremental cursor.

## `stateStoreName` (type: `string`):

Named KVS shared by scheduled incremental runs.

## Actor input object example

```json
{
  "databaseType": "postgresql",
  "connectionString": "postgresql://readonly_user:password@db.example.com:5432/app?sslmode=verify-full",
  "connection": {
    "type": "postgresql",
    "host": "db.example.com",
    "port": 5432,
    "database": "app",
    "username": "readonly_user",
    "password": "YOUR_PASSWORD",
    "ssl": true
  },
  "mode": "table",
  "schema": "public",
  "query": "SELECT id, name, total FROM customers WHERE updated_at >= $1",
  "parameters": [
    "2026-09-01T00:00:00Z"
  ],
  "cursor": {
    "field": "updated_at",
    "type": "timestamp"
  },
  "batchSize": 1000,
  "maxRows": 100000,
  "connectionTimeoutSeconds": 15,
  "queryTimeoutSeconds": 300,
  "binaryPolicy": "reject",
  "rowErrorPolicy": "fail",
  "stateKey": "INCREMENTAL_STATE",
  "stateStoreName": "universal-database-gateway-state"
}
```

# Actor output Schema

## `dataset` (type: `string`):

No description

## `summary` (type: `string`):

No description

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "databaseType": "postgresql",
    "mode": "table"
};

// Run the Actor and wait for it to finish
const run = await client.actor("solutionssmart/universal-database-gateway").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "databaseType": "postgresql",
    "mode": "table",
}

# Run the Actor and wait for it to finish
run = client.actor("solutionssmart/universal-database-gateway").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "databaseType": "postgresql",
  "mode": "table"
}' |
apify call solutionssmart/universal-database-gateway --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,solutionssmart/universal-database-gateway"
        }
    }
}

```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/ynXVC1avvZu4w5ghR/builds/Pipd7ap7WNmtHyGod/openapi.json