Proteus

A simple tool for generating an application's data access layer.

Purpose

Proteus makes your SQL queries type-safe and prevents SQL injection attacks. It processes structs with struct tags on function fields to generate Go functions at runtime. These functions map input parameters to SQL query parameters and optionally map the output parameters to the output of your SQL queries.

In addition to being type-safe, Proteus also prevents SQL injection by generating prepared statements from your SQL queries. Even dynamic in clauses are converted into injection-proof prepared statements.

Proteus is not an ORM; it does not generate SQL. It just automates away the boring parts of interacting with databases in Go.

Quick Start

Define a struct that contains function fields and tags to indicate the query and the parameter names:

type ProductDaoCtx struct {
	FindByID                      func(ctx context.Context, q proteus.ContextQuerier, id int) (Product, error)                                     `proq:"select * from Product where id = :id:" prop:"id"`
	Update                        func(ctx context.Context, e proteus.ContextExecutor, p Product) (int64, error)                                   `proq:"update Product set name = :p.Name:, cost = :p.Cost: where id = :p.Id:" prop:"p"`
	FindByNameAndCost             func(ctx context.Context, q proteus.ContextQuerier, name string, cost float64) ([]Product, error)                `proq:"select * from Product where name=:name: and cost=:cost:" prop:"name,cost"`
	FindByIDMap                   func(ctx context.Context, q proteus.ContextQuerier, id int) (map[string]interface{}, error)                      `proq:"select * from Product where id = :id:" prop:"id"`
	UpdateMap                     func(ctx context.Context, e proteus.ContextExecutor, p map[string]interface{}) (int64, error)                    `proq:"update Product set name = :p.Name:, cost = :p.Cost: where id = :p.Id:" prop:"p"`
	FindByNameAndCostMap          func(ctx context.Context, q proteus.ContextQuerier, name string, cost float64) ([]map[string]interface{}, error) `proq:"select * from Product where name=:name: and cost=:cost:" prop:"name,cost"`
	Insert                        func(ctx context.Context, e proteus.ContextExecutor, id int, name string, cost *float64) (int64, error)          `proq:"insert into product(id, name, cost) values(:id:, :name:, :cost:)" prop:"id,name,cost"`
	FindByIDSlice                 func(ctx context.Context, q proteus.ContextQuerier, ids []int) ([]Product, error)                                `proq:"select * from Product where id in (:ids:)" prop:"ids"`
	FindByIDSliceAndName          func(ctx context.Context, q proteus.ContextQuerier, ids []int, name string) ([]Product, error)                   `proq:"select * from Product where name = :name: and id in (:ids:)" prop:"ids,name"`
	FindByIDSliceNameAndCost      func(ctx context.Context, q proteus.ContextQuerier, ids []int, name string, cost *float64) ([]Product, error)    `proq:"select * from Product where name = :name: and id in (:ids:) and (cost is null or cost = :cost:)" prop:"ids,name,cost"`
	FindByIDSliceCostAndNameSlice func(ctx context.Context, q proteus.ContextQuerier, ids []int, names []string, cost *float64) ([]Product, error) `proq:"select * from Product where id in (:ids:) and (cost is null or cost = :cost:) and name in (:names:)" prop:"ids,names,cost"`
	FindByNameAndCostUnlabeled    func(ctx context.Context, q proteus.ContextQuerier, name string, cost float64) ([]Product, error)                `proq:"select * from Product where name=:$1: and cost=:$2:"`
}

The first input parameter is of type context.Context, and the second input parameter is of type proteus.ContextExecutor or proteus.ContextQuerier:

