goqu_example_test.go

Documentation: github.com/doug-martin/goqu/v9

     1  package goqu_test
     2  
     3  import (
     4  	"fmt"
     5  	"time"
     6  
     7  	"github.com/DATA-DOG/go-sqlmock"
     8  	"github.com/doug-martin/goqu/v9"
     9  	_ "github.com/doug-martin/goqu/v9/dialect/mysql"
    10  	_ "github.com/doug-martin/goqu/v9/dialect/postgres"
    11  	_ "github.com/doug-martin/goqu/v9/dialect/sqlite3"
    12  )
    13  
    14  // Creating a mysql dataset. Be sure to import the mysql adapter.
    15  func ExampleDialect_datasetMysql() {
    16  	// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
    17  
    18  	d := goqu.Dialect("mysql")
    19  	ds := d.From("test").Where(goqu.Ex{
    20  		"foo": "bar",
    21  		"baz": []int64{1, 2, 3},
    22  	}).Limit(10)
    23  
    24  	sql, args, _ := ds.ToSQL()
    25  	fmt.Println(sql, args)
    26  
    27  	sql, args, _ = ds.Prepared(true).ToSQL()
    28  	fmt.Println(sql, args)
    29  
    30  	// Output:
    31  	// SELECT * FROM `test` WHERE ((`baz` IN (1, 2, 3)) AND (`foo` = 'bar')) LIMIT 10 []
    32  	// SELECT * FROM `test` WHERE ((`baz` IN (?, ?, ?)) AND (`foo` = ?)) LIMIT ? [1 2 3 bar 10]
    33  }
    34  
    35  // Creating a mysql database. Be sure to import the mysql adapter.
    36  func ExampleDialect_dbMysql() {
    37  	// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
    38  
    39  	type item struct {
    40  		ID      int64  `db:"id"`
    41  		Address string `db:"address"`
    42  		Name    string `db:"name"`
    43  	}
    44  
    45  	// set up a mock db this would normally be
    46  	// db, err := sql.Open("mysql", dbURI)
    47  	// 	if err != nil {
    48  	// 		panic(err.Error())
    49  	// 	}
    50  	mDB, mock, _ := sqlmock.New()
    51  
    52  	d := goqu.Dialect("mysql")
    53  
    54  	db := d.DB(mDB)
    55  
    56  	// use the db.From to get a dataset to execute queries
    57  	ds := db.From("items").Where(goqu.C("id").Eq(1))
    58  
    59  	// set up mock for example purposes
    60  	mock.ExpectQuery("SELECT `address`, `id`, `name` FROM `items` WHERE \\(`id` = 1\\) LIMIT 1").
    61  		WillReturnRows(
    62  			sqlmock.NewRows([]string{"id", "address", "name"}).
    63  				FromCSVString("1, 111 Test Addr,Test1"),
    64  		)
    65  	var it item
    66  	found, err := ds.ScanStruct(&it)
    67  	fmt.Println(it, found, err)
    68  
    69  	// set up mock for example purposes
    70  	mock.ExpectQuery("SELECT `address`, `id`, `name` FROM `items` WHERE \\(`id` = \\?\\) LIMIT \\?").
    71  		WithArgs(1, 1).
    72  		WillReturnRows(
    73  			sqlmock.NewRows([]string{"id", "address", "name"}).
    74  				FromCSVString("1, 111 Test Addr,Test1"),
    75  		)
    76  
    77  	found, err = ds.Prepared(true).ScanStruct(&it)
    78  	fmt.Println(it, found, err)
    79  
    80  	// Output:
    81  	// {1 111 Test Addr Test1} true <nil>
    82  	// {1 111 Test Addr Test1} true <nil>
    83  }
    84  
    85  // Creating a mysql dataset. Be sure to import the postgres adapter
    86  func ExampleDialect_datasetPostgres() {
    87  	// import _ "github.com/doug-martin/goqu/v9/dialect/postgres"
    88  
    89  	d := goqu.Dialect("postgres")
    90  	ds := d.From("test").Where(goqu.Ex{
    91  		"foo": "bar",
    92  		"baz": []int64{1, 2, 3},
    93  	}).Limit(10)
    94  
    95  	sql, args, _ := ds.ToSQL()
    96  	fmt.Println(sql, args)
    97  
    98  	sql, args, _ = ds.Prepared(true).ToSQL()
    99  	fmt.Println(sql, args)
   100  
   101  	// Output:
   102  	// SELECT * FROM "test" WHERE (("baz" IN (1, 2, 3)) AND ("foo" = 'bar')) LIMIT 10 []
   103  	// SELECT * FROM "test" WHERE (("baz" IN ($1, $2, $3)) AND ("foo" = $4)) LIMIT $5 [1 2 3 bar 10]
   104  }
   105  
   106  // Creating a postgres dataset. Be sure to import the postgres adapter
   107  func ExampleDialect_dbPostgres() {
   108  	// import _ "github.com/doug-martin/goqu/v9/dialect/postgres"
   109  
   110  	type item struct {
   111  		ID      int64  `db:"id"`
   112  		Address string `db:"address"`
   113  		Name    string `db:"name"`
   114  	}
   115  
   116  	// set up a mock db this would normally be
   117  	// db, err := sql.Open("postgres", dbURI)
   118  	// 	if err != nil {
   119  	// 		panic(err.Error())
   120  	// 	}
   121  	mDB, mock, _ := sqlmock.New()
   122  
   123  	d := goqu.Dialect("postgres")
   124  
   125  	db := d.DB(mDB)
   126  
   127  	// use the db.From to get a dataset to execute queries
   128  	ds := db.From("items").Where(goqu.C("id").Eq(1))
   129  
   130  	// set up mock for example purposes
   131  	mock.ExpectQuery(`SELECT "address", "id", "name" FROM "items" WHERE \("id" = 1\) LIMIT 1`).
   132  		WillReturnRows(
   133  			sqlmock.NewRows([]string{"id", "address", "name"}).
   134  				FromCSVString("1, 111 Test Addr,Test1"),
   135  		)
   136  	var it item
   137  	found, err := ds.ScanStruct(&it)
   138  	fmt.Println(it, found, err)
   139  
   140  	// set up mock for example purposes
   141  	mock.ExpectQuery(`SELECT "address", "id", "name" FROM "items" WHERE \("id" = \$1\) LIMIT \$2`).
   142  		WithArgs(1, 1).
   143  		WillReturnRows(
   144  			sqlmock.NewRows([]string{"id", "address", "name"}).
   145  				FromCSVString("1, 111 Test Addr,Test1"),
   146  		)
   147  
   148  	found, err = ds.Prepared(true).ScanStruct(&it)
   149  	fmt.Println(it, found, err)
   150  
   151  	// Output:
   152  	// {1 111 Test Addr Test1} true <nil>
   153  	// {1 111 Test Addr Test1} true <nil>
   154  }
   155  
   156  // Creating a mysql dataset. Be sure to import the sqlite3 adapter
   157  func ExampleDialect_datasetSqlite3() {
   158  	// import _ "github.com/doug-martin/goqu/v9/dialect/sqlite3"
   159  
   160  	d := goqu.Dialect("sqlite3")
   161  	ds := d.From("test").Where(goqu.Ex{
   162  		"foo": "bar",
   163  		"baz": []int64{1, 2, 3},
   164  	}).Limit(10)
   165  
   166  	sql, args, _ := ds.ToSQL()
   167  	fmt.Println(sql, args)
   168  
   169  	sql, args, _ = ds.Prepared(true).ToSQL()
   170  	fmt.Println(sql, args)
   171  
   172  	// Output:
   173  	// SELECT * FROM `test` WHERE ((`baz` IN (1, 2, 3)) AND (`foo` = 'bar')) LIMIT 10 []
   174  	// SELECT * FROM `test` WHERE ((`baz` IN (?, ?, ?)) AND (`foo` = ?)) LIMIT ? [1 2 3 bar 10]
   175  }
   176  
   177  // Creating a sqlite3 database. Be sure to import the sqlite3 adapter
   178  func ExampleDialect_dbSqlite3() {
   179  	// import _ "github.com/doug-martin/goqu/v9/dialect/sqlite3"
   180  	type item struct {
   181  		ID      int64  `db:"id"`
   182  		Address string `db:"address"`
   183  		Name    string `db:"name"`
   184  	}
   185  
   186  	// set up a mock db this would normally be
   187  	// db, err := sql.Open("sqlite3", dbURI)
   188  	// 	if err != nil {
   189  	// 		panic(err.Error())
   190  	// 	}
   191  	mDB, mock, _ := sqlmock.New()
   192  
   193  	d := goqu.Dialect("sqlite3")
   194  
   195  	db := d.DB(mDB)
   196  
   197  	// use the db.From to get a dataset to execute queries
   198  	ds := db.From("items").Where(goqu.C("id").Eq(1))
   199  
   200  	// set up mock for example purposes
   201  	mock.ExpectQuery("SELECT `address`, `id`, `name` FROM `items` WHERE \\(`id` = 1\\) LIMIT 1").
   202  		WillReturnRows(
   203  			sqlmock.NewRows([]string{"id", "address", "name"}).
   204  				FromCSVString("1, 111 Test Addr,Test1"),
   205  		)
   206  	var it item
   207  	found, err := ds.ScanStruct(&it)
   208  	fmt.Println(it, found, err)
   209  
   210  	// set up mock for example purposes
   211  	mock.ExpectQuery("SELECT `address`, `id`, `name` FROM `items` WHERE \\(`id` = \\?\\) LIMIT \\?").
   212  		WithArgs(1, 1).
   213  		WillReturnRows(
   214  			sqlmock.NewRows([]string{"id", "address", "name"}).
   215  				FromCSVString("1, 111 Test Addr,Test1"),
   216  		)
   217  
   218  	found, err = ds.Prepared(true).ScanStruct(&it)
   219  	fmt.Println(it, found, err)
   220  
   221  	// Output:
   222  	// {1 111 Test Addr Test1} true <nil>
   223  	// {1 111 Test Addr Test1} true <nil>
   224  }
   225  
   226  func ExampleSetTimeLocation() {
   227  	loc, err := time.LoadLocation("Asia/Shanghai")
   228  	if err != nil {
   229  		panic(err)
   230  	}
   231  
   232  	created, err := time.Parse(time.RFC3339, "2019-10-01T15:01:00Z")
   233  	if err != nil {
   234  		panic(err)
   235  	}
   236  
   237  	// use original time with tz info
   238  	goqu.SetTimeLocation(loc)
   239  	ds := goqu.Insert("test").Rows(goqu.Record{
   240  		"address": "111 Address",
   241  		"name":    "Bob Yukon",
   242  		"created": created,
   243  	})
   244  	sql, _, _ := ds.ToSQL()
   245  	fmt.Println(sql)
   246  
   247  	// convert time to UTC
   248  	goqu.SetTimeLocation(time.UTC)
   249  	sql, _, _ = ds.ToSQL()
   250  	fmt.Println(sql)
   251  
   252  	// Output:
   253  	// INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T23:01:00+08:00', 'Bob Yukon')
   254  	// INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T15:01:00Z', 'Bob Yukon')
   255  }
   256
View as plain text