Cara menggunakan docker-php-ext-install sqlsrv

Hai semuanya, di materi study kasus kali ini kita akan membahas tentang menggunakan Database pada project Laravel. Diantaranya

1. Setup and Configure connection to Database
2. Database: Migration
3. Basic CRUD using fluent query builder
4. Deploy Manualy ke Server
5. Build & Running Docker Image
6. Cleanup

Ok langsung aja kita ke pembahasan yang pertama

Setup and Configure connection to Database

Almost every modern web application interacts with a database. Laravel makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder, and the Eloquent ORM. Currently, Laravel provides first-party support for four databases:

1. MySQL php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate6
2. PostgreSQL php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate7
3. SQLite php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate8
4. SQL Server php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate9

Sebagai study kasus kali ini kita akan menggunakan database MySQL 5.7 yang akan saya jalankan menggunakan docker seperti berikut:

For Bash script:

For Powershell script:

The configuration for Laravel’s database services is located in your application’s ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 0 configuration file. Most of the configuration options within this file are driven by the values of your application’s environment variables

Kita edit file ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 1 menjadi seperti berikut:

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=docker_laravel DB_USERNAME=docker_laravel DB_PASSWORD=docker_laravel

Setelah itu kita bisa check connectionnya menggunakan perintah

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate

Jika dijalankan maka hasilnya seperti berikut:

➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+

Dengan konfigurasi seperti berikut, kita sudah bisa connect antara PHP Laravel dengan Database MySQL v5.7

Database: Migration

Migrations are like version control for your database, allowing your team to define and share the application’s database schema definition. The new migration will be placed in your ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 2 directory. Each migration filename contains a timestamp that allows Laravel to determine the order of the migrations:

php artisan make:migration create_mahasiswa_table

Seperti berikut

Selanjutnya kita akan mengisi table ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 3 menggunakan Seeder, untuk membuat seeder kita bisa menggunakan perintah seperti berikut:

php artisan make:seeder MahasiswaSeeder

Seperti berikut:

Dan kita edit file ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 4 seperti berikut:

Selanjutnya kita jalankan perintah berikut:

php artisan migrate --seed

Kemudian coba check data di table ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 3, sekarang memiliki data seperti berikut contohnya:

Ok sekarang kita sudah success membuat database migration

Basic CRUD using fluent query builder

Setelah kita setup connection dan membuat migration table, sekarang kita buat simple CRUD yang di expose lewat Rest API. Sekarang kita buat controller baru menggunakan perintah seperti berikut:

php artisan make:controller MahasiswaDbController

Seperti berikut:

Kemudian tambahkan config route seperti berikut pada file ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 6:

Untuk melakukan testing kita buat file ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 7 seperti berikut:

Setelah semuanya berjalan dengan baik, sekarang kita bisa buat UInya dengan membuat file pada folder ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 8 misalnya dengan nama ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 9 seperti berikut:

Tambahkan juga dependency pada client yaitu php artisan make:migration create_mahasiswa_table0 dengan perintah seperti berikut:

npm install --save jquery-serializejson && \ npm install --save-dev less-loader sass-loader stylus-loader npm run-script dev

Dan yang terakhir tambahakan route untuk viewnya pada file php artisan make:migration create_mahasiswa_table1 seperti berikut:

Jika dijalankan dengan perintah seperti berikut:

php artisan serve

Kemudian akses dari browser dengan alamat localhost:8000/db maka hasilnya seperti berikut:

Deploy Manualy ke Server

Setelah kita mendevelop feature CRUD ke Database MySQL di local kita, sekarang kita akan deploy ke Server. Jadi workflownya

1. Install Database MySQL
2. Create User & Database
3. Upload source-code terbaru ke folder php artisan make:migration create_mahasiswa_table2
4. Update configuration ➜ docker-laravel git:(master) php artisan migrate:install Migration table created successfully. ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | No | 2014_10_12_000000_create_users_table | | | No | 2014_10_12_100000_create_password_resets_table | | | No | 2019_08_19_000000_create_failed_jobs_table | | +------+------------------------------------------------+-------+ ➜ docker-laravel git:(master) php artisan migrate Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (34.42ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (26.96ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (28.19ms) ➜ docker-laravel git:(master) php artisan migrate:status +------+------------------------------------------------+-------+ | Ran? | Migration | Batch | +------+------------------------------------------------+-------+ | Yes | 2014_10_12_000000_create_users_table | 1 | | Yes | 2014_10_12_100000_create_password_resets_table | 1 | | Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | +------+------------------------------------------------+-------+ 1
5. Install PHP Extension php artisan make:migration create_mahasiswa_table4, php artisan make:migration create_mahasiswa_table5
6. Jalankan perintah php artisan make:migration create_mahasiswa_table6
7. Jalankan perintah php artisan make:migration create_mahasiswa_table7
8. Restart service php artisan make:migration create_mahasiswa_table8

Untuk installasi MySQL Database kita bisa install manual atau menggunakan Docker, jadi supaya simple kita buat di docker aja dengan menggunakan perintah yang tadi yaitu seperti berikut:

Setelah itu install PHP Extension untuk module php artisan make:migration create_mahasiswa_table9 dan php artisan make:migration create_mahasiswa_table5 seperti berikut perintahnya:

export PHP_VERSION=8.0 apt install -y php${PHP_VERSION}-mysql mysql-client && \ systemctl restart apache2

Kemudian kita upload source-code terbaru dengan menggunakan perintah:

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate0

Setelah itu kita install kita lakukan migrate mengunakan perintah

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate1

Sekarang kita bisa coba akses, hasilnya seperti berikut:

Build & Running Docker Image

Ok setelah kita deploy cara manual sekarang kita build Docker imagenya, pertama kita akan update dulu file php artisan make:seeder MahasiswaSeeder1 seperti berikut:

Jadi karena saya mau semua proses dilakukan automatis mulai deploy, migrate database. Kita akan modifikasi entrypoint dan command dalam php artisan make:seeder MahasiswaSeeder1, Kita download dulu file apache2-foreground dari source-code docker-library php github repository. Kemudian edit dan simpan dalam folder php artisan make:seeder MahasiswaSeeder3 menjadi seperti berikut:

Setelah itu kita build dengan perintah seperti berikut:

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate2

Jika dijalankan maka hasilnya seperti berikut:

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate3

Sekarang kita coba jalankan containernya dengan perintah seperti berikut:

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate4

Jika dijalankan maka hasilnya seperti berikut:

php artisan migrate:install && \ php artisan migrate:status && \ php artisan migrate5

Sekarang coba akses dari browser, maka hasilnya seperti berikut:

Cleanup

Seperti biasa setelah kita mencoba schenario studi kasus tersebut. sekarang kita bersih-bersih dulu ya berikut perintahnya: