papergres

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Sep 8, 2021 License: MIT Imports: 15 Imported by: 0

README

Papergres

Papergres is an ultra lightweight micro-ORM written in golang for the postgres database server. The library provides easy ways to execute queries, insert data and sql logging.

This is a wrapper around the general purpose extensions library sqlx by jmoiron. The core postgres driver used is pq.

Papergres is used at Paperchain, built and maintained by the team.

GoDoc Build Status Go Report Card

Install

go get -u github.com/Paperchain/papergres

API Documentation

Full API documentation can be found on godoc

Backwards Compatibility

Compatibility with the most recent two versions of Go is a requirement for any new changes. Compatibility beyond that is not guaranteed.

Versioning is done with Go modules. Breaking changes (eg. removing deprecated API) will get major version number bumps.

Building & Testing

Build using the go cmd

go build

Test everything!

go test -v ./...

Usage

The simplest way to execute a query returning a single object is

// Set up your connection object
conn := NewConnection("postgres://postgres:postgres@localhost:5432/paperchain", "papergres_tests", SSLDisable)
db := conn.NewDatabase()

// Write a query in a single line
var book Book
res := db.Query("SELECT * FROM paper.book WHERE book_id = $1 LIMIT 1;", 777).ExecSingle(&book)

To retrieve a list of rows and hydrate it into a list of object

var books []Book
res := db.Query("SELECT * FROM paper.book WHERE book_id > 10 LIMIT 1;", 777).ExecAll(&books)

To insert a record into a table

// Create a struct and specify database column names via the "db" struct tag
type Book struct {
	BookId    PrimaryKey `db:"book_id"`
	Title     string     `db:"title"`
	Author    string     `db:"author"`
	CreatedAt time.Time  `db:"created_at"`
	CreatedBy string     `db:"created_by"`
}

// Instantiate your struct
book := &Book{
	Title:     "The Martian",
	Author:    "Andy Weir",
	CreatedAt: time.Now(),
	CreatedBy: "TestInsert",
}

// Perform an insert using papergres
res := db.Insert(book)
if res.Err != nil {
		log.Fatalln(res.Err.Error())
}

// Retrieve the inserted ID from the primary key
bookid := res.LastInsertId.ID

// To insert into a specific schema other than the public
schema := db.Schema("my_schema")
res := schema.Insert(book)

// To insert multiple
res, err := schema.InsertAll(books)

To search for records using the IN query clause (make sure to use ? bind variable in sql query)

var books []Book
var authors = []string{"Issac Asimov", "H. G. Wells", "Arther C. Clarke"}
res := db.Query("SELECT * FROM paper.book WHERE author IN (?);", authors).ExecAllIn(&books)

Additionally, one can always use the ExecNonQuery method to make any sql query - insert, update and select.

query := `UPDATE paper.book SET title=$1 WHERE book_id = $2;`
res := db.Query(query, "I, Robot", 42).ExecNonQuery()

Logging

The library provides an logging interface that needs to be implemented

// Instantiate your logged in your application's bootstrap
Log = &testLogger{}

// The logger interface that needs to be implemented. This is available in papergres.go
type Logger interface {
	Info(args ...interface{})
	Infof(format string, args ...interface{})
	Debug(args ...interface{})
	Debugf(format string, args ...interface{})
}

// For example, you can set your logger with something like this
type testLogger struct{}

func (t *testLogger) Info(args ...interface{}) {
	if debug {
		fmt.Println(args...)
	}
}
func (t *testLogger) Infof(format string, args ...interface{}) {
	if debug {
		fmt.Printf(format, args...)
	}
}
func (t *testLogger) Debug(args ...interface{}) {
	if debug {
		fmt.Println(args...)
	}
}
func (t *testLogger) Debugf(format string, args ...interface{}) {
	if debug {
		fmt.Printf(format, args...)
	}
}

Example of the sql logging

== POSTGRES QUERY ==
        Query:
        INSERT INTO paper.character (
        book_id,
        name,
        description,
        created_at,
        created_by
)
VALUES (
        $1,
        $2,
        $3,
        $4,
        $5
)
RETURNING character_id as LastInsertId;
        Args:
        $1: 6
        $2: Mitch Henderson
        $3: Sean Bean doesn't die in this movie
        $4: 2017-12-14 11:06:46.6077695 +0000 GMT
        $5: TestInsert
        Connection:
        connect_timeout=0
        dbname=paperchain
        fallback_application_name=papergres_tests
        host=localhost
        password=postgres
        port=5432
        sslmode=disable
        user=postgres

== RESULT ==
LastInsertId:  21
RowsAffected:  No RowsAffected returned
RowsReturned:  0
ExecutionTime: 496.2µs
Error: <nil>
== ADDITIONAL INFO ==
Repeat Index: 4 / 4


== POSTGRES QUERY ==
        Query:
        SELECT * FROM paper.character WHERE book_id = $1;
        Args:
        $1: 6
        Connection:
        connect_timeout=0
        dbname=paperchain
        fallback_application_name=papergres_tests
        host=localhost
        password=postgres
        port=5432
        sslmode=disable
        user=postgres

== RESULT ==
LastInsertId:  0
RowsAffected:  0
RowsReturned:  4
ExecutionTime: 5.549ms
Error: <nil>

Contribution

Feel free to file issues and raise a PR.

Happy Programming!

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Reset 

func Reset()

Reset resets all global variables to start-up values. All cached values will be created again when needed. Can be used to reset DB instances if one were to unexpectedly fail which I'm not sure is possible.

func Shutdown 

func Shutdown()

Shutdown performs a graceful shutdown of all DBs

Types

type Connection 

type Connection struct {
	Database    string
	User        string
	Password    string
	Host        string
	Port        string
	AppName     string
	Timeout     int
	SSLMode     SSLMode
	SSLCert     string
	SSLKey      string
	SSLRootCert string
}

Connection holds all database connection configuration.

func NewConnection 

func NewConnection(databaseURL string, appName string) Connection

NewConnection creates and returns the Connection object to the postgres server

func (Connection) NewDatabase 

func (conn Connection) NewDatabase() *Database

NewDatabase creates a new Database object

func (*Connection) String 

func (conn *Connection) String() string

String method builds a DSN(Data Source Name) connection string based on the given database connection settings and returns it.

type Database 

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

Database contains all required database attributes

func (*Database) Connection 

func (db *Database) Connection() Connection

Connection returns the connection information for a database

func (*Database) ConnectionString 

func (db *Database) ConnectionString() string

ConnectionString returns the DSN(Data Source Name) connection string for the current DB connection.

func (*Database) CreateDatabase 

func (db *Database) CreateDatabase() *Result

CreateDatabase creates a default database Good for use during testing and local dev

func (*Database) GenerateInsert 

func (db *Database) GenerateInsert(obj interface{}) *Query

GenerateInsert generates an insert query for the given object

func (*Database) Insert 

func (db *Database) Insert(obj interface{}) *Result

Insert inserts the passed in object

func (*Database) InsertAll 

func (db *Database) InsertAll(objs interface{}) ([]*Result, error)

InsertAll inserts a slice of objects concurrently. objs must be a slice with items in it. the Result slice will be in the same order as objs so a simple loop will set all the primary keys if needed: 

for i, r := range results {
	objs[i].Id = r.LastInsertId.ID
}

func (*Database) Ping 

func (db *Database) Ping() error

Ping tests the database connection

func (*Database) Query 

func (db *Database) Query(sql string, args ...interface{}) *Query

Query creates a base new query object that can be used for all database operations

func (*Database) Schema 

func (db *Database) Schema(name string) *Schema

Schema allows for certain operations that require a specific schema

func (*Database) Stats 

func (db *Database) Stats() sql.DBStats

Stats returns DBStats. Right now this only returns OpenConnections

type Field 

type Field struct {
	Value     interface{}
	Typeof    string
	Name      string
	Tag       string
	IsPrimary bool
}

Field is a struct field that represents a single entity of an object. To set a field as primary add `db_pk:true` to tag.

type LastInsertId 

type LastInsertId struct {
	ID  PrimaryKey
	Err error
}

LastInsertId is the last inserted ID from a script

type Logger 

type Logger interface {
	Info(args ...interface{})
	Infof(format string, args ...interface{})
	Debug(args ...interface{})
	Debugf(format string, args ...interface{})
}

Logger is the required interface for the papergres logger 

var Log Logger

Log is the way to log the scripting activity happening from the library to the database

type PrimaryKey 

type PrimaryKey interface{}

PrimaryKey is the type used for primary keys

type Query 

type Query struct {
	SQL      string
	Database *Database
	Args     []interface{}
	// contains filtered or unexported fields
}

Query holds the SQL to execute and the connection string

func (*Query) Exec 

func (q *Query) Exec() *Result

Exec runs a sql command given a connection and expects LastInsertId or RowsAffected to be returned by the script. Use this for INSERTs

func (*Query) ExecAll 

func (q *Query) ExecAll(dest interface{}) *Result

ExecAll gets many rows and populates the given slice dest should be a pointer to a slice

func (*Query) ExecAllIn added in v1.1.0 

func (q *Query) ExecAllIn(dest interface{}) *Result

ExecAllIn works with IN queries. It will take a slice of values and attach the slice to the query as a list of values. One key difference with bindvar used for IN query is a `?` (question mark) the query then has to be rebinded to change default bindvar to target bindvar like `$1` (dollar sign followed by a number) for postgres etc.

func (*Query) ExecNonQuery 

func (q *Query) ExecNonQuery() *Result

ExecNonQuery runs the SQL and doesn't look for any results

func (*Query) ExecSingle 

func (q *Query) ExecSingle(dest interface{}) *Result

ExecSingle fetches a single row from the database and puts it into dest. If more than 1 row is returned it takes the first one. Expects at least 1 row or it will return an error.

