Connect to OceanBase Database by using Sequel

This topic introduces 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, 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 a sample application.
5. Run the sample application.

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 necessary gems

Run the following command to install Sequel and mysql2:

gem install sequel mysql2

Step 3: Obtain the connection information of OceanBase Database

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

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

Step 4: Create the sample application

1. Create a config.rb file:

   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 main.rb sample application:

   require 'sequel'
   require 'sequel/adapters/mysql2'
   require File.expand_path('config.rb', __dir__)
   
   # Create a connection.
   db = Sequel.connect(CLIENT_CONFIG)
   
   # Create a 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 a 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 application

1. Run the following command to run the application:

   ruby main.rb
   

   Expected return 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
   

FAQ

1. Connection error: 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 error: If you encounter a privilege-related error, make sure that the user has sufficient privileges 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 operations of Sequel.
2. Use a connection pool to manage database connections.
3. Use precompiled queries.
4. Use caching properly.
5. Optimize database indexes.