ActiveRecord sample program for connecting to OceanBase Database

This topic describes how to use ActiveRecord and OceanBase Database to build an application that can perform basic operations such as table creation, data insertion, and data query.

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 gem.
3. Obtain the connection information of OceanBase Database.
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 command to check the versions of Ruby and RubyGems:

ruby -v
gem -v

Step 2: Install the required gem

1. Install the system dependencies. Here is an example for Ubuntu:

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

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 a sample model:

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

Step 5: Run the sample program

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 a record
   users = User.all
   

FAQs

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

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

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

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

Performance optimization suggestions

1. Use ActiveRecord's batch operation methods (such as insert_all)
2. Use eager loading (preload) to avoid N+1 query issues
3. Use caching reasonably
4. Optimize database indexes