coa-mysql

English | 简体中文

MySQL database components for coajs, including basic data models, cache data models, distributed ID, etc.

Feature

• Functional: Basic data connection based on mysql，SQL query based on knex. Pay attention to performance, full-featured, including original library all use methods
• Lightweight: No more than 1,000 lines of code, do not rely on other third-party libraries
• Fast and Convenient: Basic data model comes with CRUD operation, no extra code
• Automatic Cache: Cache data model automatically performs data cache management (cache generation, cache elimination, etc.), cache is based oncoa-redis
• TypeScript: All written in TypeScript, type constraint, IDE friendship

Component

• Basic data model MysqlNative: Automatically implement basic CRUD
• Cache data model MysqlCache: Take over data cache logic on the basic data model
• Distributed ID MysqlUuid: Lightweight distributed UUID

Quick Start

Install

yarn add coa-mysql

Instance configuration

import { MysqlBin } from 'coa-mysql'

// MySQL configuration
const mysqlConfig = {
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: 'root',
  charset: 'utf8mb4',
  trace: true,
  debug: false,
  databases: {
    main: { database: 'test', ms: 7 * 24 * 3600 * 1000 },
    other: { database: 'other', ms: 7 * 24 * 3600 * 1000 },
  },
}

// Initialize MySQL basic connection,
// follow-up all models depend on this example
const mysqlBin = new MysqlBin(mysqlConfig)

Basic SQL query

New user table user, the table structure is as follows

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Self-increased primary key',
  `userId` varchar(32) NOT NULL DEFAULT '' COMMENT 'user ID',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT 'name',
  `mobile` varchar(16) NOT NULL DEFAULT '' COMMENT 'mobile',
  `avatar` varchar(256) NOT NULL DEFAULT '' COMMENT 'avatar',
  `gender` int(11) NOT NULL DEFAULT '0' COMMENT 'gender, 1 male, 2 female',
  `language` varchar(16) NOT NULL DEFAULT '' COMMENT 'language',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT 'status, 1 normal 2 hidden',
  `created` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Create time',
  `updated` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Update time',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `user_userid_unique` (`userId`) USING BTREE
) COMMENT='User Table';

SQL operations on the user table

// Insert data, see https://knexjs.org/#Builder-insert
mysqlBin.io.table('user').insert({ userId: 'user-a', name: 'A', mobile: '15010001001', gender: 1, language: 'zh-CN', status: 1 })

// Query all data, see https://knexjs.org/#Builder-select
mysqlBin.io.table('user').select()
mysqlBin.io.select('*').from('user')

// Conditional queries, see https://knexjs.org/#Builder-where
mysqlBin.io.table('user').where('status', '=', 1)

// Update data, see http://knexjs.org/#Builder-update
mysqlBin.io.table('user').update({ name: 'AA', gender: 2 }).where({ userId: 'user-a' })

// Delete data, see http://knexjs.org/#Builder-del%20/%20delete
mysqlBin.io.table('user').delete().where({ userId: 'user-a' })

The io in this is a Knex object, can support all the usage of Knex.js

Basic data model

In project engineering, in order to ensure the efficiency and rigor of the query, we will not directly operate the SQL statement. Basic data modules can help us implement CURD operations. Define a basic data model User by as follows

import { MysqlBin, MysqlNative } from 'coa-mysql'

// Define the default structure of User
const userScheme = {
  userId: '' as string,
  name: '' as string,
  mobile: '' as string,
  avatar: '' as string,
  gender: 1 as number,
  language: '' as string,
  status: 1 as number,
  created: 0 as number,
  updated: 0 as number,
}
// Define the User type (automatically generated by the default structure)
type UserScheme = typeof userScheme

// Initialization by base class
const User = new (class extends MysqlNative<UserScheme> {
  constructor() {
    super(
      {
        name: 'User', // Table name, default transformation into a `snackcase` format, such as User->user UserPhoto->user_photo
        title: 'User Table', // Table note name
        scheme: userScheme, // Default structure of the table
        pick: ['userId', 'name'], // Field information displayed when querying the list
      },
      // Binding configuration instance bin
      mysqlBin
    )
  }

  // Custom method
  async customMethod() {
    // Do something
  }
})()

Generally, a data sheet corresponds to a model, and after the model is defined, we can operate the model directly to operate the table

