Getting Started with TiDB Cloud Using PHP and Laravel

TiDB Cloud is a fully-managed Database-as-a-Service (DBaaS) that brings everything great about TiDB to your cloud and lets you focus on your applications, not the complexities of your database.

In this tutorial, you will learn how to use TiDB Cloud as the backend database for your PHP application. We will be using PHP and Laravel to build a simple web application. We will:

Start a TiDB database in the TiDB cloud;
Install Laravel on our local machine;
Write the application logic;
Run the Laravel application.

Before You Begin

Install PHP (v. 8+).
Install Composer.
Use Composer to install Laravel.

Create a TiDB Developer Tier cluster

If you do not have a TiDB Cloud account, click here to sign up for an account.
Log in to your TiDB Cloud account.
On the landing page, click Create a Cluster.
On the plan selection page, in the Developer Tier plan, click Get Started for Free.
On the Create a Cluster page, set up your cluster name and root password. (In this tutorial, we will call our cluster demo.)
Select the cloud service region which is close to you to create the cluster. The default is AWS.
Select the cluster tier. The default is S1.dev.
Click Submit.

Your TiDB Cloud cluster will be created in approximately 5 to 10 minutes.

Connect to TiDB Cloud

Go to https://tidbcloud.com/console/clusters and sign in to your cluster.
On the TiDB Cloud console, in the upper right side of the panem click Connect.
On the Connect to TiDB dialog book, create the traffic filter for the cluster.
1. In Step 1, click Allow Access from Anywhere. (This option is OK for the purpose of this tutorial, but is not recommended for production.)
2. Click Create Filter.
Under the Web SQL Shell tab, click Open SQL Shell and enter the password for the cluster. You are now able to write SQL commands.

Create a database

In the SQL shell, create the laravel_demo database that your application will use:
```
CREATE DATABASE laravel_demo;
```
Create a database user. The user name is ‘demo_client‘, and you should set your own password to replace ‘<pwd>‘; for example ‘PingCAP1‘.
```
CREATE USER 'demo_client' IDENTIFIED BY '<pwd>';
```
Grant all privileges to the user you just created. Again, this is for tutorial purposes and is not recommended for production:
```
GRANT ALL PRIVILEGES ON *.* TO 'demo_client';
```

Prepare your Laravel project

Get the host address for TiDB Cloud. You will be entering this address in a later step. On the home page of TiDB cloud, click Connect. In the Connect to TiDB page, you can find the host address in Standard Connection – Step 2, in the format of xxx.xxxx.xxxx.xxxx.prod.aws.tidbcloud.com.

Create a Laravel project called laravel-demo:

composer create-project laravel/laravel laravel-demo

You can see a directory created called laravel-demo. Go to that directory:
```
cd laravel-demo
```

Edit the .env configuration file. Replace DB_HOST with the host address of TiDB Cloud, and <pwd> with your password:

DB_CONNECTION=mysql
DB_HOST=xxx.xxxx.xxxx.xxxx.prod.aws.tidbcloud.com
DB_PORT=4000
DB_DATABASE=laravel_demo
DB_USERNAME=demo_client
DB_PASSWORD=<pwd>

Write the application

After you configure the application’s database connection, you can build the application. To write the application logic, you need to define the models, create the controller, and update the URL routes.

Define models

Laravel uses the Eloquent model, an Object Relational Mapping (ORM) framework, to interact with the table. Models are typically placed in the app\Models directory. Use the following steps to create models and map the models with the corresponding table:

Generate a new model and migrate the database. Under the root directory, use the make:model Artisan command:
```
php artisan make:model Order -m
```
```
php artisan make:model Customer -m
```

Create the order table. In the database/migrations directory, open the <recent_time_stamp>_create_orders_table.php file:

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateOrdersTable extends Migration
{
    /**
    * Runs the migrations.
    *
    * @return void
    */
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('oid');
            $table->bigInteger('cid');
            $table->float('price');
        });
    }
    /**
    * Reverses the migrations.
    *
    * @return void
    */
    public function down()
    {
        Schema::dropIfExists('orders');
    }
}
?>

