picard

package module
v0.0.35 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 21, 2024 License: MIT Imports: 31 Imported by: 0

README

image

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.

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.

Contributing

See CONTRIBUTING.md

License

MIT (See License)

ci status go report card MIT license

Documentation

Overview

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

Usage:

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

Initialization:

Create a picard connection to your database. 

porm := picard.CreateConnection("postgres://localhost:5432/sampledb&user=user&password=password")

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 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 never 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 columns through picard struct tags. Picard uses reflection to read these tags and determine relational modeling. 

type tableA struct {
	Metadata       picard.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 `picard.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       picard.Metadata `picard:"tablename=table_a"`
	ID             string          `picard:"primary_key,column=id"`
	Name           string          `picard:"lookup,column=name"`
}

type tableB struct {
	Metadata picard.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       picard.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 picard.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       	picard.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 	picard.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 to`created_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       	picard.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 - Optional: 

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       picard.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 picard.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       picard.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 picard.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       picard.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 picard.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 fields: 

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 `JOIN`s 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       picard.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 picard.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 picard.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 picard.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.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func CloseConnection 

func CloseConnection()

CloseConnection closes the database connection

func CreateConnection 

func CreateConnection(connstr string) error

Deprecated in favor of NewConnection: CreateConnection creates a database connection using the provided arguments

func CreateTracedConnection 

func CreateTracedConnection(connstr, serviceName string) error

Deprecated in favor of NewConnection: CreateTracedConnection creates a database connection using the provided arguments

func Decode 

func Decode(body io.Reader, destination interface{}) error

Decode decodes a reader using a specified decoder, but also writes metadata to picard StructMetadata

func ExpectDelete 

func ExpectDelete(mock *sqlmock.Sqlmock, expect ExpectationHelper, expectedIDs []string) [][]driver.Value

ExpectDelete Mocks a delete request to the database.

func ExpectInsert 

func ExpectInsert(mock *sqlmock.Sqlmock, expect ExpectationHelper, columnNames []string, insertValues [][]driver.Value) [][]driver.Value

ExpectInsert Mocks an insert request to the database.

func ExpectLookup 

func ExpectLookup(mock *sqlmock.Sqlmock, expect ExpectationHelper, lookupKeys []string, returnData [][]driver.Value)

ExpectLookup Mocks a lookup request to the database. Makes a request for the lookup keys and returns the rows privided in the returnKeys argument

func ExpectQuery 

func ExpectQuery(mock *sqlmock.Sqlmock, expectSQL string) *sqlmock.ExpectedQuery

ExpectQuery is just a wrapper around sqlmock

func ExpectUpdate 

func ExpectUpdate(mock *sqlmock.Sqlmock, expect ExpectationHelper, updateColumnNames [][]string, updateValues [][]driver.Value, lookupResults [][]driver.Value) []driver.Result

ExpectUpdate Mocks an update request to the database.

func GetConnection 

func GetConnection() *sql.DB

GetConnection gets a connection if it has already been initialized

func GetDecoder 

func GetDecoder(config *decoding.Config) jsoniter.API

GetDecoder returns the decoder specified in the config

func GetLookupKeys 

func GetLookupKeys(expect ExpectationHelper, objects interface{}) []string

GetLookupKeys returns sample object keys from sample objects

func GetReturnDataForLookup 

func GetReturnDataForLookup(expect ExpectationHelper, foundObjects interface{}) [][]driver.Value

GetReturnDataForLookup creates sample return data from sample structs

func LoadFixturesFromFiles 

func LoadFixturesFromFiles(names []string, path string, loadType reflect.Type, jsonTagKey string) (interface{}, error)

LoadFixturesFromFiles creates a slice of structs from a slice of file names

func NewConnection added in v0.0.26 

func NewConnection(props ConnectionProps) error

NewConnection creates a database connection using the provided arguments

func RunImportTest 

func RunImportTest(testObjects interface{}, testFunction func(*sqlmock.Sqlmock, interface{}), batchSize int) error

RunImportTest Runs a Test Object Import Test

func SetConnection 

func SetConnection(db *sql.DB)

SetConnection allows clients to place an external database connection into picard

func SquashErrors 

func SquashErrors(errs []error) error

SquashErrors turns a slice of errors into a single error

Types

type ConnectionProps added in v0.0.26 

type ConnectionProps struct {
	ConnString   string
	Driver       string
	ServiceName  *string
	MaxIdleConns *int
	MaxOpenConns *int
	MaxLifeTime  *int
}

type Error 

type Error string

Error is a type of error that picard will return 

const ModelNotFoundError Error = "Model Not Found"

ModelNotFoundError is returned when functions that expect to return an existing model cannot find one

func (Error) Error 

func (err Error) Error() string

type ExpectationHelper 

type ExpectationHelper struct {
	FixtureType      interface{}
	TableMetadata    *tags.TableMetadata
	LookupFrom       string
	LookupSelect     string
	LookupWhere      string
	LookupReturnCols []string
	LookupFields     []string
	DBColumns        []string
	DataFields       []string
}

ExpectationHelper struct that contains expectations about a particular object

func (ExpectationHelper) GetFixtureValue 

func (eh ExpectationHelper) GetFixtureValue(fixtures interface{}, index int, fieldName string) driver.Value

GetFixtureValue returns the value of a particular field on a fixture

func (ExpectationHelper) GetInsertDBColumns 

func (eh ExpectationHelper) GetInsertDBColumns(includePrimaryKey bool) []string

GetInsertDBColumns returns the columns that should inserted into

func (ExpectationHelper) GetReturnDataKey 

func (eh ExpectationHelper) GetReturnDataKey(returnData [][]driver.Value, index int) string

GetReturnDataKey Returns the first column at a given index of return data

func (ExpectationHelper) GetUpdateDBColumnsForFixture 

func (eh ExpectationHelper) GetUpdateDBColumnsForFixture(fixtures interface{}, index int) []string

GetUpdateDBColumnsForFixture returnst the fields that should be updated for a particular fixture

type FilterRequest 

type FilterRequest struct {
	FilterModel  interface{}
	FieldFilters tags.Filterable
	Associations []tags.Association
	OrderBy      []qp.OrderByRequest
	Runner       sq.BaseRunner
	SelectFields []string
}
FilterRequest holds information about a request to filter on a model

Example: 

type TableA struct {
	Metadata	metadata.Metadata	`picard:"tablename=table_a"`
	ID			string				`picard:"primary_key,column=id"`
	FieldA		string				`picard:"column=field_a"`
	FieldB		string				`picard:"column=field_b"`
}

// Filter all TableA models
p.FilterModel(picard.FilterRequest{
	FilterModel: TableA{},
}

// Filter TableA models by field values
p.FilterModel(picard.FilterRequest{
	FilterModel: TableA{
		FieldA: "foo",
		FieldB: "bar",
	},
}

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

type TableA struct {
	Metadata	metadata.Metadata	`picard:"tablename=table_a"`
	ID			string				`picard:"primary_key,column=id"`
	FieldA		string				`picard:"column=field_a"`
	FieldB		string				`picard:"column=field_b"`
}

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

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')

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 lets you define parent and child relationships that neeed to be eager loaded. See tags.Associations for more info.

Runner lets the filter request execute in a transaction.

SelectFields is set to define the exact columns to query for. Without `SelectFields`, all the columns defined in the table will be included in the query.

Example: 

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

// SELECT t0.id, t0.field_b FROM table_a ...

type ForeignKeyError 

type ForeignKeyError struct {
	Err       error
	Table     string
	Key       string
	KeyColumn string
	FieldName string
}

ForeignKeyError has extra information about which lookup failed

func NewForeignKeyError 

func NewForeignKeyError(reason, table, key, keyColumn string, fieldName string) *ForeignKeyError

NewForeignKeyError returns a new ForeignKeyError object, populated with extra information about which lookup failed

func (*ForeignKeyError) Error 

func (e *ForeignKeyError) Error() string

func (*ForeignKeyError) GetFieldName 

func (e *ForeignKeyError) GetFieldName() string

GetFieldName returns the Related Field Name property

func (*ForeignKeyError) SplitKey 

func (e *ForeignKeyError) SplitKey() []string

SplitKey splits the key value into field parts

type ORM 

type ORM interface {
	FilterModel(FilterRequest) ([]interface{}, error)
	SaveModel(model interface{}) error
	CreateModel(model interface{}) error
	DeleteModel(model interface{}) (int64, error)
	Deploy(data interface{}) error
	DeployMultiple(data []interface{}) error
	StartTransaction() (*sql.Tx, error)
	Commit() error
	Rollback() error
}

ORM interface describes the behavior API of any picard ORM

func New 

func New(multitenancyValue string, performerID string) ORM

New Creates a new Picard Object and handle defaults

type PersistenceORM 

type PersistenceORM struct {
	// contains filtered or unexported fields
}

PersistenceORM provides the necessary configuration to perform an upsert of objects without IDs into a relational database using lookup fields to match and field name transformations.

func (*PersistenceORM) Commit 

func (p *PersistenceORM) Commit() error

Commit ends a transaction

func (PersistenceORM) CreateModel 

func (p PersistenceORM) CreateModel(model interface{}) error

CreateModel performs an insert operation for the provided model.

func (PersistenceORM) DeleteModel 

func (porm PersistenceORM) DeleteModel(model interface{}) (int64, error)

DeleteModel will delete models that match the provided struct, ignoring zero values. Returns the number of rows affected or an error.

func (PersistenceORM) Deploy 

func (p PersistenceORM) Deploy(data interface{}) error

Deploy is the public method to start a Picard deployment. Send in a table name and a slice of structs and it will attempt a deployment.

func (PersistenceORM) DeployMultiple 

func (p PersistenceORM) DeployMultiple(data []interface{}) error

DeployMultiple allows for doing multiple deployments in the same transaction

func (PersistenceORM) FilterModel 

func (p PersistenceORM) FilterModel(request FilterRequest) ([]interface{}, error)

FilterModel returns models that match the provided struct, ignoring zero values.

func (*PersistenceORM) Rollback 

func (p *PersistenceORM) Rollback() error

Rollback aborts a transaction

func (PersistenceORM) SaveModel 

func (p PersistenceORM) SaveModel(model interface{}) error

SaveModel performs an upsert operation for the provided model.

func (*PersistenceORM) StartTransaction 

func (p *PersistenceORM) StartTransaction() (*sql.Tx, error)

StartTranscation begins a transaction and returns a sql.Tx param (see https://golang.org/pkg/database/sql/#Tx). Picard methods use this transaction when executing queries and will initiate a rollback if there is an error Using this method makes the caller responsible for ending a transaction to prevent a transaction leak.

type QueryError 

type QueryError struct {
	Err   error
	Query string
}

QueryError holds additional information about an SQL query failure

func NewQueryError 

func NewQueryError(err error, query string) *QueryError

NewQueryError returns a new QueryError object, populated with extra information about which query failed

func (*QueryError) Error 

func (e *QueryError) Error() string

Source Files

View all Source files

Directories

Path Synopsis
Package crypto lets picard column values to be encrypted and dexrypted with an encryption key
 Package crypto lets picard column values to be encrypted and dexrypted with an encryption key
Package dbchange abstrats SQL upsert operations into structs
 Package dbchange abstrats SQL upsert operations into structs
Package decoder is used to default to jsoniter for its decoder
 Package decoder is used to default to jsoniter for its decoder
examples
blog
product
Package metadata is a field type that can be easily detected by picard.
 Package metadata is a field type that can be easily detected by picard.
picard_test provides a mock orm for unit testing
 picard_test provides a mock orm for unit testing
Package query helps maintain aliases to each table so that when joins and columns are added they can be properly aliased.
 Package query helps maintain aliases to each table so that when joins and columns are added they can be properly aliased.
Package queryparts provides struct defitintions that abstract the necessary parts of a SQL query such as joins, column aliases, order by clauses, etc.
 Package queryparts provides struct defitintions that abstract the necessary parts of a SQL query such as joins, column aliases, order by clauses, etc.
Package reflectutil contains basic go reflection utility funcs
 Package reflectutil contains basic go reflection utility funcs
Package stringutil contains basic string utility funcs for picard models, lookups, filters
 Package stringutil contains basic string utility funcs for picard models, lookups, filters
Package tags generates table metadata by reading picard struct tag annotations
 Package tags generates table metadata by reading picard struct tag annotations

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.