Connect to OceanBase Cloud by using mysql2

This topic describes how to use the mysql2 library to connect to OceanBase Cloud and 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 an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Check the versions of Ruby and RubyGems.
2. Install the required gem.
3. Obtain the connection information of OceanBase Cloud.
4. Create a sample program.
5. 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

Run the following command to install the mysql2 gem:

gem install mysql2

Step 3: Obtain the connection information of OceanBase Cloud

1. Log in to the OceanBase Cloud console. In the instance list, find the target instance and click Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the URL with the information of the created OceanBase Cloud database.

   mysql -h$host -P$port -u$user_name -p$password -D$database_name
   

   Parameter description:

   • $host: the endpoint of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.
   • $port: the port of the OceanBase Cloud database. The default value is 3306.
   • $database_name: the name of the database to be accessed.
   • $user_name: the account for accessing the database.
   • $password: the password of the account.

Step 4: Create a sample program

1. Create a configuration file named config.rb:

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

2. Create a sample program named main.rb:

   require 'mysql2'
   require_relative 'config'
   
   # Create a 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 the connection
   client.close
   

Step 5: Run the sample program

1. Run the program:

   ruby main.rb
   

   The expected return result is as follows:

   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

1. Connection error: If you cannot connect to the database, check the following:

   • Whether the database endpoint and port are correct
   • Whether the username and password are correct
   • Whether the network connection is normal

2. Permission error: If you encounter a permission-related error, make sure the user has sufficient permissions to perform the required operations.

3. SQL syntax error: If there is a syntax error in the SQL statement, check whether the SQL statement is correctly written.

Performance optimization suggestions

1. Use a connection pool to manage database connections.
2. Use prepared statements.
3. Set the query timeout to an appropriate value.
4. Use batch operations to improve performance.