An extension for Go's sql package in the standard library to support dynamic queries directly from the database, as well as on database connections, prepared statements and transactions

  • By Quinn Millican
  • Last update: Dec 9, 2022
  • Comments: 10

dynaQ logo

Go Reference go reportcard License Go version
An extension for Go's sql package in the standard library to support dynamic queries directly from the database, as well as on database connections, prepared statements and transactions

How do I use dynaQ?

Installation

go get github.com/syke99/dynaQ

Basic Usage

package main

import (
	"database/sql"
	"fmt"

	"github.com/syke99/dynaQ"
	_ "github.com/go-sql-driver/mysql"
)

func dsn(dbName string) string {
	return fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbName)
}

func main() {
	
  // create an instance of your database
    db, err := sql.Open("mysql", dsn(""))
    if err != nil {
	    panic(err)
    }
    defer db.Close()
    
    // create a new dynamic querier with your database instance,
    // you can either pass in your own time format to match the
    // format of time values stored in your database, or if they
    // match the format "2006-01-02 15:04", you can pass in ""
    // and dynaQ will default to that format
    dq := dynaQ.NewDynaQ(db, "")
    
    // after creating a dynamic querier, just pass in whatever
    // query string you want, and whatever variable amouont of query
    // arguments you need.
    // you can query for multiple rows, or just one row at once
    //    
    // testTable:
    // __| id | name | cost | available |   created-date   |__
    // ---------------------------------------------------------
    //   |  1 |  ab  | 2.10 |   true    | 2018-01-18 05:43 |
    //   |  2 |  cd  | 1.55 |   false   | 2018-01-14 06:28 |
    //   |  3 |  ef  | 3.78 |   true    | 2018-06-27 09:59 |
    //   |  4 |  gh  | 2.76 |   true    | 2018-09-04 15:09 |
    //   |  5 |  ij  | 8.13 |   true    | 2019-01-01 23:43 |
    //   |  6 |  kl  | 4.45 |   false   | 2019-01-19 10:14 |
    //   |  7 |  mn  | 2.99 |   false   | 2019-02-11 06:22 |
    //
    // single row:
    row, err := dq.DatabaseQueryRow("select * from testTable where id in (@p1, @p2, @p3, @p4)", 1, 2, 4, 7)
    if err != nil {
	    panic(err)
    }
    
    
    fmt.Println("-----------------")
    // a dynaQ column holds the type in field
    // <columnVariable>.Type for easy type
    // assertion later on
    for _, column := range row {
	     fmt.Println(fmt.Sprintf("column: %s, value: %v (type: %s)", column.Name, fmt.Sprintf("%v", column.Value), column.Type))
    }
    fmt.Println("-----------------")
    //
    // this will output:
    // -----------------
    // column: id, value: 1 (type: int64)
    // column: name, value: ab (type: string)
    // column: cost, value: 2.10 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-01-18 05:43 (type: time.Time)
    // -----------------
	
	
    // multiple rows:
    rows, err := dq.DatabaseQuery("select * from testTable where id in (@p1, @p2, @p3, @p4)", 1, 2, 4, 7)
    if err != nil {
        panic(err)
    }
    
    newRow := true
	
    fmt.Println("-----------------")
    for newRow {
    	// the first value returned by rows.NextRow()
	// is a bool signaling whether there is another
	// row following the second value returned by
	// rows.NextRow(), which represents the current
	// row for this loop
    	if ok, row := rows.NextRow(); !ok {
		newRow = false
    	}
    	fmt.Println(fmt.Sprintf("row: %d", row.CurrentRow))
    	fmt.Println("-----------------")
	for _, column := range row {
            	fmt.Println(fmt.Sprintf("column: %s, value: %v (type: %s)", column.Name, fmt.Sprintf("%v", column.Value), column.Type))
        }
	rowNumber++
	fmt.Println("-----------------")
    }
    
    //
    // this will output:
    // -----------------
    // row: 1
    // -----------------
    // column: id, value: 1 (type: int64)
    // column: name, value: ab (type: string)
    // column: cost, value: 2.10 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-01-18 05:43 (type: time.Time)
    // -----------------
    // row: 2
    // -----------------
    // column: id, value: 2 (type: int64)
    // column: name, value: cd (type string)
    // column: cost, value: 1.55 (type float64)
    // column: available, value: false (type: bool)
    // column: created-date, value: 2018-01-14 06:28 (type: time.Time)
    // -----------------
    // row: 3
    // -----------------
    // column: id, value: 4 (type: int64)
    // column: name, value: gh (type: string)
    // column: cost, value: 2.76 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-09-04 15:09 (type: time.Time)
    // -----------------
    // row: 4
    // -----------------
    // column: id, value: 7 (type: int64)
    // column: name, value: mn (type: string)
    // column: cost, value: 2.99 (type: float64)
    // column: available, value: false (type: bool)
    // column: created-date, value: 2019-02-11 06:22 (type: time.Time)
    // -----------------
}

Connections, Transactions, and PreparedStatements

