Skip to content

MySQL

MySQL is one of the world's most popular open-source relational database management systems.

The MySQL Wrapper allows you to read and write data from MySQL within your Postgres database.

Preparation

Before you can query MySQL, you need to enable the Wrappers extension and store your credentials in Postgres.

Enable Wrappers

Make sure the wrappers extension is installed on your database:

1
create extension if not exists wrappers with schema extensions;

Enable the MySQL Wrapper

Enable the mysql_wrapper FDW:

1
2
3
create foreign data wrapper mysql_wrapper
  handler mysql_fdw_handler
  validator mysql_fdw_validator;

Store your credentials (optional)

By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.

1
2
3
4
5
6
-- Save your MySQL connection string in Vault and retrieve the created `key_id`
select vault.create_secret(
  'mysql://user:password@host:3306/mydb',
  'mysql',
  'MySQL connection string for Wrappers'
);

Connecting to MySQL

We need to provide Postgres with the credentials to connect to MySQL, and any additional options. We can do this using the create server command:

1
2
3
4
5
create server mysql_server
  foreign data wrapper mysql_wrapper
  options (
    conn_string_id '<key_ID>' -- The Key ID from above.
  );

1
2
3
4
5
create server mysql_server
  foreign data wrapper mysql_wrapper
  options (
    conn_string 'mysql://user:password@host:3306/mydb'
  );

The connection string follows the standard MySQL URL format:

1
mysql://[user[:password]@][host][:port]/database

Some connection string examples:

  • mysql://root:secret@localhost:3306/mydb
  • mysql://app_user:password@db.example.com:3306/production
  • mysql://user:password@127.0.0.1:3306/testdb?ssl-mode=required

Create a schema

We recommend creating a schema to hold all the foreign tables:

1
create schema if not exists mysql;

Options

The following options are available when creating MySQL foreign tables:

  • table - Source table name in MySQL, required

    This can also be a subquery enclosed in parentheses, for example,

    1
    table '(select id, name from my_table where active = 1)'

  • rowid_column - Primary key column name, optional for data scan, required for data modify

Entities

Tables

The MySQL Wrapper supports data reads and writes from MySQL tables.

Operations

Object Select Insert Update Delete Truncate
Tables ✅ ✅ ✅ ✅ ❌

Usage

1
2
3
4
5
6
7
8
create foreign table mysql.my_table (
  id bigint,
  name text
)
  server mysql_server
  options (
    table 'people'
  );

Notes

  • Supports where, order by, limit and aggregate clause pushdown
  • Data is streamed row-by-row from MySQL, making it suitable for large result sets
  • When using rowid_column, it must be specified for data modification operations

Query Pushdown Support

This FDW supports where, order by and limit clause pushdown.

Aggregate Pushdown

The FDW pushes common aggregate queries down to MySQL so the aggregation runs remotely and only the final result rows are transferred to Postgres. This is much faster than fetching every row and aggregating locally, especially over large tables.

Supported aggregates — count(*), count(col), count(distinct col), sum(col), avg(col), min(col), max(col).

Supported shapes — scalar aggregates, group by over plain columns, with or without a where clause. Pushdown also works when the foreign table option is a sub-query.

1
2
3
4
-- All of these run as a single aggregate query on MySQL:
select count(*) from mysql.my_table;
select status, sum(amount) from mysql.my_table group by status;
select count(distinct name) from mysql.my_table where id = 1;

Cases that are not pushed down — the query still returns the correct result, but the aggregation happens in Postgres after fetching the rows:

  • The query has a having clause
  • The aggregate has a filter (where …) clause
  • A distinct modifier is used on anything other than count
  • The aggregate's argument is not a plain column (for example sum(a + 1))
  • A group by item is not a plain column (for example group by id + 1)
  • The aggregate function is not in the list above (for example stddev, group_concat)

Import Foreign Schema

This FDW supports import foreign schema to automatically create foreign table definitions by reading the MySQL table structure from information_schema.

The remote_schema maps to a MySQL database name. For example, to import all tables from the MySQL database mydb:

1
2
3
import foreign schema mydb
  from server mysql_server
  into mysql;

You can limit which tables are imported using limit to or except:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Import only specific tables
import foreign schema mydb
  limit to (users, orders)
  from server mysql_server
  into mysql;

