mysql-with-kysely

• mysql-with-kysely use mysql2 and kysely
• Handle MySQL BIGINT type as string
• Incorrect arguments to mysqld_stmt_execute error safe!
  • Since MySQL 8.0.22, mysql2 driver has Incorrect arguments to mysqld_stmt_execute issue
  • OffsetLimitTypeCastingPlugin resolves it

Installation

$ yarn add mysql-with-kysely

Usages

import { toInsertableSchema, toFullSelectableSchema, WithSchema, connect, queryBuilder } from 'mysql-with-kysely'
import type { User } from './model'

// for query builder
type Database = {
  user: WithSchema<User>
}

// for your code
type SelectableSchema = toFullSelectableSchema<Database>
type InsertableSchema = toInsertableSchema<Database>

const { db, close } = connect<Database>({ uri: 'mysql://root:root@localhost:3306/test' });

// collect your metrics
db.subscribe(({ sql, normalizedSql, durationMs, occurredAt, parameters }) => {
})

// your query builder
const qb = queryBuilder<Database>()

// write your codes type safely
// type of users is `Array<SelectableSchema['user']>`
// if you don't need created_at & updated_at, use toSelectableSchema instead of toFullSelectableSchema
const users = await db.query(qb
  .selectFrom('user')
  .selectAll()
  .orderBy('id', 'desc')
  .limit(1),
)

// type of value is `InsertableSchema['user']`
const value = { name: 'kanziw', email: '[email protected]' }     
const { insertId } = await db.execute(qb
  .insertInto('user')
  .values(value),
)

// close MySQL connection
await close()

Database type

1. WithPkId: for auto increment id column
2. WithDataLifecycleTracker
   1. created_at: for DATETIME DEFAULT CURRENT_TIMESTAMP
   2. updated_at: for DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
3. WithSchema: WithPkId & WithDataLifecycleTracker

Recommended Usages Personally

1. Write DDL
   1. See ddl.sql
2. Prepare types
   1. Apply DDL to MySQL Server using mysqldef
   2. Generate model using sql-ts
   3. See db:sync script in package.json
3. Set up your own query builder
   1. See mysql.ts
4. Write your own code!
   1. See integration.spec.ts
5. Write your test code using createMockMySqlHelper
   1. See mockMySql.spec.ts
6. Subscribe MySQL Metrics
   1. See subscribe.spec.ts