Laravel 連接 MSSQL

tags: `Laravel` `MSSQL`

category: `Back-End`

description: `Laravel 連接 MSSQL`

created_at: `2022/08/15 19:00:00`

cover image

前言

最近有個需求是使用 PHP 開發後端，然後連接的資料庫居然是 MSSQL，~~那作業系統當然就是 Windows Server 啦~~，所以最近研究好了就來紀錄一下。

為什麼使用 `Laravel Sail`

最主要是不想在自己 Local 裝 MSSQL，畢竟我應該也不會常用，所以打算用 Docker 直接建一個環境。

那既然都用 Docker 了，那乾脆整個都使用 Docker 吧，而 Laravel Sail 就是一個官方幫你包好的環境。

老樣子先開新專案

依照你的喜好去建 Laravel 專案，

composer create-project laravel/laravel example-app

或是指定版本

composer create-project laravel/laravel="8.*" example-app

~~或是跟我一樣偷懶~~直接裝在 Global 下次比較好建

composer global require laravel/installer

裝好之後你就會有 laravel 這個指令了，然後只要下簡單的指令就可以開新專案

laravel new example-app

安裝 `WSL2` 與 `Docker`

因為我目前環境是 windows，我又不想裝 Docker Desktop，感覺就肥肥的(?)

基本上安裝非常簡單，按照官方文件應該很快就能裝好，而且是中文的。

再來要安裝 Docker ，假設你是用 ubuntu 可以看這篇

安裝 `laravel/sail`

上面專案建好，也有 WSL2 的 Docker 環境之後，終於要進入正題了，當然是先安裝套件

注意: 以下所有操作都在 wsl 當中進行。

composer require laravel/sail --dev

把 docker-compose.yml 彈出到根目錄

php artisan sail:install

然後他會問你要安裝哪些服務，這些之中都沒有 MSSQL，所以先選預設就好，反正之後會把它改掉。

不過假如你先選了預設值(Mysql)，也可以先玩玩 Laravel/Sail 的威力(?)

跑下面的指令就可以把東西都跑起來(在背景)

sail up -d

之後你可以用 docker ps 去看，應該會看到兩個容器正在運作。

CONTAINER ID   IMAGE                    COMMAND                  CREATED              STATUS                        PORTS                                                                                    NAMES
de5aaa1dd956   sail-8.1/app             "start-container"        About a minute ago   Up About a minute             0.0.0.0:80->80/tcp, :::80->80/tcp, 0.0.0.0:5173->5173/tcp, :::5173->5173/tcp, 8000/tcp   example-app-laravel.test-1
2bea5ed045b4   mysql/mysql-server:8.0   "/entrypoint.sh mysq…"   About a minute ago   Up About a minute (healthy)   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060-33061/tcp                               example-app-mysql-1

這時可以到瀏覽器 localhost 或 127.0.0.1 應該就會看到 Laravel 的頁面。

再來也可以下 migration 的指令把東西倒進他幫你起的 Mysql 服務

sail artisan migrate

然後可以快速測試一下他是不是真的可以用(?)，可以到 /routes/web.php 先寫死

<?php
use Illuminate\Support\Facades\Route;
use App\Models\User;

Route::get('/', function () {
    dd(User::get());
});

這樣刷新之後應該會看到正常的 dd 頁面。

彈出 `Dockerfile`

上面有說到 Laravel Sail 是官方幫你包好的環境，上面已經彈出了 docker-compose.yml，那總會也有 Dockerfile 可以改

sail artisan sail:publish

注意: 要彈出來你的容器必須在運行中。

彈出來之後你的根目錄就會多一個 docker 的資料夾，裡面預設有各種 php 的版本，在我寫這篇的當下他是生出三個版本，分別是

自訂 PHP 版本

這時我假設我要自訂一個 7.3 的版本好了，隨便貼一個來改來玩玩。如果你沒這需求可以跳過，~~反正我只是紀錄給自己看~~

/docker/7.3/Dockerfile

