How to Migrate from DuckDB to PostgreSQL in a DAT Project

Migrating from DuckDB to PostgreSQL in a DAT project requires adding the dat-adapter-postgresql dependency, updating the db.provider value to postgresql in dat_project.yaml, and migrating your data via CSV export/import or re-seeding.

The DAT framework by junjiem provides flexible database support, allowing you to migrate from the embedded DuckDB engine to a production-grade PostgreSQL instance. When you migrate from DuckDB to PostgreSQL in an existing DAT project, you leverage external database scalability while maintaining the same natural-language query capabilities. This guide walks through the exact configuration changes, dependency updates, and data migration steps required.

Prerequisites and Configuration Changes

Before running your project against PostgreSQL, you must update your build configuration and project metadata to recognize the new database provider.

Adding the PostgreSQL Adapter Dependency

DAT uses adapter-specific factories to instantiate database connections. To enable PostgreSQL support, add the dat-adapter-postgresql artifact to your Maven pom.xml:

<dependency>
    <groupId>cn.hexinfo</groupId>
    <artifactId>dat-adapter-postgresql</artifactId>
</dependency>

This dependency provides the PostgreSqlDatabaseAdapterFactory class, which constructs a PGSimpleDataSource from your YAML configuration.

Updating dat_project.yaml Configuration

Database settings in DAT projects are centralized in dat_project.yaml. Change the db.provider field from duckdb to postgresql and supply JDBC connection parameters:

db:
  provider: postgresql
  configuration:
    url: jdbc:postgresql://<HOST>:<PORT>/<DATABASE>
    username: <YOUR_USERNAME>
    password: <YOUR_PASSWORD>
    timeout: 60 s            # optional, default 60 seconds

The ProjectUtil.adjustDatabaseConfig method in dat-sdk/src/main/java/ai/dat/boot/utils/ProjectUtil.java (lines 81-99) automatically creates the .dat/duckdb file only when the provider is duckdb. With postgresql specified, DAT bypasses local file creation and delegates connection management to the PostgreSqlDatabaseAdapterFactory.

Migrating Existing Data from DuckDB to PostgreSQL

After configuration changes, you must transfer existing data from the embedded DuckDB file to your PostgreSQL instance.

Exporting Data from DuckDB

If your project contains historical data in .dat/duckdb, use the DuckDB CLI to export tables as CSV files:

duckdb .dat/duckdb "EXPORT DATABASE '<EXPORT_DIR>' (FORMAT CSV);"

This command generates CSV files for each table along with a load.sql script.

Importing Data into PostgreSQL

Load the exported CSV files into PostgreSQL using psql or any ETL tool:

psql -h <HOST> -U <YOUR_USERNAME> -d <DATABASE> -c "\copy <TABLE> FROM '<EXPORT_DIR>/<TABLE>.csv' CSV HEADER;"

Repeat this for each table exported from DuckDB.

Alternative: Re-seeding Data with DAT

If your project uses seed files located in project_init_template/seeds/, you can skip manual export/import and let DAT repopulate the database:

dat seed -p <PROJECT_PATH>

This command reloads seed CSVs into the newly configured PostgreSQL database.

Running and Verifying the Migration

Once configuration and data migration are complete, validate that DAT correctly interfaces with PostgreSQL.

Executing the Project

Start the project using the standard DAT CLI command:

dat run -p ./my-dat-project -a default

The engine will now generate PostgreSQL-specific SQL via the PostgreSqlSemanticAdapter (implemented in dat-adapters/dat-adapter-postgresql/src/main/java/ai/dat/adapter/postgresql/PostgreSqlSemanticAdapter.java) and execute it against the configured PostgreSQL instance.

Validation Steps

Confirm successful migration by checking:

• Console output indicating successful PostgreSQL connection (Connecting to PostgreSQL…)
• Natural-language queries returning results without SQL dialect errors
• Data consistency between original DuckDB exports and PostgreSQL tables

Understanding the Technical Implementation

DAT's database abstraction layer delegates provider-specific logic to adapter factories and semantic adapters.

The ProjectUtil.adjustDatabaseConfig method conditionally initializes local storage only for DuckDB, while PostgreSqlDatabaseAdapterFactory constructs a PGSimpleDataSource from your YAML configuration. Query translation is handled by PostgreSqlSemanticAdapter, ensuring that generated SQL conforms to PostgreSQL dialect requirements.

Summary

• Add the PostgreSQL adapter dependency (dat-adapter-postgresql) to your build file to enable PostgreSQL connectivity.
• Update dat_project.yaml by changing db.provider to postgresql and supplying JDBC URL, username, and password.
• Migrate data by exporting DuckDB tables to CSV and importing them into PostgreSQL, or use dat seed to reload initial data.
• Verify the migration by running dat run and checking that queries execute against PostgreSQL via the PostgreSqlSemanticAdapter.

Frequently Asked Questions

Can I switch back to DuckDB after migrating to PostgreSQL?

Yes. Reverting requires changing db.provider back to duckdb in dat_project.yaml and ensuring the .dat/duckdb file exists. The ProjectUtil.adjustDatabaseConfig method will automatically recreate the local DuckDB file if it is missing when the provider is set to duckdb.

Does DAT support other databases besides PostgreSQL and DuckDB?

The architecture supports multiple adapters through the SPI (Service Provider Interface) pattern. While DuckDB is the default embedded engine and PostgreSQL is the primary external adapter referenced in the codebase, the adapter factory pattern in DatabaseConfig and the semantic adapter interface allow for additional database implementations.

How does DAT handle SQL dialect differences between DuckDB and PostgreSQL?

DAT uses provider-specific semantic adapters to handle dialect translation. The PostgreSqlSemanticAdapter implements the SPI to convert intermediate representations into PostgreSQL-compatible SQL, while DuckDB uses its own native adapter. This abstraction ensures that natural-language queries generate appropriate SQL syntax for the configured database engine.

Is there a performance impact when switching from DuckDB to PostgreSQL?

Performance characteristics differ based on workload. DuckDB operates as an embedded, in-process analytical engine with zero network overhead, while PostgreSQL introduces network latency and connection management. However, PostgreSQL provides superior concurrency, durability, and scalability for multi-user production environments. The PostgreSqlDatabaseAdapterFactory configures connection pooling via PGSimpleDataSource to mitigate overhead.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}