Create the new customer table. In the database/migrations directory, open the <recent_time_stamp>_create_customers_table.php:

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateCustomersTable extends Migration
{
    /**
     * Runs the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('customers', function (Blueprint $table) {
            $table->bigIncrements('cid');
            $table->string('name',100);
        });
    }
    /**
     * Reverses the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('customers');
    }
}
?>

Generate new tables. Make sure you’re in the project root folder and use the migrate Artisan command:
```
php artisan migrate
```
Navigate to the app/Models directory.

Specify which table the framework should use for the Order model. Edit the app/Models/Order.php file:

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Order extends Model
{
    protected $table = 'orders';
    protected $primaryKey = 'oid';
    public $timestamps = false;
    protected $fillable = [
        'cid',
        'price',
    ];
    protected $guarded = [
        'oid',
    ];
    protected $casts = [
        'uid'   => 'real',
        'price' => 'float',
    ];
    use HasFactory;
}
?>

Specify which table to use for the customer model. Edit the app/Models/Customer.php:

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Customer extends Model
{
    use HasFactory;
    protected $table = 'customers';
    protected $primaryKey = 'cid';
    public $timestamps = false;
    protected $fillable = [
        'name',
    ];
    protected $guarded = [
        'cid',
    ];
    protected $casts = [
        'name'  => 'string',
        'cid' => 'int',
    ];
}
?>

Create the controller

Create the controller. On the command line, enter:

php artisan make:controller CustomerController
php artisan make:controller OrderController

Edit the controller against the customer table. Edit the app/Http/Controllers/CustomerController.php file.

<?php
namespace App\Http\Controllers;

use App\Models\Customer;
use Illuminate\Http\Request;

class CustomerController extends Controller
{
     public function getByCid($cid)
     {
       $customer_info = Customer::where('cid',$cid)->get();
        if ($customer_info->count() > 0){
         return $customer_info;
           }
           return abort(404);
        }
       public function insert(Request $request) {
             return Customer::create(['name' => $request->name]);
       }
  }
 ?>

Edit the controller against the order table. Edit the app/Http/Controllers/OrderController.php file:

<?php

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Http\Request;

class OrderController extends Controller
{
     public function insert(Request $request) {
          return Order::create(['cid' => $request->cid, 'price' => $request->price]);
     }
}
 ?>

Update the URL routes

URL routing allows you to configure an application to accept request URLs. Most of the routes for your application are defined in the routes/api.php file. The simplest Laravel routes consist of a URI and a Closure callback.

<?php

use Illuminate\Http\Request;

use Illuminate\Support\Facades\Route;

use App\Http\Controllers\customerController;

/*

|--------------------------------------------------------------------------

| API Routes

|--------------------------------------------------------------------------

|

| Here is where you can register API routes for your application. These

| routes are loaded by the RouteServiceProvider within a group which

| is assigned the "api" middleware group. Enjoy building your API!

|

*/

Route::middleware('auth:sanctum')->get('/user', function (Request $request) {

    return $request->user();

});

Route::post('/customer', 'App\Http\Controllers\CustomerController@insert');

Route::post('/order', 'App\Http\Controllers\OrderController@insert');
?>

Run the Laravel application

Run the following command to start your Laravel application:
```
php artisan serve
```
Test the application by inserting some example data. Open a new terminal window, and run the following commands:
- Insert a customer called “Peter” into the customer table.
```
curl --location --request POST 'http://127.0.0.1:8000/api/customer' --form 'name="Peter"'
```
- Insert a record into the order table.
```
curl --location --request POST 'http://127.0.0.1:8000/api/order' --form 'cid=1' --form 'price="3.12"'
```

Verify whether the insertion is successful. The web SQL shell, run the following statement:

select * from laravel_demo.orders;

oid | cid | price
-----+-----+-------
1   | 1   | 3.12
(1 rows)

The result above shows that the data insertion is successful. You can also check whether the data has been inserted into the customers table.

Want to learn more?

Ready to give TiDB Cloud a try? TiDB Cloud Developer Tier is now available! You can run a TiDB cluster for free for one year on Amazon Web Services. Make sure to follow us on Twitter to stay updated on TiDB Cloud news!

Book a Demo