FROM ubuntu:20.04

LABEL maintainer="Taylor Otwell"

ARG WWWGROUP
ARG NODE_VERSION=16
ARG POSTGRES_VERSION=13

WORKDIR /var/www/html

ENV DEBIAN_FRONTEND noninteractive
ENV TZ=UTC

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN apt-get update \
    && apt-get install -y gnupg gosu curl ca-certificates zip unzip git supervisor sqlite3 libcap2-bin libpng-dev python2 \
    && mkdir -p ~/.gnupg \
    && chmod 600 ~/.gnupg \
    && echo "disable-ipv6" >> ~/.gnupg/dirmngr.conf \
    && echo "keyserver hkp://keyserver.ubuntu.com:80" >> ~/.gnupg/dirmngr.conf \
    && gpg --recv-key 0x14aa40ec0831756756d7f66c4f4ea0aae5267a6c \
    && gpg --export 0x14aa40ec0831756756d7f66c4f4ea0aae5267a6c > /usr/share/keyrings/ppa_ondrej_php.gpg \
    && echo "deb [signed-by=/usr/share/keyrings/ppa_ondrej_php.gpg] https://ppa.launchpadcontent.net/ondrej/php/ubuntu focal main" > /etc/apt/sources.list.d/ppa_ondrej_php.list \
    && apt-get update \
    && apt-get install -y php7.3-cli php7.3-dev \
       php7.3-pgsql php7.3-sqlite3 php7.3-gd \
       php7.3-curl php7.3-memcached \
       php7.3-imap php7.3-mysql php7.3-mbstring \
       php7.3-xml php7.3-zip php7.3-bcmath php7.3-soap \
       php7.3-intl php7.3-readline php7.3-pcov \
       php7.3-msgpack php7.3-igbinary php7.3-ldap \
       php7.3-redis php7.3-xdebug \
    && php -r "readfile('https://getcomposer.org/installer');" | php -- --install-dir=/usr/bin/ --filename=composer \
    && curl -sLS https://deb.nodesource.com/setup_$NODE_VERSION.x | bash - \
    && apt-get install -y nodejs \
    && npm install -g npm \
    && curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | gpg --dearmor | tee /usr/share/keyrings/yarnkey.gpg >/dev/null \
    && echo "deb [signed-by=/usr/share/keyrings/yarnkey.gpg] https://dl.yarnpkg.com/debian/ stable main" > /etc/apt/sources.list.d/yarn.list \
    && curl -sS https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /usr/share/keyrings/pgdg.gpg >/dev/null \
    && echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt focal-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
    && apt-get update \
    && apt-get install -y yarn \
    && apt-get install -y mysql-client \
    && apt-get install -y postgresql-client-$POSTGRES_VERSION \
    && apt-get -y autoremove \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

RUN setcap "cap_net_bind_service=+ep" /usr/bin/php7.3

RUN groupadd --force -g $WWWGROUP sail
RUN useradd -ms /bin/bash --no-user-group -g $WWWGROUP -u 1337 sail

COPY start-container /usr/local/bin/start-container
COPY supervisord.conf /etc/supervisor/conf.d/supervisord.conf
COPY php.ini /etc/php/7.3/cli/conf.d/99-sail.ini
RUN chmod +x /usr/local/bin/start-container

EXPOSE 8000

ENTRYPOINT ["start-container"]

基本上就是把版號都改成 7.3，然後也要改根目錄的 docker-compose.yml，也是把預設的 8.1 都改成 7.3

# For more information: https://laravel.com/docs/sail
version: '3'
services:
    laravel.test:
        build:
            context: ./docker/8.1
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.1/app
#         ...

之後重新 build

sail build --no-cache

再來一樣啟動服務

sail up -d

這時可能會出錯(服務有起來，但無法瀏覽)，因為你外部當初產這專案的時候的 PHP 版本可能太新，所以當初下載下來的套件可能不支援舊版，所以需要切進 container 中更新套件。

可以看 Log，可能會有一坨這個

