Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

Start querying DuckDB

This walkthrough uses the source-installed DuckDB plugin in ProxySQL 4.0.12. Start with a running installation from the installation guide, plus the mysql and psql command-line clients. The examples use the default Admin port, 6032, and the Linux service account proxysql. On Ubuntu 24.04, install the clients with:

sudo apt-get install default-mysql-client postgresql-client

Choose where to keep the data

The default DuckDB database is in memory. It disappears when ProxySQL stops. For this walkthrough, choose a file before creating the example table, so the data can survive a restart.

On the ProxySQL host, prepare a directory the service can write to:

sudo install -d -o proxysql -g proxysql -m 0750 /var/lib/proxysql/duckdb

Connect to ProxySQL Admin, using the password configured during installation:

mysql -h 127.0.0.1 -P 6032 -u admin -p

Set the database path and keep both DuckDB listeners local to this host:

UPDATE global_variables
SET variable_value='/var/lib/proxysql/duckdb/analytics.db'
WHERE variable_name='duckdb-database_path';

UPDATE global_variables
SET variable_value='127.0.0.1:6031'
WHERE variable_name='duckdb-mysql_ifaces';

UPDATE global_variables
SET variable_value='127.0.0.1:6034'
WHERE variable_name='duckdb-pgsql_ifaces';

SAVE DUCKDB VARIABLES TO DISK;

These changes require a restart. Exit the Admin client and restart from the shell:

sudo systemctl restart proxysql

Changing a database path does not copy existing tables. If you have already loaded data into the in-memory database, preserve it before stopping ProxySQL. For a remote client, choose suitable bindings and configure TLS and network access before sharing the endpoint.

Add endpoint users

Reconnect to Admin on port 6032. The two protocols use separate user tables. Create a dedicated account in each table, replacing both password placeholders with strong passwords. Use an unused username if duckuser already exists.

INSERT INTO mysql_users
    (username, password, active, frontend, backend, default_hostgroup)
VALUES
    ('duckuser', 'REPLACE_WITH_MYSQL_DUCKDB_PASSWORD', 1, 1, 0, 0);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

INSERT INTO pgsql_users
    (username, password, active, frontend, backend, default_hostgroup)
VALUES
    ('duckuser', 'REPLACE_WITH_PGSQL_DUCKDB_PASSWORD', 1, 1, 0, 0);

LOAD PGSQL USERS TO RUNTIME;
SAVE PGSQL USERS TO DISK;

These are frontend accounts: the plugin authenticates the clients and executes queries in DuckDB without connecting to a backend server. No backend server or hostgroup setup is needed for these endpoints.

All admitted users share the embedded database. These accounts do not create separate table permissions. Use a PostgreSQL client with SCRAM support for the default authentication policy.

Through Admin, inspect the active database path and bindings:

SELECT variable_name, variable_value
FROM runtime_global_variables
WHERE variable_name IN
    ('duckdb-database_path', 'duckdb-mysql_ifaces', 'duckdb-pgsql_ifaces');

Check that these values show the saved file path and local listeners before continuing.

Query through MySQL

From the shell, connect to the DuckDB MySQL endpoint, port 6031:

mysql -h 127.0.0.1 -P 6031 -u duckuser -p

Run each statement separately. Use an unused table name if quickstart_sales already exists:

CREATE TABLE quickstart_sales (
    region VARCHAR,
    amount DECIMAL(12,2)
);
INSERT INTO quickstart_sales VALUES
    ('north', 125.50),
    ('south', 200.00),
    ('north', 74.50);
SELECT region, SUM(amount) AS total
FROM quickstart_sales
GROUP BY region
ORDER BY region;

The totals are 200.00 for north and 200.00 for south. Both protocol endpoints accept DuckDB SQL and describe result columns as text.

Read the same table through PostgreSQL

From another shell, connect to port 6034 using the password from pgsql_users:

psql -h 127.0.0.1 -p 6034 -U duckuser main

Read the table created through MySQL:

SELECT region, amount
FROM quickstart_sales
ORDER BY region, amount;

Both clients use the same DuckDB database, with separate transactions and session settings. You do not need to copy the table between endpoints.

Because this walkthrough selected a file-backed database, its tables can be reopened after a clean restart. Finish active work before restarting the service; a restart also affects ordinary ProxySQL traffic.

Next steps