Golang SQL handling with sqlx package

Sqlx little helper for your database problems

SQLX package will help you in several everyday jobs with your SQL database. All examples below are "copy-pastable" so you can write them to file and run by go run filename.go

Get dependencies

go get github.com/jmoiron/sqlx
go get github.com/go-sql-driver/mysql

Init database

I'm using MySQL/MariaDB in this example: 

CREATE DATABASE `sqlx_test`;
USE `sqlx_test`;
CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `items` (name, description) VALUES ('mechanical keyboard', 'good for writing code');
INSERT INTO `items` (name, description) VALUES ('coffe', 'oh yummy!');
INSERT INTO `items` (name, description) VALUES ('emacs', 'your favourite text editor');

SQLX provides several helpers

Mapping single result to struct with Get

package main

import "fmt"
import "database/sql"
import "github.com/jmoiron/sqlx"
// I'm using MySQL
import _ "github.com/go-sql-driver/mysql"

type Item struct {
    Id            int            `db:"id"`
    Name          sql.NullString `db:"name"`
    Description   sql.NullString `db:"description"`
}

func main() {
    // Must.... functions will panic on fail
    db := sqlx.MustConnect("mysql", "root:PASSWORD@tcp(l:3306)/sqlx_test")
    var item Item
    // We'll get most recent item and map it into our struct
    err := db.Get(&item, "SELECT * FROM items ORDER BY id DESC LIMIT 1")
    if err != nil {
        panic(err)
    }

    fmt.Printf("id: %d, %s, %s", item.Id, item.Name.String, item.Description.String)
}

write above content into file (get.go) and run go run get.go 

❯ go run get.go

id: 3, emacs, your favourite text editor

Preparing statements with Preparex

package main

import "fmt"
import "github.com/jmoiron/sqlx"
import "database/sql"
import _ "github.com/go-sql-driver/mysql"

type Item struct {
    Id          int            `db:"id"`
    Name        sql.NullString `db:"name"`
    Description sql.NullString `db:"description"`
}

func main() {
    var item Item

    db := sqlx.MustConnect("mysql", "root:root@tcp(127.0.0.1:3306)/sqlx_test")
    stmt, err := db.Preparex(`SELECT * FROM items WHERE id=?`)

    // existing one
    err = stmt.Get(&item, 1)
    fmt.Println(item)

    // not existing one
    err = stmt.Get(&item, 3)
    fmt.Println(item)

    // handling non existing item
    if err == sql.ErrNoRows {
        fmt.Println("There is no row with id", 900)
    } else if err != nil {
        panic(err)
    }
}

Save above content into prepare_x.go and run go run prepare_x.go 

❯ go run prepare_x.go

{1 {mechanical keyboard true} {good for writing code true}}
{3 {emacs true} {your favourite text editor true}}

Querying row with QueryRowx and StructScan

package main

import "fmt"
import "database/sql"
import "github.com/jmoiron/sqlx"
import _ "github.com/go-sql-driver/mysql"

type Item struct {
    Id          int            `db:"id"`
    Name        sql.NullString `db:"name"`
    Description sql.NullString `db:"description"`
}

func main() {
    var item Item

    db := sqlx.MustConnect("mysql", "root:root@tcp(127.0.0.1:3306)/sqlx_test")

    row := db.QueryRowx("SELECT * FROM items WHERE id=?", 3)
    err := row.StructScan(&item)

    if err != nil {
        panic(err)
    }

    fmt.Println(item)
}

and output after run: 

❯ go run query_row_x.go

{3 {emacs true} {your favourite text editor true}}

Querying multiple rows with Queryx and StructScan

package main

import "fmt"
import "database/sql"
import "github.com/jmoiron/sqlx"

// We're using MySQL
import _ "github.com/go-sql-driver/mysql"

// We can map columns to fields
type Item struct {
    Id  int            `db:"id"`
    Nme sql.NullString `db:"name"`
    Dsc sql.NullString `db:"description"`
}

func main() {

    db := sqlx.MustConnect("mysql", "root:root@tcp(127.0.0.1:3306)/sqlx_test")
    rows, err := db.Queryx("SELECT id, name, description FROM items")

    if err != nil {
        panic(err)
    }

    for rows.Next() {
        var item Item
        err = rows.StructScan(&item)

        if err != nil {
            panic(err)
        }

        fmt.Printf(
            "%d - %s:  %s\n===================\n",
            item.Id,
            item.Nme.String,
            item.Dsc.String,
        )
    }
}

output: 

❯ go run query_x.go

1 - mechanical keyboard:  good for writing code
===================
2 - coffe:  oh yummy!
===================
3 - emacs:  your favourite text editor
===================

If you want to check other things feel free to

godoc -goroot=$GO github.com/jmoiron/sqlx

comments powered by Disqus