Composer detected issues in your platform:

Your Composer dependencies require a PHP version ">= 8.1.0". You are running 7.3.33-5+ubuntu20.04.1+deb.sury.org+1.

PHP Fatal error:  Composer detected issues in your platform: Your Composer dependencies require a PHP version ">= 8.1.0". You are running 7.3.33-5+ubuntu20.04.1+deb.sury.org+1. in /var/www/html/vendor/composer/platform_check.php on line 24
2022-08-15 12:51:47,290 INFO success: php entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2022-08-15 12:51:47,290 INFO exited: php (exit status 255; not expected)
2022-08-15 12:51:48,292 INFO spawned: 'php' with pid 246

可以先用 docker ps 看一下容器的 id 或是你要用 name 切進去也行。

docker exec -it <CONTAINER ID> bash

進去之後直接下 composer update，之後應該就可以了

然後一樣先寫死 /routes/web.php ，看看是不是真的改版本了

<?php
use Illuminate\Support\Facades\Route;

Route::get('/', function () {
    phpinfo();
});

應該就會看到類似這樣 PHP Version 7.3.33-5+ubuntu20.04.1+deb.sury.org+1 的東西，至少他是 7.3 開頭的版本。

`Mysql8` 爆炸了?

如果你是選預設的，可能就會炸掉，當你用到資料庫可能會看到類似這種訊息

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

原因是在 Mysql8 使用新的密碼驗證方式(caching_sha2_password)，可以在 phpinfo() 看看支不支援，舊版的是 mysql_native_password，所以也可以把使用者的密碼改一下(使用舊的驗證方式)，像這樣:

ALTER USER 'sail' IDENTIFIED WITH mysql_native_password BY 'password';

基本上跑完這一行應該就正常了，因為 Laravel sail 他預設的 Mysql 使用者就是 sail 而密碼預設是 password。

改成 `MSSQL`

上面繞了一大圈，終於又要進入正題了，把資料庫換成 MSSQL。

在 /docker 建立一個目錄 mssql 放相關的 Dockerfile 檔案

/docker/mssql/dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest

RUN mkdir -p /usr/config
WORKDIR /usr/config

COPY . /usr/config

ENTRYPOINT ["./entrypoint.sh"]

/docker/mssql/entrypoint.sh 負責設定資料庫與開啟服務

#!/bin/bash


/usr/config/configure-db.sh &
/opt/mssql/bin/sqlservr

/docker/mssql/configure-db.sh 服務起來之後先開一個表給之後 migration 用

#!/bin/bash


ERRCODE=1
CREATE_STATUS=1
i=0

while [[ $i -lt 60 ]] && [[ $ERRCODE -ne 0 ]]; do
    i=$i+1
    /opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P "$MSSQL_SA_PASSWORD" -Q "SET NOCOUNT ON; Select 1" > /dev/null
    ERRCODE=$?
    sleep 1
done

if [ "$ERRCODE" -ne "0" ]; then 
    echo "SQL Server took more than 60 seconds to start up or one or more databases are not in an ONLINE state"
    exit 1
fi

CREATE_STATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P "$MSSQL_SA_PASSWORD" -Q "CREATE DATABASE $MSSQL_DB_DATABASE")
if [ ! -z "$CREATE_STATUS" ]; then 
    echo "Warning: $CREATE_STATUS"
fi

echo "configure-db finish."

再來要修改 `docker-compose.yml`

這邊我把 volumes 直接拿掉了，因為我只是拿他當開發用，在對方提供的 windows server 上已經有裝好的 SQL Server，所以我不需要。

也就是整個 docker-compose.yml 會變成這樣:

