Skip to main content

Database Connections

LakeXpress uses two distinct database roles, each requiring its own connection entry in the credentials file:

  • LakeXpress DB — An internal database used by LakeXpress to store configuration, track sync state, and log pipeline runs. This is not a source of data; it is LakeXpress's own bookkeeping store. You point LakeXpress at it with --lxdb_auth_id.
  • Source Database — The database you want to extract data from (e.g., a production PostgreSQL or Oracle instance). You point LakeXpress at it with --source_db_auth_id.

Both connections are defined in the same credentials JSON file, distinguished by the key name you give each entry.

Supported Databases​

Source Databases​

DatabaseSupport StatusNotes
PostgreSQLSupportedCtid-based parallel export
OracleSupportedThin and thick modes, Rowid-based parallel export
SQL ServerSupportedPhysloc-based parallel export
MySQLSupportedPartition detection
MariaDBSupportedDedicated mariadb ds_type
SAP HANASupportedRequires hdbcli and HDBDOTNETCORE for FastBCP

LakeXpress DB​

DatabaseSupport StatusNotes
PostgreSQLSupportedRecommended for production use
SQL ServerSupported
MySQLSupported
SQLiteSupportedGood for single-user and development use; no server required
DuckDBSupportedGood for single-user and development use; no server required

Credentials File Format​

All database credentials are stored in a single JSON file (typically named auth.json). Each top-level key is a connection identifier you choose — you reference these identifiers on the command line via --lxdb_auth_id and --source_db_auth_id. The file can hold as many connection entries as you need.

Create a JSON file with your database credentials:

{
  "lxdb_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_LXDB_USER}",
      "password": "$env{LX_LXDB_PASSWORD}",
      "server": "localhost",
      "port": 5432,
      "database": "lakexpress_log"
    }
  },
  "source_oracle": {
    "ds_type": "oracle",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_ORACLE_USER}",
      "password": "$env{LX_ORACLE_PASSWORD}",
      "server": "oracle-server.com",
      "port": 1521,
      "database": "orclpdb1",
      "lib_dir": "/opt/oracle/instantclient_19_8"
    }
  },
  "source_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_PG_USER}",
      "password": "$env{LX_PG_PASSWORD}",
      "server": "localhost",
      "port": 5432,
      "database": "production_db"
    }
  },
  "source_mssql": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_MSSQL_USER}",
      "password": "$env{LX_MSSQL_PASSWORD}",
      "server": "mssql-server.com",
      "port": 1433,
      "database": "sales_db"
    }
  },
  "lxdb_sqlite": {
    "ds_type": "sqlite",
    "auth_mode": "filesystem",
    "info": {
      "filepath": "/path/to/lakexpress_log.sqlite"
    }
  }
}

Command-Line Examples​

Passing the Credentials File​

# PostgreSQL source, PostgreSQL LakeXpress DB
./LakeXpress -a auth.json \
  --lxdb_auth_id lxdb_postgres \
  --source_db_auth_id source_postgres

# Oracle source, SQL Server LakeXpress DB
./LakeXpress -a auth.json \
  --lxdb_auth_id lxdb_mssql \
  --source_db_auth_id source_oracle

# SQL Server source, SQLite LakeXpress DB
./LakeXpress -a auth.json \
  --lxdb_auth_id lxdb_sqlite \
  --source_db_auth_id source_mssql

# MySQL source, MySQL LakeXpress DB
./LakeXpress -a auth.json \
  --lxdb_auth_id lxdb_mysql \
  --source_db_auth_id source_mysql

# MySQL source, DuckDB LakeXpress DB
./LakeXpress -a auth.json \
  --lxdb_auth_id lxdb_duckdb \
  --source_db_auth_id source_mysql

Publishing Credentials​

Publishing targets use their own credential entries in the same credentials.json file. See each publishing guide for authentication setup:

Security Best Practices​

1. Credential Storage​

  • Never commit credentials to version control
  • Restrict auth.json permissions: 
    chmod 600 auth.json
  • Use environment-specific auth files (dev, staging, prod)

2. Least Privilege​

  • Create dedicated database users for LakeXpress
  • Grant only SELECT on source databases — LakeXpress never writes to them
  • Grant full privileges on the LakeXpress DB — LakeXpress needs to create and update its own tracking tables there

3. Network Security​

  • Use SSL/TLS connections
  • Restrict database access by IP
  • Use VPN or private networks

4. Password Management​

  • Use strong passwords (12+ characters, mixed case, numbers, symbols)
  • Rotate passwords regularly
  • Consider secret managers (AWS Secrets Manager, HashiCorp Vault)

5. Environment Variables for Credentials​

LakeXpress supports $env{VAR_NAME} syntax in any string value in the credentials JSON. An error is raised if a referenced variable is not set.

{
  "source_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_PG_USER}",
      "password": "$env{LX_PG_PASSWORD}",
      "server": "localhost",
      "port": 5432,
      "database": "production_db"
    }
  },
  "source_mssql": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_MSSQL_USER}",
      "password": "$env{LX_MSSQL_PASSWORD}",
      "server": "mssql-server.com",
      "port": 1433,
      "database": "sales_db"
    }
  }
}

Set variables before running LakeXpress:

# Linux
export LX_PG_USER="dataexport"
export LX_PG_PASSWORD="SecureP@ssw0rd"
export LX_MSSQL_USER="sa"
export LX_MSSQL_PASSWORD="StrongP@ssw0rd"

# Windows (cmd)
set LX_PG_USER=dataexport
set LX_PG_PASSWORD=SecureP@ssw0rd

# Windows (PowerShell)
$env:LX_PG_USER = "dataexport"
$env:LX_PG_PASSWORD = "SecureP@ssw0rd"

Plain-text values still work -- values without $env{ are returned as-is.

See Also​

Copyright © 2026 Architecture & Performance.