sqlxml

README

sqlxml

---

An easy-to-use extension for sqlx ，base on xml files and named query/exec

this repo is under development, please do not use it in production.

install

go get github.com/nvac/sqlxml

Usage

1. set database config in xml file

• name: needs to be unique in same environment
• env: custom string，runtime environment
• driver: database driver
• dsn: data source name
• maxIdleConns: sets the maximum number of connections in the idle connection pool. if default values is required, remove the attr
• maxOpenConns: sets the maximum number of open connections to the database. if default values is required, remove the attr
• connMaxLifetimeSeconds: sets the maximum amount of time a connection may be reused. . if default values is required, remove the attr
• connMaxIdleTimeSeconds: sets the maximum amount of time a connection may be idle. if default values is required, remove the attr

<?xml version="1.0" encoding="utf-8" ?>

<databases>
    <database name="ReadDb"
              env="dev"
              driver="mysql"
              dsn="user:password@tcp(127.0.0.1:3306)/test?charset=utf8mb4&amp;parseTime=True"
              maxIdleConns="5"
              maxOpenConns="10"
              connMaxLifetimeSeconds="30"
              connMaxIdleTimeSeconds="30"
    />
    
    <database name="WriteDb"
              env="dev"
              driver="mysql"
              dsn="user:password@tcp(127.0.0.1:3306)/test?charset=utf8mb4&amp;parseTime=True"
              maxIdleConns="5"
              maxOpenConns="10"
              connMaxLifetimeSeconds="30"
              connMaxIdleTimeSeconds="30"
    />
</databases>

2. write sql script in xml file

• name: needs to be unique
• database: using the above configured database
• content: ensure in CDATA

<?xml version="1.0" encoding="utf-8" ?>

<scripts>
    <script name="GetUser">
        <![CDATA[
            SELECT username, password
            FROM `user`
            WHERE username = :username
        ]]>
    </script>

    <script name="ListUser">
        <![CDATA[
            SELECT username, password
            FROM `user`
            LIMIT 10 OFFSET 0
        ]]>
    </script>
    
    <script name="AddUser">
        <![CDATA[
            INSERT INTO user (username, password)
            VALUES (:username, :password)
        ]]>
    </script>
</scripts>

1. inti & use sqlxml

package main

import (
  "context"

  "github.com/nvac/sqlxml"
)

type User struct {
  Username string `db:"username"`
  Password string `db:"password"`
}

func main() {
  opts := &sqlxml.Options{
    DatabaseFile:     "config/databases.xml",
    ScriptsGlobFiles: "config/scripts/*.xml",
    Env:              "dev",
    DsnDecryptFunc: func(source string) string {
      return source
    },
  }

  client := sqlxml.NewClient(opts)
  if client.Error() != nil {
    panic(client.Error())
  }

  readDb := client.Database("ReadDb")
  if readDb.Error() != nil {
    panic(readDb.Error())
  }

  ctx := context.TODO()

  var user User
  if err := readDb.QueryRow(ctx, "GetUser", &user, map[string]interface{}{
    "username": "lisa",
  }); err != nil {
    panic(err)
  }

  var users []User
  if err := readDb.QueryRows(ctx, "ListUser", &users, map[string]interface{}{}); err != nil {
    panic(err)
  }

  writeDb := client.Database("ReadDb")
  if writeDb.Error() != nil {
    panic(writeDb.Error())
  }

  if _, err := writeDb.Exec(ctx, "AddUser", map[string]interface{}{
    "username": "root",
    "password": "123456",
  }); err != nil {
    panic(err)
  }
}

License

MIT © nvac

Documentation

Index

• Variables
• func GetNStmt(ctx context.Context, d *Database, scriptName string) (*sqlx.NamedStmt, error)
• type Client
  • func NewClient(opt *Options) *Client
  • func (c *Client) Database(dbName string) *Database
  • func (c *Client) Error() error
• type Database
  • func (d *Database) Error() error
  • func (d *Database) Exec(ctx context.Context, scriptName string, arg any) (sql.Result, error)
  • func (d *Database) QueryRow(ctx context.Context, scriptName string, arg any, result any) error
  • func (d *Database) QueryRowByMap(ctx context.Context, scriptName string, arg map[string]any, result any) error
  • func (d *Database) QueryRows(ctx context.Context, scriptName string, arg any, result any) error
  • func (d *Database) QueryRowsByMap(ctx context.Context, scriptName string, arg map[string]any, result any) error
• type Options

Variables

var ErrNoScript = errors.New("sqlxml: no script in config file")

Functions

func GetNStmt

func GetNStmt(ctx context.Context, d *Database, scriptName string) (*sqlx.NamedStmt, error)

Types

type Client

type Client struct {
	// contains filtered or unexported fields
}

func NewClient

func NewClient(opt *Options) *Client

func (*Client) Database

func (c *Client) Database(dbName string) *Database

func (*Client) Error

func (c *Client) Error() error

type Database

type Database struct {
	// contains filtered or unexported fields
}

func (*Database) Error

func (d *Database) Error() error

func (*Database) Exec

func (d *Database) Exec(ctx context.Context, scriptName string, arg any) (sql.Result, error)

func (*Database) QueryRow

func (d *Database) QueryRow(ctx context.Context, scriptName string, arg any, result any) error

func (*Database) QueryRowByMap

func (d *Database) QueryRowByMap(ctx context.Context, scriptName string, arg map[string]any, result any) error

func (*Database) QueryRows

func (d *Database) QueryRows(ctx context.Context, scriptName string, arg any, result any) error

func (*Database) QueryRowsByMap

func (d *Database) QueryRowsByMap(ctx context.Context, scriptName string, arg map[string]any, result any) error

type Options

type Options struct {
	DatabaseFile     string
	ScriptsGlobFiles string
	Env              string
	DsnDecryptFunc   func(dsn string) string
}