ODBC: Universal Database Connector

Extension Credit

This guide uses the nanodbc extension by the DuckDB community. Thanks to the contributors who made universal database connectivity possible through ODBC!

Connect flAPI to any database that provides an ODBC driver - including Oracle, Teradata, DB2, Informix, SAP HANA, and proprietary enterprise systems. Perfect for legacy system integration and databases without native DuckDB extensions.

Why Use ODBC with flAPI?

"If it has an ODBC driver, flAPI can connect to it."

Many enterprise systems don't have modern APIs:

• Legacy databases - Oracle 9i, DB2, Informix
• Proprietary systems - vendor-specific databases
• Enterprise software - SAP HANA, Teradata, Vertica
• Mainframe data - AS/400, z/OS DB2
• BI tools - Microsoft Access, FoxPro

flAPI + ODBC provides:

• Universal compatibility - 1000s of ODBC drivers available
• No custom code - standard ODBC interface
• Modern REST APIs - legacy data, modern access
• Caching layer - speed up slow legacy systems
• SQL abstraction - hide complex vendor SQL

Supported Databases (via ODBC)

Enterprise Databases

• Oracle - all versions (8i through 23c)
• IBM DB2 - z/OS, LUW, iSeries (AS/400)
• IBM Informix - all versions
• Teradata - data warehouse platform
• SAP HANA - in-memory database
• Vertica - columnar analytics database

Proprietary/Legacy

• Microsoft Access - .mdb and .accdb files
• FileMaker Pro - custom business applications
• FoxPro - legacy desktop databases
• Sybase ASE - enterprise database
• Progress OpenEdge - business application platform

Cloud/Modern (when no native driver)

• Amazon Redshift - via ODBC driver
• Azure SQL Database - alternative to native
• Google BigQuery - alternative to native extension

Architecture

Installation

Step 1: Install ODBC Driver

On Linux (Ubuntu/Debian):

# Install ODBC manager
sudo apt-get install unixodbc unixodbc-dev

# Example: Install Oracle driver
sudo apt-get install oracle-instantclient-odbc

# Example: Install PostgreSQL driver (for testing)
sudo apt-get install odbc-postgresql

On macOS:

brew install unixodbc

# Example: PostgreSQL driver
brew install psqlodbc

On Windows:

Drivers usually come with database client software or download from vendor website.

Step 2: Configure ODBC DSN

/etc/odbcinst.ini

# Define driver location
[Oracle ODBC]
Description = Oracle ODBC Driver
Driver = /usr/lib/oracle/19.3/client64/lib/libsqora.so.19.1

[PostgreSQL]
Description = PostgreSQL ODBC Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so

/etc/odbc.ini

# Define data sources
[OracleProd]
Driver = Oracle ODBC
ServerName = oracle-prod.company.com
Port = 1521
Database = PRODDB
UID = flapi_reader
PWD = ${ORACLE_PASSWORD}

[TeradataDW]
Driver = Teradata
DBCName = teradata-dw.company.com
Database = ANALYTICS
UID = api_user
PWD = ${TERADATA_PASSWORD}

Step 3: Configure flAPI

flAPI's ODBC pattern uses init: to install/load nanodbc and ATTACH the DSN as a DuckDB catalog. Then reference the catalog in templates via the connection's properties.

flapi.yaml

connections:
  oracle-legacy:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
      ATTACH 'DSN=OracleProd;UID=flapi_reader;PWD=${ORACLE_PASSWORD}' AS odbcdb (TYPE nanodbc);
    properties:
      catalog: odbcdb

template:
  environment-whitelist:
    - '^ORACLE_.*'
    - '^TERADATA_.*'
    - '^DB2_.*'

The catalog property is just a free-form value used in SQL templates as {{ conn.catalog }} so you can swap DSNs without editing every SQL file.

Example 1: Oracle Database

Configuration

flapi.yaml

connections:
  oracle-erp:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
      ATTACH 'DSN=OracleERP;UID=flapi_reader;PWD=${ORACLE_PASSWORD}' AS oracledb (TYPE nanodbc);
    properties:
      catalog: oracledb
      schema: HR

Query Oracle Table

sqls/employees.sql

SELECT
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_title,
  salary,
  department_name
