Connect to OceanBase Database by using Ruby on Rails

Ruby on Rails is a full-stack web application framework that uses the Ruby programming language. It includes ActiveRecord as its ORM component. ActiveRecord provides the following features:

• Convention-based object-relational mapping (ORM)
• Support for model associations and inheritance
• Query interface for building complex database queries
• Support for database migrations
• Built-in validation and callback mechanisms

This topic describes how to use ActiveRecord in Ruby on Rails to connect to OceanBase Database and perform basic database operations, including table creation, data insertion, data updates, and data queries.

Prerequisites

• You have installed Ruby 3.2.1 or later.
• You have installed Rails 6.0 or later.
• 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. Install Ruby and Rails.
3. Create a Rails application and configure the database connection.
4. Create a model and implement basic CRUD operations.
5. Run the application and verify the results.

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 an 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 during ODP deployment. If you connect to OceanBase Database directly, the default port is 2881, which can be customized during OceanBase Database deployment.

• $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. For more information about user privileges, see Privilege types in MySQL mode.

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

• $password: the password for the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

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

Step 2: Install Ruby and Rails

1. Install Ruby and Rails:

   # Install dependencies
   apt install -y curl vim git build-essential libmysqlclient-dev libssl-dev
   
   # Install RVM
   curl -L https://raw.githubusercontent.com/wayneeseguin/rvm/master/binscripts/rvm-installer | bash -s stable
   
   # Load the RVM environment
   source /usr/local/rvm/src/rvm/scripts/rvm
   
   # Change the Ruby source in RVM to the mirror of Ruby China
   echo "ruby_url=https://cache.ruby-china.com/pub/ruby" > /usr/local/rvm/src/rvm/config/db
   
   # Install RVM dependencies
   rvm requirements
   
   # Create necessary directories
   mkdir -p /usr/local/rvm/src/rvm/rubies
   mkdir -p /usr/local/rvm/src/rvm/archives
   mkdir -p /usr/local/rvm/src/rvm/src/ruby-3.2.1
   
   # Install Ruby 3.2.1
   rvm install 3.2.1
   rvm use 3.2.1 --default
   
   # Install Bundler and Rails
   gem install bundler
   gem install rails
   
   # Verify the installation
   rails -v
   
   # Install MySQL2
   gem install mysql2 -v '0.5.6'
   

2. Create a new Rails application:

   # Create a new Rails application and skip the test framework
   rails new oceanbase_rails --skip-test
   cd oceanbase_rails
   

3. Modify the Gemfile to ensure that tzinfo-data is included:

   gem 'tzinfo-data', platforms: [:ruby]
   

4. Install dependencies:

   bundle install
   

Step 3: Create a model and implement CRUD operations

1. Create a user model app/models/user.rb:

   class User < ApplicationRecord
     validates :username, presence: true
     validates :email, presence: true, uniqueness: true
     validates :age, numericality: { only_integer: true, allow_nil: true }
   end
   

2. Create a controller app/controllers/users_controller.rb to implement CRUD operations:

   class UsersController < ApplicationController
     # Get all users
     def index
       @users = User.all
       render json: @users
     end
   
   # Temporarily disable CSRF verification
   skip_before_action :verify_authenticity_token, if: :json_request?
   
     # Create a new user
     def create
       @user = User.new(user_params)
       if @user.save
         render json: @user, status: :created
       else
         render json: @user.errors, status: :unprocessable_entity
       end
     end
   
     # Get a single user
     def show
       @user = User.find(params[:id])
       render json: @user
     rescue ActiveRecord::RecordNotFound
       render json: { error: 'User not found' }, status: :not_found
     end
   
     # Update user information
     def update
       @user = User.find(params[:id])
       if @user.update(user_params)
         render json: @user
       else
         render json: @user.errors, status: :unprocessable_entity
       end
     end
   
     def json_request?
     request.format.json?
     end
   
     private
   
     def user_params
       params.require(:user).permit(:username, :email, :age)
     end
   end
   

3. Configure the routes by editing config/routes.rb:

   Rails.application.routes.draw do
     resources :users
   end
   

Step 4: Test the API

1. Start the Rails server:

   rails server -p 3000
   

2. Use curl or Postman to test the API:

   curl -X POST http://localhost:3000/users 
   
   -H "Content-Type: application/json"
   
   -H "Accept: application/json"
   
   -d '{"user": {"username":"testuser","email":"test@example.com","age":25}}'
   
   curl -X PATCH http://localhost:3000/users/1 \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   -d '{"user": {"age":26}}'
   
   curl -X DELETE http://localhost:3000/users/1 
   
   -H "Content-Type: application/json"
   
   -H "Accept: application/json"
   

References

• Active Record Basics
• Rails API documentation
• Getting started with Ruby on Rails
• OceanBase Database documentation
• Ruby official documentation