Laravel Sail with Microsoft SQL Server

Because there is always somebody with their database in Azure

Photo by Surface on Unsplash

Long story short, I was tasked with another internal app for a company, this time with a database running Microsoft SQL Server 2022 (“SQL Server” from now on) on Microsoft Azure.

As far as I know, SQL Server is not the only database engine you can use in Azure, but I can only assume resellers shove it in as a default option and nobody bats an eye.

It’s always good practice to replicate the same database engine to be used in production. Laravel comes with SQLite by default, which is very lenient, so you’ll never know if your queries will truly work on MySQL, MariaDB, PostgreSQL or SQL Server.

While I tried to make a PR for Laravel Sail, it was shot down after a few days because of reasons. So, I’ll leave the instructions here for posterity, if sometime in the road I need to spin up Laravel Sail and put SQL Server to test how my SQL queries perform.

The big elephant in the room: Linux

As you may know, Laravel Sail works over Docker, and all containers are Linux based. Luckly, Microsoft has available two image versions for SQL Server for Linux, one based on Azure Edge and another that’s just SQL Server:

• Azure SQL Edge: mcr.microsoft.com/azure-sql-server
• Microsoft SQL Server: mcr.microsoft.com/mssql/server

While both work almost the same, the Azure one works natively on ARM64, which is great for MacBooks and future Windows on ARM devices.

The problem with the ARM64 image is that it doesn’t come with sqlcmd command line utility, so you will need a third-party utility to connect as the sa user (Super Administrator) to create the databases and logins. Alternatives range from DBeaver, Azure Data Studio, HammerDB, HeidiSQL, DataGrip, among many, many others. Since I’m on the Jetbrains bandwagon, DataGrip comes out-of-the-box.

With that said, we have to setup four things:

1. Add the environment variables to enable SQL Server.
2. Add the service in the docker-compose.yml file.
3. Edit the Dockerfile to compile the pdo_sqlsrv extension.
4. Add an entrypoint to setup the SQL Server database first time.

1. Setting up the environment

In your .env file, you will need to set the proper environment variables to connect to the sqlsrv service. The database environment variables should look like this:

DB_CONNECTION=sqlsrv
DB_HOST=sqlsrv
DB_PORT=1433
DB_DATABASE=laravel
DB_USERNAME=sail
DB_PASSWORD=password
DB_ROOT_PASSWORD=p@ssw0rd
ACCEPT_EULA=Y

Note that there are two new environment variables, DB_ROOT_PASSWORD and ACCEPT_EULA. The first is pretty straightforward, this is a strong password due to default SQL Server policies regarding the Super Administrator credentials, so blame Microsoft. The second one is necessary to accept the Microsoft SQL Server EULA, otherwise both PHP extension and SQL Server won’t turn on.

2. Setting up Docker Compose

When you install Laravel Sail, it copies a docker-compose.yml file that spin ups all services. We can easily add the sqlsrv service like so:

services:
    # ...

    # Microsoft SQL Server
    sqlsrv:
        image: 'mcr.microsoft.com/mssql/server:2022-latest'
        # image: 'mcr.microsoft.com/azure-sql-server' # Uncomment if ARM64
        ports:
            # Forward the database port to the host
            - '${FORWARD_DB_PORT:-1433}:1433'
        environment:
            # Add a default Super Admin password that is strong
            MSSQL_SA_PASSWORD: '${DB_ROOT_PASSWORD}'
            # The default Database Name to connect to.
            MSSQL_DB_NAME: '${DB_DATABASE}'
            # The default Database User to connect as.
            MSSQL_USER: '${DB_USERNAME}'
            # The Database User Password (not the super admin).
            MSSQL_PASSWORD: '${DB_PASSWORD}'
            # Accept the EULA, or fail at running.
            ACCEPT_EULA: '${ACCEPT_EULA}'
        volumes:
            # Create a volume for persistence
            - 'sail-sqlsrv:/var/opt/mssql'
            # Use a custom custom entrypoint 
            - '.devcontainer/entrypoint.sh:/entrypoint.sh'
        networks:
            - sail
        entrypoint: '/entrypoint.sh'
        healthcheck:
            # This simple test checks for the port availability, works on
            # on both x64 and ARM64 versions of Azure Edge SQL, plus the
            # x64 version of Microsoft SQL Server.
            test:
                - CMD
                - 'timeout'
                - '1'
                - 'bash'
                - '-c'
                - 'cat < /dev/null > /dev/tcp/127.0.0.1/1433'
            timeout: 5s
            retries: 3

The next thing is to install the SQL Server extensions for PHP. Technically, you only need pdo_sqlsrv, unless you’re doing your SQL queries directly through the sqlsrv extension.

