> ## Documentation Index
> Fetch the complete documentation index at: https://tyk.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Configure SQL Storage

> Learn how to configure PostgreSQL, MySQL and MariaDB for Tyk Dashboard, Tyk MDCB, Tyk Pump and Tyk Developer Portal, including connection settings and connection pool tuning.

Several Tyk components can use SQL as their persistent database, but they don't all share a single instance:

* Tyk Dashboard, Tyk MDCB and Tyk Pump connect to the same PostgreSQL instance as part of the Core Platform's [shared persistent database](https://tyk.io/docs/api-management/persistent-database.md).
* Tyk Developer Portal maintains its own, separate database - see [Install Developer Portal](https://tyk.io/docs/portal/install.md#architecture) for how that fits into a Portal deployment.

Tyk tests each component with different database engines:

| Component | PostgreSQL | MySQL / MariaDB |
| - | - | - |
| Tyk Dashboard, Tyk MDCB, Tyk Pump | Tested | Not recommended - untested |
| Tyk Developer Portal | Tested | Tested |

## Tested Versions

Tyk tests each release against the versions below. Other versions may work, but Tyk does not test them.

### Core Platform

| Tyk Version | Tested PostgreSQL Versions |
| - | - |
| From 5.X onwards | 15.x, 16.x, 17.x, 18.x |

The following managed services are expected to work as a drop-in replacement for PostgreSQL. Tyk does not test them directly:

* [Amazon RDS for PostgreSQL](https://aws.amazon.com/rds/postgresql/)
* [Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)
* [Azure Database for PostgreSQL](https://learn.microsoft.com/en-us/azure/postgresql/)
* [Google Cloud SQL for PostgreSQL](https://cloud.google.com/sql/docs/postgres)

> [!NOTE]
>
> [Sqlite](https://www.sqlite.org/index.html) support was previously offered for development and testing only. It reached End of Life and is no longer supported from Tyk 5.7.0 onward. Use PostgreSQL, MongoDB, or another listed compatible alternative instead.

### Developer Portal

| Database Type | Tested Versions |
| - | - |
| PostgreSQL | 15.x, 16.x, 17.x, 18.x |
| MySQL | 8.4, 9.7 |
| MariaDB | 10.11, 11.4, 11.8, 12.3 |

MariaDB 10.6 is expected to work, but Tyk no longer tests it.

The following managed services are expected to work as a drop-in replacement for PostgreSQL. Tyk does not test them directly:

* [Amazon RDS for PostgreSQL](https://aws.amazon.com/rds/postgresql/)
* [Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)
* [Azure Database for PostgreSQL](https://learn.microsoft.com/en-us/azure/postgresql/)
* [Google Cloud SQL for PostgreSQL](https://cloud.google.com/sql/docs/postgres)

## Configuration Reference

Each component configures SQL storage differently - see [Configuration Examples](#configuration-examples) below for each component's own fields. A few options are worth explaining up front, since they apply across more than one component.

### Connection String

A connection string combines the address Tyk uses to reach your database (host and port), access credentials, and the database name.

| Field | Purpose |
| - | - |
| `host` | Database server hostname or IP address. |
| `port` | Database server port. |
| `user` | Username to authenticate as. |
| `password` | Password for that user, if your database requires one. |
| `dbname` | Name of the database to connect to. |

**PostgreSQL** uses space-separated `key=value` pairs:

```text theme={null}
host=tyk-db port=5432 user=admin password=secr3t dbname=tyk-demo-db
```

**MySQL / MariaDB** uses a single positional string instead, formatted `username:password@tcp(host:port)/dbname`:

```text theme={null}
admin:secr3t@tcp(tyk-db:3306)/tyk-demo-db
```

Some managed database services let you scale reads independently of writes, by exposing separate endpoints: one for the primary, which handles writes, and one or more read replicas, which serve reads without adding load to the primary. Amazon RDS is a common example, with distinct writer and reader endpoints.

* Tyk Dashboard can take advantage of this by pointing writes and reads at two different connection strings instead of one.
* Tyk MDCB and Tyk Pump only write to the persistent storage, and so would not benefit from separate read and write connections.
* Tyk Developer Portal also uses a single connection string.

### Table Sharding

Unlike [MongoDB](https://tyk.io/docs/api-management/dashboard-analytics/analytics-storage-management.md#mongodb), SQL has no built-in way to expire old data automatically. Left alone, a table such as `tyk_analytics` (Logs) or `tyk_aggregated` (Aggregate Analytics) just grows forever, and deleting old rows from a huge table with a plain `DELETE` gets slower as the table grows.

When enabled, **table sharding** solves this by creating a new table per day instead of a single large table. Each table uses a common name as a prefix, adding the date to generate a unique table name, for example `tyk_analytics_20230327`. Deleting a day's data then just means dropping that one table, instead of running a slow `DELETE` against everything.

* Tyk Dashboard, Tyk MDCB, and Tyk Pump can be configured to use sharded tables.
* Tyk Developer Portal does not expose this option.

> [!WARNING]
>
> The writers and readers of a [data category](https://tyk.io/docs/api-management/persistent-database.md#how-tyk-uses-persistent-storage) must all have the same table sharding configuration (enabled or disabled).

#### Maintaining Consistency

When the component writing a sharded category starts up, it checks that day's table against the current schema and adds any missing columns, for example after an upgrade that extends the schema; it never drops or renames existing columns. By default, this only happens for the current day's table - older dated tables are left exactly as they were when created.

Tyk Pump's `migrate_sharded_tables` setting extends this to every table matching its prefix, not just today's, scanning the whole set on startup and updating any that are out of date. Tyk MDCB has no equivalent setting, so it only ever updates the current day's table.

> [!WARNING]
>
> This scan-and-update runs on every restart, not just once, and touches every table matching the prefix. In a deployment with months or years of daily-sharded tables, that can mean scanning and potentially altering hundreds or thousands of tables - startup can take a long time, and the pump won't resume processing until it completes, with sustained read and write load that can affect other services sharing the database too. Only enable `migrate_sharded_tables` when you actually need it, such as the first restart after an upgrade that changed the schema, then turn it back off.

### TLS

TLS encrypts the connection between Tyk and your database, and can optionally authenticate Tyk to the database using a client certificate (mutual TLS) rather than just a password. Many managed database providers require it, or a compliance policy may.

TLS is configured by adding extra parameters to the [connection string](#connection-string). These parameters depend on the SQL engine:

| Purpose | PostgreSQL | MySQL / MariaDB |
| - | - | - |
| Enable/require encryption | `sslmode` (`disable`, `require`, `verify-ca`, or `verify-full`) | `tls` (`true`, `false`, `skip-verify`, or `preferred`) |
| Certificate Authority file | `sslrootcert` | not supported |
| Client certificate and key (mTLS) | `sslcert` / `sslkey` | not supported |

**PostgreSQL**:

```text theme={null}
host=tyk-db port=5432 user=admin password=secr3t dbname=tyk-demo-db sslmode=verify-full sslrootcert=/path/to/ca.crt
```

**MySQL / MariaDB**:

```text theme={null}
admin:secr3t@tcp(tyk-db:3306)/tyk-demo-db?tls=true
```

For MySQL and MariaDB, `tls=true` encrypts the connection and verifies the server's certificate against your system's trusted root CAs - this covers the common case of a certificate signed by a public CA, as most managed cloud database providers use. For a private or self-signed certificate, `tls=skip-verify` encrypts the connection without verifying the server's identity; limit this to development or temporary testing. Avoid `tls=preferred` in production: it can silently fall back to an unencrypted connection if TLS negotiation fails.

## Connection Pool Management

Each component keeps one or more [pools of connections](https://tyk.io/docs/api-management/persistent-database.md#connection-pool-management) to the database. You can limit the size of each pool and how long its connections stay open. The table shows how many pools each component opens, and the default value of each setting:

| Component | Number of Pools | Max Open Connections | Max Idle Connections | Max Connection Lifetime | Max Connection Idle Time |
| - | - | - | - | - | - |
| Tyk Dashboard | 8 | Unlimited | 2 | Unlimited | Unlimited |
| Tyk MDCB | 1 to 7, depending on configuration | Unlimited | 2 | Unlimited | Unlimited |
| Tyk Pump | One per configured SQL pump | Unlimited | 2 | Unlimited | Unlimited |
| Tyk Developer Portal | 1 | Unlimited | 2 | 30 minutes | Unlimited (not configurable) |

You can change these settings in:

* Tyk Dashboard 5.15.0 and later
* Tyk MDCB 2.14.0 and later
* Tyk Pump 1.18.0 and later
* Tyk Developer Portal, except the idle time

The sections below show how to configure each component.

**Sizing Guidance**

Size `max_open_connections` against the maximum number of connections that your database allows. Divide that maximum by the number of instances of each component that share the database, and by the number of pools that each instance opens (see the table). The result is a safe per-pool limit that keeps every instance within the database limit.

### Tyk Dashboard

Tyk Dashboard's pools are configured per category, under `storage.<category>.postgres` (or the equivalent environment variables):

```json expandable theme={null}
{
  "storage": {
    "main": {
      "postgres": {
        "max_open_connections": 20,
        "max_idle_connections": 5,
        "connection_max_lifetime": "30m",
        "connection_max_idle_time": "5m"
      }
    }
  }
}
```

| Field | Description |
| - | - |
| `max_open_connections` | The maximum number of open connections in the pool. Defaults to unlimited. |
| `max_idle_connections` | The maximum number of idle connections kept in the pool. Defaults to 2. |
| `connection_max_lifetime` | How long a connection can be reused before being closed. Accepts a Go duration string, such as `30s`, `5m`, or `1h`. Defaults to unlimited. |
| `connection_max_idle_time` | How long a connection can sit idle before being closed. Accepts a Go duration string. Defaults to unlimited. This is the setting to reach for if a Dashboard deployment needs to release database connections during extended idle periods, for example the inactive side of a blue/green deployment. |

> [!NOTE]
>
> These settings apply **per connection pool**, and Tyk Dashboard maintains [eight of them](https://tyk.io/docs/api-management/persistent-database.md#connection-pool-management) - one read and one write pool for each of its four categories of data. Setting `max_open_connections` to `10` for `main` permits up to 20 open connections for that category alone (10 for reads, 10 for writes), not 10 across the whole Dashboard.

### Tyk MDCB

Tyk MDCB's pools are configured in the `analytics.postgres` block of `tyk_sink.conf` (or the equivalent environment variables):

```json expandable theme={null}
{
  "analytics": {
    "type": "postgres",
    "connection_string": "user=root password=admin dbname=tyk-demo-db host=tyk-db port=5432",
    "postgres": {
      "max_open_connections": 10,
      "max_idle_connections": 2,
      "connection_max_lifetime": "30m",
      "connection_max_idle_time": "5m"
    }
  }
}
```

The fields have the same meaning as for [Tyk Dashboard](#tyk-dashboard). The environment variables are:

* `TYK_MDCB_ANALYTICSCONFIG_POSTGRES_MAXOPENCONNECTIONS`
* `TYK_MDCB_ANALYTICSCONFIG_POSTGRES_MAXIDLECONNECTIONS`
* `TYK_MDCB_ANALYTICSCONFIG_POSTGRES_CONNECTIONMAXLIFETIME`
* `TYK_MDCB_ANALYTICSCONFIG_POSTGRES_CONNECTIONMAXIDLETIME`

Tyk MDCB applies the same settings to each of its pools. The number of pools depends on how Tyk MDCB handles analytics:

| Pools | Purpose | Opened When |
| - | - | - |
| 1 | [Main Storage](https://tyk.io/docs/api-management/persistent-database.md#main-storage) | Always |
| 3 | [Traffic Logs](https://tyk.io/docs/api-management/persistent-database.md#traffic-logs): standard, GraphQL and MCP | `forward_analytics_to_pump` is `false` |
| 3 | [Aggregate Analytics](https://tyk.io/docs/api-management/persistent-database.md#aggregate-analytics): standard, GraphQL and MCP | `forward_analytics_to_pump` and `dont_store_aggregate` are both `false` |

> [!NOTE]
>
> These settings apply **per connection pool**. With the default configuration, Tyk MDCB opens seven pools, so it can open up to seven times `max_open_connections` connections. When you set `max_open_connections`, Tyk MDCB logs the number of pools and the total number of connections at startup.

Tyk MDCB doesn't start if `max_open_connections` or `max_idle_connections` is negative, or if `connection_max_lifetime` or `connection_max_idle_time` is not a valid, non-negative duration.

### Tyk Pump

Tyk Pump's pools are configured per pump. Set them in the `meta.postgres` block of each SQL pump in `pump.conf`, and in `uptime_pump_config.postgres` for the uptime pump (or the equivalent environment variables):

```json expandable theme={null}
{
  "pumps": {
    "sql": {
      "type": "sql",
      "meta": {
        "type": "postgres",
        "connection_string": "user=root password=admin dbname=tyk-demo-db host=tyk-db port=5432",
        "postgres": {
          "max_open_connections": 10,
          "max_idle_connections": 2,
          "connection_max_lifetime": "30m",
          "connection_max_idle_time": "5m"
        }
      }
    }
  }
}
```

The fields have the same meaning as for [Tyk Dashboard](#tyk-dashboard). They apply only when the `type` in `meta` is `postgres`.

The environment variables follow the same pattern as the other settings of each pump. For example:

| Pump | Example Environment Variable |
| - | - |
| `sql` | `TYK_PMP_PUMPS_SQL_META_POSTGRES_MAXOPENCONNECTIONS` |
| `sql_aggregate` | `TYK_PMP_PUMPS_SQLAGGREGATE_META_POSTGRES_MAXOPENCONNECTIONS` |
| Uptime pump | `TYK_PMP_UPTIMEPUMPCONFIG_POSTGRES_MAXOPENCONNECTIONS` |

Tyk Pump opens one pool for each SQL pump that you configure, including the uptime pump, and limits each pool with the settings of its pump.

> [!NOTE]
>
> These settings apply **per connection pool**. For example, if you configure both the `sql` and `sql_aggregate` pumps with `max_open_connections` set to `10`, Tyk Pump can open up to 20 connections.

If `max_open_connections` or `max_idle_connections` is negative, or if `connection_max_lifetime` or `connection_max_idle_time` is not a valid, non-negative duration, Tyk Pump doesn't start that pump, and logs the error. Its other pumps still start. If `max_idle_connections` is greater than `max_open_connections`, Tyk Pump logs a warning and uses `max_open_connections` as the limit.

### Tyk Developer Portal

Tyk Developer Portal's pool is controlled in the `Database` block (or via environment variables):

```json expandable theme={null}
{
  "Database": {
    "MaxOpenConnections": 30,
    "MaxIdleConnections": 15,
    "ConnectionMaxLifetime": 1800000
  }
}
```

| Field | Description |
| - | - |
| [`MaxOpenConnections`](https://tyk.io/docs/product-stack/tyk-enterprise-developer-portal/deploy/configuration#portal_database_max_open_connections) | The maximum number of open connections in the pool. Defaults to unlimited. |
| [`MaxIdleConnections`](https://tyk.io/docs/product-stack/tyk-enterprise-developer-portal/deploy/configuration#portal_database_max_idle_connections) | The maximum number of idle connections kept in the pool. Defaults to 2. |
| [`ConnectionMaxLifetime`](https://tyk.io/docs/product-stack/tyk-enterprise-developer-portal/deploy/configuration#portal_database_connection_max_lifetime) | How long a connection can be reused before being closed, in milliseconds (not a duration string). Defaults to `1800000` (30 minutes). Set to `0` for unlimited. |

For example, with a database allowing 60 connections across two Developer Portal instances: `MaxOpenConnections` of 30 and `MaxIdleConnections` of 15 per instance can handle around 90 active users, based on Tyk's own performance testing.

> [!NOTE]
>
> Tyk Developer Portal does not currently have an equivalent to Tyk Dashboard's `connection_max_idle_time`.

## Configuration Examples

### Tyk Dashboard

Tyk Dashboard uses a `storage.<category>` block per category of data, in `tyk_analytics.conf` (or via environment variables). `<category>` is one of `main`, `analytics`, `logs`, or `uptime` - each of the [four categories of data](https://tyk.io/docs/api-management/persistent-database.md#how-tyk-uses-persistent-storage) is configured independently, though all four normally point at the same database instance.

> [!NOTE]
>
> Tyk Dashboard uses the `main` category's configuration when no corresponding configuration is available for `logs`, `uptime` or `analytics`.

In this example, Traffic Logs use a separate, sharded database from the other three categories, and Main Storage splits reads and writes across two connection strings, pointing writes at the primary and reads at a read replica:

```json expandable theme={null}
{
  "storage": {
    "main": {
      "type": "postgres",
      "write_connection_string": "user=root password=admin dbname=tyk-primary-db host=tyk-db port=5432",
      "read_connection_string": "user=root password=admin dbname=tyk-primary-db host=tyk-db-replica port=5432"
    },
    "logs": {
      "type": "postgres",
      "connection_string": "user=root password=admin dbname=tyk-logs-db host=tyk-db port=5432",
      "table_sharding": true
    }
  }
}
```

| Field | Description |
| - | - |
| `type` | `postgres` or `mysql`. MySQL is accepted but not recommended and untested for Tyk Dashboard - see [Tested Versions](#tested-versions). |
| `connection_string` | The database [connection string](#connection-string). See [TLS](#tls) for encryption parameters. |
| `read_connection_string` / `write_connection_string` | Split reads and writes across two connection strings instead of one - see [Connection String](#connection-string). |
| `table_sharding` | Store records in per-day tables - see [Table Sharding](#table-sharding). |

### Tyk MDCB

Tyk MDCB uses a top-level `analytics` block, in `tyk_sink.conf` (or via environment variables):

```json expandable theme={null}
{
  "analytics": {
    "type": "postgres",
    "connection_string": "user=root password=admin dbname=tyk-demo-db host=tyk-db port=5432"
  }
}
```

| Field | Description |
| - | - |
| `type` | `mongo` or `postgres`. MySQL is not recognized - if set, Tyk MDCB silently falls back to `mongo` rather than failing. |
| `connection_string` | The database [connection string](#connection-string). See [TLS](#tls) for encryption parameters. |
| `table_sharding` | Store records in per-day tables - see [Table Sharding](#table-sharding). |
| `batch_size` | Maximum records written per batch. |

### Tyk Pump

Tyk Pump connects to SQL through its [SQL pump types](https://tyk.io/docs/api-management/dashboard-analytics/control-plane-pumps.md#choosing-a-pump-type), each declared in `pump.conf` (or using equivalent environment variables).

For full details of the SQL pump types and their configuration see [Control Plane Pumps](https://tyk.io/docs/api-management/dashboard-analytics/control-plane-pumps.md#sql).

### Tyk Developer Portal

Tyk Developer Portal uses a `Database` block, in `portal.conf` (or via environment variables):

```json expandable theme={null}
{
  "Database": {
    "Dialect": "postgres",
    "ConnectionString": "host=tyk-db port=5432 user=admin password=secr3t dbname=tyk-demo-db"
  }
}
```

| Field | Description |
| - | - |
| [`Dialect`](https://tyk.io/docs/product-stack/tyk-enterprise-developer-portal/deploy/configuration#portal_database_dialect) | `postgres`, `mysql`, or `mariadb`. |
| [`ConnectionString`](https://tyk.io/docs/product-stack/tyk-enterprise-developer-portal/deploy/configuration#portal_database_connectionstring) | The database [connection string](#connection-string). See [TLS](#tls) for encryption parameters. |

## Migrating Tyk Dashboard from MongoDB to SQL

Tyk Dashboard provides a migration command to move data from an existing MongoDB instance to a SQL platform. This migrates all data from the `main` category (APIs, Policies, Users, UserGroups, Webhooks, Certificates, Portal Settings, Portal Catalogs, Portal Pages, Portal CSS, etc.).

> [!NOTE]
>
> The migration tool will not migrate any Traffic Logs, Aggregate Analytics, or Uptime Test Results data.

1. Make sure your new SQL platform and the existing MongoDB instance are both running.
2. Configure the `main` part of the `storage` section of your `tyk-analytics.conf`:

```json expandable highlight={7} theme={null}
{
...
  "storage": {
    ...
    "main": {
      "type": "postgres",
      "connection_string": "user=root password=admin dbname=tyk-demo-db host=tyk-db port=5432"
    }
  }
}
```

3. Run the following command:

```console theme={null}
./tyk-analytics migrate-sql
```

You will see an output listing the transfer of each database table. For example: `Migrating 'tyk_apis' collection. Records found: 7`.

4. You can now remove your MongoDB configuration from `tyk-analytics.conf`.
5. Restart your Tyk Dashboard.