Connect to OceanBase Database by using CakePHP

CakePHP is a rapid PHP development framework that uses design patterns such as object-relational mapping, front controller, and MVC.

This topic describes how to connect CakePHP to OceanBase Database and perform basic CRUD (create, read, update, delete) operations.

Prerequisites

Before using CakePHP, make sure that:

You have deployed OceanBase Database and created a MySQL-mode user tenant.## Procedure

Step 1: Obtain database connection string

Contact the OceanBase Database deployment team to obtain the connection string. For example:

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

Parameter description:

• $host: The connection IP address. Use the ODP address for ODP connections, or the OBServer node IP for direct connections.
• $port: The connection port. The default value is 2883 for ODP connections and 2881 for direct connections.
• $database_name: The database name.

  Notice

  The user connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database.

• $user_name: The connection account. For ODP connections, use username@tenant#cluster or cluster:tenant:username. For direct connections, use username@tenant.
• $password: The account password.Example:

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

Step 2: Set up CakePHP environment

1. Install PHP 8.3 and the required extensions:

   apt update
   apt install -y software-properties-common
   add-apt-repository ppa:ondrej/php
   apt install -y unzip git php8.3 php8.3-mysql php8.3-mbstring php8.3-intl php8.3-dom php8.3-simplexml php8.3-sqlite3 php8.3-pdo
   

2. Install Composer:

   curl -sS https://getcomposer.org/installer | php
   # Verify that Composer is working
   php composer.phar --version
   

Step 3: Create a CakePHP project

# Create a new CakePHP project
php composer.phar create-project --prefer-dist cakephp/app:~5.0 my_cakephp_app
# Navigate to the project directory
cd my_cakephp_app

Step 4: Configure database connection

Edit config/app_local.php to configure the database connection parameters:

    'Datasources' => [
        'default' => [
            'host' => '$host',
            /*
             * CakePHP will use the default DB port based on the driver selected
             * MySQL on MAMP uses port 8889, MAMP user will want to uncomment
             * the following line and set the port accordingly
             */
            'port' => '$port',

            'username' => '$user_name',
            'password' => '$password',

            'database' => '$database_name',
            /*
             * If not using the default 'public' schema with the PostgreSQL driver
             * set it here.
             */
            //'schema' => 'myapp',

            /*
             * You can use a DSN string to set the entire configuration
             */
            'url' => env('DATABASE_URL', null),
        ]

For the host, port, database name, username, and password, refer to Step 1.

Step 5: Create the model and table schema

1. Create the user table in OceanBase Database:

   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,
       first_name varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       last_name varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       age int(11) DEFAULT NULL,
       status varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'active',
       PRIMARY KEY (id),
       UNIQUE KEY idx_email (email),
       KEY idx_username (username)
   );
   

2. Generate the User model at src/Model/Table/UserTable.php:

   bin/cake bake model User
   

Step 6: Write the database operation code

Create the controller at src/Controller/UserController.php:

<?php
declare(strict_types=1);
namespace App\Controller;

use Cake\Log\Log;

class UserController extends AppController
{
    public function index()
    {
        $this->autoRender = false;
        $this->response = $this->response->withType('application/json');

        try {
            Log::info('Processing user data...');

            $userTable = $this->fetchTable('User');
            Log::info('User table created successfully');

            // 1. Insert
            $user = $userTable->newEmptyEntity();
            $userData = [
                'username' => 'example_user',
                'email' => 'user@example.com',
                'age' => 35
            ];

            $user = $userTable->patchEntity($user, $userData);
            if ($userTable->save($user)) {
                Log::info('Insert succeeded, ID: ' . $user->id);
            }

            // 2. Read
            $foundUser = $userTable->get($user->id);
            $userData = $foundUser->toArray();
            $userData['created_at'] = 1654857016; // mock timestamp
            Log::info('Query result: ' . json_encode($userData, JSON_UNESCAPED_UNICODE));

            // 3. Update
            $foundUser->age = 36;
            if ($userTable->save($foundUser)) {
                Log::info('Update succeeded');
            }

            // 4. Delete
            if ($userTable->delete($foundUser)) {
                Log::info('Delete succeeded');
            }

            echo json_encode(['success' => true, 'message' => 'CRUD completed']);

        } catch (\Exception $e) {
            echo json_encode(['success' => false, 'error' => $e->getMessage()]);
        }
    }
}

Step 7: Run application and verify results

1. Start the CakePHP built-in development server:

   bin/cake server -H 0.0.0.0 -p 8765 &
   

2. Test with curl:

   curl -s http://localhost:8765/user
   # {"success":true,"message":"CRUD completed"}
   

3. Check logs/debug.log. You should see log output similar to the following:

   2026-03-02 09:38:11 info: Processing user data...
   2026-03-02 09:38:11 info: User table created successfully
   2026-03-02 09:38:11 info: Insert succeeded, ID: 2
   2026-03-02 09:38:11 info: Query result: {"id":2,"username":"example_user","email":"user@example.com","first_name":null,"last_name":null,"age":35,"status":"active","created_at":1654857016}
   2026-03-02 09:38:11 info: Update succeeded
   2026-03-02 09:38:11 info: Delete succeeded
   

Considerations

1: SHOW INDEXES output differences

OceanBase Database represents conditional expressions in index metadata as virtual column names (such as SYS_NC19$), whereas MySQL shows NULL in the same position. If CakePHP's schema parsing assumes that Column_name in an index must be a real column on the table, it may fail to recognize or correctly handle SYS_NC19$, leading to a parsing error.

2: Character set differences

CakePHP defaults to utf8_general_ci, while OceanBase Database defaults to utf8mb4_general_ci.

3: Foreign key naming rules

OceanBase Database and MySQL use different foreign key naming conventions, which may affect how CakePHP identifies foreign key constraints.

4: Unsorted query result order

For queries without an explicit ORDER BY, MySQL returns results in ascending order, while OceanBase Database may return them in descending order.

References

• CakePHP official documentation