This topic describes how to build an application using Sequel and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have installed Ruby and RubyGems.
- You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For more information, see Create a cluster instance and Create a tenant.
Procedure
- Check the versions of Ruby and RubyGems.
- Install the necessary gems.
- Obtain the connection information for OceanBase Cloud.
- Create the sample program.
- 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 necessary gems
Use gem to install Sequel and mysql2:
gem install sequel mysql2
Step 3: Obtain the connection information for OceanBase Cloud
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get a connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud database.
mysql -h$host -P$port -u$user_name -p$password -D$database_nameParameter description:
$host: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The connection port for OceanBase Cloud, default is 3306.$database_name: The name of the database to access.$user_name: The account for accessing the database.$password: The password for the account.
Step 4: Create the sample program
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 }Create the sample program
main.rb:require 'sequel' require 'sequel/adapters/mysql2' require File.expand_path('config.rb', __dir__) # Create 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 Connection db.disconnect
Step 5: Run the sample program
Run the program:
ruby main.rbExpected 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
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.
Permission error: If you encounter a permission-related error, ensure the user has sufficient permissions to perform the required operation.
SQL syntax error: If an SQL statement contains a syntax error, check whether the SQL statement syntax is correct.
Performance optimization suggestions
- Use batch processing operations with Sequel.
- Manage database connections using a connection pool.
- Use prepared queries.
- Use caching appropriately.
- Optimize database indexes.