gg-mysql-connector

gg-mysql-connector is a TypeScript-first MySQL toolkit for:

• defining table schemas in code
• migrating tables and views into MySQL
• generating TypeScript and Zod artifacts from database structure
• querying MySQL through a typed connector

It is designed for projects that want one source of truth for schema and application types.

Installation

npm install gg-mysql-connector

Root API

import {
  GGMySQLConnector,
  MySQLSchemaManager,
  classifyDbError,
  formatSchemaDiffResult,
  generateZodSchemaFromMySQL,
  setSlowQueryReporter,
  type MyModel,
  type ColumnContent,
  type ColumnType,
  type CompositeColumn,
  type DatabaseConfigInterface,
  type SchemaDiffResult,
  type SlowQueryEvent,
  type DbErrorCategory,
  type GenerateZodSchemaFromMySQLParams,
  type GenerateZodSchemaFromMySQLResult,
} from "gg-mysql-connector"

Main Concepts

Database Config

Pool options are optional. Defaults remain compatible with older versions.

const dbInfo: DatabaseConfigInterface = {
  host,
  user,
  password,
  database,
  port: 3306,
  dateStrings: true,
  connectionLimit: 3,
  waitForConnections: true,
  connectTimeout: 10000,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 1000,
}

const db = new GGMySQLConnector(dbInfo)

Defaults:

• connectionLimit: 10
• waitForConnections: true
• connectTimeout: 60000
• queueLimit: 0

Invalid numeric values fall back to defaults. Boolean options are used only when the value is actually boolean.

1. Define schema with MyModel

import { MyModel } from "gg-mysql-connector"

const model: MyModel[] = [
  {
    tableName: "user",
    columns: [
      {
        COLUMN_NAME: "id",
        DATA_TYPE: "int",
        AUTO_INCREMENT: true,
      },
      {
        COLUMN_NAME: "name",
        DATA_TYPE: "varchar(512)",
      },
      {
        COLUMN_NAME: "email",
        DATA_TYPE: "varchar(512)",
        IS_UNIQUE: true,
      },
      {
        COLUMN_NAME: "status",
        DATA_TYPE: "enum",
        POSSIBLE_VALUE: ["ACTIVE", "DISABLED"],
        COLUMN_DEFAULT: "ACTIVE",
      },
      {
        COLUMN_NAME: "created_at",
        DATA_TYPE: "datetime",
        IS_NOT_NULL: true,
        COLUMN_DEFAULT: "current_timestamp()",
      },
    ],
  },
  {
    tableName: "post",
    columns: [
      {
        COLUMN_NAME: "id",
        DATA_TYPE: "int",
        AUTO_INCREMENT: true,
      },
      {
        COLUMN_NAME: "user_id",
        DATA_TYPE: "int",
        IS_INDEX: true,
        FOREIGN_KEY: [
          {
            tableName: "user",
            columnName: "id",
            ON_UPDATE: "CASCADE",
            ON_DELETE: "CASCADE",
          },
        ],
      },
      {
        COLUMN_NAME: "title",
        DATA_TYPE: "varchar(512)",
      },
      {
        COLUMN_NAME: "content",
        DATA_TYPE: "longtext",
      },
    ],
    compositeColumn: [
      {
        type: "INDEX",
        columnName: ["user_id", "title"],
      },
    ],
  },
]

2. Migrate tables and views with MySQLSchemaManager

import { MySQLSchemaManager } from "gg-mysql-connector"

const dbInfo = {
  host: "127.0.0.1",
  user: "root",
  password: "password",
  database: "example_app",
  port: 3306,
  dateStrings: true,
}

const migrator = new MySQLSchemaManager(dbInfo, model)

migrator.isShowProgress = true
migrator.isVerbose = false
migrator.isForcePrintSQLStatement = false

await migrator.init()
await migrator.pushSchemaToDatabase()

What pushSchemaToDatabase() does:

• creates the database if it does not exist
• creates missing tables
• adds or modifies columns
• updates indexes and unique keys
• updates foreign keys

Push views:

const views = [
  {
    viewName: "post_view",
    sqlStatement: `
      CREATE OR REPLACE VIEW post_view AS
      SELECT p.id, p.title, u.name AS user_name
      FROM post p
      LEFT JOIN user u ON u.id = p.user_id
    `,
  },
]

await migrator.pushViewsToDatabase(views)

3. Check schema without mutating the database

Use checkSchema() in CI/CD before deployment when you need to verify that the target database already matches the current model.

import {
  ModelGeneratorV2,
  formatSchemaDiffResult,
  type SchemaDiffResult,
} from "gg-mysql-connector"

