# How to Migrate from DuckDB to PostgreSQL in a DAT Project
> Easily migrate from DuckDB to PostgreSQL in your DAT project. Learn how to add the postgresql adapter update your config and migrate your data with simple steps.
- Repository: [Junjie.M/dat](https://github.com/junjiem/dat)
- Tags: migration-guide
- Published: 2026-03-05
---
**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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/pom.xml):
```xml
cn.hexinfo
dat-adapter-postgresql
```
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`](https://github.com/junjiem/dat/blob/main/dat_project.yaml). Change the `db.provider` field from `duckdb` to `postgresql` and supply JDBC connection parameters:
```yaml
db:
provider: postgresql
configuration:
url: jdbc:postgresql://:/
username:
password:
timeout: 60 s # optional, default 60 seconds
```
The `ProjectUtil.adjustDatabaseConfig` method in [`dat-sdk/src/main/java/ai/dat/boot/utils/ProjectUtil.java`](https://github.com/junjiem/dat/blob/main/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:
```bash
duckdb .dat/duckdb "EXPORT DATABASE '' (FORMAT CSV);"
```
This command generates CSV files for each table along with a [`load.sql`](https://github.com/junjiem/dat/blob/main/load.sql) script.
### Importing Data into PostgreSQL
Load the exported CSV files into PostgreSQL using `psql` or any ETL tool:
```bash
psql -h -U -d -c "\copy FROM '/.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:
```bash
dat seed -p
```
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:
```bash
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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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.