koneal2013/picard

An ORM for go services that interact with PostgreSQL databases.

Picard

Picard is an ORM for go services that interact with PostgreSQL databases.

Usages

Here are some ways you can use picard:

• CRUD on relational data models
• Eager loading associations
• Enforcing multitenancy
• Specify and auto-populate audit columns
• Upserting with transactions

Initialization

// This is forwarded to the "database/sql" Open method, targeting "postgres"
err := picard.CreateConnection("host=localhost port=5432 dbname=sampledb user=user password=password sslmode=disable")

To create a new Picard ORM object for use in your project, you'll need a multitenancy value and performer id.

porm := picard.New(orgID, userID)

Then you can use any of the functionality on the ORM.

You can close the connection with picard.CloseConnection

Transactions

All picard methods start one transaction per method when executing queries. It will rollback the transaction when there is an error or commit it when the operation is complete.

If you would like to take control of transactions so you can use them across multiple methods, call StartTransaction() before calling these methods. The transaction started in StartTransaction can be completed with Commit() or aborted with Rollback(). Use these methods to prevent dangling transactions. Picard will always rollback using this initiated transaction if it encounters an error, but will not commit a transaction for you.

Model Mapping via Structs

Picard lets you abstract database tables into structs with individual fields that may represent table columns. These structs can then be initialized with values and passed as arguments to picard methods that perform CRUD operations on the database. Struct fields are annotated with tags that tell picard extra information about the field, like if it is part of a key, if it is part of a relationship with another struct, if it need encryption, etc.

Struct Tags

Structs are mapped to database tables and their columns through picard struct tags. Picard uses reflection to read these tags and determine referential integrity for relational modeling.

Example:

import (
	"github.com/skuid/picard/metadata"
)

type tableA struct {
	Metadata       metadata.Metadata `picard:"tablename=table_a"`
	ID             string            `picard:"primary_key,column=id"`
	TenantID       string            `picard:"multitenancy_key,column=tenant_id"`
	FieldA         string            `picard:"lookup,column=field_a"`
	FieldB         string            `picard:"column=field_b"`
	Secret	       string	           `picard:encrypted,column=secret`
}

Table Metadata

A special field of the type metadata.Metadata is required in all structs used with picard. This field stores information about that particular struct as well as metadata about the associated database table.

tablename

Specifies the name of the table in the database.

Basic Column Tags

column

Specifies the column name that is associated with this field. Include column=<name>, where <name> is the name of the column in the database

primary_key

Indicates that this column is a primary key in the database.

multitenancy_key

Indicates that this column is used as a multitenancy key needed to differentiate between tenants. Annotating this field will add it to all WHERE clauses.

lookup

Tells picard that this column may be used in the where clause as part of the unique key for that object. Indicates that this field should be used in the componund key for checking to see if this record already exists in the database. Lookup fields are used in picard deployments to determine whether an insert or update is necessary. Include lookup in the picard annotations.

Relationship Tags (Belongs To) - Optional

type tableA struct {
	Metadata       metadata.Metadata 	`picard:"tablename=table_a"`
	ID             string          		`picard:"primary_key,column=id"`
	Name           string          		`picard:"lookup,column=name"`
}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       	string         	  `picard:"primary_key,column=id"`
	Name     	string          	`picard:"lookup,column=name"`
	TableAID 	string 						`picard:"foreign_key,required,column=tablea_id"`
	// tableB belongsTo tableA
	OneTableA   tableA
}

foreign_key

Specifies the field on the related struct that contains the foreign key for this relationship. During a picard deployment, this field will be populated with the value primary_key column of the parent object.

Relationship Tags (Has Many)

type tableA struct {
	Metadata       metadata.Metadata 	`picard:"tablename=table_a"`
	ID             string          		`picard:"primary_key,column=id"`
	OrganizationID string          		`picard:"multitenancy_key,column=organization_id"`
	Name           string          		`picard:"lookup,column=name"`
	Password       string          		`picard:"encrypted,column=password"`

	// tableA has many tableBs
	AllTheBs       []tableB        `picard:"child,foreign_key=TableAID"`
}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableAID string 						`picard:"foreign_key,required,column=tablea_id"`
}

