hooopo

Build a Rails App with TiDB and the ActiveRecord TiDB Adapter

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.

hooopo

LearnSQL

schemas

name	column_type	ext_info	ref	default	comment
id	bigint	[pk, increment, not null]	items.schema_id
version_id	bigint	[null]	versions.id
name	varchar	[null]
description	text	[null]

hooopo

          data_type          | udt_name
-----------------------------+-----------
 ARRAY                       | _float8
 ARRAY                       | _int4
 ARRAY                       | _int8
 ARRAY                       | _numeric
 ARRAY                       | _text
 ARRAY                       | _varchar
 bigint                      | int8
 boolean                     | bool

hooopo

SELECT COLUMN_NAME,    DATA_TYPE,    IS_NULLABLE,    COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS limit 10;
+----------------------+-----------+-------------+----------------+
| COLUMN_NAME          | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+----------------------+-----------+-------------+----------------+
| CHARACTER_SET_NAME   | varchar   | NO          |                |
| DEFAULT_COLLATE_NAME | varchar   | NO          |                |
| DESCRIPTION          | varchar   | NO          |                |
| MAXLEN               | bigint    | NO          | 0              |
| COLLATION_NAME       | varchar   | NO          |                |
| CHARACTER_SET_NAME   | varchar   | NO          |                |

hooopo

select 
      kcu.table_name, 
      array_agg(kcu.column_name::text order by kcu.ordinal_position asc) as key_column 
  from information_schema.table_constraints tco 
       join information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name 
         and kcu.constraint_schema = tco.constraint_schema 
         and kcu.constraint_name = tco.constraint_name 
where tco.constraint_type = 'PRIMARY KEY' 
group by 1;

hooopo

WITH RECURSIVE tree AS (
   SELECT id,
          parent_id,
          name,
          '{}'::varchar[] AS p
     FROM sectors
    WHERE parent_id is null
    UNION
   SELECT e.id,
          e.parent_id,

hooopo

\copy (SELECT row_number() over() AS "序号", n.nspname as "系统名", c.relname as "表名",pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "大小",pg_catalog.obj_description(c.oid, 'pg_class') as "中文名",c.reltuples AS "条数" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1 DESC,2) to '/tmp/tables.csv' WITH CSV HEADER;

hooopo

       SELECT
          pg_attribute.attname,
          format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
        FROM
          pg_index, pg_class, pg_attribute, pg_namespace
       WHERE
          nspname = 'public' AND
          relname = 'orders' AND
          indrelid = pg_class.oid AND
          pg_class.relnamespace = pg_namespace.oid AND

hooopo

SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = 'public.companies'::regclass AND indisprimary = 'f';
                                             pg_get_indexdef
---------------------------------------------------------------------------------------------------------
 CREATE INDEX index_companies_on_company_edition_id ON public.companies USING btree (company_edition_id)
 CREATE UNIQUE INDEX index_companies_on_uuid ON public.companies USING btree (uuid)

hooopo

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"