Connect to OceanBase Database by using Yii 2.0 ORM

Yii 2.0 is a high-performance PHP framework offering powerful ORM features. Its ActiveRecord class provides an elegant way to interact with databases and supports a variety of database systems, including OceanBase Database.

Key features of Yii 2.0 ORM:

• Full ORM support, including model definition, CRUD operations, and relationships
• Compatible with multiple databases: MySQL, PostgreSQL, SQLite, and more
• Advanced features like query builders, transaction management, and data validation
• Efficient caching and lazy loading Seamless integration with the Yii 2.0 framework

This guide walks you through connecting to OceanBase Database by using Yii 2.0 ORM and performing basic operations like creating tables, inserting, updating, and querying data.

Prerequisites

• You have installed PHP 7.4 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 Yii 2.0 project and install dependencies.
3. Configure the database connection.
4. Create models and database migrations.
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. If you connect through OceanBase Database Proxy (ODP), use the IP address of an ODP node. If you connect directly, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. If you connect through ODP, the default value is 2883, which can be customized when you deploy ODP. If you connect directly, the default value is 2881, which can be customized when you deploy OceanBase Database.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the account for connecting to the tenant. If you connect through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. If you connect directly, the format 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: Create a Yii 2.0 project and install dependencies

1. Use Composer to create a new Yii 2.0 project:

   # Install the basic application template of Yii 2.0
   composer create-project --prefer-dist yiisoft/yii2-app-basic yii2-oceanbase
   cd yii2-oceanbase
   

2. Install the database dependencies:

   composer require --prefer-dist yiisoft/yii2-debug
   composer require --prefer-dist yiisoft/yii2-gii
   

3. Verify the installation:

   php yii help
   

   If the installation is successful, the version information of Yii will be displayed.

Step 3: Configure the database connection

Edit the config/db.php file to configure the OceanBase Database connection:

<?php

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=your_oceanbase_host;port=2881;dbname=your_database',
    'username' => 'your_username',
    'password' => 'your_password',
    'charset' => 'utf8mb4',
    'tablePrefix' => 'tbl_',
    'enableSchemaCache' => true,
    'schemaCacheDuration' => 3600,
    'schemaCache' => 'cache',
    'enableQueryCache' => true,
    'queryCacheDuration' => 3600,
    'attributes' => [
        PDO::ATTR_TIMEOUT => 30, // Set the timeout to 30 seconds.
    ],
];

Step 4: Create models and schema

