Connect to OceanBase Database by using the Yii 2.0 ORM

Yii 2.0 is a high-performance PHP framework that provides powerful ORM features. The ActiveRecord class of Yii 2.0 provides an elegant solution for database operations and supports multiple database systems, including OceanBase Database.

The main features of the Yii 2.0 ORM are as follows:

• Full ORM support, including model definition, CRUD operations, and association relationships
• Support for multiple database systems, including MySQL, PostgreSQL, and SQLite
• Advanced features such as query builders, transaction handling, and data validation
• A high-performance caching mechanism and lazy loading
• Deep integration with the Yii 2.0 framework

This topic describes how to connect to OceanBase Database by using the Yii 2.0 ORM and perform basic database operations, such as creating tables, inserting data, updating data, and querying data.

Prerequisites

• You have installed PHP 7.4 or later.
• You have installed Composer.
• 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 Yii 2.0 project and install the dependencies.
3. Configure the database connection.
4. Create models and database migrations.
5. Write the database operation code.
6. Run the program and verify the result.

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 connect to OceanBase Database through OceanBase Database Proxy (ODP), use the IP address of the ODP. If you connect to OceanBase Database directly, use the IP address of the OBServer node.

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

• $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.

• $user_name: the tenant connection account. The common format for connecting to OceanBase Database through ODP is username@tenant name#cluster name or cluster name:tenant name:username. The format for connecting to OceanBase Database directly is username@tenant name.

• $password: the account password.

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 the 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 is displayed.

Step 3: Configure the database connection

Edit the config/db.php file and 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 a model and table schema

1. Create a User model 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 a 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 the database operation code

1. Create the UserController.php file in the controllers directory:

   <?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 a 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('Query result: ' . 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('5 rows inserted successfully');
   
               // 5. Query all data
               $count = User::find()->count();
               Yii::info("Current user count: $count");
   
               // 6. Pagination query
               $pageSize = 3;
               $users = User::find()
                   ->orderBy(['id' => SORT_DESC])
                   ->limit($pageSize)
                   ->all();
   
               Yii::info('Pagination query result: ' . count($users) . ' rows');
   
               // 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 Yii built-in development server:

   php yii serve
   

2. Access 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. View the runtime/logs/app.log file. You should see similar log outputs as follows:

   2025-06-10 11:30:15 [info][application] Starting to process user data...
   2025-06-10 11:30:16 [info][application] The user table user was created successfully.
   2025-06-10 11:30:16 [info][application] The data was inserted successfully, ID: 1.
   2025-06-10 11:30:16 [info][application] The data was queried: {"id":1,"username":"oceanbase_user1654857016","email":"******@oceanbase.com","age":35,"created_at":1654857016}
   2025-06-10 11:30:16 [info][application] The data was updated successfully.
   2025-06-10 11:30:16 [info][application] The data was inserted in batches successfully.
   2025-06-10 11:30:16 [info][application] The current number of users is 6.
   2025-06-10 11:30:16 [info][application] The query result is 3.
   2025-06-10 11:30:16 [info][application] The data was deleted successfully.
   

4. The response result is as follows:

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

References

• Yii 2.0 official documentation
• Yii 2.0 database access objects
• OceanBase Database documentation
• PHP official documentation