-- Import all tables except specific ones
import foreign schema mydb
  except (tmp_data, archive)
  from server mysql_server
  into mysql;

An additional option is available for import foreign schema:

  • strict - If set to true, the import will fail if any MySQL column type cannot be mapped to a Postgres type. Defaults to false (unsupported column types are silently skipped).
1
2
3
4
import foreign schema mydb
  from server mysql_server
  into mysql
  options (strict 'true');

Primary key columns are automatically detected and set as the rowid_column option on the generated foreign table, enabling INSERT, UPDATE, and DELETE operations without any manual configuration.

Supported Data Types

Postgres Type MySQL Type
boolean boolean, bool, tinyint(1)
smallint tinyint (non-boolean), smallint, year
integer mediumint, int, integer
bigint bigint
real float
double precision double, double precision
numeric decimal, numeric
text char, varchar, tinytext, text, mediumtext, longtext, enum, set
date date
timestamp datetime, timestamp
time time
jsonb json

Note

tinyint(1) is mapped to boolean as it is the conventional MySQL representation for boolean values. All other tinyint variants are mapped to smallint.

decimal and numeric columns with explicit precision and scale (e.g. decimal(12,2)) are imported as numeric(p,s) in Postgres.

Limitations

This section describes important limitations and considerations when using this FDW:

  • Only a subset of MySQL data types are supported; columns with unmapped types are skipped during import foreign schema unless strict mode is enabled
  • timestamp and datetime values are treated as naive timestamps without timezone conversion
  • MySQL json columns are mapped to Postgres jsonb; invalid JSON will cause an error during reads
  • Materialized views using foreign tables may fail during logical backups

Examples

Basic example

This example shows how to query a MySQL table from Postgres.

First, create the source table in MySQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Run on MySQL
create table people (
  id bigint primary key auto_increment,
  name varchar(100),
  email varchar(200)
);

insert into people (name, email) values
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

Then create the foreign table in Postgres and query it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create foreign table mysql.people (
  id bigint,
  name text,
  email text
)
  server mysql_server
  options (
    table 'people'
  );

select * from mysql.people;

Data modification example

This example demonstrates INSERT, UPDATE, and DELETE on a foreign table. The rowid_column option is required for data modification:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create foreign table mysql.people (
  id bigint,
  name text,
  email text
)
  server mysql_server
  options (
    table 'people',
    rowid_column 'id'
  );

-- Insert a new row
insert into mysql.people (name, email)
values ('Dave', 'dave@example.com');

-- Update an existing row
update mysql.people
set email = 'alice_new@example.com'
where id = 1;

-- Delete a row
delete from mysql.people
where id = 2;

Aggregate Query Examples

These examples assume an orders table in MySQL and a matching foreign table on Postgres:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Run on MySQL
create table orders (
  id      bigint primary key auto_increment,
  user_id bigint not null,
  amount  decimal(12,2) not null,
  status  varchar(50) not null
);

insert into orders (user_id, amount, status) values
  (1, 100.00, 'paid'),
  (1,  50.00, 'paid'),
  (2, 200.00, 'pending'),
  (2,  75.00, 'paid'),
  (3, 300.00, 'paid');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Foreign table on Postgres
create foreign table mysql.orders (
  id      bigint,
  user_id bigint,
  amount  numeric,
  status  text
)
  server mysql_server
  options (
    table 'orders'
  );

Each query below runs a single aggregate query against MySQL and returns just the result rows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Total order count
select count(*) from mysql.orders;

-- Total revenue from paid orders
select sum(amount) from mysql.orders where status = 'paid';

-- Per-user order count and revenue
select user_id, count(*) as orders, sum(amount) as revenue
from mysql.orders
group by user_id
order by user_id;

-- Smallest and largest order
select min(amount), max(amount) from mysql.orders;

-- Number of distinct users who placed an order
select count(distinct user_id) from mysql.orders;

-- Average order value per status
select status, avg(amount) as avg_amount
from mysql.orders
group by status;

Import foreign schema example

This example imports all tables from a MySQL database automatically:

1
2
3
4
5
6
7
8
-- Import all tables from the MySQL 'shop' database
import foreign schema shop
  from server mysql_server
  into mysql;

-- The foreign tables are now available
select * from mysql.products limit 10;
select * from mysql.orders where status = 'pending';