Gg-mysql-connector NPM

GG-Mysql-Connector

gg-mysql-connector is an intuitive MySQL model creator designed to simplify database management and operations by synchronizing TypeScript models with MySQL tables. This package allows you to declare and manage your MySQL database structure programmatically, with strong type enforcement.

Features

Declare Database Structure: Define your database tables and columns as TypeScript variables.
Sync Database Structure: Automatically synchronize your declared models with the actual MySQL database.
Automated View Management: Automatically create or update SQL views based on your declarations.
Generate TypeScript Models: Generate interfaces that mirror your MySQL structure, ensuring type-safe operations.
Type-Safe MySQL Connector: Provides type-enforced MySQL operations like CRUD (Select, Update, Delete).

Installation

npm install gg-mysql-connector

1. Declare Your Model

Start by declaring your database tables and columns as TypeScript variables:

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)" },
    ],
  },
  {
    tableName: "item",
    columns: [
      { COLUMN_NAME: "id", DATA_TYPE: "int", AUTO_INCREMENT: true },
      { COLUMN_NAME: "name", DATA_TYPE: "varchar(512)" },
      { COLUMN_NAME: "price", DATA_TYPE: "float" },
      {
        COLUMN_NAME: "status",
        DATA_TYPE: "varchar(64)",
        POSSIBLE_VALUE: ["ACTIVE", "DISABLE"],
      },
      { COLUMN_NAME: "description", DATA_TYPE: "varchar(512)" },
    ],
  },
]

const viewData: MyViewModel[] = [
  {
    viewName: "item_view",
    sqlStatement: SQL`CREATE OR REPLACE VIEW item_view AS SELECT id ,name , amount, price FROM item`,
  },
]

2. Sync Model and Views with MySQL & Generate Model Interface

Use the provided functions to sync your model with MySQL and generate TypeScript interfaces.

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

const migrator = new ModelGenerator(
  {
    host: "your-host",
    user: "your-user",
    password: "your-password",
    database: "test_ggdb_connector",
  },
  model
)

await migrator.init()
await migrator.pushModelToDB()

// from file
await migrator.pushViewToDB("./views")
// from code
await migrator.pushViewToDB_v2(viewData)
await migrator.generateModelInterface({
  appName: "app",
  model: model,
  outputDirectory: ["./"],
})

process.exit()

3. CRUD Operations with Type Enforcement

Once you've generated your interfaces, you can perform type-enforced MySQL operations.

import GGMySQLConnector from "gg-mysql-connector"
import app_INF from "./your-output-directory/app_INF" // Generated in step 2

const db = new GGMySQLConnector<app_INF>({
  host: "your-host",
  user: "your-user",
  password: "your-password",
  database: "test_ggdb_connector",
})

await db.init()

// Select operations
const result_1 = await db.select("item")
const result_2 = await db.selectByID("user", 2)
const result_3 = await db.selectByMatchParams("item", { id: 5 })
const result_4 = await db.selectByMatchParams("item", { name: "pen", price: 3 })

// Update operations
const result_5 = await db.update("user", { id: 3, name: "Tony" })
const result_6 = await db.update("item", { id: 3, price: 4, name: "Ruler" })
const result_7 = await db.updateOnlyID("item", { oldID: 7, newID: 4 })

// Delete operations
const result_8 = await db.deleteByID("item", 5)
const result_9 = await db.deleteByMatchParams("item", { name: "pen", price: 5 })

// raw query
const result_10 = await db.query("SELECT * FROM item where id = ?", [5])