Connect to OceanBase Database by using Laravel Eloquent ORM

Laravel is a sophisticated PHP web framework that features a built-in Eloquent Object-Relational Mapping (ORM), offering a clean and intuitive ActiveRecord implementation for easy database operations.

This guide walks you through using Laravel’s Eloquent ORM to connect to OceanBase Database, covering essential steps like creating tables, inserting data, and querying records.

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-compatible 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 dependencies.
3. Configure the database connection.
4. Create models and controllers.
5. Write code for database operations.
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

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connections through OceanBase Database Proxy (ODP), use the IP address of an ODP node. For direct connections, use the IP address of an OBServer node.
• $port: the port for connecting to OceanBase Database. The default port for connections through ODP is 2883, which can be customized when ODP is deployed. The default port for direct connections is 2881, which can be customized when OceanBase Database is deployed.
• $database_name: the name of the database to be accessed.
• $user_name: the account for connecting to the tenant. The format for connections through ODP is username@tenant name#cluster name or cluster name:tenant name:username. The format for direct connections is username@tenant name.
• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

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

Step 2: Install Laravel and its dependencies

1. Use Composer to create a new Laravel project:

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

2. 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 the model and controller

1. Create the 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 controller app/Http/Controllers/UserController.php:

   <?php
   
   namespace App\Http\Controllers;
   
   use App\Models\User;
   use Illuminate\Http\Request;
   
   class UserController extends Controller
   {
       // Create user table
       public function __construct()
       {
           User::createTable();
       }
   
       // Create 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 user
       public function show($id)
       {
           $user = User::findOrFail($id);
           return response()->json($user);
       }
   
       // Update 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 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 routes to routes/api.php:

   <?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 like Postman to test the following endpoints:

   • 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 a specific user
   • PUT http://localhost:8000/api/users/1 - Update a user
   • DELETE http://localhost:8000/api/users/1 - Delete a user

References

• Laravel Database Documentation
• Laravel Eloquent ORM

• OceanBase Database Documentation

• PHP Documentation