Skip to main content

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.yaml
connections:
  oracle-legacy:
    init: |
      -- Install nanodbc extension
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
    properties:
      dsn: 'OracleProd'  # Matches /etc/odbc.ini
      connection_string: 'DSN=OracleProd;UID=flapi_reader;PWD=${ORACLE_PASSWORD}'

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

Example 1: Oracle Database​

Configuration​

flapi.yaml
connections:
  oracle-erp:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
    properties:
      dsn: 'OracleERP'
      schema: 'HR'

Query Oracle Table​

sqls/employees.sql
SELECT
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_title,
  salary,
  department_name
FROM odbc_scan(
  '{{{conn.dsn}}}',
  'HR',
  '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
{{#params.limit}}
LIMIT {{{params.limit}}}
{{/params.limit}}
sqls/employees.yaml
url-path: /hr/employees/
template-source: employees.sql
connection:
  - oracle-erp
cache-template: employees_cache.sql
cache-schedule: "0 */6 * * *"  # Refresh every 6 hours

parameters:
  department:
    type: string
    description: Filter by department
  min_salary:
    type: number
    description: Minimum salary
  limit:
    type: integer
    description: Max results
    maximum: 1000

Example 2: IBM DB2 (Mainframe)​

AS/400 Connection​

connections:
  db2-mainframe:
    init: |
      INSTALL nanodbc FROM community;
      LOAD nanodbc;
    properties:
      connection_string: >
        Driver={IBM i Access ODBC Driver};
        System=as400.company.com;
        UID=${DB2_USER};
        PWD=${DB2_PASSWORD};
        DefaultLibraries=PRODLIB;

Query Legacy System​

sqls/inventory.sql
-- Query AS/400 inventory system
SELECT
  item_code,
  item_description,
  quantity_on_hand,
  quantity_on_order,
  reorder_point,
  last_order_date
FROM odbc_query(
  '{{{conn.connection_string}}}',
  'SELECT * FROM PRODLIB.INVENTORY WHERE STATUS = ''A'''
)
{{#params.low_stock}}
WHERE 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;
    properties:
      connection_string: >
        Driver={Teradata};
        DBCName=teradata.company.com;
        Database=ANALYTICS;
        UID=${TERADATA_USER};
        PWD=${TERADATA_PASSWORD};
        CharSet=UTF8;
sqls/sales_analytics.sql
-- Query Teradata warehouse
SELECT
  sale_date,
  product_category,
  region,
  SUM(revenue) as total_revenue,
  SUM(quantity) as units_sold,
  COUNT(DISTINCT customer_id) as unique_customers
FROM odbc_scan(
  '{{{conn.dsn}}}',
  'ANALYTICS',
  '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​

# CRITICAL: Cache ODBC data locally
cache-template: oracle_cache.sql
cache-schedule: "0 */4 * * *"  # Every 4 hours
cache-strategy: full-refresh
sqls/oracle_cache.sql
-- Pull data from Oracle into DuckDB cache
SELECT *
FROM odbc_scan('{{{conn.dsn}}}', '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
SELECT *
FROM odbc_scan('{{{conn.dsn}}}', 'HR', 'EMPLOYEES')
WHERE last_updated_date > (
  SELECT COALESCE(MAX(last_updated_date), '1970-01-01')
  FROM cached_employees
)

Vendor-Specific SQL Handling​

Oracle-Specific Features​

-- Oracle syntax: ROWNUM, SYSDATE, NVL
SELECT
  employee_id,
  NVL(manager_id, 0) as manager_id,
  TO_CHAR(hire_date, 'YYYY-MM-DD') as hire_date
FROM odbc_query(
  '{{{conn.dsn}}}',
  'SELECT * FROM HR.EMPLOYEES WHERE ROWNUM <= 100'
)

DB2-Specific Features​

-- DB2 syntax: FETCH FIRST, CURRENT DATE
SELECT *
FROM odbc_query(
  '{{{conn.connection_string}}}',
  'SELECT * FROM PRODLIB.ITEMS WHERE CREATED >= CURRENT DATE - 30 DAYS FETCH FIRST 1000 ROWS ONLY'
)

Teradata-Specific Features​

-- Teradata syntax: TOP, CAST
SELECT *
FROM odbc_query(
  '{{{conn.dsn}}}',
  'SELECT TOP 1000 * FROM 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 odbc_scan(dsn, 'PROD', 'SALES')

-- ✅ GOOD: Filter at source (only recent data)
SELECT * FROM odbc_scan(dsn, 'PROD', 'SALES')
WHERE sale_date >= CURRENT_DATE - INTERVAL '90' DAY

3. Connection Pooling​

connections:
  oracle-erp:
    properties:
      connection_string: >
        DSN=Oracle;
        UID=${USER};
        PWD=${PASS};
        Pooling=true;
        Max Pool Size=10;

4. Timeout Configuration​

properties:
  connection_string: >
    DSN=Oracle;
    ConnectionTimeout=30;
    CommandTimeout=60;

5. 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 connection string:

connection_string: >
  DSN=Oracle;
  CharSet=UTF8;
  NCharSet=UTF8;

Issue: Connection timeout​

Error: Connection timeout expired

Solutions:

  1. Increase timeout: 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
properties:
  connection_string: 'DSN=Oracle;UID=admin;PWD=password123'

# ✅ GOOD
properties:
  connection_string: 'DSN=Oracle;UID=${DB_USER};PWD=${DB_PASSWORD}'

2. Use Separate ODBC User​

Don't use production/admin accounts:

-- Create dedicated read-only user
CREATE USER flapi_api_reader IDENTIFIED BY secure_password;
GRANT SELECT ON schema.table TO flapi_api_reader;

3. Encrypt Connections​

connection_string: >
  DSN=Oracle;
  Encrypt=yes;
  TrustServerCertificate=no;

4. IP Whitelisting​

Configure database firewall to only allow flAPI server IPs.

Cost Optimization​

1. Reduce Database Load​

# Infrequent refreshes for static data
cache-schedule: "0 2 * * *"  # Once daily at 2am

2. Off-Peak Queries​

# Query during off-peak hours (cheaper on some systems)
cache-schedule: "0 2,14 * * *"  # 2am and 2pm

3. Minimize Data Transfer​

-- Only fetch required columns
SELECT id, name, status  -- Not SELECT *
FROM odbc_scan(...)

Migration Examples​

From Oracle to PostgreSQL​

# Phase 1: ODBC to Oracle
connections:
  legacy:
    init: LOAD nanodbc;
    properties:
      dsn: 'Oracle'

# Phase 2: Migrate to PostgreSQL
connections:
  modern:
    init: LOAD postgres;
    properties:
      host: postgres.company.com

SQL stays mostly the same - flAPI provides abstraction!

Next Steps​

Additional Resources​

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!

🍪 Cookie Settings