Connecting to TiDB cluster with mysql2 gem

The following guide will show you how to connect to the TiDB cluster with Ruby driver mysql2 and perform basic SQL operations like create, read, update, and delete.

Notice:

TiDB is a MySQL-compatible database, which means you can connect to a TiDB cluster in your application using the familiar driver/ORM framework from the MySQL ecosystem.

The only difference is that if you connect to a TiDB Serverless cluster with public endpoint, you MUST enable TLS connection on the mysql2 driver.

Prerequisites

To complete this guide, you need:

• Ruby >= 3.0 installed on your machine
• Bundler installed on your machine
• Git installed on your machine
• A TiDB cluster running

If you don't have a TiDB cluster yet, please create one with one of the following methods:

1. (Recommend) Start up a TiDB Serverless cluster instantly with a few clicks on TiDB Cloud.
2. Start up a TiDB Playground cluster with TiUP CLI on your local machine.

Getting Started

This section demonstrates how to run the sample application code and connect to TiDB with mysql2 driver.

1. Clone the repository

Run the following command to clone the sample code locally：

git clone https://github.com/tidb-samples/tidb-ruby-mysql2-quickstart.git
cd tidb-ruby-mysql2-quickstart

2. Install dependencies

Run the following command to install the dependencies (including the mysql2 package) required by the sample code：

bundle install

Install dependencies to existing project

For your existing project, run the following command to install the packages:

• mysql2: The ruby MySQL driver for the database connection and SQL operations.
• dotenv: The utils package to loading environment variables from the .env file.

bundle add mysql2 dotenv

3. Obtain connection parameters

(Option 1) TiDB Serverless

You can obtain the database connection parameters on TiDB Cloud's Web Console through the following steps:

1. Navigate to the Clusters page, and then click the name of your target cluster to go to its overview page.

2. Click Connect in the upper-right corner.

3. In the connection dialog, select General from the Connect With dropdown and keep the default setting of the Endpoint Type as Public.

4. If you have not set a password yet, click Create password to generate a random password.

5. Copy the connection parameters shown on the code block.

   

   The connection dialog of TiDB Serverless

(Option 2) TiDB Dedicated

You can obtain the database connection parameters on TiDB Cloud's Web Console through the following steps:

1. Navigate to the Clusters page, and then click the name of your target cluster to go to its overview page.

2. Click Connect in the upper-right corner. A connection dialog is displayed.

3. Create a traffic filter for the cluster.

   1. Click Allow Access from Anywhere to add a new CIDR address rule to allow clients from any IP address to access.
   2. Click Create Filter to confirm the changes.

4. Under Step 2: Download TiDB cluster CA in the dialog, click Download TiDB cluster CA for TLS connection to TiDB clusters.

5. Under Step 3: Connect with a SQL client in the dialog, select General from the Connect With dropdown and select Public from the Endpoint Type dropdown.

6. Copy the connection parameters shown on the code block.

(Option 3) TiDB Self-Hosted

Prepare the following connection parameters for your cluster:

• host: The IP address or domain name where the TiDB cluster running (For example: 127.0.0.1).
• port: The port on which your database server is running (Default: 4000).
• user: The name of your database user (Default: root).
• password: The password of your database user (No password for TiDB Playground by default).

4. Set up the environment variables

(Option 1) TiDB Serverless

1. Make a copy of the .env.example file to the .env file.
2. Edit the .env file, and replace the placeholders for <host>, <user>, and <password> with the copied connection parameters.
3. Modify DATABASE_ENABLE_SSL to true to enable a TLS connection. (Required for public endpoint)

DATABASE_HOST=<host>
DATABASE_PORT=4000
DATABASE_USER=<user>
DATABASE_PASSWORD=<password>
DATABASE_NAME=test
DATABASE_ENABLE_SSL=true

(Option 2) TiDB Dedicated

1. Make a copy of the .env.example file to the .env file.
2. Edit the .env file, and replace the placeholders for <host>, <user>, and <password> with the copied connection parameters.
3. Modify DATABASE_ENABLE_SSL to true to enable a TLS connection. (Required for public endpoint)
4. Modify DATABASE_SSL_CA to the file path of the CA certificate provided by TiDB Cloud. (Required for public endpoint)

DATABASE_HOST=<host>
DATABASE_PORT=4000
DATABASE_USER=<user>
DATABASE_PASSWORD=<password>
DATABASE_NAME=test
DATABASE_ENABLE_SSL=true
DATABASE_SSL_CA=/path/to/ca.pem

(Option 3) TiDB Self-Hosted

1. Make a copy of the .env.example file to the .env file.
2. Edit the .env file, and replace the placeholders for <host>, <user>, and <password> with the copied connection parameters.

