MySQL Pool

Golang MySQL wrapper supporting chain calls, read-write separation, query builder, and complete connection management.

Node.js version here | PHP version here

Key Features

Read-Write Separation

Support read-write connection pool configuration, enabling pre-connections to improve efficiency.

Query Builder

Provide a chainable SQL query builder interface to prevent SQL injection attacks.

CRUD Operations

Complete support for create, read, update, and delete operations.

Dependencies

• github.com/go-sql-driver/mysql

Usage

Installation

go get github.com/pardnchiu/go-mysql

Environment Variables

You can configure the database connection using environment variables. Below is an example .env file:

# default 127.0.0.1
MYSQL_READ_HOST=
# default 3306
MYSQL_READ_PORT=
# default root
MYSQL_READ_USER=
# default empty
MYSQL_READ_PASSWORD=
# default utf8mb4
MYSQL_READ_CHARSET=
# default 4
MYSQL_READ_CONNECTION=

# * default is read
MYSQL_WRITE_HOST=
MYSQL_WRITE_PORT=
MYSQL_WRITE_USER=
MYSQL_WRITE_PASSWORD=
MYSQL_WRITE_CHARSET=
MYSQL_WRITE_CONNECTION=

Initialization

package main

import (
  "fmt"
  "log"
  
  goMysql "github.com/pardnchiu/go-mysql"
)

func main() {
  config := goMysql.Config{
    Read: &goMysql.DBConfig{
      Host:       "localhost",
      Port:       3306,
      User:       "root",
      Password:   "password",
      Charset:    "utf8mb4",
      Connection: 10,
    },
    Write: &goMysql.DBConfig{
      Host:       "localhost",
      Port:       3306,
      User:       "root",
      Password:   "password",
      Charset:    "utf8mb4",
      Connection: 5,
    },
  }
  
  // Initialize
  pool, err := goMysql.New(config)
  if err != nil {
    log.Fatal(err)
  }
  defer pool.Close()
  
  // Insert data
  userData := map[string]interface{}{
    "name":  "John Doe",
    "email": "john@example.com",
    "age":   30,
  }
  
  lastID, err := pool.Write.
    DB("myapp").
    Table("users").
    Insert(userData)
  if err != nil {
    log.Fatal(err)
  }
  
  fmt.Printf("Inserted user ID: %d\n", lastID)
  
  // Query data
  rows, err := pool.Read.
    DB("myapp").
    Table("users").
    Select("id", "name", "email").
    Where("age", ">", 18).
    OrderBy("created_at", "DESC").
    Limit(10).
    Get()
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  
  for rows.Next() {
    var id int
    var name, email string
    err := rows.Scan(&id, &name, &email)
    if err != nil {
      log.Fatal(err)
    }
    fmt.Printf("User: %s (%s)\n", name, email)
  }
}

package main

import (
  "fmt"
  "log"
  
  goMysql "github.com/pardnchiu/go-mysql"
)

func main() {
  // Initialize by .env, without config
  pool, err := goMysql.New()
  if err != nil {
    log.Fatal(err)
  }
  defer pool.Close()
  
  ...
}

Configuration Overview

type Config struct {
  Read  *DBConfig
  Write *DBConfig
}

type DBConfig struct {
  Host       string // Database host address
  Port       int    // Database port
  User       string // Database username
  Password   string // Database password
  Charset    string // Character set (default: utf8mb4)
  Connection int    // Maximum connections
}

Supported Operations

Query

// Basic query
rows, err := pool.Read.
  DB("database_name").
  Table("users").
  Select("id", "name", "email").
  Where("status", "active").
  Get()

// Complex conditional query
rows, err := pool.Read.
  DB("database_name").
  Table("users").
  Select("*").
  Where("age", ">", 18).
  Where("status", "active").
  Where("name", "LIKE", "John").
  OrderBy("created_at", "DESC").
  Limit(10).
  Offset(20).
  Get()

// JOIN query
rows, err := pool.Read.
  DB("database_name").
  Table("users").
  Select("users.name", "profiles.bio").
  LeftJoin("profiles", "users.id", "profiles.user_id").
  Where("users.status", "active").
  Get()

// Count total
rows, err := pool.Read.
  DB("database_name").
  Table("users").
  Select("id", "name").
  Where("status", "active").
  Total().
  Limit(10).
  Get()

CRUD

// Insert data
data := map[string]interface{}{
  "name":  "Jane Doe",
  "email": "jane@example.com",
  "age":   25,
}

lastID, err := pool.Write.
  DB("database_name").
  Table("users").
  Insert(data)

// Update data
updateData := map[string]interface{}{
  "age":    26,
  "status": "updated",
}

result, err := pool.Write.
  DB("database_name").
  Table("users").
  Where("id", 1).
  Update(updateData)

// Upsert operation
data := map[string]interface{}{
  "email": "unique@example.com",
  "name":  "New User",
}

updateData := map[string]interface{}{
  "name": "Updated User",
  "last_login": "NOW()",
}

lastID, err := pool.Write.
  DB("database_name").
  Table("users").
  Upsert(data, updateData)

// Increment values
result, err := pool.Write.
  DB("database_name").
  Table("users").
  Where("id", 1).
  Increase("view_count", 1).
  Update()

SQL

// Direct query
rows, err := pool.Read.Query("SELECT * FROM users WHERE age > ?", 18)

// Direct execution
result, err := pool.Write.Exec("UPDATE users SET last_login = NOW() WHERE id = ?", userID)

License

This project is licensed under the MIT license.

Author

邱敬幃 Pardn Chiu

---

©️ 2025 邱敬幃 Pardn Chiu