Getting Started with Rails & TiDB
Tags: tidb, mysql, rails, activerecord
Published: 2021-04-21 17:54:57

---
title: Getting Started with Rails & TiDB
published: true
description: Perhaps the first Rails + TiDB integration tutorial, there are so few articles on the web for newbies to get started, and there is a real barrier to integrating a complex ORM like ActiveRecord with TiDB, so I wrote this tutorial to get started
tags: TiDB,MySQL,Rails,ActiveRecord
cover_image: https://l.ruby-china.com/photo/hooopo/3d24d840-45ec-420e-9f84-f8e48989e73c.png!large
---

# Getting Started with Rails & TiDB

Perhaps the first Rails + TiDB integration tutorial, there are so few articles on the web for newbies to get started, and there is a real barrier to integrating a complex ORM like ActiveRecord with TiDB, so I wrote this tutorial to get started.

## Build a local TiDB development environment

 **Installing TiUP**
 
 The TiUP installation process is very straightforward for both Darwin and Linux operating systems, and can be successfully installed with a single command.

```bash
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
```

**Local start cluster**

```bash
tiup playground
```

Output

```txt
tiup playground
Starting component ``playground``: /Users/hooopo/.tiup/components/playground/v1.4.1/tiup-playground
Use the latest stable version: v5.0.0

    Specify version manually: tiup playground <version>
    The stable version: tiup playground v4.0.0
    The nightly version: tiup playground nightly

Playground Bootstrapping...
Start pd instance
Start tikv instance
Start tidb instance
Waiting for tidb instances ready
127.0.0.1:4000 ... Done
Start tiflash instance
Waiting for tiflash instances ready
127.0.0.1:3930 ... Done
CLUSTER START SUCCESSFULLY, Enjoy it ^-^
To connect TiDB: mysql --host 127.0.0.1 --port 4000 -u root -p (no password)
To view the dashboard: http://127.0.0.1:2379/dashboard
To view the Prometheus: http://127.0.0.1:9090
To view the Grafana: http://127.0.0.1:3000
```
**dashboard**

![](https://l.ruby-china.com/photo/hooopo/3d24d840-45ec-420e-9f84-f8e48989e73c.png!large)

**detailed documentation**

* https://docs.pingcap.com/tidb/stable/tiup-overview


## Rails & TiDB Configuration

**Create a Rails project**

```
rails new myapp --database=mysql
```

**database.yml configuration**

```yml
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  port: 4000
  username: root
  password:
  host: 127.0.0.1
  variables:
    tidb_enable_noop_functions: ON
```

Note that the default port for the local cluster started by tidup is 4000, set the database connection variable tidb_enable_noop_functions: ON because Rails requires the get_lock function, which is turned off by default in tidb.

If you configure the database link using the URI method, it is similar to.

```yml
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  url: <%= ENV.fetch("DB_URL") || "mysql2://root:pass@localhost:4000/myapp" %>
  variables:
    tidb_enable_noop_functions: ON
```

**primary-key, auto-increment, unique-index**

Create a users table.

```ruby
class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.string :email
      t.string :password
      t.string :username

      t.timestamps
    end
  end
end
```
Add a unique index

```ruby
class AddUniqueIndexForEmail < ActiveRecord::Migration[6.1]
  def change
    add_index :users, :email, unique: true
  end
end
```

There is no difference to using standalone MySQL, TiDB is already very compatible and much easier to get started with than other distributed databases, some of which are incompatible with primary keys, self-incrementing, unique indexes and other features that require additional handling.

Take a look at the generated data table.

```txt
mysql> show create table users;
+ -------+------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `index_users_on_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 |
+-------+------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------+
1 row in set (0.01 sec)
```

**savepoint patch**

The only obstacle to combining TiDB and ActiveRecord is that TiDB does not support savepoint, I wrote a simple patch to solve it:

```ruby
# https://github.com/rails/rails/blob/6-1-stable/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb#L313
require 'active_record/connection_adapters/abstract/database_statements.rb'


module DisableSavepoint
  def transaction(requires_new: nil, isolation: nil, joinable: true)
    if requires_new
      requires_new = nil
      Rails.logger.warn "savepoint statement was used, but your db not support, ignored savepoint."
      Rails.logger.warn caller
      super(requires_new: requires_new, isolation: isolation, joinable: joinable)
    else
      super(requires_new: requires_new, isolation: isolation, joinable: joinable)
    end
  end
end

ActiveRecord::ConnectionAdapters::DatabaseStatements.send(:prepend, DisableSavepoint)
```

The principle is that Rails will only introduce savepoint when transaction passes the parameter requires_new to true, and then output logs to migrate through the patch where requires_new is true to nil. My experience is that most Rails projects don't use savepoint much, so it's not hard to migrate if you want to. When running migration, savepoint is introduced, but in scenarios where there is no concurrent migration, it's not a big deal to remove it directly.

## Links

* [What's New in TiDB 5.0](https://docs.pingcap.com/tidb/dev/release-5.0.0)
* [tidb_enable_noop_functions-new-in-v40
](https://docs.pingcap.com/tidb/stable/system-variables#tidb_enable_noop_functions-new-in-v40)
* [Compatibility with MySQL](https://docs.pingcap.com/tidb/stable/mysql-compatibility)
* [rails-tidb demo full source code](https://github.com/hooopo/rails-tidb)