Connect to OceanBase Database by using Sequel

This topic describes how to build an application by using Sequel 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 the sample program.
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

Install Sequel and mysql2 by using gem:

gem install sequel 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 the sample program

1. Create the configuration file config.rb:

   PASSWORD = ENV['OB_PASSWORD'] || 'your_password'
   
   CLIENT_CONFIG = {
     adapter: 'mysql2',
     host: 'your_host',
     port: your_port,
     username: 'your_username',
     password: PASSWORD,
     all_databases: true
   }
   

2. Create the sample program main.rb:

   require 'sequel'
   require 'sequel/adapters/mysql2'
   require File.expand_path('config.rb', __dir__)
   
   # Create the connection
   db = Sequel.connect(CLIENT_CONFIG)
   
   # Create the database
   db_name = "ruby_test_db"
   db.run("CREATE DATABASE IF NOT EXISTS #{db_name}")
   
   db.disconnect
   
   # Reconnect to the new database
   CLIENT_CONFIG[:database] = db_name
   db = Sequel.connect(CLIENT_CONFIG)
   puts "Database '#{db_name}' selected"
   
   # Create the table
   db.run("DROP TABLE IF EXISTS users")
   db.run("CREATE TABLE users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   )")
   
   # Insert sample data
   sample_names = ["Alice", "Bob", "Charlie", "David"]
   sample_names.each do |name|
     db.run("INSERT INTO users (name) VALUES ('#{name}')")
   end
   
   puts "Table created and sample data inserted successfully"
   puts "Inserted #{sample_names.size} sample records"
   
   # Query and display all data
   results = db[:users]
   puts "\nUsers:"
   results.each do |row|
     puts "ID: #{row[:id]}, Name: #{row[:name]}, Created at: #{row[:created_at]}"
   end
   
   # Close the connection
   db.disconnect
   

Step 5: Run the sample program

1. Run the program:

   ruby main.rb
   

   Expected result:

   Database 'ruby_test_db' selected
   Table created and sample data inserted successfully
   Inserted 4 sample records
   
   Users:
   ID: 1, Name: Alice, Created at: 2025-05-21 15:20:06 +0800
   ID: 2, Name: Bob, Created at: 2025-05-21 15:20:06 +0800
   ID: 3, Name: Charlie, Created at: 2025-05-21 15:20:06 +0800
   ID: 4, Name: David, Created at: 2025-05-21 15:20:06 +0800
   

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 Sequel batch operations.
2. Use a connection pool to manage database connections.
3. Use prepared queries.
4. Use caching appropriately.
5. Optimize database indexes.