SQL Server Linux for Symfony development

May 21, 2017 | No Comments | Programming | docker doctrine mssql sql-server symfony

Recently Microsoft released SQL Server Linux version. So let’s try this out for our simple Symfony2 project and test how it works for common tasks.

Installation of SQL Server Linux

I use Ubuntu 16.04 / Symfony 3.2 / Doctrine ORM 2.5 and Docker. It’s less problematic way to use SQL Server. You should have basic bash and Docker knowledges to perform this.

So let’s create Docker Compose file:

version: "2"
services:
  mssql:
    image: microsoft/mssql-server-linux
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: "#Strong1Pass"
    ports:
      - "1433:1433"

And run:

sudo docker-compose up -d

Congratulations, our container has been created and server started.

Driver

On this step we should connect PHP7 and SQL Server properly. First step is install pecl extensions:

sudo pecl install sqlsrv pdo_sqlsrv

Then we need add them in php.ini or additional configuration files. My example is based on php-fpm version but basic concept is obvious.

#Create base files
echo "extension= pdo_sqlsrv.so" | sudo tee /etc/php/7.0/mods-available/pdo_sqlsrv.ini
echo "extension= sqlsrv.so" | sudo tee /etc/php/7.0/mods-available/sqlsrv.ini
# Symlink to web server php cgi
sudo ln -s /etc/php/7.0/mods-available/pdo_sqlsrv.ini /etc/php/7.0/fpm/conf.d/20-pdo_sqlsrv.conf
sudo ln -s /etc/php/7.0/mods-available/sqlsrv.ini /etc/php/7.0/fpm/conf.d/20-sqlsrv.conf
#Symflink to cli version
sudo ln -s /etc/php/7.0/mods-available/pdo_sqlsrv.ini /etc/php/7.0/cli/conf.d/20-pdo_sqlsrv.conf
sudo ln -s /etc/php/7.0/mods-available/sqlsrv.ini /etc/php/7.0/cli/conf.d/20-sqlsrv.conf

Symfony

Sample Symfony doctrine configuration in app/config/config.yml

doctrine:
    dbal:
        driver: pdo_sqlsrv
        host: localhost
        port: 1433
        dbname: symfony
        user: "SA"
        password: "#Strong1Pass"
        charset: UTF8

    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true

So now we can create database from cli:

php bin/console doctrine:database:create

As example generated sql for this entity:

<?php
 
namespace AppBundle\Entity;
 
use Doctrine\ORM\Mapping as ORM;
 
/**
 * Post
 *
 * @ORM\Table(name="post")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\PostRepository")
 */
class Post
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
 
    /**
     * @var string
     *
     * @ORM\Column(name="content", type="text")
     */
    private $content;
 
 
    /**
     * Get id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }
 
    /**
     * Set content
     *
     * @param string $content
     *
     * @return Post
     */
    public function setContent($content)
    {
        $this->content = $content;
 
        return $this;
    }
 
    /**
     * Get content
     *
     * @return string
     */
    public function getContent()
    {
        return $this->content;
    }
}

php bin/console doctrine:schema:update --dump-sql

CREATE TABLE post (id INT IDENTITY NOT NULL, content VARCHAR(MAX) NOT NULL, PRIMARY KEY (id));

Conclusion

I didn’t use SQL Server in production. But we can install it without problems. So it can be used without any problems on modern systems and frameworks.