ODBC

Connect & Extract data from databases via Open Database Connectivity (ODBC)

Open Database Connectivity (ODBC) provides a standardized interface for accessing various databases using ODBC drivers. This connector enables data extraction from databases that may not have a dedicated Sling connector, or when ODBC is the preferred access method. This is available starting v1.5.3.

Setup

Before using ODBC connections, ensure you have the appropriate ODBC driver installed for your database:

macOS (via Homebrew):

# SQL Server
brew install microsoft/mssql-release/msodbcsql18

# PostgreSQL
brew install psqlodbc

# MySQL
brew install mysql-connector-odbc

Linux (Debian/Ubuntu):

# SQL Server
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)"
sudo apt-get update
sudo apt-get install msodbcsql18

# PostgreSQL
sudo apt-get install odbc-postgresql

# MySQL
sudo apt-get install libmyodbc

Windows: Download and install the appropriate ODBC driver from the vendor's website.

Connection Properties

• conn_string (required) -> The ODBC connection string (DSN or DSN-less format)

• conn_template (optional) -> Specify the SQL template name to use for query syntax. This should be the name of a template (e.g., sqlserver, postgres, mysql, db2, sap_hana). If not provided, Sling auto-detects from the connection string.

Custom Templates for Unsupported Drivers

For ODBC drivers that connect to databases not natively supported by Sling (e.g., DB2, SAP HANA, Teradata), you can create your own custom template:

1. Create a template file at ~/.sling/templates/{template_name}.yaml (e.g., ~/.sling/templates/db2.yaml)

2. Define the SQL syntax and type mappings specific to your database (see Template Overrides for the template structure)

3. Reference the template name in your connection's conn_template property

For example, to connect to DB2 via ODBC:

# ~/.sling/templates/db2.yaml
core:
  limit: select {fields} from {table}{where_clause} fetch first {limit} rows only

metadata:
  schemas: |
    select schemaname as schema_name
    from syscat.schemata
    order by schemaname

  tables: |
    select tabschema as schema_name, tabname as table_name, 'false' as is_view
    from syscat.tables
    where type = 'T'
      {{if .schema -}} and tabschema = '{schema}' {{- end}}
    order by tabname

  columns: |
    select colname as column_name, typename as data_type
    from syscat.columns
    where tabschema = '{schema}' and tabname = '{table}'
    order by colno

general_type_map:
  string: varchar(255)
  integer: integer
  bigint: bigint
  decimal: decimal(38,10)
  float: double
  bool: smallint
  date: date
  datetime: timestamp
  text: clob

Then reference it in your connection:

connections:
  MY_DB2:
    type: odbc
    conn_string: "Driver={IBM DB2 ODBC DRIVER};Database=mydb;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=myuser;Pwd=mypassword"
    conn_template: db2

Using sling conns

# SQL Server via ODBC (DSN-less)
sling conns set MY_ODBC type=odbc conn_string='Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes'

# PostgreSQL via ODBC
sling conns set MY_ODBC type=odbc conn_string='Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword'

# Using DSN
sling conns set MY_ODBC type=odbc conn_string='DSN=MyDataSource;UID=myuser;PWD=mypassword'

# With explicit template override
sling conns set MY_ODBC type=odbc conn_string='DSN=MyDSN' conn_template=sqlserver

Environment Variable

# SQL Server via ODBC
export MY_ODBC='{ type: odbc, conn_string: "Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes" }'

# PostgreSQL via ODBC
export MY_ODBC='{ type: odbc, conn_string: "Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword" }'

# With explicit template
export MY_ODBC='{ type: odbc, conn_string: "DSN=MyDSN", conn_template: sqlserver }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  # SQL Server via ODBC (DSN-less)
  MSSQL_ODBC:
    type: odbc
    conn_string: "Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes"

  # PostgreSQL via ODBC
  POSTGRES_ODBC:
    type: odbc
    conn_string: "Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword"

  # Using DSN
  MY_DSN:
    type: odbc
    conn_string: "DSN=MyDataSource;UID=myuser;PWD=mypassword"

  # With built-in template for SQL dialect
  CUSTOM_DB:
    type: odbc
    conn_string: "DSN=MyCustomDSN"
    conn_template: sqlserver

  # With custom template (create ~/.sling/templates/db2.yaml first)
  MY_DB2:
    type: odbc
    conn_string: "Driver={IBM DB2 ODBC DRIVER};Database=mydb;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=myuser;Pwd=mypassword"
    conn_template: db2

Replication Example

Here's an example replication configuration to extract data from an ODBC source to PostgreSQL:

source: MSSQL_ODBC
target: MY_POSTGRES

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  departments:

  # Extract using SQL query
  orders:
    sql: SELECT * FROM dbo.orders
    object: public.orders

  # Extract specific columns
  customers:
    sql: SELECT id, name, email FROM dbo.customers
    object: public.customers

Connection String Formats

DSN (Data Source Name)

Use a pre-configured DSN from your ODBC configuration:

DSN=MyDataSource;UID=myuser;PWD=mypassword

DSN-less (Driver-based)

Connect directly without a DSN:

SQL Server:

Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes

PostgreSQL:

Driver={PostgreSQL};Server=localhost;Port=5432;Database=mydb;UID=myuser;PWD=mypassword

MySQL:

Driver={MySQL ODBC 8.0 Driver};Server=localhost;Port=3306;Database=mydb;UID=myuser;PWD=mypassword

Troubleshooting

Driver Not Found

If you receive a "driver not found" error, verify that:

1. The ODBC driver is installed

2. The driver name in your connection string matches exactly (including brackets and version)

3. On Linux/macOS, check /etc/odbcinst.ini for installed drivers

SQL Syntax Errors

If you encounter SQL syntax errors (e.g., LIMIT vs TOP), ensure:

1. The driver type is being correctly auto-detected from your connection string

2. Or explicitly set conn_template to match your database type

Connection Timeout

For slow connections, you may need to add timeout parameters to your connection string:

Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;Connection Timeout=30

If you are facing issues connecting, please reach out to us at [email protected], on discord or open a Github Issue here.