1. Create the User model in models/User.php:

   <?php
   
   namespace app\models;
   
   use Yii;
   use yii\db\ActiveRecord;
   use yii\db\Connection;
   use yii\db\Schema;
   
   class User extends ActiveRecord
   {
       public static function tableName()
       {
           return '{{%user}}';
       }
   
       public function rules()
       {
           return [
               [['username', 'email'], 'required'],
               ['email', 'email'],
               ['age', 'integer', 'min' => 0],
               ['username', 'string', 'max' => 100],
               ['email', 'string', 'max' => 100],
               [['created_at'], 'safe'],
           ];
       }
   
       public function beforeSave($insert)
       {
           if (parent::beforeSave($insert)) {
               if ($this->isNewRecord) {
                   $this->created_at = time();
               }
               return true;
           }
           return false;
       }
   

2. Create the user table:

   CREATE TABLE user (
   id int(11) NOT NULL AUTO_INCREMENT,
   username varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
   email varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
   age int(11) DEFAULT NULL,
   created_at int(11) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY idx-email (email),
   KEY idx-username (username)
   );
   

Step 5: Write database operation code

1. Create the controller controllers/UserController.php:

   <?php
   
   namespace app\controllers;
   
   use Yii;
   use yii\web\Controller;
   use yii\web\Response;
   use app\models\User;
   
   class UserController extends Controller
   {
       public function actionIndex()
       {
   
           // Start transaction
           $transaction = Yii::$app->db->beginTransaction();
   
           try {
               // 1. Insert data
               $user = new User();
               $user->username = 'oceanbase_user' . time();
               $user->email = 'user' . time() . '******@oceanbase.com';
               $user->age = rand(20, 60);
   
               if (!$user->save()) {
                   throw new \Exception('Failed to save user: ' . json_encode($user->getErrors(), JSON_UNESCAPED_UNICODE));
               }
   
               Yii::info('Data inserted successfully, ID: ' . $user->id);
   
               // 2. Query data
               $foundUser = User::findOne($user->id);
               if (!$foundUser) {
                   throw new \Exception('Failed to query user: User does not exist');
               }
               Yii::info('Queried data: ' . json_encode($foundUser->attributes, JSON_UNESCAPED_UNICODE));
   
               // 3. Update data
               $foundUser->age += 1;
               if (!$foundUser->save()) {
                   throw new \Exception('Failed to update user: ' . json_encode($foundUser->getErrors(), JSON_UNESCAPED_UNICODE));
               }
               Yii::info('Data updated successfully');
   
               // 4. Batch insert example
               $rows = [];
               $columns = ['username', 'email', 'age', 'created_at'];
               $time = time();
   
               for ($i = 0; $i < 5; $i++) {
                   $rows[] = [
                       'user' . $i . '_' . $time,
                       'user' . $i . '_' . $time . '@example.com',
                       rand(20, 60),
                       $time
                   ];
               }
   
               Yii::$app->db->createCommand()->batchInsert(
                   User::tableName(),
                   $columns,
                   $rows
               )->execute();
   
               Yii::info('Batch insert of 5 records succeeded');
   
               // 5. Query all data
               $count = User::find()->count();
               Yii::info("Current user count: $count");
   
               // 6. Paginated query
               $pageSize = 3;
               $users = User::find()
                   ->orderBy(['id' => SORT_DESC])
                   ->limit($pageSize)
                   ->all();
   
               Yii::info('Paginated query result: ' . count($users) . 'records');
   
               // 7. Delete data
               if ($foundUser->delete() === false) {
                   throw new \Exception('Failed to delete user');
               }
               Yii::info('Data deleted successfully');
   
               $transaction->commit();
   
               return $this->asJson([
                   'success' => true,
                   'message' => 'Operation successful',
                   'data' => [
                       'userCount' => $count,
                       'sampleUser' => $foundUser->attributes
                   ]
               ]);
   
           } catch (\Exception $e) {
               $transaction->rollBack();
               Yii::error($e->getMessage());
               return $this->asJson([
                   'success' => false,
                   'message' => 'Operation failed: ' . $e->getMessage(),
               ]);
           }
       }
   }
   

Step 6: Run the program and verify the results

1. Start the built-in development server of Yii:

   php yii serve
   

2. Visit curl http://localhost:8080/index.php?r=user/index in your browser or use curl to test:

   curl http://localhost:8080/index.php?r=user/index
   

3. Check the runtime/logs/app.log file. You will see log outputs similar to the following:

   2025-06-10 11:30:15 [info][application] Starting to process user data...
   2025-06-10 11:30:16 [info][application] Successfully created the user table.
   2025-06-10 11:30:16 [info][application] Data inserted successfully, ID: 1
   2025-06-10 11:30:16 [info][application] Query result: {"id":1,"username":"oceanbase_user1654857016","email":"******@oceanbase.com","age":35,"created_at":1654857016}
   2025-06-10 11:30:16 [info][application] Data updated successfully
   2025-06-10 11:30:16 [info][application] Batch insertion of 5 records completed
   2025-06-10 11:30:16 [info][application] Current number of users: 6
   2025-06-10 11:30:16 [info][application] Pagination query result: 3 records
   2025-06-10 11:30:16 [info][application] Data deleted successfully
   

4. Here is an example of the response:

   {
       "success":true,"message":"Operation successful","data":{"userCount":6,"sampleUser":{"id":1,"username":"oceanbase_user1749628918","email":"******@oceanbase.com","age":31,"created_at":1749628919}}
   }
   

References

• Yii 2.0 Documentation
• Yii 2.0 Database Access Objects
• OceanBase Database Documentation
• PHP Documentation