Skip to content

Instantly share code, notes, and snippets.

@rossnelson
Created January 26, 2021 14:24
Show Gist options
  • Save rossnelson/cbb192d314b6c89b7148e919ab25986c to your computer and use it in GitHub Desktop.
Save rossnelson/cbb192d314b6c89b7148e919ab25986c to your computer and use it in GitHub Desktop.
golang and ms sqlserver using sqlx and go-mssqldb
version: '3'
services:
mssql:
image: mcr.microsoft.com/mssql/server:2017-CU8-ubuntu
environment:
- ACCEPT_EULA=Y
- SA_PASSWORD=Melon40twomonkeys
ports:
- '1433:1433'
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/denisenkom/go-mssqldb"
"github.com/jmoiron/sqlx"
)
var schema = `
CREATE TABLE users (
user_id int,
first_name varchar(81),
last_name varchar(80),
email varchar(250),
password varchar(250)
);
`
// User holds information about a user
type User struct {
UserID int `db:"user_id"`
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
Email string
Password sql.NullString
}
func main() {
// this connects & tries a simple 'SELECT 1', panics on error
// use sqlx.Open() for sql.Open() semantics
db, err := sqlx.Connect(
"mssql",
"sqlserver://SA:Melon40twomonkeys@127.0.0.1:1433?database=testingstuff&connection+timeout=30",
)
if err != nil {
log.Fatalln(err)
}
// exec the schema or fail;
db.MustExec(schema)
tx := db.MustBegin()
tx.NamedExec(
"INSERT INTO users (user_id, first_name, last_name, email) VALUES (:user_id, :first_name, :last_name, :email)",
&User{UserID: 1, FirstName: "Jane", LastName: "Citizen", Email: "jane.citzen@example.com"},
)
tx.Commit()
// Query the database, storing results in a []User (wrapped in []interface{})
people := []User{}
db.Select(&people, "SELECT * FROM users ORDER BY first_name ASC")
fmt.Println(people)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment