Querying a MSSQL database from Symfony

For a recent project, we had to connect and query a Microsoft SQL database from a Symfony project. Since we use Docker to run the apps in separate containers, we added a new container to simulate the MSSQL database.

We added the following code to our docker-compose.yml file in the root of our Symfony project:

version: '2'

services:
  mssql:
    image: 'microsoft/mssql-server-linux:latest'
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=<password>
    ports:
      - '1433:1433'
    volumes:
      - mssqldata:/var/opt/mssql/data

  mssql-setup:
    build: ./mssql-setup
    image: 'activelamp/iamucla-mssql-setup'
    environment:
      - SA_PASSWORD=<password>
    links:
      - mssql:api-mssql
volumes:
  mssqldata: {}

In the mssql-setup folder in the root of our Symfony project, we have another Dockerfile with the following contents to set up the database:

FROM ubuntu:16.04

# Install update instance
RUN apt-get -y update \
    && apt-get install curl -y \
    && apt-get install apt-transport-https \
    && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list \
    && apt-get -y update \
    && ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev

# Create sqlsetup directory
RUN mkdir -p /usr/src/sqlsetup
WORKDIR /usr/src/sqlsetup

# Bundle sqlsetup source
COPY . /usr/src/sqlsetup

# Grant permissions for the import-data script to be executable
RUN chmod +x /usr/src/sqlsetup/setup-db.sh

CMD /bin/bash ./setup-db.sh

As you can see in the above code, we execute a script called setup-db.sh . This imports the setup.sql file for creating the needed tables (both files are also located in the mssql-setup folder):

#wait for the SQL Server to come up
sleep 30s

#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S api-mssql -U sa -P $SA_PASSWORD -d master -i setup.sql

In the beginning of this project, we started off using the LeaseWeb DoctrinePdoDblib driver but quickly noticed that this driver wasn't returning the expected error messages coming from MSSQL and it also did not return any message strings returned from custom Stored Procedures. That's when we made the switch to use the official Microsoft SQL drivers for PHP.

But this required some changes to our root Dockerfile so we could install these drivers in our Symfony container.

FROM php:7.1.16-apache

RUN curl -sS https://getcomposer.org/installer | php \
  && mv composer.phar /usr/local/bin/composer \
  && chmod ugo+x /usr/local/bin/composer

.....

RUN apt-get update && apt-get install -y \
  vim \
  git \
  unzip \
  wget \
  curl \
  libmcrypt-dev \
  libcurl4-openssl-dev \
  mysql-client \
  nodejs \
  libxml2-dev \
  libldb-dev libldap2-dev \
  build-essential \
  freetds-bin \
  freetds-dev \
  apt-transport-https

#Microsoft Drivers for PHP for SQL Server: https://github.com/Microsoft/msphpsql
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
  && curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list \
  && apt-get install -y locales \
  && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
  && locale-gen

RUN apt-get -y update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql
RUN apt-get install -y unixodbc-dev

RUN pear config-set php_ini `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"` system
RUN pecl install sqlsrv
RUN pecl install pdo_sqlsrv

RUN echo "extension=sqlsrv.so\nextension=pdo_sqlsrv.so" > /usr/local/etc/php/conf.d/symfony.ini

.....

As you can see in the above snippet, we had to install the sqlsrv and pdo_sqlsrv drivers and enable these extensions in our symfony.ini file.

The next step was to define the connection in our Symfony config.yml file as follows:

# Doctrine Configuration
doctrine:
  dbal:
    connections:
      mssql:
        driver: pdo_sqlsrv
        host: '%mssql_database_host%'
        port: '%mssql_database_port%'
        dbname: '%mssql_database_name%'
        user: '%mssql_database_user%'
        password: '%mssql_database_password%'
        charset: UTF8
        wrapper_class: AppBundle\Connections\ConnectionMSSQL

As you can see, we defined a wrapper_class in our connection, this ConnectionMSSQL class extends the Doctrine\DBAL\Connection. In this ConnectionMSSQL class, you can write all your functions to query the DB, e.g.:

public function findUserById($id)
{
    $sql = 'SELECT * FROM my_users
         WHERE UPPER(id) = UPPER(:id)';
    return $this->fetchAll(
        $sql, [
            'id' => strtoupper($id),
        ]
    );
}

Querying a MSSQL database from Symfony

Authored by