dynaQ also allows for using dynamic queries on database connections, database transactions, and prepared statements after creating your dynamic querier with dynaQ.NewDynaQ(db *sql.DB), you can call NewDqConn(conn *sql.Conn), NewDqTransaction(tx *sql.Tx), or NewDqPreparedStatement(query string) respectively before querying

dynaQ Usage Ideas

  1. SSR with html templates and dynamic queries
  2. Creating dynamic slices with values of various types from Database queries
  3. And more!! The possibilities are endless!!

Who?

This library was developed by Quinn Millican (@syke99)

License

This repo is under the MIT license, see LICENSE for details.

Download

dynaQ.zip

Comments(10)

  • 1

    Bug: pass []any as any in variadic function

    i am writing a linter to lint that pass []any as any in variadic function

    and run github Actions for some top go packages, this package failed

    see github actions lint result here https://github.com/alingse/asasalint/runs/7284512845?check_suite_focus=true

    Error: /home/runner/work/asasalint/asasalint/dynaQ/dq.go:99:60: pass []any as any to func Query func(dBase *database/sql.DB, query string, timeFormat string, queryParams ...interface{}) ([][]github.com/syke99/dynaQ/pkg/resources/models.QueryValue, error)
    Error: /home/runner/work/asasalint/asasalint/dynaQ/dq.go:115:63: pass []any as any to func QueryRow func(dBase *database/sql.DB, query string, timeFormat string, queryParams ...interface{}) ([]github.com/syke99/dynaQ/pkg/resources/models.QueryValue, error)
    Error: /home/runner/work/asasalint/asasalint/dynaQ/dq.go:132:[7](https://github.com/alingse/asasalint/runs/7284512845?check_suite_focus=true#step:7:8)6: pass []any as any to func QueryWithContext func(dBase *database/sql.DB, ctx context.Context, query string, timeFormat string, queryParams ...interface{}) ([][]github.com/syke99/dynaQ/pkg/resources/models.QueryValue, error)
    
  • 2

    After the Row/ColumnValues refactor, add convenience methods to Row

    The new naming convention, along with the addition of the Row struct, will provide for the addition of more convenience methods for even easier use.

    The Row struct will hold two values: -One is a virtualization of the returned table of values in the representation of a 2 dimensional slice. This will allow for a convenience method of accessing the precise value by index (most useful in a small dataset of returned values). -The second will be a map[string][]ColumnValue. This will allow the user to access each individual column by name and get all the rows (and their values) in that column.

  • 3

    Create a Connection method that uses default context

    Create a method for Connection dynamic queries that defaults to using context.Background() as the default context. This will give users the option to use dq.ConnectionQueryContext() to use a specific context.

  • 4

    Reimplement Single Row results

    Using *sql.DB.QueryRow()/*sql.Conn.QueryRow() from Go's database/sql package doesn't work for dynaQ to function correctly for only retrieving a single row because *sql.Row doesn't return the name(s) of the column(s).

    So instead, to support this functionality, NewDynaQ() will have a parameter to pass in the type of sql database you're interacting with (there will be a list of constants to choose from and this argument will be required). This parameter will then be passed to a method that wraps the user-provided query based on the value of this parameter to only return a single row from the database. That way, *sql.DB.Query()/*sql.Conn.QueryContext() can be used in order to grab one row and have access to column names.

  • 5

    Remove Prepared Statements and Transactions, as well as querying single row

    The QueryRow can't be supported because it doesn't return a way to access the column names. Prepared statements and transactions querying is different and fails on a nil pointer exception (likely due to requiring a db connection already created)

  • 6

    Write godoc comments

    While ignoring godoc comments allowed me to get dynaQ built quickly, it needs this documentation now that it's at a useable point. Users should be able to read the godoc to learn how to use this package beyond the simple example provided in the README. Documentation comments should also be added now before any possible additional features/support is added.

  • 7

    Refactor from SingleRowResult/MultiRowResult to ResultRows

    There's really no need to have a single row result struct and a multi row result struct. It should be refactored into a ResultRows struct, which is essentially the same as MultiRowResult with an extra method that does the same as what SingleRowResult's Row() method does (returns the very first row). the database/sql package's .QueryRow() method only returns one row. So the same amount of resources will get allocated during query result scanning, regardless. This will also keep code cleaner and less bloated.

  • 8

    Change Columns map[string][]ColumnValue to [][]ColumnValue on MultiRowResult

    There are two reasons for this:

    • The way that dynaQ gets implemented and the problem it solves doesn't really make having a map to call all ColumnValues of a column by the column's name. This is by design.

    • While it would be great to have the option, since it doesn't make since, and indexing an array/slice vs doing a search on a map key (whenever ranging)

  • 9

    PR to Sync

    This branch was built from a local branch copy of a remote branch that was already deleted. Plus, it has some changes that will be added with it, but aren't actually relevant to this issue. So, this PR is going to be opened to address any possible merge conflicts and to create a new branch for the associated issue.

  • 10

    Improve code coverage to >= 80%

    There are some unit tests written, but it's only around 45% coverage. This needs to be bumped up to an 80% minimum coverage (a preferred range would be >= 90%)