Connect to OceanBase Database by using ActiveRecord

This topic describes how to build an application by using ActiveRecord and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Prerequisites

• You have installed Ruby and RubyGems.
• You have installed OceanBase Database and created a MySQL-compatible tenant.

Procedure

1. Check the versions of Ruby and RubyGems.
2. Install the required gems.
3. Obtain the OceanBase Database connection information.
4. Create and configure a Rails application.
5. Run the sample program.

Step 1: Check the versions of Ruby and RubyGems

Open the terminal and run the following commands to check the versions of Ruby and RubyGems:

ruby -v
gem -v

Step 2: Install the required gems

1. Install system dependencies (using Ubuntu as an example):

   sudo apt-get install ruby-dev mysql-client libmysqlclient-dev
   

2. If you use RVM (Ruby Version Manager):

   rvm requirements
   

3. If you use rbenv:

   rbenv install -l  # List available versions
   rbenv install x.x.x  # Install a specific version
   rbenv global x.x.x  # Set the global version
   

4. Install ActiveRecord and mysql2 by using gem:

   gem install activerecord mysql2
   

Step 3: Obtain the OceanBase Database connection information

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

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

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database.
• $port: the port for connecting to OceanBase Database.
• $database_name: the name of the database to be accessed.
• $user_name: the account for connecting to the tenant.
• $password: the password of the account.

Step 4: Create and configure a Rails application

1. Create a new Rails application:

   rails new activerecord_oceanbase -d mysql
   

2. Configure the database connection information:

   Add the following configuration to config/database.yml:

   development:
     adapter: mysql2
     encoding: utf8mb4
     database: your_database
     username: your_username
     password: your_password
     host: your_host
     port: your_port
   

3. Create an example model:

   # app/models/user.rb
   class User < ActiveRecord::Base
     validates :name, presence: true
   end
   

Step 5: Run the sample program

1. Create the database table:

   # db/migrate/20230520_create_users.rb
   class CreateUsers < ActiveRecord::Migration[6.1]
     def change
       create_table :users do |t|
         t.string :name
         t.timestamps
       end
     end
   end
   

2. Run the migration:

   rails db:migrate
   

3. Test in the Rails console:

   rails c
   

   # Create a record
   user = User.create(name: "John Doe")
   

   # Query records
   users = User.all
   

Common issues

1. Connection errors: If you cannot connect to the database, check:

   • Whether the database address and port are correct
   • Whether the username and password are correct
   • Whether the network connection is normal

2. Privilege errors: If you encounter privilege-related errors, ensure that the user has sufficient privileges to perform the required operations.

3. SQL syntax errors: If the SQL statement has syntax errors, check whether the SQL syntax is correct.

Performance optimization recommendations

1. Use ActiveRecord batch operations (such as insert_all).
2. Use preload to avoid N+1 query issues.
3. Use caching appropriately.
4. Optimize database indexes.