The TiDB Self-Hosted cluster using non-encrypted connection between TiDB's server and clients by default, SKIP the below steps if your cluster doesn't enable TLS connections.

3. (Optional) Modify DATABASE_ENABLE_SSL to true to enable a TLS connection.
4. (Optional) Modify DATABASE_SSL_CA to the file path of the trusted CA certificate defined with ssl-ca option.

DATABASE_HOST=<host>
DATABASE_PORT=4000
DATABASE_USER=<user>
DATABASE_PASSWORD=<password>
DATABASE_NAME=test
# DATABASE_ENABLE_SSL=true
# DATABASE_SSL_CA=/path/to/ca.pem

5. Run the sample code

Run the following command to execute the sample code:

ruby app.rb

If the connection is successful, the console will output the version of the TiDB cluster.

Expected execution output:

🔌 Connected to TiDB cluster! (TiDB version: 5.7.25-TiDB-v7.1.0)
⏳ Loading sample game data...
✅ Loaded sample game data.

🆕 Created a new player with ID 12.
ℹ️ Got Player 12: Player { id: 12, coins: 100, goods: 100 }
🔢 Added 50 coins and 50 goods to player 12, updated 1 row.
🚮 Deleted 1 player data.

Example codes

Connect to TiDB cluster

The following code use the environment variables (stored in the .env file) as the connection options to establish a database connection with the TiDB cluster:

require 'dotenv/load'
require 'mysql2'
Dotenv.load # Load the environment variables from the .env file

options = {
  host: ENV['DATABASE_HOST'] || '127.0.0.1',
  port: ENV['DATABASE_PORT'] || 4000,
  username: ENV['DATABASE_USER'] || 'root',
  password: ENV['DATABASE_PASSWORD'] || '',
  database: ENV['DATABASE_NAME'] || 'test'
}
options.merge(ssl_mode: :verify_identity) unless ENV['DATABASE_ENABLE_SSL'] == 'false'
options.merge(sslca: ENV['DATABASE_SSL_CA']) if ENV['DATABASE_SSL_CA']
client = Mysql2::Client.new(options)

For TiDB Serverless

To connect TiDB Serverless with the public endpoint, please set up the environment variable DATABASE_ENABLE_SSL to true to enable TLS connection.

By default, the mysql2 gem will search for existing CA certificates in a particular order until a file is discovered.

1. /etc/ssl/certs/ca-certificates.crt # Debian / Ubuntu / Gentoo / Arch / Slackware
2. /etc/pki/tls/certs/ca-bundle.crt # RedHat / Fedora / CentOS / Mageia / Vercel / Netlify
3. /etc/ssl/ca-bundle.pem # OpenSUSE
4. /etc/ssl/cert.pem # MacOS / Alpine (docker container)

While it is possible to specify the CA certificate path manually, this approach may cause significant inconvenience in multi-environment deployment scenarios, as different machines and environments may store the CA certificate in varying locations. Therefore, setting sslca to nil is recommended for flexibility and ease of deployment across different environments.

For TiDB Dedicated

To connect TiDB Dedicated with the public endpoint, please set up the environment variable DATABASE_ENABLE_SSL to true to enable TLS connection and using DATABASE_SSL_CA to specify the file path of CA certificate downloaded from TiDB Cloud Web Console.

Insert data

The following query creates a single Player with two fields and return the last_insert_id:

def create_player(client, coins, goods)
  result = client.query(
    "INSERT INTO players (coins, goods) VALUES (#{coins}, #{goods});"
  )
  client.last_id
end

For more information, refer to Insert Data.

Query data

The following query returns a single Player record by ID:

def get_player_by_id(client, id)
  result = client.query(
    "SELECT id, coins, goods FROM players WHERE id = #{id};"
  )
  result.first
end

For more information, refer to Query Data.

Update data

The following query updated a single Player record by ID:

def update_player(client, player_id, inc_coins, inc_goods)
  result = client.query(
    "UPDATE players SET coins = coins + #{inc_coins}, goods = goods + #{inc_goods} WHERE id = #{player_id};"
  )
  client.affected_rows
end

For more information, refer to Update Data.

Delete data

The following query deletes a single Player record:

def delete_player_by_id(client, id)
  result = client.query(
    "DELETE FROM players WHERE id = #{id};"
  )
  client.affected_rows
end

For more information, refer to Delete Data.

What's next

• Connecting to TiDB cluster with Rails and ActiveRecord ORM
• Explore the real-time analytics feature on the TiDB Cloud Playground.
• Read the TiDB Developer Guide to learn more details about application development with TiDB.
  • HTAP Queries
  • Transaction
  • Optimizing SQL Performance