Configure SQL Storage

Several Tyk components support 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.
Tyk Developer Portal maintains its own, separate database - see Install Developer Portal for how that fits into a Portal deployment.

Not all components support the same engines:

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

Supported Versions

Core Platform

Tyk Version	PostgreSQL Version
From 5.X onwards	PSQL 14.x, 15.x, 16.x, 17.x

You can also use the following as a drop in replacement for PostgreSQL:

Sqlite 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	Version
PostgreSQL	14.x, 15.x, 16.x, 17.x
MySQL	4 or later
MariaDB	10.6, 10.11, 11.4, 11.8

You can also use the following as a drop in replacement for PostgreSQL:

Configuration Reference

Each component configures SQL storage differently - see 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:

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:

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, 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.

The writers and readers of a data category 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.

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. 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:

host=tyk-db port=5432 user=admin password=secr3t dbname=tyk-demo-db sslmode=verify-full sslrootcert=/path/to/ca.crt

MySQL / MariaDB:

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 maintains one or more pools of connections to the database:

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

Sizing Guidance For Tyk Dashboard and Tyk Developer Portal, the two components with configurable pools, size max_open_connections against your database’s own maximum connection limit: divide that limit by the number of instances you’re running, and by however many pools each instance opens (one for Tyk Developer Portal, eight for Tyk Dashboard), to find a safe per-pool ceiling that keeps every instance’s connections within the database’s own limit.

Tyk Dashboard

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

{
  "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.

These settings apply per connection pool, and Tyk Dashboard maintains eight of them - 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 Developer Portal

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

{
  "Database": {
    "MaxOpenConnections": 30,
    "MaxIdleConnections": 15,
    "ConnectionMaxLifetime": 1800000
  }
}

Field	Description
`MaxOpenConnections`	The maximum number of open connections in the pool. Defaults to unlimited.
`MaxIdleConnections`	The maximum number of idle connections kept in the pool. Defaults to 2.
`ConnectionMaxLifetime`	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.

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 is configured independently, though all four normally point at the same database instance.

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:

{
  "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 Supported Versions.
`connection_string`	The database connection string. See TLS for encryption parameters.
`read_connection_string` / `write_connection_string`	Split reads and writes across two connection strings instead of one - see Connection String.
`table_sharding`	Store records in per-day tables - see Table Sharding.

Tyk MDCB

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

{
  "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. See TLS for encryption parameters.
`table_sharding`	Store records in per-day tables - see Table Sharding.
`batch_size`	Maximum records written per batch.

Tyk Pump

Tyk Pump connects to SQL through its SQL pump types, 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.

Tyk Developer Portal

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

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

Field	Description
`Dialect`	`postgres`, `mysql`, or `mariadb`.
`ConnectionString`	The database connection string. See 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.).

The migration tool will not migrate any Traffic Logs, Aggregate Analytics, or Uptime Test Results data.

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

{
...
  "storage": {
    ...
    "main": {
      "type": "postgres",
      "connection_string": "user=root password=admin dbname=tyk-demo-db host=tyk-db port=5432"
    }
  }
}

Run the following command:

./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.

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

Getting Started

Deploy Tyk

Design & Create APIs

Secure APIs

Control Traffic

Routing Traffic

Transform Traffic

Observe APIs

Maintain APIs

Use Cases

Guides

Reference

Developer Support

Supported Versions

Core Platform

Developer Portal

Configuration Reference

Connection String

Table Sharding

Maintaining Consistency

TLS

Connection Pool Management

Tyk Dashboard

Tyk Developer Portal

Configuration Examples

Tyk Dashboard

Tyk MDCB

Tyk Pump

Tyk Developer Portal

Migrating Tyk Dashboard from MongoDB to SQL

​Supported Versions

​Core Platform

​Developer Portal

​Configuration Reference

​Connection String

​Table Sharding

​Maintaining Consistency

​TLS

​Connection Pool Management

​Tyk Dashboard

​Tyk Developer Portal

​Configuration Examples

​Tyk Dashboard

​Tyk MDCB

​Tyk Pump

​Tyk Developer Portal

​Migrating Tyk Dashboard from MongoDB to SQL

Supported Versions

Core Platform

Developer Portal

Configuration Reference

Connection String

Table Sharding

Maintaining Consistency

TLS

Connection Pool Management

Tyk Dashboard

Tyk Developer Portal

Configuration Examples

Tyk Dashboard

Tyk MDCB

Tyk Pump

Tyk Developer Portal

Migrating Tyk Dashboard from MongoDB to SQL