title	summary
App Development for Laravel	Learn how to build a simple PHP application based on TiDB and Laravel.

App Development for Laravel

Note:

This document has been archived. This indicates that this document will not be updated thereafter. You can see Developer Guide Overview for more details.

This tutorial shows you how to build a simple PHP application based on TiDB with Laravel. The sample application to build here is a simple CRM tool where you can add, query, and update customer and order information.

Step 1. Start a TiDB cluster

Start a pseudo TiDB cluster on your local storage:

docker run -p 127.0.0.1:$LOCAL_PORT:4000 pingcap/tidb:v5.1.0

The above command starts a temporary and single-node cluster with mock TiKV. The cluster listens on the port $LOCAL_PORT. After the cluster is stopped, any changes already made to the database are not persisted.

Note:

To deploy a "real" TiDB cluster for production, see the following guides:

Deploy TiDB using TiUP for On-Premises

Deploy TiDB on Kubernetes

You can also use TiDB Cloud, a fully-managed Database-as-a-Service (DBaaS), which offers free trial.

Step 2. Create a database

In the SQL shell, create the laravel_demo database that your application will use:

{{< copyable "" >}}
```
CREATE DATABASE laravel_demo;
```
Create a SQL user for your application:

{{< copyable "" >}}
```
CREATE USER <username> IDENTIFIED BY <password>;
```
Take note of the username and password. You will use them in your application code when initializing the project.
Grant necessary permissions to the SQL user you have just created:

{{< copyable "" >}}
```
GRANT ALL ON laravel_demo.* TO <username>;
```

Step 3. Prepare your Laravel project

Install Composer.

Laravel uses Composer, a dependency manager for PHP, to manage its dependencies. Before using Laravel, make sure you have Composer installed on your machine:

{{< copyable "" >}}
```
brew install composer
```
Note:

The installation method might vary depending on your platform. See Installation - Linux / Unix / macOS for more details.
Install Laravel.

Download the Laravel installer and install the Laravel framework using Composer:

{{< copyable "" >}}
```
composer global require laravel/installer
```
Create a project.

Now that you have Laravel installed, you can start a project using the following command:

{{< copyable "" >}}
```
laravel new laravel-demo
```
Edit the configuration.

After creating your Laravel project, you need to edit the configuration file for the application to connect to TiDB:

{{< copyable "" >}}
```
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=4000
DB_DATABASE=laravel_demo
DB_USERNAME=root
DB_PASSWORD=
```

Step 4. Write the application logic

After you have configured the application's database connection, you can start building out the application. To write the application logic, you need to define the models, create the controller, and update the URL routes.

Define modules

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

Use the make:model Artisan command to generate a new model and generate a database migration:

{{< copyable "" >}}
```
php artisan make:model Order -m
php artisan make:model Customer -m
```
The new migration will be placed in your database/migrations directory.

Edit the database/migrations/2021_10_08_064043_order.php file to create the order table. File names will change over time.

<?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('order', function (Blueprint $table) {
            $table->bigIncrements('oid');
            $table->bigInteger('cid');
            $table->float('price');
        });
    }

    /**
    * Reverses the migrations.
    *
    * @return void
    */
    public function down()
    {
        Schema::dropIfExists('order');
    }
}

Edit the database/migrations/2021_10_08_064056_customer.php file to create the customer table. File names will change over time.