FROM {{ conn.catalog }}.{{ conn.schema }}.EMPLOYEES
WHERE 1=1
{{#params.department}}
  AND department_name = '{{{ params.department }}}'
{{/params.department}}
{{#params.min_salary}}
  AND salary >= {{ params.min_salary }}
{{/params.min_salary}}
ORDER BY hire_date DESC

sqls/employees.yaml

url-path: /hr/employees/
method: GET
template-source: employees.sql
connection:
  - oracle-erp

cache:
  enabled: true
  table: oracle_employees
  schedule: 6h
  template-file: employees_cache.sql

request:
  - field-name: department
    field-in: query
    description: Filter by department
    required: false
    validators:
      - type: string
        regex: '^[A-Za-z ]{1,50}$'
  - field-name: min_salary
    field-in: query
    description: Minimum salary
    required: false
    validators:
      - type: int
        min: 0
        max: 100000000

Example 2: IBM DB2 (Mainframe)

AS/400 Connection

connections:
  db2-mainframe:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
      ATTACH 'Driver={IBM i Access ODBC Driver};System=as400.company.com;UID=${DB2_USER};PWD=${DB2_PASSWORD};DefaultLibraries=PRODLIB;' AS db2db (TYPE nanodbc);
    properties:
      catalog: db2db
      library: PRODLIB

Query Legacy System

sqls/inventory.sql

-- Query AS/400 inventory table
SELECT
  item_code,
  item_description,
  quantity_on_hand,
  quantity_on_order,
  reorder_point,
  last_order_date
FROM {{ conn.catalog }}.{{ conn.library }}.INVENTORY
WHERE status = 'A'
{{#params.low_stock}}
  AND quantity_on_hand < reorder_point
{{/params.low_stock}}
ORDER BY item_code

Example 3: Teradata Data Warehouse

connections:
  teradata-dw:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
      ATTACH 'Driver={Teradata};DBCName=teradata.company.com;Database=ANALYTICS;UID=${TERADATA_USER};PWD=${TERADATA_PASSWORD};CharSet=UTF8;' AS tdwh (TYPE nanodbc);
    properties:
      catalog: tdwh
      schema: ANALYTICS

sqls/sales_analytics.sql

SELECT
  sale_date,
  product_category,
  region,
  SUM(revenue) AS total_revenue,
  SUM(quantity) AS units_sold,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM {{ conn.catalog }}.{{ conn.schema }}.FACT_SALES
WHERE sale_date >= CURRENT_DATE - INTERVAL 90 DAY
{{#params.region}}
  AND region = '{{{ params.region }}}'
{{/params.region}}
GROUP BY sale_date, product_category, region
ORDER BY sale_date DESC

Performance Optimization

Problem: ODBC Queries are Slow

Legacy databases can be very slow (5-30 seconds per query).

Solution: Aggressive Caching

cache:
  enabled: true
  table: oracle_employees
  schedule: 4h
  template-file: oracle_cache.sql

sqls/oracle_cache.sql

-- Pull data from Oracle into DuckLake cache
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT *
FROM {{ conn.catalog }}.HR.EMPLOYEES
WHERE hire_date >= CURRENT_DATE - INTERVAL 5 YEAR

Performance improvement:

• Direct ODBC: 5-30 seconds
• Cached: 1-10ms (500-3000x faster)

Incremental Refresh

sqls/oracle_incremental_cache.sql

-- Only fetch new/updated records
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT *
FROM {{ conn.catalog }}.HR.EMPLOYEES
{{#cache.previousSnapshotTimestamp}}
WHERE last_updated_date > TIMESTAMP '{{ cache.previousSnapshotTimestamp }}'
{{/cache.previousSnapshotTimestamp}}

Vendor-Specific SQL Handling

Oracle-Specific Features

-- Oracle syntax: ROWNUM, SYSDATE, NVL — pushed down to the DSN
SELECT
  employee_id,
  NVL(manager_id, 0) AS manager_id,
  TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date
FROM {{ conn.catalog }}.HR.EMPLOYEES
WHERE ROWNUM <= 100

DB2-Specific Features

-- DB2 syntax: FETCH FIRST, CURRENT DATE
SELECT *
FROM {{ conn.catalog }}.PRODLIB.ITEMS
WHERE created >= CURRENT DATE - 30 DAYS
FETCH FIRST 1000 ROWS ONLY

Teradata-Specific Features

-- Teradata syntax: TOP, CAST
SELECT TOP 1000 *
FROM {{ conn.catalog }}.ANALYTICS.SALES
WHERE sale_date >= CAST(CURRENT_DATE - 90 AS DATE)

Best Practices

1. Use Read-Only Credentials

-- In Oracle: Create read-only user
CREATE USER flapi_reader IDENTIFIED BY secure_password;
GRANT CONNECT, SELECT ANY TABLE TO flapi_reader;

-- Revoke write permissions
REVOKE INSERT, UPDATE, DELETE FROM flapi_reader;

2. Limit Data Volume

-- BAD: Pull entire table (millions of rows)
SELECT * FROM {{ conn.catalog }}.PROD.SALES

-- GOOD: Filter at source (only recent data)
SELECT * FROM {{ conn.catalog }}.PROD.SALES
WHERE sale_date >= CURRENT_DATE - INTERVAL 90 DAY

3. Connection Pooling

The nanodbc extension manages its own pool per DuckDB session. To configure pooling, add the relevant options to the ODBC connection string inside the ATTACH call (Pooling=true;Max Pool Size=10) — see the upstream nanodbc / driver docs for supported options.

4. Test ODBC Connection First

# Test ODBC connection before configuring flAPI
isql OracleProd flapi_reader password

# Should connect successfully
SQL> SELECT COUNT(*) FROM HR.EMPLOYEES;

Troubleshooting

Issue: "Data source not found"

Error: [unixODBC][Driver Manager]Data source name not found

Solutions:

1. Verify DSN exists in /etc/odbc.ini
2. Check DSN name matches exactly (case-sensitive)
3. Test with isql DSN_NAME username password

Issue: "Driver not found"

Error: [unixODBC][Driver Manager]Can't open lib

Solutions:

1. Install ODBC driver package
2. Update /etc/odbcinst.ini with correct driver path
3. Verify driver file exists: ls -l /path/to/driver.so

Issue: Slow queries

Problem: Every query takes 10+ seconds

Solutions:

1. Enable caching (most important)
2. Add indexes in source database
3. Limit data pulled (WHERE clauses)
4. Use scheduled batch loads instead of real-time

Issue: Character encoding problems

Error: Garbled text or special characters

Solution: Set charset in the connection string passed to ATTACH:

init: |
  INSTALL nanodbc FROM community;
  LOAD nanodbc;
  ATTACH 'DSN=Oracle;CharSet=UTF8;NCharSet=UTF8;' AS oracledb (TYPE nanodbc);

Issue: Connection timeout

Error: Connection timeout expired

Solutions:

1. Increase timeout in the connection string: ConnectionTimeout=60
2. Check firewall rules
3. Verify network connectivity to database
4. Test direct connection (not through flAPI)

Security Considerations

1. Never Hardcode Credentials

# BAD
init: |
  ATTACH 'DSN=Oracle;UID=admin;PWD=password123' AS oracledb (TYPE nanodbc);

# GOOD
init: |
  ATTACH 'DSN=Oracle;UID=${DB_USER};PWD=${DB_PASSWORD}' AS oracledb (TYPE nanodbc);

2. Use Separate ODBC User

Don't use production/admin accounts:

CREATE USER flapi_api_reader IDENTIFIED BY secure_password;
GRANT SELECT ON schema.table TO flapi_api_reader;

3. Encrypt Connections

init: |
  ATTACH 'DSN=Oracle;Encrypt=yes;TrustServerCertificate=no;' AS oracledb (TYPE nanodbc);

4. IP Whitelisting

Configure database firewall to only allow flAPI server IPs.

Cost Optimization

1. Reduce Database Load

# Infrequent refreshes for static data
cache:
  enabled: true
  table: static_lookup
  schedule: 24h

2. Minimize Data Transfer

-- Only fetch required columns
SELECT id, name, status  -- not SELECT *
FROM {{ conn.catalog }}.HR.EMPLOYEES

Migration Examples

From Oracle to PostgreSQL

# Phase 1: ODBC to Oracle
connections:
  legacy:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
      ATTACH 'DSN=Oracle;UID=${USER};PWD=${PASS}' AS legacy_db (TYPE nanodbc);
    properties:
      catalog: legacy_db

# Phase 2: Migrate to PostgreSQL
connections:
  modern:
    init: |
      INSTALL postgres;
      LOAD postgres;
      ATTACH 'host=postgres.company.com dbname=prod user=${USER} password=${PASS}' AS legacy_db (TYPE postgres);
    properties:
      catalog: legacy_db

SQL stays mostly the same — flAPI provides abstraction.

Next Steps

• PostgreSQL: Modern alternative to legacy databases
• Caching Setup: Essential for ODBC performance
• Examples: See caching patterns
• Deployment: Deploy to production

Additional Resources

• DuckDB nanodbc Extension: Official extension docs
• unixODBC Documentation: ODBC manager docs
• ODBC Driver List: Database-specific connection strings

---

Perfect for Legacy Systems

ODBC is your bridge from legacy to modern. Connect flAPI to that 20-year-old Oracle system, add caching, and suddenly it feels fast and modern. No database migration required!