This topic describes how to build an application using mysql2 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 details, see Create a cluster instance and Create a tenant.
Procedure
- Check the versions of Ruby and RubyGems.
- Install the required gem.
- 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 required gem
Use gem to install mysql2:
gem install 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. 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 used to access 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 = { host: 'your_host', port: your_port, username: 'your_username', password: PASSWORD, all_databases: true }Create the sample program
main.rb:require 'mysql2' require_relative 'config' # Create Connection client = Mysql2::Client.new(CLIENT_CONFIG) # Create a database db_name = "ruby_test_db" client.query("CREATE DATABASE IF NOT EXISTS #{db_name}") client.query("USE #{db_name}") puts "Database '#{db_name}' selected" # Create a table client.query("DROP TABLE IF EXISTS users") client.query("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| client.query("INSERT INTO users (name) VALUES ('#{name}')") end puts "Table created and sample data inserted successfully" puts "Inserted #{sample_names.length} sample records" # Query and display all data results = client.query("SELECT * FROM users") puts "\nUsers:" results.each do |row| puts "#{row['id']}: #{row['name']} (created: #{row['created_at']})" end # Close Connection client.close
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: 1: Alice (created: 2025-05-20 17:56:22 +0800) 2: Bob (created: 2025-05-20 17:56:22 +0800) 3: Charlie (created: 2025-05-20 17:56:22 +0800) 4: David (created: 2025-05-20 17:56:22 +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 syntax of the SQL statement is correct.
Performance optimization suggestions
- Use a connection pool to manage database connections.
- Use prepared statements.
- Set the query timeout period appropriately.
- Use batch processing to improve performance.