<?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('customer', function (Blueprint $table) {
            $table->bigIncrements('cid');
            $table->string('name',100);
        });
    }

    /**
     * Reverses the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('customer');
    }
}

Use the migrate Artisan command to generate tables.

> $ php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (634.92ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (483.58ms)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (456.25ms)
Migrating: 2019_12_14_000001_create_personal_access_tokens_table
Migrated:  2019_12_14_000001_create_personal_access_tokens_table (877.47ms)
Migrating: 2021_10_08_081739_create_orders_table
Migrated:  2021_10_08_081739_create_orders_table (154.53ms)
Migrating: 2021_10_08_083522_create_customers_table
Migrated:  2021_10_08_083522_create_customers_table (82.02ms)

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

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = 'order';

    protected $primaryKey = 'oid';

    public $timestamps = false;

    protected $fillable = [
        'cid',
        'price',
    ];

    protected $guarded = [
        'oid',
    ];

    protected $casts = [
        'uid'   => 'real',
        'price' => 'float',
    ];

    use HasFactory;
}

Edit the app/Models/Customer.php file to tell the framework which table to use for our customer model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Customer extends Model
{
    use HasFactory;
    protected $table = 'customer';

    protected $primaryKey = 'cid';

    public $timestamps = false;

    protected $fillable = [
        'name',
    ];

    protected $guarded = [
        'cid',
    ];

    protected $casts = [
        'name'  => 'string',
        'cid' => 'int',
    ];
}

Create the controller

To create the controller via the command line, run the following commands:

{{< copyable "" >}}
```
php artisan make:controller CustomerController
php artisan make:controller OrderController
```

Edit app/Http/Controllers/CustomerController.php to control the action against the customer table.

<?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 app/Http/Controllers/OrderController.php to control the action against the order table.

<?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]);
    }

    public function delete($oid)
    {
        return Order::where('oid', $oid)->delete();
    }

    public function updateByOid(Request $request, $oid)
    {
        return Order::where('oid', $oid)->update(['price' => $request->price]);
    }

    public function queryByCid(Request $request)
    {
        return Order::where('cid', $request->query('cid'))->get();
    }
}

Update the URL routes

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

<?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::get('/customer/{id}', 'App\Http\Controllers\CustomerController@getByCid');
Route::post('/customer', 'App\Http\Controllers\CustomerController@insert');


Route::post('/order', 'App\Http\Controllers\OrderController@insert');
Route::delete('/order/{oid}', 'App\Http\Controllers\OrderController@delete');
Route::post('/order/{oid}','App\Http\Controllers\OrderController@updateByOid');
Route::get('/order','App\Http\Controllers\OrderController@queryByCid');

Step 5. Run the Laravel application

If you have PHP installed locally and you would like to use PHP's built-in development server to serve your application, you can use the serve Artisan command to start a development server at http://localhost:8000:

php artisan serve

To test the application by inserting some example data, run the following commands:

curl --location --request POST 'http://127.0.0.1:8000/api/customer' --form 'name="Peter"'

curl --location --request POST 'http://127.0.0.1:8000/api/order' --form 'cid=1' --form 'price="3.12"'

curl --location --request POST 'http://127.0.0.1:8000/api/order/1' --form 'price="312"'

curl --location --request GET 'http://127.0.0.1:8000/api/order?cid=1'

To verify whether the insertion is successful, execute the following statement in the SQL shell:

MySQL root@127.0.0.1:(none)> select * from laravel_demo.order;
+-----+-----+-------+
| oid | uid | price |
+-----+-----+-------+
| 1   | 1   | 312.0 |
+-----+-----+-------+
1 row in set
Time: 0.008s

The result above shows that the data insertion is successful.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

dev-guide-outdated-for-laravel.md

dev-guide-outdated-for-laravel.md

App Development for Laravel

Step 1. Start a TiDB cluster

Step 2. Create a database

Step 3. Prepare your Laravel project

Step 4. Write the application logic

Define modules

Create the controller

Update the URL routes

Step 5. Run the Laravel application

Files

dev-guide-outdated-for-laravel.md

Latest commit

History

dev-guide-outdated-for-laravel.md

File metadata and controls

App Development for Laravel

Step 1. Start a TiDB cluster

Step 2. Create a database

Step 3. Prepare your Laravel project

Step 4. Write the application logic

Define modules

Create the controller

Update the URL routes

Step 5. Run the Laravel application