foreign_key

Indicates that this field represents a foreign key in another table. The column tag should not be set for child fields since this field on the struct doesn't actually correlate to a column on the table.

child

Indicates that this field contains additional structs with picard metadata that are related to this struct with a "Belongs To" relationship. Include foreign_key= to identify the column name on the child struct. It is only valid on fields that are maps or slices of structs.

related

Denotes a field on the struct that will hold related data for parent and junction models. The field specified here must be of kind struct. Picard will hydrate this field with related data.

Special Tags - Optional

encrypted

Tells picard to encrypt and decrypt this field as it gets loaded or saved. Your program must set a 32 byte encryption key with the picard crypto package to use this functionality.

import "github.com/skuid/picard/crypto"
crypto.SetEncryptionKey([]byte("the-key-has-to-be-32-bytes-long!"))

delete_orphans

Add delete_orphans to cascade delete related data for fields annotated with foreign_key and child on deletes, updates, and deploys. It will only delete records if the child relationship struct is not nil. In the example below, associated tableB records will be deleted when the parent tableA is removed.

type tableA struct {
	Metadata       	metadata.Metadata 	`picard:"tablename=table_a"`
	ID             	string          		`picard:"primary_key,column=id"`
	Name           	string          		`picard:"lookup,column=name"`
	AllTheBs       	[]tableB       		  `picard:"child,foreign_key=TableAID,delete_orphans"`
}

type tableB struct {
	Metadata 	metadata.Metadata 	`picard:"tablename=table_b"`
	ID       	string          		`picard:"primary_key,column=id"`
	TableAID	string 							`picard:"foreign_key,required,column=tablea_id"`
}

required

Add required to foreign_key fields to make the lookup of related data required, otherwise a ForeignKeyError will be returned.

audit fields

Save and update audit fields without needing to hardcode their value for every struct. The performer id that is set in picard.New is automatically added tocreated_by and updated_by fields. created_at and updated_at are populated with the exact time the model is saved or updated.

type tableA struct {
	Metadata       	metadata.Metadata `picard:"tablename=table_a"`
	ID             	string          	`picard:"primary_key,column=id"`
	Name           	string         	  `picard:"lookup,column=name"`

	// audit fields
	CreatedByID string    `picard:"column=created_by_id,audit=created_by"`
	UpdatedByID string    `picard:"column=updated_by_id,audit=updated_by"`
	CreatedDate time.Time `picard:"column=created_at,audit=created_at"`
	UpdatedDate time.Time `picard:"column=updated_at,audit=updated_at"`
}

Advanced tags

key_mapping

The key_mapping annotation is an advanced way to link the key of a map[string]model{} to a field on the child struct. This indicates that the value in this property should be mapped to the specified field of the related object.

type tableA struct {
	Metadata       metadata.Metadata 		`picard:"tablename=table_a"`
	ID             string          			`picard:"primary_key,column=id"`
	OrganizationID string          			`picard:"multitenancy_key,column=organization_id"`
	Name           string          			`picard:"lookup,column=name"`
	BMap           map[string]tableB    `picard:"child,foreign_key=TableAID,key_mapping=Name"`
}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableAID string 						`picard:"foreign_key,lookup,required,column=tablea_id"`
}

In the example above, the keytype of map[string]tableB maps to the Name field on the tableB struct.

This is only valid for fields that are marked as a foreign key.

value_mapping

This indicates which fields on the parent to map to fields of the child during a picard deployment.

