# DBX

DBX is a micro-library to do common DB operations using [sqlx](https://github.com/jmoiron/sqlx) and [squirrel](https://github.com/Masterminds/squirrel).

Features:

 * `LoadOne` and `LoadAll` to read rows into structs using [squirrel](https://github.com/Masterminds/squirrel) queries.
 * `InsertOne`, `UpdateOne` and `DeleteOne` to write rows with structs.
 * `NamedExecOne` (one row) and `NamedExec` (multiple rows) to exec queries with bindvars.
 * `Fields` to read, filter and transform db fields from structs and maps.
 * `MustBegin`, `RollbackUnlessComitted` and `Commit` to handle transactions in the context.
 * For everything else, use [sqlx](https://github.com/jmoiron/sqlx) directly through `DB`, which also implements all regular `database/sql` methods like `Query` and `QueryRow`.


### Connect

Connect with `sqlx`, then instantiate `dbx` with the connected database.

```go
db := sqlx.MustConnect("postgres", "host=localhost port=5433 ...")
d := &dbx.DBX{DB: db}
```

### Usage Example

```go
// Hero represents a record on the heroes table
type Hero struct {
	ID        string    `db:"id"`
	Name      string    `db:"name"`
	Level     int       `db:"level"`
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
}

// Build read queries with squirrel
q := squirrel.Select("*").From("heroes").Where("name = ?", "Miles Morales")

// Load a row into the struct
var spiderman Hero
err := d.LoadOne(ctx, &spiderman, q)

if err == sql.ErrNotFound {
	log.Prinln("Not found")
}
if err != nil {
	log.Println(err.Error())
}

// Load a list
var list []Hero
err = d.LoadAll(ctx, &list, q)

// Insert
spiderwoman := Hero{ID: "2", Name: "Spider-Gwen", Level: 9, CreatedAt: time.Now()}
err = d.InsertOne(ctx, "heroes", spiderwoman, dbx.Exclude("updated_at"))

// Level up!
spiderwoman.Level += 1
err = d.UpdateOne(ctx, "heroes", spiderwoman, dbx.FindBy("id"), dbx.Only("level"))
```

### Transactions

Transaction functions annotate a context with an active transaction.

```go
// Begin transaction
ctx = dbx.MustBegin(ctx, d.DB)
defer dbx.RollbackUnlessComitted(ctx, nil)

// DBX methods using this ctx run inside the transaction
err := d.DeleteOne(ctx, "heroes", dbx.Values{"id": "123"})
if err != nil {
	// returning before doing Commit will automatically rollback the transaction
	return err
}

// Commit
err = d.Commit(ctx)
```

### DB Models Pattern

DBX works great when building your own DB Models (also called Stores or Data Access Objects).

For example, some methods to manage heroes:

```go
const HeroesTable = "heroes"

// query builder configured for Postgres
var qb = squirrel.StatementBuilder.PlaceholderFormat(squirrel.Dollar)

type Heroes struct {
	*DBX
}

type ListHeoresParams struct {
	MaxLevel int
	Limit int
	Offset int
}

func (d *Heroes) ListHeroes(ctx context.Context, params ListHeoresParams) ([]Hero, error) {
	q := qb.Select("*").From(HeroesTable)
	if params.MaxLevel > 0 {
		q = q.Where("level <= ?", params.MaxLevel)
	}
	q = q.Limit(params.Limit).Offset(params.Offset)

	var list []Hero
	err = d.LoadAll(ctx, &list, q)
	return list, err
}

func (d *Heroes) CreateHero(ctx context.Context, h *Hero) error {
	h.ID = NewUUID()
	h.CreatedAt = time.Now()
	return d.InsertOne(ctx, HeroesTable, h, dbx.Exclude("updated_at"))
}

func (d *Heroes) UpdateHero(ctx context.Context, h *Hero) error {
	h.UpdateAt = time.Now()
	return d.UpdateOne(ctx, HeroesTable, h, dbx.FindBy("id"), dbx.Exclude("created_at"))
}

func (d *Heroes) UpdateHeroLevel(ctx context.Context, id string, level int) error {
	return d.UpdateOne(ctx, HeroesTable, dbx.Values{
		"id": id,
		"level": level,
		"updated_at": time.Now(),
	}, dbx.FindBy("id"))
}

func (d *Heroes) DeleteHero(ctx context.Context, id string) error {
	return d.DeleteOne(ctx, HeroesTable, dbx.Values{"id": id})
}
```

## Contributing

Run tests:

```sh
go test ./...
```