# For more information: https://laravel.com/docs/sail
version: '3'
services:
    laravel.test:
        build:
            context: ./docker/7.3
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-7.3/app
        extra_hosts:
            - 'host.docker.internal:host-gateway'
        ports:
            - '${APP_PORT:-80}:80'
            - '${VITE_PORT:-5173}:${VITE_PORT:-5173}'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
            XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'
            XDEBUG_CONFIG: '${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'
        volumes:
            - '.:/var/www/html'
        networks:
            - sail
        depends_on:
            - mssql
    mssql:
        build:
            context: ./docker/mssql
            dockerfile: Dockerfile
        image: 'mssql-custom'
        ports:
            - '${FORWARD_DB_PORT:-1433}:1433'
        environment:
            ACCEPT_EULA: 'Y'
            MSSQL_SA_PASSWORD: '${DB_PASSWORD}'
            MSSQL_DB_DATABASE: '${DB_DATABASE}'
        networks:
            - sail
networks:
    sail:
        driver: bridge

再來一樣重新 build:

sail build --no-cache

然後在 up 之前，要注意先去修改 .env 的帳號密碼，帳號預設是 sa，而密碼比較嚴格，需要至少 8 個字以上，且符合以下條件至少三種:

大寫
小寫
數字
符號

都改好之後就可以把服務起起來試試看:

sail up -d

不放心的話你可以看看 log 有沒有出現 configure-db finish. 這樣的東西。

修改 `Database Driver`

把 env 改成這樣

DB_CONNECTION=sqlsrv
DB_HOST=mssql
DB_PORT=1433

這時候你嘗試去跑 sail artisan migrate 應該會噴出 could not find driver 這樣的錯誤訊息，這是因為你的 php 沒有裝 sqlsrv 的 driver，所以要去修改 php 的 Dockerfile

這邊需要改的有點多，先講大概改了什麼，最後會再貼一個完整版

&& apt-get install -y mysql-client \
改成
&& apt-get install -y gcc \
       musl-dev \
       make \

在很長的那串 RUN 後面串上

    ..... \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql18  \
    && apt-get install -y unixodbc unixodbc-dev  \
    && pecl install sqlsrv \
    && pecl install pdo_sqlsrv \
    && printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/8.1/mods-available/sqlsrv.ini \
    && printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/8.1/mods-available/pdo_sqlsrv.ini \
    && phpenmod sqlsrv pdo_sqlsrv

總之最後以我 demo 的 7.3 版本，會長成下面這樣 (很多沒用到的東西可以自己刪掉)

FROM ubuntu:20.04

LABEL maintainer="Taylor Otwell"

ARG WWWGROUP
ARG NODE_VERSION=16
ARG POSTGRES_VERSION=13

WORKDIR /var/www/html

