Connect from Java via Postgres endpoint

Preview

This feature is in preview and is subject to change.

You can query MotherDuck from Java using the standard PostgreSQL JDBC driver — no DuckDB installation required.

For connection parameters, SSL options, and limitations, see the Postgres Endpoint reference.

Prerequisites

You'll need a MotherDuck access token. Set it as an environment variable:

export MOTHERDUCK_TOKEN="your_token_here"

Add the PostgreSQL JDBC driver to your project:

Maven

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.11</version>
</dependency>

Gradle

implementation 'org.postgresql:postgresql:42.7.11'

Connect

import java.sql.*;

public class MotherDuckExample {
    public static void main(String[] args) throws SQLException {
        String token = System.getenv("MOTHERDUCK_TOKEN");
        String url = "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:"
                   + "?sslmode=verify-full"
                   + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory";

        try (Connection conn = DriverManager.getConnection(url, "postgres", token);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10")) {

            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(meta.getColumnName(i) + "=" + rs.getString(i));
                    if (i < columnCount) System.out.print(", ");
                }
                System.out.println();
            }
        }
    }
}

You can also configure the connection using a Properties object:

import java.sql.*;
import java.util.Properties;

Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", System.getenv("MOTHERDUCK_TOKEN"));
props.setProperty("sslmode", "verify-full");
props.setProperty("sslfactory", "org.postgresql.ssl.DefaultJavaSSLFactory");

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:",
    props
);

Connection pooling and timeouts

Use a JDBC connection pool in production. With HikariCP, set a connection timeout, idle timeout, maximum lifetime, and query timeout:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.3.3</version>
</dependency>

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

HikariConfig config = new HikariConfig();
config.setJdbcUrl(
    "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:"
    + "?sslmode=verify-full"
    + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory"
);
config.setUsername("postgres");
config.setPassword(System.getenv("MOTHERDUCK_TOKEN"));
config.setMaximumPoolSize(10);
config.setMinimumIdle(0);
config.setConnectionTimeout(5_000);
config.setIdleTimeout(30_000);
config.setMaxLifetime(300_000);
config.addDataSourceProperty("connectTimeout", "5");
config.addDataSourceProperty("cancelSignalTimeout", "5");

try (HikariDataSource dataSource = new HikariDataSource(config);
     Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement()) {

    stmt.setQueryTimeout(60);

    try (ResultSet rs = stmt.executeQuery(
        "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10"
    )) {
        while (rs.next()) {
            System.out.println(rs.getString("title"));
        }
    }
}

setConnectionTimeout(5_000) fails fast when a connection cannot be checked out. setIdleTimeout(30_000) and setMinimumIdle(0) let HikariCP close unused connections quickly, and setMaxLifetime(300_000) periodically replaces long-lived connections. HikariCP validates connections before reuse and removes broken connections from the pool.

statement_timeout is not supported through the Postgres endpoint today. Use JDBC Statement.setQueryTimeout(...) for client-side cancellation.

SSL notes

The PostgreSQL JDBC driver looks for a root certificate at ~/.postgresql/root.crt by default. To use your JVM's built-in trust store instead (which includes standard CAs like Let's Encrypt), set sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory.

If certificate verification doesn't work in your environment, you can fall back to sslmode=require, which encrypts the connection but doesn't verify the server certificate.

For more details on SSL options, see SSL and certificate verification.