Ninja Pumpkin Mutants

    TypeScript icon, indicating that this package has built-in type declarations

    1.6.1 • Public • Published


    GitHub license npm version npm downloads PRs Welcome

    English | 简体中文

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


    • 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


    • 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


    yarn add coa-mysql

    Instance configuration

    import { MysqlBin } from 'coa-mysql'
    // MySQL configuration
    const mysqlConfig = {
      host: '',
      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',
      UNIQUE KEY `user_userid_unique` (`userId`) USING BTREE
    ) COMMENT='User Table';

    SQL operations on the user table

    // Insert data, see'user').insert({ userId: 'user-a', name: 'A', mobile: '15010001001', gender: 1, language: 'zh-CN', status: 1 })
    // Query all data, see'user').select()'*').from('user')
    // Conditional queries, see'user').where('status', '=', 1)
    // Update data, see'user').update({ name: 'AA', gender: 2 }).where({ userId: 'user-a' })
    // Delete data, see'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() {
            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
      // 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
    const redisCache = new RedisCache(new RedisBin({ host: '' }))
    // 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


    npm i coa-mysql

    DownloadsWeekly Downloads






    Unpacked Size

    58.5 kB

    Total Files


    Last publish


    • adaex