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.
LEAVE A COMMENT