3. Installing pdo_sqlsrv

The laravel.test service contains the PHP runtime. There is no way to install extensions when you build the image, not even as an argument, but you can do it by editing the Dockerfile or after by connecting directly to the container.

Personally, I like more editing the Dockerfile.

We can copy-paste the vendor/laravel/sail/runtime/{php-version} directory into your project anywhere where it makes sense, like docker/runtime/8.3. Then in your docker-compose.yml file, reference that directory instead of the Laravel Sail one.

services:
    laravel.test:
        build:
            context: ./docker/runtime/8.3 # The new directory
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
                ACCEPT_EULA: '${ACCEPT_EULA}' # Accept the EULA to compile

In any case, you should go into the Dockerfile and add the command to install the extensions only if the ACCEPT_EULA is Y. This allow us to disable the compilation step if we don’t accept the EULA, like when you share the same Dockerfile among multiple proyects.

ARG SQLSRV_VERSION=5.12.0

RUN RELEASE=$(lsb_release -r -s) \
  if [ "$ACCEPT_EULA" = "Y" ]; then \
    curl -sS https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc \
    && curl -sS https://packages.microsoft.com/config/ubuntu/$RELEASE/prod.list | tee /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && apt-get install -y mssql-tools18 unixodbc-dev \
    && echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc \
    && pecl install pdo_sqlsrv-$SQLSRV_VERSION \
    && printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/8.3/mods-available/pdo_sqlsrv.ini \
    && phpenmod pdo_sqlsrv \
    && bash -c "source ~/.bashrc" \
    && apt-get -y autoremove \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*
  fi

Notice the SQLSRV_VERSION argument. We don’t need this, but it’s better to set the version of the extension since these only work on actively maintained PHP versions.

Anyway, you may encapsulate that script into custom bash file somewhere and call it from the Dockerfile too. If you plan the latter, remember to set it as executable using chmod +x or --chmod=0755 if you’re using the COPY or ADD instructions.

COPY --chmod=0755 compile_pdo_sqlsrv.sh /compile.sh

RUN compile_pdo_sqlsrv.sh

4. Initializing the SQL Server

The problem with Microsoft’s Docker images for SQL Server is that they’re pretty much a port of the Windows versions with nothing of real utility for Linux or the Docker ecosystem itself. In other words, creating the first user and database must be done manually.

The first step is to run the SQL Server. After is running, we need to execute an SQL query to set up everything, preferable, embedded into a bash file so variable substitution can work.

-- Create the default database
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '$MSSQL_DB_NAME') BEGIN
    CREATE DATABASE $MSSQL_DB_NAME;
END
GO

-- Create the "sail" login with superpowers
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = "$MSSQL_USER") BEGIN
    CREATE LOGIN $MSSQL_USER WITH
        PASSWORD = "$MSSQL_PASSWORD",
        DEFAULT_DATABASE = $MSSQL_DB_NAME,
        CHECK_EXPIRATION = OFF,
        CHECK_POLICY = OFF;
END
GO

-- Switch to the default database
USE $MSSQL_DB_NAME;
GO

-- Create the "sail" user assigned to the login
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = "$MSSQL_USER") BEGIN
    CREATE USER $MSSQL_USER FOR LOGIN $MSSQL_USER;
    ALTER ROLE db_owner ADD MEMBER $MSSQL_USER;
END
GO

-- Go back to the system database
USE sys
GO

-- Create the testing database
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = "testing") BEGIN
    CREATE DATABASE testing;
END
GO

-- Switch to the testing database
USE testing;
GO

-- Create a testing user assigned to the testing database
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = "testing") BEGIN
    CREATE USER testing FOR LOGIN testing WITH
      PASSWORD = "",
      DEFAULT_DATABASE = "testing",
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
    ALTER ROLE db_datareader ADD MEMBER testing;
    ALTER ROLE db_datawriter ADD MEMBER testing;
    ALTER ROLE db_ddladmin ADD MEMBER testing;
END
GO

As you can see, there is a lot of plumbing, but in any case, it should work to enable development of your app and testing scenarios.

If you want the bash script, I added it to my GitHub Gists so you can copy-paste it into your own development environment.

Connecting to SQL Server

Because the sail binary doesn’t have a way to connect directly to the sqlsrv service, you will have to use docker compose exec to enter the SQL Server command line and run your queries.

Anyway, the command you would want to use is this:

/opt/mssql-tools/bin/sqlcmd -No -d $MSSQL_DB_NAME -P $MSSQL_PASSWORD -U $MSSQL_USER

If you’re on ARM64, then you should use a third-party utility to connect to the bound port (1433) using the Super Administrator credentials and do your bidding.