Build an application with Sequel and seekdb

This topic describes how to build an application with Sequel and seekdb to perform basic operations such as creating tables, inserting data, and querying data.

Prerequisites

• You have installed Ruby and RubyGems.
• You have installed seekdb.

Procedure

1. Check the versions of Ruby and RubyGems.
2. Install the required gem.
3. Obtain the seekdb connection string.
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 Sequel and mysql2:

gem install sequel mysql2

Step 3: Obtain the seekdb connection string

Contact the seekdb deployment personnel or administrator to obtain the database connection string.

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

Parameter description:

• $host: the IP address for connecting to seekdb.
• $port: the port for connecting to seekdb.
• $database_name: the name of the database to be accessed.
• $user_name: the account for connecting to seekdb.
• $password: the password of the account.

Step 4: Create a sample program

1. Create a 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 a sample program main.rb:

   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 program

1. Run the program:

   ruby main.rb

   Expected output:

   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

FAQs

1. 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

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 syntax of the SQL statement is correct.

Performance optimization recommendations

1. Use Sequel's batch operations.
2. Use a connection pool to manage database connections.
3. Use precompiled queries.
4. Use caching appropriately.
5. Optimize database indexes.