How to install Pyodbc and connect to MS SQL (FreeTDS & unixODBC)

When deploying a Python application (such as Django) that needs to connect to a Microsoft SQL Server database, you typically use the pyodbc library. However, pyodbc requires underlying OS-level dependencies, specifically unixodbc and a compatible ODBC driver, to function.

Because Upsun containers are highly secure and do not grant root or sudo access, you cannot run apt-get install to install the official Microsoft ODBC Debian packages. Additionally, attempting to compile unixodbc from source using make often fails due to missing system-level build tools.

Resolution

The most reliable way to install unixodbc and a compatible open-source SQL Server driver (FreeTDS) on Upsun without root access is to use Homebrew for Linux during your build phase.

Step 1: Create the Driver Configuration File

In the root of your project repository, create a configuration file named odbcinst.ini. This file tells unixodbc where to find the FreeTDS driver that Homebrew will install.

odbcinst.ini:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /app/.linuxbrew/lib/libtdsodbc.so

(Note: /app is the absolute path to your application's root directory inside the Upsun container, which is where Homebrew will be installed.)

Step 2: Update Your Build Hook

You need to update your application configuration to install Homebrew, download the drivers, and register them before compiling your Python dependencies.

Update your .upsun/config.yaml to include these steps in your hooks.build phase:

applications:
    myapp:
        # Define your Python version
        type: 'python:3.11' 
        
        hooks:
            build: |
                set -e
                
                # 1. Install Homebrew non-interactively
                NONINTERACTIVE=1 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
                
                # 2. Add Homebrew to the environment PATH
                eval "$(/app/.linuxbrew/bin/brew shellenv)"
                
                # 3. Install required packages (unixodbc and the FreeTDS driver)
                brew install gcc openssl freetds unixodbc
                
                # 4. Register the FreeTDS driver with unixODBC using your config file
                odbcinst -i -d -f odbcinst.ini
                
                # 5. Install your Python dependencies (e.g., pip, pipenv, poetry)
                pip install -r requirements.txt

Important Notes

• Initial Build Time: The very first time this build hook runs, your deployment will take longer than usual because Homebrew needs to install itself and compile the core dependencies. Homebrew caches these files, so subsequent deployments will be much faster.

• Requirements: Ensure pyodbc is included in your requirements.txt. Because unixodbc is now available in the container's path via Homebrew, pip will be able to successfully compile the pyodbc wheel.

• Driver Usage: In your application's database connection string (e.g., your Django DATABASES setting), ensure you specify the driver name exactly as defined in your INI file: Driver={FreeTDS}.