// ContextQuerier defines the interface of a type that runs a SQL query with a context
type ContextQuerier interface {
	// QueryContext executes a query that returns rows, typically a SELECT.
	// The args are for any placeholder parameters in the query.
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

// ContextExecutor defines the interface of a type that runs a SQL exec with a context
type ContextExecutor interface {
	// ExecContext executes a query without returning any rows.
	// The args are for any placeholder parameters in the query.
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

The remaining input parameters can be primitives, structs, maps of string to interface{}, or slices.

For queries, return types can be:

empty
a single value being returned (a primitive, struct, or map of string to interface{})
a single value that's a slice of primitive, struct, or a map of string to interface{}
a primitive, struct, or map of string to interface{} and an error
a slice of primitive, struct, or a map of string to interface{} and an error

For insert/updates, return types can be:

empty
an int64 that indicates the number of rows affected
a sql.Result
an int64 that indicates the number of rows affected and an error
a sql.Result and an error

The proq struct tag stores the query. You place variable substitutions between : s. Proteus allows you to refer to fields in maps and structs, as well as elements in arrays or slices using . as a path separator. If you have a struct like this:

type Person struct {
    Name string
    Address Address
    Pets []Pet
} 

type Pet struct {
    Name string
    Species string
}

type Address struct {
    Street string
    City string
    State string
}

You can write a query like this:

insert into person(name, city, pet1_name, pet2_name) values (:p.Name:, :p.Address.City:, :p.Pets.0.Name:, :p.Pets.1.Name:)

Note that the index for an array or slice must be an int literal and the key for a map must be a string.

2. If you want to map response fields to a struct, define a struct with struct tags to indicate the mapping:

type Product struct {
	Id   int     `prof:"id"`
	Name string  `prof:"name"`
	Cost float64 `prof:"cost"`
}

3. Pass an instance of the Dao struct to the proteus.ShouldBuild function:

var productDao = ProductDao{}

func init() {
	err := proteus.ShouldBuild(context.Background(), &productDao, proteus.Sqlite)
	if err != nil {
		panic(err)
	}
}

4. Open a connection to a SQL database:

	db := setupDb()
	defer db.Close()

5. Make calls to the function fields in your Proteus-populated struct:

    ctx := context.Background()
	fmt.Println(productDao.FindById(ctx, db, 10))
	p := Product{10, "Thingie", 56.23}
	fmt.Println(productDao.Update(ctx, db, p))
	fmt.Println(productDao.FindById(ctx, db, 10))
	fmt.Println(productDao.FindByNameAndCost(ctx, db, "fred", 54.10))
	fmt.Println(productDao.FindByNameAndCost(ctx, db, "Thingie", 56.23))

	//using a map of [string]interface{} works too!
	fmt.Println(productDao.FindByIdMap(ctx, db, 10))
	fmt.Println(productDao.FindByNameAndCostMap(ctx, db, "Thingie", 56.23))

	fmt.Println(productDao.FindById(ctx, db, 11))
	m := map[string]interface{}{
		"Id":   11,
		"Name": "bobbo",
		"Cost": 12.94,
	}
	fmt.Println(productDao.UpdateMap(ctx, db, m))
	fmt.Println(productDao.FindById(ctx, db, 11))

	fmt.Println(productDao.FindByIDSlice(ctx, db, []int{1, 3, 5}))
	fmt.Println(productDao.FindByIDSliceAndName(ctx, db, []int{1, 3, 5}, "person1"))
	fmt.Println(productDao.FindByIDSliceNameAndCost(ctx, db, []int{1, 3, 5}, "person3", nil))
	fmt.Println(productDao.FindByIDSliceCostAndNameSlice(ctx, db, []int{1, 3, 5}, []string{"person3", "person5"}, nil))

Proteus without the context

If you are using an older database driver that does not work with the context.Context, Proteus provides support for them as well:

type ProductDao struct {
	FindByID                      func(q proteus.Querier, id int) (Product, error)                                     `proq:"select * from Product where id = :id:" prop:"id"`
	Update                        func(e proteus.Executor, p Product) (int64, error)                                   `proq:"update Product set name = :p.Name:, cost = :p.Cost: where id = :p.Id:" prop:"p"`
	FindByNameAndCost             func(q proteus.Querier, name string, cost float64) ([]Product, error)                `proq:"select * from Product where name=:name: and cost=:cost:" prop:"name,cost"`
	FindByIDMap                   func(q proteus.Querier, id int) (map[string]interface{}, error)                      `proq:"select * from Product where id = :id:" prop:"id"`
	UpdateMap                     func(e proteus.Executor, p map[string]interface{}) (int64, error)                    `proq:"update Product set name = :p.Name:, cost = :p.Cost: where id = :p.Id:" prop:"p"`
	FindByNameAndCostMap          func(q proteus.Querier, name string, cost float64) ([]map[string]interface{}, error) `proq:"select * from Product where name=:name: and cost=:cost:" prop:"name,cost"`
	Insert                        func(e proteus.Executor, id int, name string, cost *float64) (int64, error)          `proq:"insert into product(id, name, cost) values(:id:, :name:, :cost:)" prop:"id,name,cost"`
	FindByIDSlice                 func(q proteus.Querier, ids []int) ([]Product, error)                                `proq:"select * from Product where id in (:ids:)" prop:"ids"`
	FindByIDSliceAndName          func(q proteus.Querier, ids []int, name string) ([]Product, error)                   `proq:"select * from Product where name = :name: and id in (:ids:)" prop:"ids,n

Proteus

Install / Use

README

Proteus

Purpose

Quick Start

Proteus without the context