Introduction

Database Exporter ¹ is a CLI that uses Connector-X to dump a database into a directory of Parquet files and a duckdb database.

See Usage for help getting started and Development for guidance on development.

A name is in the works

Usage

Overview

This tool requires a config file to describe the databases details and then uses cli options to describe the target format.

CLI

note

Exporting a parquet with 0 rows with --row-limit=0 will work fine if one only needs the schema

The CLI provides a --help which should be sufficiently clear, generally the recipe is:

# From Source
cargo run -- -c ~/.config/database_exporter/config.toml --row-limit=6 -e data/raw/

# From Binary
./database-export -c ~/.config/database_exporter/config.toml --row-limit=6 -e data/raw/

Config File

note

The config file is TOML due to it's excellent support in Rust and human-friendly syntax

Overview

The config file takes a list of database connections with a key, this key will become the directory ¹ for the parquets and the schema name in duckdb.

#TODO I think they're flat right now

For example here are the configurations for a SQL Server, Postgres and SQLite databases ²:

See also the Chinook Dataset which is handy for development.

["Local SQL Server Container"]
username = "sa"
password = "Some(!) G00d P4ssword?"
database = "chinook"
host = "localhost"
port = "1433"
database_type = "sqlserver"

["Local Postgres Container"]
username="postgres"
password="postgres"
database="chinook"
# do I have one here?
host="vidar"
port="5432"
database_type = "postgres"


["Joplin SQLite Database"]
database_type = "sqlite"
database = "/home/ryan/.config/joplin-desktop/database.sqlite"
username=""
password=""
host=""
port=""

Custom Row Limits Override

warning

