DEV Community: hooopo wang

Build a Rails App with TiDB and the ActiveRecord TiDB Adapter

hooopo wang — Fri, 27 Aug 2021 08:48:17 +0000

TiDB is an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads. It is MySQL compatible and features horizontal scalability, strong consistency, and high availability.

I assumed using TiDB as a backend storage layer of Ruby on Rails application perhaps is a great way to manage storages into one place.

This post describes how to get started and how to use TiDB as backend of Ruby on Rails applications for developers.

Example source codes are available at rails-tidb in GitHub.

Setting up local TiDB server

Install tiup

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

Starting TiDB playground

$ tiup playground  nightly

Then, we can connect to the TiDB instance just as connecting to MySQL.

mysql --host 127.0.0.1 --port 4000 -u root -p

Initialize Ruby on Rails application

$ ruby -v
ruby 2.7.0

$ rails -v
Rails 6.1.4

$ rails new tidb-rails --database=mysql --api

Add activerecord-tidb-adapter to Gemfile

$ bundle add activerecord-tidb-adapter --version "~> 6.1.0"

After creating a new app, edit config/database.yml to configure connection settings to TiDB.

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

development:
  <<: *default
  database: tidb_rails_development

No need to add additional database configurations to use TiDB. It’s ready to use TiDB as a database of the Rails app!

Create a database

$ bundle exec rails db:create
Created database 'tidb_rails_development'
Created database 'tidb_rails_test'

Manipulate TiDB data through Rails app

Defining Model using rails g command.

$ bundle exec rails g model user email:string name:string gender:integer
...
$ vim ./db/migrate/20210826174523_create_users.rb # edit

db/migrate/20210826174523_create_users.rb

class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.string :email, index: {unique: true}
      t.string :name
      t.integer :gender

      t.timestamps
    end
  end
end

Then, apply database migration.

$ bundle exec rails db:migrate
== 20210826174523 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.1717s
== 20210826174523 CreateUsers: migrated (0.1717s) =============================

Launch Rails console to play with the app.

$ bundle exec rails c
Running via Spring preloader in process 13378
Loading development environment (Rails 6.1.4.1)
irb(main):001:0> 30.times.each { |i| User.create!(email: "user-#{i}@example.com", name: "user-#{i}", gender: i % 3) }
   (1.2ms)  select version()
  TRANSACTION (0.8ms)  BEGIN
  User Create (93.5ms)  INSERT INTO `users` (`email`, `name`, `gender`, `created_at`, `updated_at`) VALUES ('user-0@example.com', 'user-0', 0, '2021-08-26 17:50:40.661945', '2021-08-26 17:50:40.661945')
  TRANSACTION (14.9ms)  COMMIT
...
=> 30
irb(main):002:0> User.count
   (8.9ms)  SELECT COUNT(*) FROM `users`