type tableA struct {
	Metadata       metadata.Metadata 		`picard:"tablename=table_a"`
	ID             string          			`picard:"primary_key,column=id"`
	OrganizationID string          			`picard:"multitenancy_key,column=organization_id"`
	Name           string          			`picard:"lookup,column=name"`
	BMap           map[string]tableB    `picard:"child,foreign_key=TableAID,value_mapping=Name"`
}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableAID string 						`picard:"foreign_key,lookup,required,column=tablea_id"`
}

This is similar to key_mapping, except that the value type of the map is linked to the Name field in tableB.

This is only valid for fields that are marked as a foreign key.

grouping_criteria

The grouping_criteria annotation is used to describe which field on the parent struct is linked to a field child struct. Without grouping criteria specified we use the primary key from the parent as a filter condition on the child. If we want to link together values form a parent to a child we use this.

type tableA struct {
	Metadata       metadata.Metadata 		`picard:"tablename=table_a"`
	ID             string          			`picard:"primary_key,column=id"`
	OrganizationID string          			`picard:"multitenancy_key,column=organization_id"`
	Name           string          			`picard:"lookup,column=name"`
	AllTheBs []ChildModel          			`picard:"child,grouping_criteria=ParentA.ID->ID"`

}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableAID string 						`picard:"foreign_key,lookup,required,column=tablea_id,related=ParentA"`
	ParentA  tableA

}

In the example above, ParentA.ID->ID indicates a link between tableA's ID field and tableB's ParentID field specifically on that ID field.

Filter

This will execute an SQL query against the database to access data. Everything is based off of the picard.FilterRequest struct you provide.

Filter Model

The picard.FilterModel composes a SELECT statement. The WHERE clause will always includes the multitenancy key set in picard.New.

results, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{}
})

Passing in a newly constructed struct will return all the records for that model.

To filter a model by a field value, specify field value you want to filter by:

result, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{
		FieldA: "jeanluc",
	},
})

Select Fields

SelectFields lets you define the exact columns to query for. Without SelectFields, all the columns defined in the table will be included in the query.

results, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{
		FieldA: "jeanluc",
	},
	SelectFields: []string{
		"Id",
		"FieldB",
	},
})

Ordering

Define the ordering of filter results by setting the OrderBy field with OrderByRequest via the queryparts.

Order by a single field

import qp "github.com/skuid/picard/queryparts"

results, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{},
	OrderBy: []qp.OrderByRequest{
		{
			Field:      "FieldA",
			Descending: true,
		},
	},
})

// SELECT ... ORDER BY t0.field_a DESC

Order by multiple field

results, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{},
	OrderBy: []qp.OrderByRequest{
		{
			Field:      "FieldA",
			Descending: false,
		},
		{
			Field:      "FieldB",
			Descending: false,
		},
	},
})

// SELECT ... ORDER BY field_a, field_b

FieldFilters

FieldFilters generates a WHERE clause grouping with either an OR grouping via tags.OrFilterGroup or an AND grouping via tags.AndFilterGroup. The tags.FieldFilter

import "github.com/skuid/picard/tags"

orResults, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{},
	FieldFilters: tags.OrFilterGroup{
		tags.FieldFilter{
			FieldName:   "FieldA",
			FilterValue: "foo",
		},
		tags.FieldFilter{
			FieldName:   "FieldB",
			FilterValue: "bar",
		},
	},
})

// SELECT ... WHERE (t0.field_a = 'foo' OR t0.field_b = 'bar')

andResults, err := p.FilterModel(picard.FilterRequest{
	FilterModel: tableA{},
	FieldFilters: tags.AndFilterGroup{
		tags.FieldFilter{
			FieldName:   "FieldA",
			FilterValue: "foo",
		},
		tags.FieldFilter{
			FieldName:   "FieldB",
			FilterValue: "bar",
		},
	},
})

// SELECT ... WHERE (t0.field_a = 'foo' AND t0.field_b = 'bar')

Associations

We can eager load associations of a model by passing in a slice of tags.Association in the filterRequest for a filterModel. Picard constructs all the necessary JOINs from determining relationships via picard struct tags on model fields. This will help you avoid making n+1 queries to grab data for relationship models.