ENV DEBIAN_FRONTEND noninteractive
ENV TZ=UTC

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN apt-get update \
    && apt-get install -y gnupg gosu curl ca-certificates zip unzip git supervisor sqlite3 libcap2-bin libpng-dev python2 \
    && mkdir -p ~/.gnupg \
    && chmod 600 ~/.gnupg \
    && echo "disable-ipv6" >> ~/.gnupg/dirmngr.conf \
    && echo "keyserver hkp://keyserver.ubuntu.com:80" >> ~/.gnupg/dirmngr.conf \
    && gpg --recv-key 0x14aa40ec0831756756d7f66c4f4ea0aae5267a6c \
    && gpg --export 0x14aa40ec0831756756d7f66c4f4ea0aae5267a6c > /usr/share/keyrings/ppa_ondrej_php.gpg \
    && echo "deb [signed-by=/usr/share/keyrings/ppa_ondrej_php.gpg] https://ppa.launchpadcontent.net/ondrej/php/ubuntu focal main" > /etc/apt/sources.list.d/ppa_ondrej_php.list \
    && apt-get update \
    && apt-get install -y php7.3-cli php7.3-dev \
       php7.3-pgsql php7.3-sqlite3 php7.3-gd \
       php7.3-curl php7.3-memcached \
       php7.3-imap php7.3-mysql php7.3-mbstring \
       php7.3-xml php7.3-zip php7.3-bcmath php7.3-soap \
       php7.3-intl php7.3-readline php7.3-pcov \
       php7.3-msgpack php7.3-igbinary php7.3-ldap \
       php7.3-redis php7.3-xdebug \
    && php -r "readfile('https://getcomposer.org/installer');" | php -- --install-dir=/usr/bin/ --filename=composer \
    && curl -sLS https://deb.nodesource.com/setup_$NODE_VERSION.x | bash - \
    && apt-get install -y nodejs \
    && npm install -g npm \
    && curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | gpg --dearmor | tee /usr/share/keyrings/yarnkey.gpg >/dev/null \
    && echo "deb [signed-by=/usr/share/keyrings/yarnkey.gpg] https://dl.yarnpkg.com/debian/ stable main" > /etc/apt/sources.list.d/yarn.list \
    && curl -sS https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /usr/share/keyrings/pgdg.gpg >/dev/null \
    && echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt focal-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
    && apt-get update \
    && apt-get install -y yarn \
    && apt-get install -y gcc \
       musl-dev \
       make \
    && apt-get install -y postgresql-client-$POSTGRES_VERSION \
    && apt-get -y autoremove \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/* \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql18  \
    && apt-get install -y unixodbc unixodbc-dev  \
    && pecl install sqlsrv \
    && pecl install pdo_sqlsrv \
    && printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.3/mods-available/sqlsrv.ini \
    && printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.3/mods-available/pdo_sqlsrv.ini \
    && phpenmod sqlsrv pdo_sqlsrv 

RUN setcap "cap_net_bind_service=+ep" /usr/bin/php7.3

RUN groupadd --force -g $WWWGROUP sail
RUN useradd -ms /bin/bash --no-user-group -g $WWWGROUP -u 1337 sail

COPY start-container /usr/local/bin/start-container
COPY supervisord.conf /etc/supervisor/conf.d/supervisord.conf
COPY php.ini /etc/php/7.3/cli/conf.d/99-sail.ini
RUN chmod +x /usr/local/bin/start-container

EXPOSE 8000

ENTRYPOINT ["start-container"]

再來老樣子，重 Build !

sail build --no-cache

然後再跑一次 migrate，錯誤變成了下面這樣:

SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate] (SQL: select * 
from sys.sysobjects where id = object_id(migrations) and xtype in ('U', 'V'))

這裡可以選擇去處理憑證，又或者修改一下 Laravel 的設定。

幫 /config/database.php 的 connections.sqlsrv 加上一條 trust_server_certificate

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '1433'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    // 下面這條
    'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
],

然後再到 env 設定一下:

DB_TRUST_SERVER_CERTIFICATE=true

這時你在 migrate 一次，就會很開心的看到他成功了

Migration table created successfully.

稍微 seed 玩一下，/database/seeders/DatabaseSeeder.php

<?php
  public function run()
  {
      \App\Models\User::factory(10)->create();
  }

然後跑 seed

sail artisan db:seed

然後一樣先寫死測一下 /routes/web.php

<?php
use Illuminate\Support\Facades\Route;
use App\Models\User;

Route::get('/', function () {
    dd(User::get());
});

就會看到像是這樣的東西

Illuminate\Database\Eloquent\Collection {#1220 ▼
  #items: array:10 [▶]
  #escapeWhenCastingToString: false
}

到這邊就成功了!

最後

如果懶得改 php 的 Dockerfile 可以用我自己包的一個 8.1 的版本，這樣你的 Dockerfile 只要一行。

FROM laijunbin/laravel-sail-sqlsrv:8.1

可以在 DockerHub 上面看到(~~雖然我不知道什麼時候才會補內容，不過可以先看內容確定東西正常~~)

下一篇會來說明如何整合 Github Actions 做到 CI/CD。

最後更新時間: 2022年08月15日.

Laravel 連接 MSSQL

tags: Laravel MSSQL

category: Back-End

description: Laravel 連接 MSSQL

created_at: 2022/08/15 19:00:00

前言

為什麼使用 Laravel Sail

老樣子先開新專案

安裝 WSL2 與 Docker

安裝 laravel/sail

彈出 Dockerfile