func (*Query) Repeat 

func (q *Query) Repeat(times int, pSelectorFn SelectParamsFn) *Repeat

Repeat will execute a query N times. The param selector function will pass in the current iteration and expect back the destination obj and args for that index. Make sure to use pointers to ensure the sql results fill your structs. Use this when you want to run the same query for many different parameters, like getting data for child entities for a collection of parents. This function executes the iterations concurrently so each loop should not rely on state from a previous loops execution. The function should be extremely fast and efficient with DB resources. Returned error will contain all errors that occurred in any iterations.

Example usage: 

params := func(i int) (dest interface{}, args []interface{}) {
	p := &parents[i] 						// get parent at i to derive parameters
	args := MakeArgs(p.Id, true, "current") // create arg list, variadic
	return &p.Child, args 					// &p.Child will be filled with returned data
}
// len(parents) is parent slice and determines how many times to execute query
results, err := db.Query(sql).Repeat(len(parents), params).Exec()

func (*Query) String 

func (q *Query) String() string

String returns a SQL query and it's arguments along with connection info in a pretty format.

type Repeat 

type Repeat struct {
	Query    *Query
	ParamsFn SelectParamsFn
	N        int
}

Repeat holds the iteration count and params function for executing a query N times.

func (*Repeat) Exec 

func (r *Repeat) Exec() ([]*Result, error)

Exec executes the repeat query command. Internally this will prepare the statement with the database and then create go routines to execute each statement.

type Result 

type Result struct {
	LastInsertId  LastInsertId
	RowsAffected  RowsAffected
	RowsReturned  int
	ExecutionTime time.Duration
	Err           error
}

Result holds the results of an executed query

func Exec 

func Exec(sql string, conn Connection, args ...interface{}) *Result

Exec executes an ad-hoc query against a connection. This is only recommended for use if you have a weird case where you need to modify the connection string just for this query, like when creating a new database. Otherwise just new New() and save the DAtabase instance.

func ExecNonQuery 

func ExecNonQuery(sql string, conn Connection, args ...interface{}) *Result

ExecNonQuery executes an ad-hoc query against a connection. This is only recommended for use if you have a weird case where you need to modify the connection string just for this query, like when creating a new database. Otherwise just new New() and save the Database instance.

func NewResult 

func NewResult() *Result

NewResult returns an empty Result

func (*Result) String 

func (r *Result) String() string

String method returns query execution results in a pretty format

type RowsAffected 

type RowsAffected struct {
	Count int64
	Err   error
}

RowsAffected is the returned rows affected from a script

type SSLMode 

type SSLMode string

SSLMode defines all possible SSL options 

const (
	// SSLDisable - No SSL
	SSLDisable SSLMode = "disable"
	// SSLRequire - Always SSL, no verification
	SSLRequire SSLMode = "require"
	// SSLVerifyCA - Always SSL, verifies that certificate was signed by trusted CA
	SSLVerifyCA SSLMode = "verify-ca"
	// SSLVerifyFull - Always SSL, verifies that certificate was signed by trusted CA
	// and server host name matches the one in the certificate
	SSLVerifyFull SSLMode = "verify-full"
)

type Schema 

type Schema struct {
	Name     string
	Database *Database
}

Schema holds the schema to query along with the Database

func (*Schema) GenerateInsert 

func (s *Schema) GenerateInsert(obj interface{}) *Query

GenerateInsert generates the insert query for the given object

func (*Schema) GenerateInsertWithPK added in v1.1.0 

func (s *Schema) GenerateInsertWithPK(obj interface{}) *Query

GenerateInsertWithPK generates the insert query for the given object in which PrimaryKey value is also supposed to be populated during insert.

func (*Schema) Insert 

func (s *Schema) Insert(obj interface{}) *Result

Insert inserts the passed in object in DB.

NOTE: It does not account for client side generated value for a primary key and expects that the logic for populating value of primary key should reside in the database as sequence.

DO NOT use Insert() if you wish to populate a client side generated value in primary key, use InsertWithPK() instead.

func (*Schema) InsertAll 

func (s *Schema) InsertAll(objs interface{}) ([]*Result, error)

InsertAll inserts a slice of objects concurrently. objs must be a slice with items in it. the Result slice will be in the same order as objs so a simple loop will set all the primary keys if needed: 

for i, r := range results {
	objs[i].Id = r.LastInsertId.ID
}

func (*Schema) InsertWithPK added in v1.1.0 

func (s *Schema) InsertWithPK(obj interface{}) *Result

InsertWithPK performs inserts on objects and persists the Primary key value to DB as well. It will fail to insert duplicate values to DB.

NOTE: Proceed with caution! Only use this method you wish to persist a client side generated value in Primary key and don't rely on database sequenece to autogenerate PrimaryKey values.

type SelectParamsFn 

type SelectParamsFn func(i int) (dest interface{}, args []interface{})

SelectParamsFn is a function that takes in the iteration and returns the destination and args for a SQL execution.

Source Files

View all Source files

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.