const manager = new ModelGeneratorV2(dbInfo, model)
const result: SchemaDiffResult = await manager.checkSchema(views)

if (!result.ok) {
  console.error(formatSchemaDiffResult(result))
  process.exit(1)
}

checkSchema() is read-only. It does not call init(), does not create a database, and does not run schema or data mutation such as CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, REPLACE, or TRUNCATE. It reads MySQL metadata through INFORMATION_SCHEMA and SHOW INDEX; it may select the target database context if needed for metadata reads, but it must not create or modify anything.

Do not call init() before checkSchema() if you want a no-mutation check. init() belongs to the migration path and may create the database.

Recommended deployment flow:

1. Run checkSchema() and fail fast if the database is not ready.
2. Run the migration step separately with init() and pushModelToDB() or pushSchemaToDatabase() when you intentionally want to mutate the database.
3. Run checkSchema() again to verify the migrated schema before starting the application.

Code Generation

Generate interface from database structure

await migrator.generateTypeScriptInterfaces({
  appName: "app",
  model,
  outputDirectory: ["./src/generated"],
})

This writes:

• app_INF.ts

Generate const structure and DB Zod file

await migrator.generateModelConstAndZodStructure({
  appName: "app",
  model,
  outputDirectory: ["./src/generated"],
})

This writes:

• app_model_const_v2.ts
• app_model_db_zod_v2.ts

Generate Zod file only

await migrator.generateZodSchemaFiles({
  appName: "app",
  model,
  outputDirectory: ["./src/generated"],
})

This writes:

• app_model_zod.ts

Generate Zod directly from MySQL

import { generateZodSchemaFromMySQL } from "gg-mysql-connector"

await generateZodSchemaFromMySQL({
  model,
  dbInfo,
  outputFilePath: "./src/generated/db_zod_structure.ts",
  exportName: "db_zod_structure",
})

Querying with GGMySQLConnector

import { GGMySQLConnector } from "gg-mysql-connector"
import type app_INF from "./generated/app_INF"

const db = new GGMySQLConnector<app_INF, ["id"]>(dbInfo)

await db.init()

dateStrings is forwarded to every mysql2/promise pool and connection created by the package. When dateStrings: true, MySQL DATE, DATETIME, and TIMESTAMP values are returned as strings instead of JavaScript Date objects. If omitted, mysql2 keeps its default behavior.

Read helpers

await db.select("user")
await db.selectMany("user", {
  where: { status: "ACTIVE" },
  orderBy: { id: "DESC" },
  limit: 50,
  offset: 0,
})

await db.selectOneByID("user", 1)
await db.selectOne("user", { email: "[email protected]" })
await db.selectByMatchParams("user", { name: "alice" })
await db.selectOneByMatchParams("user", { email: "[email protected]" })
await db.selectByIDs("user", [1, 2, 3])

await db.existsByID("user", 1)
await db.existsByMatchParams("user", { email: "[email protected]" })
await db.count("user", { status: "ACTIVE" })
await db.countByMatchParams("user", { status: "ACTIVE" })

await db.paginate("user", {
  where: { status: "ACTIVE" },
  orderBy: { id: "DESC" },
  page: 1,
  pageSize: 20,
})

await db.selectByDateRange("invoice", {
  columnName: "created_at",
  startDate: "2026-01-01",
  endDate: "2026-01-31",
})

Write helpers

await db.insert("user", { name: "alice" })
const user = await db.insertAndSelect("user", { name: "charlie" })

await db.insertMany("user", [{ name: "alice" }, { name: "bob" }])
await db.upsert(
  "user",
  { id: 1, name: "alice updated" },
  {
    conflictColumns: ["id"],
    updateColumns: ["name"],
  },
)

await db.update("user", { id: 1, name: "alice 2" })
await db.updateByMatchParams("user", {
  where: { email: "[email protected]" },
  set: { name: "alice 3" },
})
await db.updateOnlyID("user", { oldID: 1, newID: 100 })

await db.deleteByID("user", 100)
await db.deleteByMatchParams("user", { name: "alice 2" })
await db.deleteMany("user", { status: "DISABLED" })
await db.deleteByIDs("user", [1, 2, 3])

Search

await db.search("user", {
  text: "alice active",
  columns: ["name", "email"],
})

// Backward-compatible full-table search. This casts every column to CHAR and can be slow.
await db.selectBySearchTextInRow("user", "alice active")

Raw SQL

await db.query("SELECT * FROM user WHERE id = ?", [1])

Raw queries return rows, matching the original mysql2/promise pool query behavior used by this package.

You can attach lightweight context to a query. Context is optional and is only used in instrumentation events and error output:

await db.query("SELECT * FROM booking WHERE id = ?", [bookingID], {
  tag: "booking.getDetail",
  operationName: "GetBookingGroupDetail",
})

Transactions

Use transaction() when multiple statements must run on the same MySQL connection.

const result = await db.transaction(async (tx) => {
  await tx.query("INSERT INTO user SET ?", [{ name: "alice" }], {
    tag: "user.create",
  })

  await tx.query("UPDATE account SET user_count = user_count + 1 WHERE id = ?", [
    accountID,
  ])

  return { ok: true }
})

Transaction behavior:

• uses one connection for every tx.query() in the callback
• calls beginTransaction() before the callback
• commits when the callback resolves
• rolls back when the callback throws
• always releases the connection in finally
• returns the callback result
• does not auto-retry the whole transaction
• nested transactions are not supported and throw a clear error

tx.query() supports the same query instrumentation options as db.query(). Slow query events from transaction queries include transactionID and transactionQueryIndex.

Notes:

• selectByID() still exists for backward compatibility, but it is deprecated. Use selectOneByID() instead.
• search() requires explicit columns. selectBySearchTextInRow() is kept for legacy full-table search, but it can be slow on large tables.
• select() returns the full table. Prefer selectMany() with limit, where, and orderBy in application code.
• SQL identifiers generated by CRUD helpers are quoted internally. Raw query() calls are passed through unchanged.

Supported Column Types

Current ColumnType values:

• int
• tinyint
• float
• double
• text
• longtext
• date
• time
• datetime
• enum
• varchar(8)
• varchar(16)
• varchar(32)
• varchar(64)
• varchar(128)
• varchar(256)
• varchar(512)

Logging

MySQLSchemaManager has separate logging controls:

• isShowProgress shows migration progress per table and per column
• isVerbose shows extra migration/debug logs
• isForcePrintSQLStatement prints executed SQL statements

Example:

migrator.isShowProgress = true
migrator.isVerbose = false
migrator.isForcePrintSQLStatement = false

GGMySQLConnector has query logging controls:

db.isPrintStatement = false
db.isPrintError = true

await db.query("SELECT 1", [], {
  isPrint: false,
  isPrintError: false,
})

isPrint uses mysql2 formatting for legacy debug output and may include parameter values. Do not enable it for sensitive production logs.

Query Instrumentation

The connector can emit generic query events without knowing anything about your app schema, request context, logger, or web framework.

Slow query reporter

import { setSlowQueryReporter, type SlowQueryEvent } from "gg-mysql-connector"

setSlowQueryReporter(async (event: SlowQueryEvent) => {
  // Send to your app logger, metrics system, APM, or database log service.
  // The package does not write app-specific DB logs by itself.
  console.log(event)
})

Reporter behavior:

• fire-and-forget; query execution does not await the reporter
• reporter throw/reject is caught and logged briefly
• params are summarized, not formatted into SQL
• raw SQL preview is truncated to 500 characters

SlowQueryEvent includes:

type SlowQueryEvent = {
  database: string
  durationMs: number
  statementType: string
  tableHint: string | null
  sqlPreview: string
  paramsSummary: object
  status: "success" | "error" | "timeout-warning" | "connection-lost" | "pool-invalid" | "pool-recreated"
  eventType?: "slow-query" | "timeout-warning" | "connection-lost" | "pool-invalid" | "pool-recreated"
  totalMs: number
  tag?: string
  operationName?: string
  transactionID?: string
  transactionQueryIndex?: number
  attempt?: number
  acquireMs?: number
  queryMs?: number
  reason?: string
  poolType?: string
  poolKeys?: string[]
  isRecreatingPool?: boolean
  poolUnhealthy?: boolean
  errorCode?: string
  errorMessage?: string
  errorCategory?: DbErrorCategory
}

Slow query thresholds

Default slow query threshold is 1000ms.

GG_MYSQL_SLOW_QUERY_THRESHOLD_MS=1000
GG_MYSQL_SLOW_QUERY_LOG=1
GG_MYSQL_SLOW_QUERY_CONSOLE=1

Query-level overrides:

await db.query(sql, params, {
  slowQueryThresholdMs: 250,
  disableSlowQueryLog: false,
})

Threshold values must be finite numbers greater than 0. Invalid values fall back to env/default, so bad config does not accidentally report every query.

Controls:

• GG_MYSQL_SLOW_QUERY_LOG=0 disables both console warning and reporter events
• GG_MYSQL_SLOW_QUERY_CONSOLE=0 disables console warning only; reporter still runs
• disableSlowQueryLog: true disables both console warning and reporter for that query

Query timeout warning

Timeout warning detects a query that is still running after a threshold. It does not cancel the query and does not throw.

Default timeout warning threshold is 30000ms.

