Connect to OceanBase Database using Ruby on Rails

Ruby on Rails is a full-stack web framework built with the Ruby programming language. It comes with ActiveRecord as its default Object-Relational Mapping (ORM) layer, offering features such as:

• Convention-based ORM
• Support for model associations and inheritance
• Query interface for building complex database queries
• Database migration support
• Built-in validation and callback mechanisms

This topic walks you through using ActiveRecord in Ruby on Rails to connect to OceanBase Database and perform basic database operations like creating tables, inserting data, updating data, and querying data.

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-compatible 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 program 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

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connections through OceanBase Database Proxy (ODP), use the IP address of an ODP node. For direct connections, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connections through ODP, the default value is 2883, which can be customized during ODP deployment. For direct connections, the default value is 2881, which can be customized during OceanBase Database deployment.

• $database_name: the name of the database to be accessed.

  Notice

  The user used to connect 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-compatible mode.

• $user_name: the account for connecting to the tenant. For connections through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connections, the format is username@tenant name.

• $password: the password of 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 RVM environment
   source /usr/local/rvm/src/rvm/scripts/rvm
   
   # Use the official source. By default, no configuration is needed.
   # If downloads are slow, consider setting up a mirror that is closer to your location.
   
   # 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 without the test framework
   rails new oceanbase_rails --skip-test
   cd oceanbase_rails
   

3. Modify the Gemfile to include tzinfo-data:

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

4. Install dependencies:

   bundle install
   

Step 3: Create a model and implement CRUD operations

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

• Rails Guide - Active Record Basics
• Rails API Documentation
• Getting Started with Ruby on Rails
• OceanBase Database Documentation
• Ruby Documentation