=> 30
irb(main):003:0> User.first
  User Load (5.8ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
=> #<User id: 1, email: "user-0@example.com", name: "user-0", gender: 0, created_at: "2021-08-26 17:50:40.661945000 +0000", updated_at: "2021-08-26 17:50:40.661945000 +0000">

Summary

TiDB offers MySQL interfaces which can be used as backend database layers of Ruby on Rails applications.

We can use ActiveRecord ORM directly, or use activerecord-tidb-adpater, a lightweight extension of ActiveRecord that supports several rails versions, including 5.2, 6.1, and 7.0.

The activerecord-tidb-adapter provides compatible patches and some tidb-specific functions, such as Sequence.

Getting Started with Rails & TiDB

hooopo wang — Wed, 21 Apr 2021 17:54:57 +0000

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.

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

Local start cluster

tiup playground

Output

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

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

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.

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.

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

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.

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:

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

Problems with google analytics

hooopo wang — Fri, 05 Mar 2021 21:15:49 +0000

Google analytics is a great free behavioural analysis tool that is perfectly suited to the needs of small and medium sized businesses, and is even used by some unicorn level companies.

However, Google analytics is not perfect and when you want to use GA data in depth, you will find many limitations.

GA's API does not provide raw data at event level or hit level, only aggregated data; this can be solved by purchasing GA360 and bigquery solutions, but it costs at least $15w per year
GA data has retention and sampling limits, as per rules: https://support.google.com/analytics/answer/7667196?hl=zh-Hans
Free means your data is used to train Google's ad serving
dimension and metrics limits, probably API limits of 7 dimensions and 10 metrics

Overall, it means that you don't own the data collected by GA, you can only consume the reports. Sometimes we need to use behavioural data in depth, for example in scenarios such as the following.

Customised reporting logic
Integration with existing data platforms, such as taking behavioural data and using it for recommendation systems and search systems
Developing a CRM system based on behavioural data, such as sending SMS messages to users who have clicked on a product.

I have recently been planning to develop an alternative to Google Analytics to try and solve the problems mentioned above.

The shape of the product is basically established, directly using the GA4 sdk and then configuring the transport_url to achieve the replacement, providing a simple visualisation + custom queries, or docking metabase yourself.

Open source and SaaS versions are available.

Compared to the advantages of local deployment, data is permanently stored in their own servers, do not need to be subject to the GA retention time and sampling rules, and provides the original data, GA, if you get the original data you need to use a paid service, GA360 such, $150k per year, and export to bigquery is also a paid service.

Advantages over other open source Google Analytics alternatives.

Research the existing open source products, such as plausible and umani, are more than 5k star open source projects; are only to provide the basic model of the function, these and GA than still very much worse; such as e-commerce analysis, custom events, multiple subsites, custom dimensions and metrics and other features are not provided.

I wrote an open source alternative to google analytics using timescaledb and rails and opentresty

hooopo wang — Fri, 05 Mar 2021 21:13:01 +0000

Last time I shared some thoughts on Google Analytics, I planned to implement a Google Analytics that can be deployed privately, and after months of trying, I finally finished writing the prototype. It's only the simplest of features at the moment, with some killer features to be added later...

Hypercable Analytics is a fully featured high performance scalable, open source, standalone deployable alternative to Google Analytics, build with timescaledb openresty redis and rails.

screenshot

demo site

https://learnsql.io (Note: demo project, data will be cleared later, email: hoooopo@gmail.com password: 111111)
https://hackershare.dev (site with hypercable analytics tracker installed)
https://github.com/HyperCable/hypercable

benchmark

Tested with a 6C cloud server, can handle about 15k rps, can do some more optimizations and compare with open source competitor.

Some roadmap

More built-in visual reports
Built-in metabase-like interface and SQL custom exploration
UI for e-commerce module (data structures are currently supported)

hackershare: Social bookmarking reinvented!

hooopo wang — Thu, 24 Sep 2020 08:27:54 +0000

Hackershare is a bookmark sharing platform, you can share the webpage you are browsing with one click through chrome extension. Different from bookmark management tools such as pocket, hackershare encourages sharing your bookmarks instead of keeping them privately.

Popular tags are a must-have feature of the bookmark tool, which can facilitate the management of your bookmarks. To become a powerful information organization tool is the goal of hackershare. In the future, many optimizations will be made on the label, such as synonym ring, preferred term, upper term, etc.

Hackershare is a discovery platform. The system sorts the content by users' clicks, browses, favorites, comments, etc., and calculates the daily, weekly, monthly, and total popularity rankings. At present, the content of hackershare is mainly for programming development, product operation, UI design, entrepreneurial thinking, etc.

Hackershare is an RSS subscription platform; the system will regularly crawl technology-related popular RSS feeds. You only need to subscribe to receive notifications of RSS updates. The effect is the same as an RSS reader. At the same time, you can also subscribe to the content you are interested in according to other dimensions, such as following users and following tags, so that users and tags-related content will be pushed to you, and you can master technical consultation with one click.

In the future, users will be supported to submit their own RSS feeds, which you can use to promote your blog and products.

Hackershare is an open source project with a wide range of applications. For example, if it is used to put product links, it can become a diversion station worth buying;

If used for news consultation, it can become a news station;

If the information is shared within the team, it can become an internal knowledge base.

Compared with ordinary forums or CMS, hackershare is lighter, SEO friendly, has better information architecture, and automation features. If you build some diversion stations, you can even save the time of writing crawlers.

Project address: https://hackershare.dev
github：https://github.com/hooopo/hackershare
chrome extension：https://github.com/hooopo/hackershare-ext

Online DB Doc tool

hooopo wang — Sun, 21 Jun 2020 12:57:44 +0000

DrawERD add a new function to generate db doc for your database structures. https://drawerd.com

Free database structure tool online

hooopo wang — Sun, 26 Apr 2020 08:46:54 +0000

Why ERD

The database model is the core of your application, describing data tables, data types, entity relationships and constraints, and is the most important means of communication during the project development phase. A clear ERD can make it easier for the team to understand the needs and grasp the overall picture of the application.

Scenario

Startup projects

For start-up projects or new requirements, being able to correctly establish a data model that meets business needs is a key factor in the project's smooth iteration. With tools like DrawERD, you can quickly transform your requirements into visual ERDs, and reach consensus among team members. There is no need to repeatedly check whether "category and product are one-to-many or many-to-many?" Information that is ignored but particularly critical.

Legacy projects
For legacy projects, the general business has been very stable, but the newcomer has just joined the team, and the business system in the face of hundreds of tables is often unintelligible. With DrawERD, newcomers can quickly understand project data relationships and have a systematic understanding of applications. If your database already has hundreds of tables and you plan to migrate from a monolithic application to a MicroService, then DrawERD's grouping function is the best tool. By simulating the grouping of modules, you can clearly determine which entity is placed in which service More reasonable.
Why not alternative?

The picture above is the core feature of DrawERD. Compared with the popular modeling tools on the market, it does a lot of tradeoff. Let me talk about the reasons for each decision.

SaaS vs desktop tool：Compared to desktop tools, team collaboration is a goal of DrawERD. You can generate urls and embed them in your project management tools, and changes in ERD will be automatically synchronized.
Auto layout vs Drawing on canvas manually：Many tools edit ERD based on drag and drop on the canvas. This way looks cool, but when it is actually used, if your application reaches dozens of tables, it is a disaster. DrawERD uses automatic layout, which will automatically render a fresh and beautiful SVG image according to your entities and relationships. At the same time, you can choose a combination of mode and layout for rendering.
Database agnostic vs Database binding：Some tools need to rely on the database connection to reverse the data structure. DrawERD chooses to use static analysis. You only need to export the CSV file from the information_schema of your existing database to upload. For new projects, you only need to create entities and relationships on the interface. Rely on any foreign key and meta-information of the database. At the same time, for the rails project, DrawERD integrates the Rails ERD gem, you can seamlessly migrate.

Preview

Full Mode

Simple Mode

Grouping

Full video here: https://www.loom.com/share/e30d06ba299b43bc8b68f369b47f745a
Try DrawERD online: https://drawerd.com

Petri Net Workflow engine for Rails

hooopo wang — Sat, 08 Feb 2020 19:46:02 +0000

Petri Flow

Workflow engine for Rails: https://github.com/hooopo/petri_flow

Features

Full petri net features support (seq, parallel, iterative, timed, automitic etc.)
Both approval workflow and business workflow.
Simple web admin for workflow definition and case management.
Build-in simple dynamic form.
Replaceable dynamic form.
Graph screen for workflow definition.
Graph screen for case and token migration.
Powerful guard expression.
MySQL and Postgres Support.
Powerful assignment management.
Flexible integration of organizational structure system(role, group, position or department etc.)

Docs

Screenshots

iterative routing

parallel_routing

guard

case state graph

Installation

Add this line to your application's Gemfile:

gem 'petri_flow', require: 'wf'

And then execute:

$ bundle

Install graphviz

brew install graphviz

Migration:

bundle exec rake wf:install:migrations
bundle exec rails db:create
bundle exec rails db:migrate

Usage

Add wf_config:

# config/initializers/wf_config.rb
Wf::Workflow.class_eval do
  def self.user_class
    ::User
  end

  # you can add more org class, for example, Role, Department, Position etc.
  def self.org_classes
    { group: ::Group }
  end
end

Set parties:

class User < ApplicationRecord
  belongs_to :group, optional: true
  has_one :party, as: :partable, class_name: 'Wf::Party'

  # NOTICE: group or user or role all has_many users
  has_many :users, foreign_key: :id

  after_create do
    create_party(party_name: name)
  end
end

class Group < ApplicationRecord
  has_many :users
  has_one :party, as: :partable, class_name: 'Wf::Party'
  after_create do
    create_party(party_name: name)
  end
end

then

bundle exec rails

visit:

http://localhost:3000/wf

Demo App: https://github.com/hooopo/petri_flow_demo

Contributing

Contribution directions go here.

License

The gem is available as open source under the terms of the MIT License.

DEV Community: hooopo wang

Build a Rails App with TiDB and the ActiveRecord TiDB Adapter

Setting up local TiDB server

Initialize Ruby on Rails application

Create a database

Manipulate TiDB data through Rails app

Summary

Getting Started with Rails & TiDB

Getting Started with Rails & TiDB

Build a local TiDB development environment

Rails & TiDB Configuration

Links

Problems with google analytics

I wrote an open source alternative to google analytics using timescaledb and rails and opentresty

screenshot

demo site

benchmark

Some roadmap

hackershare: Social bookmarking reinvented!

Online DB Doc tool

Free database structure tool online

Why ERD

Scenario

Preview

Petri Net Workflow engine for Rails

Petri Flow

Features

Docs

Screenshots

iterative routing

parallel_routing

guard

case state graph

Installation

Usage

Contributing

License