GG_MYSQL_QUERY_TIMEOUT_WARNING_MS=30000

Query-level options:

await db.query(sql, params, {
  timeoutWarningMs: 5000,
  disableTimeoutWarning: false,
})

Set GG_MYSQL_QUERY_TIMEOUT_WARNING_MS=0 or disableTimeoutWarning: true to disable timeout warning. Invalid env values fall back to the default.

Query timeout and pool recovery

Queries fail fast by default after 10000ms using mysql2 query timeout support.

GG_MYSQL_QUERY_TIMEOUT_MS=10000
GG_MYSQL_ACQUIRE_TIMEOUT_MS=2000
GG_MYSQL_POOL_RECREATE_WAIT_MS=2000

Query-level overrides:

await db.query(sql, params, {
  queryTimeoutMs: 3000,
  retrySelectOnConnectionError: true,
  poolRecreateWaitMs: 1000,
})

Set GG_MYSQL_QUERY_TIMEOUT_MS=0 or queryTimeoutMs: 0 to disable query timeout for long-running maintenance queries. Connection acquire timeout defaults to 2000ms; it prevents requests from sitting behind a stuck pool for 20-30 seconds.

When the connector sees connection-level errors such as PROTOCOL_CONNECTION_LOST, ECONNRESET, ETIMEDOUT, PROTOCOL_SEQUENCE_TIMEOUT, or connector acquire/pool timeout errors, it marks the pool unhealthy and recreates it with single-flight protection. Concurrent requests wait for the in-flight recreate up to poolRecreateWaitMs, then fail with GG_MYSQL_POOL_UNHEALTHY.

The connector validates that the active pool has getConnection() before acquiring a connection. If the pool object is invalid, it emits pool-invalid with pool diagnostics, marks the pool unhealthy, recreates the pool once, and retries the query if allowRetry !== false. If recovery is disabled or fails, it throws GG_MYSQL_INVALID_POOL instead of leaking a raw TypeError.

The connector does not automatically retry non-idempotent statements. A SELECT can be retried once after pool recreation only when retrySelectOnConnectionError: true is set. Retry attempts include attempt in instrumentation events.

Pool acquire timing

query() uses pool.getConnection() so instrumentation can split pool wait time from query execution time:

The connector measures acquireMs, queryMs, and totalMs, then releases the connection in finally. Transaction queries already use one connection, so they only report queryMs.

DB error classification

Use classifyDbError() to normalize MySQL and connection errors:

import { classifyDbError } from "gg-mysql-connector"

try {
  await db.query(sql, params)
} catch (error) {
  const category = classifyDbError(error)
  if (category === "DUPLICATE_KEY") {
    // handle duplicate key
  }
  throw error
}

Categories:

• LOCK_TIMEOUT
• DEADLOCK
• CONNECTION
• DUPLICATE_KEY
• DATA_TOO_LONG
• DATA_TRUNCATED
• BAD_FIELD
• SYNTAX
• VALIDATION
• UNKNOWN

SlowQueryEvent error events include errorCategory, errorCode, and errorMessage.

Testing

Integration tests read database credentials from environment variables:

GG_MYSQL_CONNECTOR_TEST_HOST=127.0.0.1
GG_MYSQL_CONNECTOR_TEST_PORT=3306
GG_MYSQL_CONNECTOR_TEST_USER=root
GG_MYSQL_CONNECTOR_TEST_PASSWORD=password
GG_MYSQL_CONNECTOR_TEST_DATABASE=test_ggdb_connector
GG_MYSQL_CONNECTOR_TEST_DATE_STRINGS=true

The repository includes .env.example. Local .env files are ignored by git.

Run all tests:

set -a
source .env
set +a
npm test -- --runInBand

Run type checks:

npx tsc --noEmit
npx tsc -p tests/tsconfig.json

The integration test truncates only the tables defined in its test model before running.

Build and package check:

npm run build
npm pack --dry-run

The package publishes files from dist. The prepack script runs tsc so generated files such as dist/connector/SlowQueryReporter.js and dist/connector/DbErrorClassifier.js are included in npm packs.

Project Structure

Current source layout:

src/
  codegen/
  connector/
  db/
  migrator/
  types/

Notes

• This package assumes MySQL and uses mysql2/promise.
• The connector is intentionally app-agnostic. It does not know about request objects, Express, RunMyRoom, or app-specific log schemas.
• The connector does not auto-EXPLAIN queries and does not cancel long-running queries by default.
• Generated files should usually be written into a separate output folder in your app, not into this package source tree.
• Backward-compatible aliases still exist for older names such as ModelGeneratorV2, mysqlToZod, columnType, columnContent, and compoisteColumn.