Connect to OceanBase Database by using ActiveRecord

This topic introduces 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, data query, and other basic operations.

Prerequisites

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

Procedure

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

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 the system dependencies (Ubuntu is used as an example):

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

2. If you are using Ruby Version Manager (RVM):

   rvm requirements
   

3. If you are using rbenv:

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

4. Use gem to install ActiveRecord and mysql2:

   gem install activerecord mysql2
   

Step 3: Obtain the connection information of OceanBase Database

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

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

Parameter description:

• $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 tenant account.
• $password: the password of the account.

Step 4: Create and configure the 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 a sample model:

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

Step 5: Run the sample application

1. Create a 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
   

FAQ

1. Connection error: If you cannot connect to the database, check whether the following items are correct:

   • The database address and port
   • The username and password
   • The network connection

2. Permission error: If you encounter a permission-related error, make sure that the user has sufficient permissions to perform the required operation.

3. SQL syntax error: If there is a syntax error in the SQL statement, check whether the SQL syntax is correct.

Performance optimization suggestions

1. Use batch operation methods of ActiveRecord, such as insert_all.
2. Use preloading (preload) to avoid N+1 queries.
3. Use caching properly.
4. Optimize database indexes.