There is not yet logic to change the sort order for the custom limit as it was not required for my use case (sufficiently cheap to pull the entire table

If one wants to Override the limit for certain tables, this can be specified in the toml file like so:

["Joplin SQLite Database".override_limits]
"resources" = 10  # Return first 10 rows
"tags" = -1       # Return all Rows

In this example the resources table will only return 10 rows, however, the "

Custom Queries

One can include custom queries like so:

\[["Joplin SQLite Database".custom_queries]\]
name = "00_test"
description = "A Test Query"
query = "SELECT id FROM notes"

\[["Joplin SQLite Database".custom_queries]\]
name = "01_test"
description = "A Test Query"
query = "SELECT body FROM notes"

This will result in two new parquet files: 00_test.parquet and 01_test.parquet. This can be useful where the user needs only the most recent data or only an inner join on data, for example the following will return the 10 most recent results:

note

Both queries will run, however custom queries run second and clobber any created file.

["Joplin SQLite Database".override_limits]
"resources" = 0   # Grab Nothing

\[["Joplin SQLite Database".custom_queries]\]
name = "resources"
description = "Get the 10 most recent resources"
query = "SELECT * FROM resources ORDER BY user_updated_time DESC LIMIT 10"

Parameters

Database Types

The available database_types are limited by the available connector-x sources, currently implemented is:

warning

MySQL has not been tested, pull requests welcome.

#![allow(unused)]
fn main() {
#[derive(Debug, Serialize, Deserialize, Clone)]
#[serde(rename_all = "lowercase")]
pub enum DatabaseType {
    SQLServer,
    Postgres,
    MySQL,
    SQLite,
}
}

So, for example, Postgres would correspond to database_type=postgres.

Examples

Data Fetching

This is the config that I'm using in our data analysis pipeline:

database_exporter.exe --row-limit=1000 -c config.toml

["SQL Server"]
username = "Ryan.greenup"
password = "xxxxxxxx"
database = "xxxxxxxx"
host = "xxxxxxxx"
port = "1433"
database_type = "sqlserver"

["SQL Server".override_limits]
# We want full timetable
"timetable" = 0

["SQL Server".custom_queries]("SQL Server".custom_queries.md)
name = "timetable"
description = "Full Timetable"
query = "SELECT * FROM timetable"


[postgres]
username = "xxxxxxxx"
password = "xxxxxxxx"
database = "xxxxxxxx"
host = "xxxxxxxx"
port = "5432"
database_type="postgres"

Development

Compiling

Generally

cargo run -- -c ~/.config/database_exporter/config_local2.toml --help

On all major operating systems, except Windows, there is support for automatically creating a database.duckdb:

cargo build --release --features "duckdb"

Windows

If compiling for windows using the GNU target, it's necessary to use an optimized build, it seems some issues are inlined away this way:

note

Database Exporter is developed in an Arch Based Docker container using Distrobox

warning

Windows must compile:

Without the "duckdb" feature, and
With --release optimizations

# Add the Windows Target
rustup target add x86_64-pc-windows-gnu

# Install the linker
sudo pacman -S extra/mingw-w64-gcc

# Compile
cargo build --target x86_64-pc-windows-gnu --no-default-features --release

If this does not build, consider trying:

rustup target add x86_64-pc-windows-msvc

Containers

It is recommended to use containers to test workflows on local containers before running against target data.

SQL Server Container

Components

Docker Compose

version: '3.8'

services:
  sql-server:
    image: mcr.microsoft.com/mssql/server
    container_name: sql-server-container
    environment:
      SA_PASSWORD: ${SA_PASSWORD}
      ACCEPT_EULA: Y
    ports:
      - "1433:1433"
    # volumes:
    #   - "./Chinook_SqlServer.sql:/docker-entrypoint-initdb.d/1.sql"

ENV

note

Microsoft software is highly interactive, which can be confusing inside containers, here is the Password requirements

Greater than 8 Characters
Upper Case
Lowercase
digits
Symbols

2025-01-29 04:01:41.14 spid54s     ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..
2025-01-29 04:01:41.15 spid54s     An error occurred during server setup. See previous errors for more information.

SA_PASSWORD=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# username is sa
# database is master
# https://medium.com/@seventechnologiescloud/local-sqlserver-database-via-docker-compose-the-ultimate-guide-f1d9f0ac1354

Get Data

curl \
    https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_SqlServer.sql \
    > Chinook_SqlServer.sql

Usage

# Start the container
docker compose down
docker compose up -d
docker compose logs -f

# Import the Data
sqlcmd \
    -H localhost \
    -P '238923klsdklsdklDSDSDS@!!@' \
    -U 'sa' \
    -C  \
    -i Chinook_SqlServer.sql

Postgres Container

For development I used the following postgres container:

version: '3.1'

services:

  db:
    image: postgres
    restart: unless-stopped
    environment:
      POSTGRES_PASSWORD: example
      POSTGRES_HOST_AUTH_METHOD: trust
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./data/pgdata:/var/lib/postgresql/data/pgdata
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8787:8080
  pgadmin:
      container_name: pgadmin4_container
      image: dpage/pgadmin4
      restart: always
      environment:
        PGADMIN_DEFAULT_EMAIL: admin@admin.com
        PGADMIN_DEFAULT_PASSWORD: root
      volumes:
        - ./data/pgadmin:/var/lib/pgadmin
      ports:
        - "5050:80"

Limitations

Windows Support

Duckdb is not supported on Windows ¹, as a result the feature is locked behind the "duckdb" feature flag.

I'm not sure why, PR's welcome, GDB returns the following (seems like a .dll issue):

gdb .\extract_to_sqlite_rs.exe
run
bt

During startup program exited with code `0xc0000135`

I have tried:

winget install DuckDB.cli
scoop install duckdb

However, it did not help

SQLite Types

I've had some issues with the NUMERIC type in SQLite, this causes Rusqlite to pass an error up to Connector-X like so:

#![allow(unused)]
fn main() {
called `Result::unwrap()` on an `Err` value:

  ArrowError(
      SQLiteArrowTransportError(
          Source(
              SQLiteError(
                  InvalidColumnType( 7, "latitude", Real)))))


}

To overcome this create a new table with the same data:

-- Step 1: Create a new table with the desired REAL type
CREATE TABLE new_notes_test (
    id TEXT PRIMARY KEY,
    latitude NUMERIC
);

-- Step 2: Copy data from the old table to the new table
INSERT INTO new_notes_test (id, value)
SELECT id, latitude
FROM notes;

From here one may:

Keep the New Table

-- Step 3: Drop the old table
DROP TABLE notes;

-- Step 4: Rename the new table to the original table name
ALTER TABLE new_notes_test
RENAME TO notes;

Keep the Old Table
```
DROP TABLE new_notes_test
```

Keep Both Tables

Keep second table with a different type to preserve all previous behaviour but allow exporting with this tool.

This requires a few triggers:

Insert Trigger

CREATE TRIGGER insert_trigger
AFTER INSERT ON ExampleTable
BEGIN
    INSERT INTO NewExampleTable (id, value)
    VALUES (NEW.id, NEW.value);
END;

Update Trigger

CREATE TRIGGER update_trigger
AFTER UPDATE ON ExampleTable
BEGIN
    UPDATE NewExampleTable
    SET value = NEW.value
    WHERE id = OLD.id;
END;

Read Trigger

CREATE TRIGGER delete_trigger
AFTER DELETE ON ExampleTable
BEGIN
    DELETE FROM NewExampleTable
    WHERE id = OLD.id;
END;

Roadmap

Documentation