type tableA struct {
	Metadata       metadata.Metadata 	`picard:"tablename=table_a"`
	ID             string          		`picard:"primary_key,column=id"`
	OrganizationID string          		`picard:"multitenancy_key,column=organization_id"`
	Name           string          		`picard:"lookup,column=name"`
	Password       string          		`picard:"encrypted,column=password"`
	AllTheBs       []tableB        		`picard:"child,foreign_key=TableAID"`
}

type tableB struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_b"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableAID string 						`picard:"foreign_key,lookup,required,column=tablea_id"`
	AllTheCs []tableC						`picard:"child,foreign_key=TableBID"`
}

type tableC struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_c"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableBID string 						`picard:"foreign_key,lookup,required,column=tableb_id"`
}

type tableD struct {
	Metadata metadata.Metadata 	`picard:"tablename=table_d"`
	ID       string          		`picard:"primary_key,column=id"`
	Name     string          		`picard:"lookup,column=name"`
	TableCID string 						`picard:"foreign_key,lookup,required,related=ParentC,column=tablec_id"`
	ParentC  tableC
}

func doLookup() {
	filter := tableA{
		Name: "foo"
	}

	results, err := picardORM.FilterModel(picard.FilterRequest{
		FilterModel: filterModel,
	})
	// test err and results array for length
	tbl := results[0].(tableA)
}
func doEagerLoadLookUp() {
	// eager load children of tableA
	results, err := picardORM.FilterModel(picard.FilterRequest{
		FilterModel: tableA{
			Name: "foo"
		}, 
		Associations: []tags.Association{
			{
				Name: "AllTheBs",
				SelectFields: []string{
					"ID",
					"Name",
				},
				Associations: []tags.Association{
					Name: "AllTheCs",
					SelectFields: []string{
						"ID",
						"Name",
					},
				},
			},
		},
	})
	// eager load parent tableC of child tableD
	results, err := picardORM.FilterModel(picard.FilterRequest{
		FilterModel: tableC{
			Name: "lavender"
		}, 
		Associations: []tags.Association{
			{
				Name: "ParentC",
			},
		},
	})
}

The Name field is where the association struct will live on the associated struct, as annotated by child or . Like the top level filter model, associations may specify query fields with SelectFields`. Associations models may even have their own nested associations.

CreateModel

Insert a single record by constructing a new model struct with the necessary field values set.

err := picardORM.CreateModel(tableA{
	Name: "NCC-1701-D",
})

SaveModel

Upsert a single table record for the columns set with values specified in a model struct. The primary key value must be set for an update to occur, otherwise there will be an insert.

err := picardORM.SaveModel(tableA{
	ID: "7e671345-0dbb-4e40-9cb2-b37b3b940827",
	Name: "USS Enterprise",
})

Any audit fields will also be updated here. See Deploy for upserting multiple models.

Error types

ModelNotFoundError is returned when attempting to delete a model that doesn't exist.

DeleteModel

Delete a single record by passing in a picard annotated struct with a column set to a value you wish to filter by. This filter is added to the WHERE clause. This method returns the number or rows removed.

rowCount, err := picardORM.DeleteModel(tableA{
	Name: "NCC-1701-D",
})

Error types

ModelNotFoundError is returned when attempting to delete a model that doesn't exist.

Deploy

Under the hood, deployments are just upserts for a slice of models.

 err = picardORM.Deploy([]tableA{
	tableA{
		Name: "apple",
		AllTheBs: []tableB{
			tableB{
				Name: "celery",
				AllTheCs: []tableC{
					tableC{
						Name: "thyme",
					}
				}
			},	
		},
	},
	tableA{
		Name: "orange",
		AllTheBs: []tableB{
			tableB{
				Name: "carrot",
			},
			tableB{
				Name: "zucchini",
			},
		},
	},
 })

Error types

ModelNotFoundError is returned when attempting to delete a model that doesn't exist.