mysql-with-kysely

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: '[e-mail]' }     
const { insertId } = await db.execute(qb
  .insertInto('user')
  .values(value),
)

// close MySQL connection
await close()

Database type

• WithPkId: for auto increment id column
• WithDataLifecycleTracker
  • created_at: for DATETIME DEFAULT CURRENT_TIMESTAMP
  • updated_at: for DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• WithSchema: WithPkId & WithDataLifecycleTracker

Recommended Usages Personally

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