// Insert
await User.insert({ name: 'Tom', gender: 1 }) // return 'id001', userId = 'id001' of this data

// Batch insert
await User.mInsert([
  { name: 'Tom', gender: 1 },
  { name: 'Jerry', gender: 1 },
]) // return ['id002','id003']

// Update by ID
await User.updateById('id002', { name: 'Lily' }) // return 1

// Batch update by ID array
await User.updateByIds(['id002', 'id003'], { status: 2 }) // return 2

// Update or insert the ID (id exists if updated, if there is no insert)
await User.upsertById('id002', { name: 'Tom', gender: 1 }) // return 1, update one data of userId = 'id02'
await User.upsertById('id004', { name: 'Lily', gender: 1 }) // return 0, insert a new data of userId = 'id04'

// Delete by ID array
await User.deleteByIds(['id003', 'id004']) // return 2

// Query one by ID, the second parameter settings return the data contained in the result
await User.getById('id001', ['name']) // data is {userId:'id001',name:'Tom',gender:1,status:1,...} return {userId:'id001',name:'Tom'}

// Get multiple data by ID array
await User.mGetByIds(['id001', 'id002'], ['name']) // return {id001:{userId:'id001',name:'Tom'},id002:{userId:'id002',name:'Lily'}}

// Truncate table
await User.truncate() // void, do not report an error is to operate successfully

// Custom method
await User.customMethod() // call a custom method

In the actual project, we may need to define multiple models, and there are some public methods on each model. At this time, we can abstract a base class model, other models inherit this base class model

import { CoaMysql } from 'coa-mysql'

// Define the base class of a model by mysqlBin, each model can use this base class
export class MysqlNativeModel<T> extends MysqlNative<T> {
  constructor(option: CoaMysql.ModelOption<T>) {
    // Configure the instance bin binding
    super(option, mysqlBin)
  }

  // You can also define some general methods
  commonMethod() {
    // do something
  }
}

// Define user model by base model
const User = new (class extends MysqlNativeModel<UserScheme> {
  constructor() {
    super({ name: 'User', title: 'User Table', scheme: userScheme, pick: ['userId', 'name'] })
  }

  // Custom method
  async customMethodForUser() {
    // Do something
  }
})()

// Define Manager model by base model
const Manager = new (class extends MysqlNativeModel<ManagerScheme> {
  constructor() {
    super({ name: 'Manager', title: 'Manager Table', scheme: managerScheme, pick: ['managerId', 'name'] })
  }
})()

// Both user model and manager model can call common method
await User.commonMethod()
await Manager.commonMethod()

// Only user models can call custom method
await User.customMethodForUser()

Cache data model

Based on coa-redis to achieve fast and efficient data cache logic, and unify the cache, maintain the life cycle of the cache, to ensure the consistency of cache and mysql data

Need to install coa-redis before use, instructions for use to view here

The method of use of cache data model is exactly the same as the basic data model. Only need to replace the MysqlNative to be MysqlCache

import { CoaMysql, MysqlCache } from 'coa-mysql'
import { RedisBin, RedisCache } from 'coa-redis'

// Define a Redis instance, detail usage see https://github.com/coajs/coa-redis
const redisCache = new RedisCache(new RedisBin({ host: '127.0.0.1' }))

// Define the base class for a cache data model
export class MysqlCacheModel<T> extends MysqlCache<T> {
  constructor(option: CoaMysql.ModelOption<T>) {
    // Bind the configuration instance and the redisCache instance on this base class
    super(option, mysqlBin, redisCache)
  }
}

// Define cache user model by cache base class
const UserCached = new (class extends MysqlCacheModel<UserScheme> {
  constructor() {
    super({ name: 'User', title: 'User Table', scheme: userScheme, pick: ['userId', 'name'] })
  }
})()

// Query data
await User.getById('id001') // First query will read the database
await User.getById('id001') // The second call will read data directly from the cache

// Insert, delete, update, just like the basic data model
await User.insert({ name: 'Tom', gender: 1 }) // return 'id001'
await User.updateById('id001', { name: 'Lily' }) // return 1

The cache model automatically maintains and manages caches. If the cache already exists, then call updated updated the data, and automatically remove the latest data from the database when querying the data again. Realization Principle Click here (todo) Learn more