JDBC

DB-API (Python) · JDBC · ODBC

dev.potluckdb:potluckdb-jdbc implements JDBC 4.3 over the JVM package. DriverManager finds it through the service loader. Statements read and write the synchronized database. DatabaseMetaData answers catalog questions from SQLite’s schema.

The JVM installation page describes installation of the runtime that the driver loads.

Requirements

JDK 17 or newer on macOS, Linux (glibc) or Windows. The driver is not built for Android.

What ships

The Java column of the compatibility matrix, reached through JDBC. Each release runs the program on the platforms in this table. Other platforms have no verification for this interface.

Platform	Targets	Package
macOS	`macos-arm64`, `macos-x86_64`	`dev.potluckdb:potluckdb-jdbc`
Linux (glibc)	`linux-x86_64-gnu`, `linux-arm64-gnu`	`dev.potluckdb:potluckdb-jdbc`
Windows	`windows-x86_64`, `windows-arm64`	`dev.potluckdb:potluckdb-jdbc`

Install

repositories {
    maven("https://downloads.potluckdb.com/maven")
}

dependencies {
    implementation("dev.potluckdb:potluckdb-jdbc:0.1.0")
}

The artifact depends on the JVM package, which contains the runtime for every desktop target. If your tool takes a driver jar, supply this jar and its dependency.

Connect

One connection string serves DB-API, JDBC and ODBC. potluckdb:<path> names the local database. Parameters after ? name the remote and options. Each parameter appears at most once, and no other parameters exist.

The string never contains secrets. Storage credentials come from the provider’s standard chain, as the installation pages describe. The encryption key comes from a file named in the string. Strings outside the grammar fail before anything opens.

The URL is jdbc: followed by the connection string. A Properties object may carry the same parameters instead. A parameter given in both is refused.

Connection connection = DriverManager.getConnection(
    "jdbc:potluckdb:app.potluckdb?remote=s3://my-bucket/notes&region=eu-west-1");

Parameter	Value	Sets
`remote`	`dir:<path>`, `s3://<bucket>/<namespace>`, `gs://<bucket>/<namespace>` or `az://<account>/<container>/<namespace>`	The remote. Without it, the database is local only.
`region`	a region name	The region of an `s3://` remote. A string that sets it without an `s3://` remote is refused.
`endpoint`	a URL	The endpoint of an S3-compatible storage service. A string that sets it without an `s3://` remote is refused.
`sync`	`automatic` or `manual`	The synchronization policy, with the factory’s defaults.
`prefetch`	`automatic` or `disabled`	The prefetch policy, with the factory’s defaults.
`checkpoint`	`automatic` or `disabled`	The checkpoint policy, with the factory’s defaults.
`key_file`	a path	A file holding the encryption key as hexadecimal text. The encryption key never appears in the string.
`license_key`	a license key	The `license_key` open option. If absent, the environment and configuration directory apply.
`analytical_engine`	a path	The `analytical_engine` open option.
`client_id`	an identifier	The `client_id` open option.
`local_budget_bytes`	an integer	The open option of that name.
`minimum_local_budget_bytes`	an integer	The open option of that name.
`local_compaction_bytes`	an integer	The open option of that name.
`timeout_ms`	an integer	The connection option of that name.
`maximum_rows`	an integer	The connection option of that name.

The options themselves are described on the options page.

Behavior

executeQuery reads and executeUpdate writes. execute distinguishes them before execution, as the SDK’s query and execute do. Auto-commit defaults to on, making each write a transaction. With auto-commit off, the first write starts a transaction, and commit or rollback ends it.

A statement failure can make SQLite roll back the whole transaction, such as a storage error or INSERT OR ROLLBACK. The error reports this. Every later statement, commit() and setAutoCommit(true) then fails with invalid_state until you call rollback().

Call rollback() to acknowledge the rollback.
Run the whole transaction again.

A failed first write needs no acknowledgement because no earlier statements were lost.

ResultSetMetaData contains column names. DatabaseMetaData answers getTables, getColumns and getPrimaryKeys from SQLite’s schema. Every other catalog method returns an empty result.

Use Connection.unwrap with dev.potluckdb.jdbc.PotluckDBConnection to access sync and resolveConflict. They return SDK values.

Temporary errors throw SQLTransientException. Other errors throw SQLNonTransientException. Both expose code, category and action through dev.potluckdb.jdbc.PotluckDBError. getSQLState returns HY000.

Quickstart

The program opens a database with a directory remote and a manual synchronization policy. It writes with auto-commit, reads the row back through the result set’s metadata and synchronizes once through the unwrapped connection. See Concepts for the remote model.

Compile with the driver and its JVM dependency on the class path.
Run the program.

It prints quickstart: ok.

// One database, one remote, through JDBC. The driver registers itself; the URL
// is jdbc: followed by the connection string every standard interface accepts.

import dev.potluckdb.SyncResult;
import dev.potluckdb.jdbc.PotluckDBConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class Quickstart {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:potluckdb:app.potluckdb?remote=dir:shared-remote&sync=manual";
        try (Connection connection = DriverManager.getConnection(url)) {
            // Auto-commit is on: each write is its own transaction.
            try (Statement statement = connection.createStatement()) {
                statement.executeUpdate("CREATE TABLE IF NOT EXISTS note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)");
            }
            try (PreparedStatement insert = connection.prepareStatement("INSERT INTO note(body) VALUES(?)")) {
                insert.setString(1, "buy milk");
                if (insert.executeUpdate() != 1) throw new IllegalStateException("one row is inserted");
            }

            // Reads return a result set whose metadata carries the column names.
            try (Statement statement = connection.createStatement();
                 ResultSet rows = statement.executeQuery("SELECT id, body FROM note ORDER BY id")) {
                if (!rows.getMetaData().getColumnName(2).equals("body")) throw new IllegalStateException("columns are named");
                String body = null;
                while (rows.next()) body = rows.getString("body");
                if (!"buy milk".equals(body)) throw new IllegalStateException("the row is read back");
            }

            // The SDK's own operations are one unwrap away: one synchronization, its outcome as a value.
            PotluckDBConnection potluckdb = connection.unwrap(PotluckDBConnection.class);
            SyncResult outcome = potluckdb.sync();
            if (!(outcome instanceof SyncResult.Pushed || outcome instanceof SyncResult.UpToDate)) throw new IllegalStateException("the push succeeds: " + outcome);
            System.out.println("quickstart: ok");
        }
    }
}

Verified by the release

Each release verifies this program through installed JVM and JDBC packages on macOS, Linux (glibc) and Windows. It checks connection strings, statements, transactions, metadata, and errors.