Connect to OceanBase Database by using Laravel Eloquent ORM

Laravel is an elegant PHP web development framework. The built-in Eloquent ORM provides a concise ActiveRecord implementation, making database operations simple and intuitive.

This topic describes how to use Laravel Eloquent ORM to connect to OceanBase Database and perform basic operations such as creating tables, inserting data, and querying data.

Prerequisites

• You have installed PHP 8.0 or later.
• You have installed the Composer package management tool.
• You have installed OceanBase Database and created a MySQL tenant.
• You have obtained the connection string of OceanBase Database.

Procedure

1. Obtain the connection string of OceanBase Database.
2. Create a Laravel project and install the required dependencies.
3. Configure the database connection.
4. Create models and controllers.
5. Write the database operation code.
6. Run the program and verify the results.

Step 1: Obtain the connection string of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection string.

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connecting to OceanBase Database. If you use OceanBase Database Proxy (ODP) to connect to OceanBase Database, use the IP address of an ODP node. If you directly connect to OceanBase Database, use the IP address of an OBServer node.
• $port: the port for connecting to OceanBase Database. If you use ODP to connect to OceanBase Database, the default port is 2883, which can be customized when you deploy ODP. If you directly connect to OceanBase Database, the default port is 2881, which can be customized when you deploy OceanBase Database.
• $database_name: the name of the database to be accessed.
• $user_name: the account for connecting to the tenant. The format for connecting to a tenant by using ODP is username@tenant name#cluster name or cluster name:tenant name:username. The format for directly connecting to a tenant is username@tenant name.
• $password: the password of the account.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install Laravel and its dependencies

1. Run the following command to create a new Laravel project by using Composer:

   composer create-project laravel/laravel laravel-oceanbase
   cd laravel-oceanbase
   

2. Run the following command to verify the installation:

   php artisan --version
   

Step 3: Configure the database connection

Edit the config/database.php file and configure the MySQL connection options:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '2881'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Step 4: Create a model and a controller

1. Create a user model app/Models/User.php:

   <?php
   
   namespace App\Models;
   
   use Illuminate\Database\Eloquent\Model;
   use Illuminate\Support\Facades\Schema;
   
   class User extends Model
   {
       protected $table = 'users';
   
       protected $fillable = ['username', 'email', 'age'];
   
       /**
        * Create the users table.
        */
       public static function createTable()
       {
           if (!Schema::hasTable('users')) {
               Schema::create('users', function ($table) {
                   $table->id();
                   $table->string('username', 100);
                   $table->string('email', 100)->unique();
                   $table->integer('age')->nullable();
                   $table->timestamps();
               });
   
               return true;
           }
           return false;
       }
   }
   

Step 5: Write database operation code

1. Create the app/Http/Controllers/UserController.php controller:

   <?php
   
   namespace App\Http\Controllers;
   
   use App\Models\User;
   use Illuminate\Http\Request;
   
   class UserController extends Controller
   {
       // Create the user table
       public function __construct()
       {
           User::createTable();
       }
   
       // Create a user
       public function create(Request $request)
       {
           $user = User::create([
               'username' => $request->input('username'),
               'email' => $request->input('email'),
               'age' => $request->input('age'),
           ]);
   
           return response()->json($user);
       }
   
       // Query a user
       public function show($id)
       {
           $user = User::findOrFail($id);
           return response()->json($user);
       }
   
       // Update a user
       public function update(Request $request, $id)
       {
           $user = User::findOrFail($id);
           $user->update([
               'username' => $request->input('username', $user->username),
               'email' => $request->input('email', $user->email),
               'age' => $request->input('age', $user->age),
           ]);
   
           return response()->json($user);
       }
   
       // Delete a user
       public function delete($id)
       {
           $user = User::findOrFail($id);
           $user->delete();
   
           return response()->json(['message' => 'User deleted successfully']);
       }
   
       // Query all users
       public function index()
       {
           $users = User::all();
           return response()->json($users);
       }
   }
   

2. Add the following route to the routes/api.php file:

   <?php
   
   use Illuminate\Support\Facades\Route;
   use App\Http\Controllers\UserController;
   
   // Manually add the /api prefix
   Route::prefix('api')->group(function () {
       Route::get('/users', [UserController::class, 'index']);
       Route::post('/users', [UserController::class, 'create']);
       Route::get('/users/{id}', [UserController::class, 'show']);
       Route::put('/users/{id}', [UserController::class, 'update']);
       Route::delete('/users/{id}', [UserController::class, 'delete']);
   });
   

3. Modify the app/Providers/AppServiceProvider.php file:

   <?php
   
   namespace App\Providers;
   
   use Illuminate\Support\ServiceProvider;
   
   class AppServiceProvider extends ServiceProvider
   {
       /**
       * Register any application services.
       */
       public function register(): void
       {
           //
       }
   
       /**
       * Bootstrap any application services.
       */
       public function boot(): void
       {
           //$this->loadRoutesFrom(base_path('routes/api.php'));
       }
   }
   

Step 6: Run the program and verify the results

1. Start the development server:

   php artisan serve
   

2. Use an API tool such as Postman to test the following interfaces:

   • GET http://localhost:8000/api/users - Get the user list
   • POST http://localhost:8000/api/users - Create a new user
   • GET http://localhost:8000/api/users/1 - Get the specified user
   • PUT http://localhost:8000/api/users/1 - Update the user
   • DELETE http://localhost:8000/api/users/1 - Delete the user

References

• Laravel Database Documentation
• Laravel Eloquent ORM
• OceanBase Database Documentation
• PHP Official Documentation