pg

package module
v1.0.1 Latest Latest
Warning

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

 Go to latest Published: Jul 6, 2023 License: MIT Imports: 14 Imported by: 1

README

kataras/pg

build status report card godocs view examples

A high-performance Go library that provides a simple and elegant way to interact with PostgreSQL databases. It allows you to define your entities as structs with pg tags, register them in a schema, and perform CRUD operations using a repository pattern. It also handles database connection, schema creation and verification, and query generation and execution. You can use PG to write concise and readable code that works with PostgreSQL databases.


💻 Installation

The only requirement is the Go Programming Language.

Create a new project
$ mkdir myapp
$ cd myapp
$ go mod init myapp
$ go get github.com/kataras/pg@latest
Install on existing project 
$ cd myapp
$ go get github.com/kataras/pg@latest

Run

$ go mod tidy -compat=1.20 # -compat="1.20" for windows.
$ go run .

📖 Example

PG contains extensive and thorough documentation making it easy to get started with the library.

package main

import (
  "context"
  "fmt"
  "log"
  "time"

  "github.com/kataras/pg"
)

// Base is a struct that contains common fields for all entities.
type Base struct {
  ID        string    `pg:"type=uuid,primary"` // UUID as primary key
  CreatedAt time.Time `pg:"type=timestamp,default=clock_timestamp()"` // Timestamp of creation
  UpdatedAt time.Time `pg:"type=timestamp,default=clock_timestamp()"` // Last update
}

// Customer is a struct that represents a customer entity.
type Customer struct {
  Base // Embed Base struct

  Firstname string `pg:"type=varchar(255)"` // First name of the customer
}

func main() {
  // Default value for struct field tag `pg`.
  // It can be modified to a custom one as well, e.g.
  // pg.SetDefaultTag("db")

  // Create Schema instance.
  schema := pg.NewSchema()
  // First argument is the table name, second is the struct entity.
  schema.MustRegister("customers", Customer{})

  // Create Database instance.
  connString := "postgres://postgres:admin!123@localhost:5432/test_db?sslmode=disable"
  db, err := pg.Open(context.Background(), schema, connString)
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()

  // If needed, create and verify the database schema
  // based on the pg tags of the structs.
  //
  // Alternatively, you can generate
  // Go schema files from an existing database:
  // see the ./gen sub-package for more details.
  if err = db.CreateSchema(context.Background()); err != nil {
    log.Fatal(err)
  }

  if err = db.CheckSchema(context.Background()); err != nil {
    log.Fatal(err)
  }

  // Create a Repository of Customer type.
  customers := pg.NewRepository[Customer](db)

  var newCustomer = Customer{
    Firstname: John,
  }

  // Insert a new Customer.
  err = customers.InsertSingle(context.Background(), newCustomer, &newCustomer.ID)
  if err != nil {
    log.Fatal(err)
  }

  // Get by id.
  /*
  query := `SELECT * FROM customers WHERE id = $1 LIMIT 1;`
  existing, err := customers.SelectSingle(context.Background(), query, newCustomer.ID)
  OR:
  */
  existing, err := customers.SelectByID(context.Background(), newCustomer.ID)
  if err != nil {
    log.Fatal(err)
  }

  log.Printf("Existing Customer (SelectSingle):\n%#+v\n", existing)

  // List all.
  query = `SELECT * FROM customers ORDER BY created_at DESC;`
  allCustomers, err := customers.Select(context.Background(), query)
  if err != nil {
    log.Fatal(err)
  }

  log.Printf("All Customers (%d):", len(allCustomers))
  for _, customer := range allCustomers {
    fmt.Printf("- (%s) %s\n", customer.ID, customer.Firstname)
  }
}

If you already have a database, you can use the gen sub-package to create structs that match its schema.

✒️ ASCII art

┌───────────────────────┐
│  NewSchema() *Schema  ├───────────────────────────────────┐
├───────────────────────┘                                   │
│                                                           │
├─────────────────────────────────────────────────────┐     │
│  Schema                                             │     │
├─────────────────────────────────────────────────────┤     │
│  - MustRegister(tableName string, emptyStruct any)  │     │
└─────────────────────────────────────────────────────┘     │
                                                            │
                                                            │
                                                            │
                                ┌───────────────────────────┘     ┌─────────────────────────┐
                                │                                 │                         │
┌───────────────────────────────▼─────────────────────────────────┴───────────┐             │
│  Open(ctx context.Context, schema *Schema, connString string) (*DB, error)  │             │
├─────────────────────────────────────────────────────────────────────────────┘             │
│                                                                                           │
├─────────────────────────────────────────────────────────────────────────────────────┐     │
│  DB                                                                                 │     │
├─────────────────────────────────────────────────────────────────────────────────────┤     │
│                                                                                     │     │
│  - CreateSchema(ctx context.Context) error                                          │     │
│  - CheckSchema(ctx context.Context) error                                           │     │
│                                                                                     │     │
│  - InTransaction(ctx context.Context, fn (*DB) error) error                         │     │
│  - IsTransaction() bool                                                             │     │
│                                                                                     │     │
│  - Query(ctx context.Context, query string, args ...any) (Rows, error)              │     │
│  - QueryRow(ctx context.Context, query string, args ...any) Row                     │     │
│                                                                                     │     │
│  - Exec(ctx context.Context, query string, args... any) (pgconn.CommandTag, error)  │     │
│                                                                                     │     │
│  - Listen(ctx context.Context, channel string) (*Listener, error)                   │     │
│  - Notify(ctx context.Context, channel string, payload any) error                   │     │
│  - Unlisten(ctx context.Context, channel string) error                              │     │
│                                                                                     │     │
│  - Close() error                                                                    │     │
└─────────────────────────────────────────────────────────────────────────────────────┘     │
                                                                                            │
                                                                                            │
                                                                                            │
                                                                                            │
                      ┌─────────────────────────────────────────────────────────────────────┘
                      │
┌─────────────────────▼─────────────────────┐
│  NewRepository[T](db *DB) *Repository[T]  │
├───────────────────────────────────────────┘
│
├────────────────────────────────────────────────────────────────────────────────────────────┐
│  Repository[T]                                                                             │
├────────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                            │
│  - InTransaction(ctx context.Context, fn func(*Repository[T]) error) error                 │
│  - IsTransaction() bool                                                                    │
│                                                                                            │
│  - Select(ctx context.Context, query string, args ...any) ([]T, error)                     │
│  - SelectSingle(ctx context.Context, query string, args ...any) (T, error)                 │
│  - SelectByID(ctx context.Context, id any) (T, error)                                      │
│  - SelectByUsernameAndPassword(ctx context.Context, username, plainPwd string) (T, error)  │
│                                                                                            │
│  - Insert(ctx context.Context, values ...T) error                                          │
│  - InsertSingle(ctx context.Context, value T, destIdPtr any) error                         │
│                                                                                            │
│  - Update(ctx context.Context, values ...T) (int64, error)                                 │
│  - UpdateOnlyColumns(ctx context.Context, columns []string, values ...T) (int64, error)    │
│                                                                                            │
│  - Upsert(ctx context.Context, uniqueIndex string, values ...T) error                      │
│  - UpsertSingle(ctx context.Context, uniqueIndex string, value T, destIdPtr any) error     │
│                                                                                            │
│  - Delete(ctx context.Context, values ...T) (int64, error)                                 │
└────────────────────────────────────────────────────────────────────────────────────────────┘

🛄 Data types

PostgreSQL data type Struct field tag type options
BigInt bigint, int8
BigIntArray bigint[], int8[]
BigSerial bigserial, serial8
Bit bit
BitVarying bit varying, varbit
Boolean boolean, bool
Box box
Bytea bytea
Character character, char
CharacterArray character[], char[]
CharacterVarying character varying, varchar
CharacterVaryingArray character varying[], varchar[]
Cidr cidr
Circle circle
Date date
DoublePrecision double precision, float8
Inet inet
Integer integer, int, int4
IntegerArray integer[], int[], int4[]
IntegerDoubleArray integer[][], int[][], int4[][]
Interval interval
JSON json
JSONB jsonb
Line line
Lseg lseg
MACAddr macaddr
MACAddr8 macaddr8
Money money
Numeric numeric, decimal
Path path
PgLSN pg_lsn
Point point
Polygon polygon
Real real, float4
SmallInt smallint, int2
SmallSerial smallserial, serial2
Serial serial4
Text text
TextArray text[]
TextDoubleArray text[][]
Time time, timetz, time without time zone
Timestamp timestamp, timestamptz
TsQuery tsquery
TsVector tsvector
TxIDSnapshot txid_snapshot
UUID uuid
XML xml
Int4Range int4range
Int4MultiRange int4multirange
Int8Range int8range
Int8MultiRange int8multirange
NumRange numrange
NumMultiRange nummultirange
TsRange tsrange
TsMultirange tsmultirange
TsTzRange tstzrange
TsTzMultiRange tstzmultirange
DateRange daterange
DateMultiRange datemultirange
CIText citext
HStore hstore
Data type examples

UUID

type Entity struct {
  ID string `pg:"type=uuid,primary"`
}

Timestamp

type Entity struct {
  CreatedAt time.Time `pg:"type=timestamp,default=clock_timestamp()"`
}

Varchar

type Entity struct {
  PhotoURL string `pg:"type=varchar(255)"`
}

Varchar Array

type Entity struct {
  SearchTerms []string `pg:"type=varchar[]"`
}

Integer

type Entity struct {
  ReadTimeMinutes int `pg:"type=smallint,default=1,check=read_time_minutes > 0"`
}

Custom JSON Object

type Entity struct {
  Feature Feature `pg:"type=jsonb"`
}

Array of custom JSON objects

type Entity struct {
  Tags []Tag `pg:"type=jsonb"`
}

📦 3rd-party Packages

List of 3rd-party packages based on PG.

🛡 Security Vulnerabilities

If you discover a security vulnerability within pg, please send an e-mail to kataras2006@hotmail.com. All security vulnerabilities will be promptly addressed.

📝 License

This project is licensed under the MIT license.

Documentation

Overview

Example

Example is a function that demonstrates how to use the Registry and Repository types to perform database operations within a transaction. It uses the Customer, Blog, and BlogPost structs as the entities to be stored and manipulated in the database. It also prints "OK" if everything succeeds, or an error message otherwise. 

db, err := openTestConnection()
if err != nil {
	fmt.Println(err.Error())
	return
}
defer db.Close()

// Registry code.
registry := NewRegistry(db) // Create a new Registry instance with the DB instance.

// Execute a function within a database transaction, passing it a Registry instance that uses the transactional DB instance.
err = registry.InTransaction(context.Background(), func(registry *Registry) error {
	customers := registry.Customers() // Get the CustomerRepository instance from the Registry.

	customerToInsert := Customer{ // Create a Customer struct to be inserted into the database.
		CognitoUserID: "373f90eb-00ac-410f-9fe0-1a7058d090ba",
		Email:         "kataras2006@hotmail.com",
		Name:          "kataras",
	}

	// Insert the customer into the database and get its ID.
	err = customers.InsertSingle(context.Background(), customerToInsert, &customerToInsert.ID)
	if err != nil {
		return fmt.Errorf("insert single: %w", err)
	}

	// Modify cognito user id.
	newCognitoUserID := "1e6a93d0-6276-4a43-b90a-4badad8407bb"
	// Update specific columns by id:
	updated, err := customers.UpdateOnlyColumns(
		context.Background(),
		// TODO: make a generator for: models.Customers.Columns.CognitoUserID.Name so
		// end-developers have static safety for columns.
		[]string{"cognito_user_id"},
		Customer{
			BaseEntity: BaseEntity{
				ID: customerToInsert.ID,
			},
			CognitoUserID: newCognitoUserID,
		})
	// Full update of the object by id (except id and created_at, updated_at columns):
	// updated, err := customers.Update(context.Background(),
	// 	Customer{
	// 		BaseEntity: BaseEntity{
	// 			ID: customerToInsert.ID,
	// 		},
	// 		CognitoUserID: newCognitoUserID,
	// 		Email:         customerToInsert.Email,
	// 		Name:          customerToInsert.Name,
	// 	})
	if err != nil {
		return fmt.Errorf("update: %w", err)
	} else if updated == 0 {
		return fmt.Errorf("update: no record was updated")
	}

	// Select the customer from the database by its ID.
	customer, err := customers.SelectSingle(context.Background(), `SELECT * FROM customers WHERE id = $1;`, customerToInsert.ID)
	if err != nil {
		return fmt.Errorf("select single: %w", err)
	}

	if customer.CognitoUserID != newCognitoUserID {
		return fmt.Errorf("expected cognito user id to be updated but it wasn't ('%s' vs '%s')",
			newCognitoUserID, customer.CognitoUserID)
	}
	if customer.Email == "" {
		return fmt.Errorf("expected email field not be removed after update")
	}
	if customer.Name == "" {
		return fmt.Errorf("expected name field not be removed after update")
	}

	// Test Upsert by modifying the email.
	customerToUpsert := Customer{
		CognitoUserID: customer.CognitoUserID,
		Email:         "kataras2023@hotmail.com",
		Name:          "kataras2023",
	}

	// Manually passing a column as the conflict column:
	// err = customers.UpsertSingle(context.Background(), "email", customerToUpsert, &customerToUpsert.ID)
	//
	// Automatically find the conflict column or expression by setting it to empty value:
	// err = customers.UpsertSingle(context.Background(), "", customerToUpsert, &customerToUpsert.ID)
	// Manually passing a unique index name, pg will resolve the conflict columns:
	err = customers.UpsertSingle(context.Background(), "customer_unique_idx", customerToUpsert, &customerToUpsert.ID)
	if err != nil {
		return fmt.Errorf("upsert single: %w", err)
	}

	if customerToUpsert.ID == "" {
		return fmt.Errorf("expected customer id to be filled after upsert")
	}

	// Delete the customer from the database by its struct value.
	deleted, err := customers.Delete(context.Background(), customer)
	if err != nil {
		return fmt.Errorf("delete: %w", err)
	} else if deleted == 0 {
		return fmt.Errorf("delete: was not removed")
	}

	exists, err := customers.Exists(context.Background(), customer.CognitoUserID)
	if err != nil {
		return fmt.Errorf("exists: %w", err)
	}
	if exists {
		return fmt.Errorf("exists: customer should not exist")
	}

	// Do something else with customers.
	return nil
})

if err != nil {
	fmt.Println(fmt.Errorf("in transaction: %w", err))
	return
}

// Insert a blog.
blogs := registry.Blogs()
newBlog := Blog{
	Name: "test_blog_1",
}
err = blogs.InsertSingle(context.Background(), newBlog, &newBlog.ID)
if err != nil {
	fmt.Println(fmt.Errorf("insert single: blog: %w", err))
	return
}

// Insert a blog post to the blog.
blogPosts := registry.BlogPosts()
newBlogPost := BlogPost{
	BlogID:          newBlog.ID,
	Title:           "test_blog_post_1",
	PhotoURL:        "https://test.com/test_blog_post_1.png",
	SourceURL:       "https://test.com/test_blog_post_1.html",
	ReadTimeMinutes: 5,
	Category:        1,
	SearchTerms: []string{
		"test_search_blog_post_1",
		"test_search_blog_post_2",
	},
	ReadDurations: []time.Duration{
		5 * time.Minute,
		10 * time.Minute,
	},
	Feature: Feature{
		IsFeatured: true,
	},
	OtherFeatures: Features{
		Feature{
			IsFeatured: true,
		},
		Feature{
			IsFeatured: false,
		},
	},
	Tags: []Tag{
		{"test_tag_1", "test_tag_value_1"},
		{"test_tag_2", 42},
	},
}
err = blogPosts.InsertSingle(context.Background(), newBlogPost, &newBlogPost.ID)
if err != nil {
	fmt.Println(fmt.Errorf("insert single: blog post: %w", err))
	return
}

query := `SELECT * FROM blog_posts WHERE id = $1 LIMIT 1;`
existingBlogPost, err := blogPosts.SelectSingle(context.Background(), query, newBlogPost.ID)
if err != nil {
	fmt.Println(fmt.Errorf("select single: blog post: %s: %w", newBlogPost.ID, err))
	return
}

// Test select single jsonb column of a custom type of array of custom types.
//
var otherFeatures Features
err = blogPosts.QueryRow(
	context.Background(),
	`SELECT other_features FROM blog_posts WHERE id = $1 LIMIT 1;`,
	newBlogPost.ID,
).Scan(&otherFeatures)
// OR
// otherFeatures, err := QuerySingle[Features](
// 	context.Background(),
// 	db,
// 	`SELECT other_features FROM blog_posts WHERE id = $1 LIMIT 1;`,
// 	newBlogPost.ID,
// )
if err != nil {
	fmt.Println(fmt.Errorf("select single jsonb column of custom array type of custom type: blog post: %s: %w", newBlogPost.ID, err))
	return
}

if expected, got := len(otherFeatures), len(existingBlogPost.OtherFeatures); expected != got {
	fmt.Printf("expected %d other_features but got %d", expected, got)
	return
}

if !reflect.DeepEqual(otherFeatures, existingBlogPost.OtherFeatures) {
	fmt.Printf("expected other_features to be equal but got %#+v and %#+v", otherFeatures, existingBlogPost.OtherFeatures)
	return
}
Example (Notify_JSON) 
schema := NewSchema()
db, err := Open(context.Background(), schema, getTestConnString())
if err != nil {
	fmt.Println(err)
	return
}
// defer db.Close()

const channel = "chat_json"

conn, err := db.Listen(context.Background(), channel)
if err != nil {
	fmt.Println(fmt.Errorf("listen: %w", err))
}

go func() {
	// To just terminate this listener's connection and unlisten from the channel:
	defer conn.Close(context.Background())

	for {
		notification, err := conn.Accept(context.Background())
		if err != nil {
			fmt.Println(fmt.Errorf("accept: %w\n", err))
			return
		}

		payload, err := UnmarshalNotification[Message](notification)
		if err != nil {
			fmt.Println(fmt.Errorf("N: %w", err))
			return
		}

		fmt.Printf("channel: %s, payload.sender: %s, payload.body: %s\n",
			notification.Channel, payload.Sender, payload.Body)
	}
}()

firstMessage := Message{
	Sender: "kataras",
	Body:   "hello",
}
if err = db.Notify(context.Background(), channel, firstMessage); err != nil {
	fmt.Println(fmt.Errorf("notify: first message: %w", err))
	return
}

secondMessage := Message{
	Sender: "kataras",
	Body:   "world",
}

if err = db.Notify(context.Background(), channel, secondMessage); err != nil {
	fmt.Println(fmt.Errorf("notify: second message: %w", err))
	return
}

time.Sleep(5 * time.Second) // give it sometime to receive the notifications, this is too much though.

Output:
channel: chat_json, payload.sender: kataras, payload.body: hello
channel: chat_json, payload.sender: kataras, payload.body: world

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrEmptyPayload = fmt.Errorf("empty payload")

ErrEmptyPayload is returned when the notification payload is empty.

View Source 
var ErrIntentionalRollback = errors.New("skip error: intentional rollback")

ErrIntentionalRollback is an error that can be returned by a transaction function to rollback the transaction.

View Source 
var ErrIsReadOnly = errors.New("repository is read-only")

ErrIsReadOnly is returned by Insert and InsertSingle if the repository is read-only.

View Source 
var (
	// ErrNoRows is fired from a query when no results are came back.
	// Usually it's ignored and an empty json array is sent to the client instead.
	ErrNoRows = pgx.ErrNoRows
)
View Source 
var Presenter = func(td *desc.Table) bool {
	td.Type = desc.TableTypePresenter
	return true
}

Presenter is a TableFilterFunc that sets the table type to "presenter" and returns true. A presenter is a table that is used to present data from one or more tables with custom select queries. It's not a base table neither a view. Example: 

schema.MustRegister("customer_presenter", CustomerPresenter{}, pg.Presenter)
View Source 
var View = func(td *desc.Table) bool {
	td.Type = desc.TableTypeView
	return true
}

View is a TableFilterFunc that sets the table type to "view" and returns true.

Example: 

schema.MustRegister("customer_master", FullCustomer{}, pg.View)
View Source 
var WithLogger = func(logger tracelog.Logger) ConnectionOption {
	return func(poolConfig *pgxpool.Config) error {
		tracer := &tracelog.TraceLog{
			Logger:   logger,
			LogLevel: tracelog.LogLevelTrace,
		}

		poolConfig.ConnConfig.Tracer = tracer
		return nil
	}
}

WithLogger is a ConnectionOption. It sets the logger for the connection pool.

Functions

func IsErrColumnNotExists 

func IsErrColumnNotExists(err error, col string) bool

IsErrColumnNotExists reports whether the error is caused because the "col" defined in a select query was not exists in a row. There is no a typed error available in the driver itself.

func IsErrDuplicate 

func IsErrDuplicate(err error) (string, bool)

IsErrDuplicate reports whether the return error from `Insert` method was caused because of a violation of a unique constraint (it's not typed error at the underline driver). It returns the constraint key if it's true.

func IsErrForeignKey 

func IsErrForeignKey(err error) (string, bool)

IsErrForeignKey reports whether an insert or update command failed due to an invalid foreign key: a foreign key is missing or its source was not found. E.g. ERROR: insert or update on table "food_user_friendly_units" violates foreign key constraint "fk_food" (SQLSTATE 23503)

func IsErrInputSyntax 

func IsErrInputSyntax(err error) (string, bool)

IsErrInputSyntax reports whether the return error from `Insert` method was caused because of invalid input syntax for a specific postgres column type.

func QuerySingle 

func QuerySingle[T any](ctx context.Context, db *DB, query string, args ...any) (entry T, err error)

QuerySingle executes the given query and returns a single T entry.

Example: 

names, err := QuerySingle[MyType](ctx, db, "SELECT a_json_field FROM users;")

func QuerySlice 

func QuerySlice[T any](ctx context.Context, db *DB, query string, args ...any) ([]T, error)

QuerySlice executes the given query and returns a list of T entries. Note that the rows scanner will directly scan an element of T, meaning that the type of T should be a database scannabled type (e.g. string, int, time.Time, etc.).

Example: 

names, err := QuerySlice[string](ctx, db, "SELECT name FROM users;")

func SetDefaultSearchPath 

func SetDefaultSearchPath(searchPath string)

SetDefaultSearchPath sets the default search path for the database.

func SetDefaultTag 

func SetDefaultTag(tag string)

SetDefaultTag sets the default tag name for the struct fields.

func UnmarshalNotification 

func UnmarshalNotification[T any](n *Notification) (T, error)

UnmarshalNotification returns the notification payload as a custom type of T.

Types

type Column 

type Column = desc.Column

Column is a type alias for desc.Column.

type ColumnFilter 

type ColumnFilter = desc.ColumnFilter

ColumnFilter is a type alias for desc.ColumnFilter.

type ConnectionOption 

type ConnectionOption func(*pgxpool.Config) error

ConnectionOption is a function that takes a *pgxpool.Config and returns an error. It is used to set the connection options for the connection pool. It is used by the Open function.

See `WithLogger` package-level function too.

type DB 

type DB struct {
	Pool              *pgxpool.Pool
	ConnectionOptions *pgx.ConnConfig
	// contains filtered or unexported fields
}

DB represents a database connection that can execute queries and transactions. It wraps a pgxpool.Pool and a pgx.ConnConfig to manage the connection options and the search path. It also holds a reference to a Schema that defines the database schema and migrations.

func Open 

func Open(ctx context.Context, schema *Schema, connString string, opts ...ConnectionOption) (*DB, error)

Open creates a new DB instance by parsing the connection string and establishing a connection pool. It also sets the search path to the one specified in the connection string or to the default one if not specified. It takes a context and a schema as arguments and returns the DB instance or an error if any.

Example Code: 

const (

	host     = "localhost" // The host name or IP address of the database server.
	port     = 5432        // The port number of the database server.
	user     = "postgres"  // The user name to connect to the database with.
	password = "admin!123" // The password to connect to the database with.
	schema   = "public"    // The schema name to use in the database.
	dbname   = "test_db"   // The database name to connect to.
	sslMode  = "disable"   // The SSL mode to use for the connection. Can be disable, require, verify-ca or verify-full.

)

connString := fmt.Sprintf("host=%s port=%d user=%s password=%s search_path=%s dbname=%s sslmode=%s pool_max_conns=%d pool_min_conns=%d pool_max_conn_lifetime=%s pool_max_conn_idle_time=%s pool_health_check_period=%s", ...)
OR
connString := "postgres://postgres:admin!123@localhost:5432/test_db?sslmode=disable&search_path=public"

db, err := Open(context.Background(), schema, connString)
Example 
package main

import (
	"context"
	"fmt"
)

func main() {
	db, err := openTestConnection()
	if err != nil {
		handleExampleError(err)
		return
	}
	defer db.Close()

	// Work with the database...
}

func openTestConnection() (*DB, error) {
	// Database code.
	schema := NewSchema()
	schema.MustRegister("customers", Customer{})  // Register the Customer struct as a table named "customers".
	schema.MustRegister("blogs", Blog{})          // Register the Blog struct as a table named "blogs".
	schema.MustRegister("blog_posts", BlogPost{}) // Register the BlogPost struct as a table named "blog_posts".

	// Open a connection to the database using the schema and the connection string.
	db, err := Open(context.Background(), schema, getTestConnString())
	if err != nil {
		return nil, err
	}
	// Let the caller close the database connection pool: defer db.Close()

	// Let's clear the schema, so we can run the tests even if already ran once in the past.
	if err = db.DeleteSchema(context.Background()); err != nil { // DON'T DO THIS ON PRODUCTION.
		return nil, fmt.Errorf("delete schema: %w", err)
	}

	if err = db.CreateSchema(context.Background()); err != nil { // Create the schema in the database if it does not exist.
		return nil, fmt.Errorf("create schema: %w", err)
	}

	if err = db.CheckSchema(context.Background()); err != nil { // Check if the schema in the database matches the schema in the code.
		return nil, fmt.Errorf("check schema: %w", err)
	}

	return db, nil
}

func openEmptyTestConnection() (*DB, error) { // without a schema.
	schema := NewSchema()
	// Open a connection to the database using the schema and the connection string.
	return Open(context.Background(), schema, getTestConnString())
}

func createTestConnectionSchema() error {
	db, err := openTestConnection()
	if err != nil {
		return err
	}

	db.Close()
	return nil
}

// getTestConnString returns a connection string for connecting to a test database.
// It uses constants to define the host, port, user, password, schema, dbname, and sslmode parameters.
func getTestConnString() string {
	const (
		host     = "localhost" // The host name or IP address of the database server.
		port     = 5432        // The port number of the database server.
		user     = "postgres"  // The user name to connect to the database with.
		password = "admin!123" // The password to connect to the database with.
		schema   = "public"    // The schema name to use in the database.
		dbname   = "test_db"   // The database name to connect to.
		sslMode  = "disable"   // The SSL mode to use for the connection. Can be disable, require, verify-ca or verify-full.
	)

	connString := fmt.Sprintf("host=%s port=%d user=%s password=%s search_path=%s dbname=%s sslmode=%s",
		host, port, user, password, schema, dbname, sslMode) // Format the connection string using the parameters.

	return connString // Return the connection string.
}

func OpenPool 

func OpenPool(schema *Schema, pool *pgxpool.Pool) *DB

OpenPool creates a new DB instance with the given context, schema and pool. It copies the connection config from the pool and sets the search path and schema fields of the DB instance. It returns a pointer to the DB instance.

Use the `Open` function to create a new DB instance of a connection string instead.

func (*DB) Begin 

func (db *DB) Begin(ctx context.Context) (*DB, error)

Begin starts a new database transaction and returns a new DB instance that operates within that transaction.

func (*DB) CheckSchema 

func (db *DB) CheckSchema(ctx context.Context) error

CheckSchema checks if the database schema matches the expected table definitions by querying the information schema and comparing the results.

func (*DB) Close 

func (db *DB) Close()

Close closes the database connection pool and its transactions.

func (*DB) Commit 

func (db *DB) Commit(ctx context.Context) error

Commit commits the current database transaction and returns any error that occurs.

func (*DB) CreateSchema 

func (db *DB) CreateSchema(ctx context.Context) error

CreateSchema creates the database schema by executing a series of SQL commands in a transaction.

func (*DB) CreateSchemaDumpSQL 

func (db *DB) CreateSchemaDumpSQL(ctx context.Context) (string, error)

CreateSchemaDumpSQL dumps the SQL commands for creating the database schema.

func (*DB) Delete 

func (db *DB) Delete(ctx context.Context, values ...any) (int64, error)

Delete deletes one or more values from the database by building and executing an SQL query based on the values and the table definition.

func (*DB) DeleteSchema 

func (db *DB) DeleteSchema(ctx context.Context) error

DeleteSchema drops the database schema.

func (*DB) DisableAutoVacuum 

func (db *DB) DisableAutoVacuum(ctx context.Context) error

DisableAutoVacuum disables autovacuum for the whole database.

func (*DB) DisableTableAutoVacuum 

func (db *DB) DisableTableAutoVacuum(ctx context.Context, tableName string) error

DisableTableAutoVacuum disables autovacuum for a specific table.

func (*DB) Exec 

func (db *DB) Exec(ctx context.Context, query string, args ...any) (pgconn.CommandTag, error)

Exec executes SQL. The query can be either a prepared statement name or an SQL string. Arguments should be referenced positionally from the sql "query" string as $1, $2, etc.

func (*DB) ExecFiles 

func (db *DB) ExecFiles(ctx context.Context, fileReader interface {
	ReadFile(name string) ([]byte, error)
}, filenames ...string) error

ExecFiles executes the SQL statements in the given files.

Example: 

//go:embed _embed
var embedDir embed.FS

[...]
err := db.ExecFiles(context.Background(), embedDir, "_embed/triggers.sql", "_embed/functions.sql")

func (*DB) Exists 

func (db *DB) Exists(ctx context.Context, value any) (bool, error)

Exists returns true if a row exists in the table that matches the given value's non-zero fields or false otherwise.

func (*DB) GetVersion 

func (db *DB) GetVersion(ctx context.Context) (string, error)

GetVersion returns the version number of the PostgreSQL database as a string.

func (*DB) InTransaction 

func (db *DB) InTransaction(ctx context.Context, fn func(*DB) error) error

InTransaction runs a function within a database transaction and commits or rolls back depending on the error value returned by the function. Note that: After the first error in the transaction, the transaction is rolled back. After the first error in query execution, the transaction is aborted and no new commands should be sent through the same transaction.

func (*DB) Insert 

func (db *DB) Insert(ctx context.Context, values ...any) error

Insert inserts one or more values into the database by calling db.InsertSingle for each value within a transaction.

func (*DB) InsertSingle 

func (db *DB) InsertSingle(ctx context.Context, value any, idPtr any) error

InsertSingle inserts a single value into the database by building and executing an SQL query based on the value and the table definition.

func (*DB) IsAutoVacuumEnabled 

func (db *DB) IsAutoVacuumEnabled(ctx context.Context) (enabled bool, err error)

IsAutoVacuumEnabled returns true if autovacuum is enabled for the database.

Read more about autovacuum at: https://www.postgresql.org/docs/current/runtime-config-autovacuum.html.

func (*DB) IsTransaction 

func (db *DB) IsTransaction() bool

IsTransaction reports whether this database instance is in transaction.

func (*DB) ListColumns 

func (db *DB) ListColumns(ctx context.Context, tableNames ...string) ([]*desc.Column, error)

ListColumns returns a list of columns definitions for the given table names.

Example 
db, err := openEmptyTestConnection()
if err != nil {
	handleExampleError(err)
	return
}
defer db.Close()

columns, err := db.ListColumns(context.Background())
if err != nil {
	handleExampleError(err)
	return
}

expectedTags := []string{
	`[blog_posts.id] pg:"name=id,type=uuid,primary,default=gen_random_uuid()"`,
	`[blog_posts.created_at] pg:"name=created_at,type=timestamp,default=clock_timestamp()"`,
	`[blog_posts.updated_at] pg:"name=updated_at,type=timestamp,default=clock_timestamp()"`,
	`[blog_posts.blog_id] pg:"name=blog_id,type=uuid,ref=blogs(id CASCADE deferrable),index=btree"`,
	`[blog_posts.title] pg:"name=title,type=varchar(255),unique_index=uk_blog_post"`,
	`[blog_posts.photo_url] pg:"name=photo_url,type=varchar(255)"`,
	`[blog_posts.source_url] pg:"name=source_url,type=varchar(255),unique_index=uk_blog_post"`,
	`[blog_posts.read_time_minutes] pg:"name=read_time_minutes,type=smallint,default=1,check=read_time_minutes > 0"`,
	`[blog_posts.category] pg:"name=category,type=smallint,default=0"`,
	`[blog_posts.search_terms] pg:"name=search_terms,type=varchar[]"`,
	`[blog_posts.read_durations] pg:"name=read_durations,type=bigint[]"`,
	`[blog_posts.feature] pg:"name=feature,type=jsonb"`,
	`[blog_posts.other_features] pg:"name=other_features,type=jsonb"`,
	`[blog_posts.tags] pg:"name=tags,type=jsonb"`,
	`[blogs.id] pg:"name=id,type=uuid,primary,default=gen_random_uuid()"`,
	`[blogs.created_at] pg:"name=created_at,type=timestamp,default=clock_timestamp()"`,
	`[blogs.updated_at] pg:"name=updated_at,type=timestamp,default=clock_timestamp()"`,
	`[blogs.name] pg:"name=name,type=varchar(255)"`,
	`[customers.id] pg:"name=id,type=uuid,primary,default=gen_random_uuid()"`,
	`[customers.created_at] pg:"name=created_at,type=timestamp,default=clock_timestamp()"`,
	`[customers.updated_at] pg:"name=updated_at,type=timestamp,default=clock_timestamp()"`,
	`[customers.cognito_user_id] pg:"name=cognito_user_id,type=uuid,unique_index=customer_unique_idx"`,
	`[customers.email] pg:"name=email,type=varchar(255),unique_index=customer_unique_idx"`,
	`[customers.name] pg:"name=name,type=varchar(255),index=btree"`,
}

for i, column := range columns {
	expected := expectedTags[i]
	got := fmt.Sprintf("[%s.%s] %s", column.TableName, column.Name, column.FieldTagString(true))

	if expected != got {
		fmt.Printf("expected tag:\n%s\nbut got:\n%s\n", expected, got)
	}
}

fmt.Println("OK")

Output:
OK

func (*DB) ListColumnsInformationSchema 

func (db *DB) ListColumnsInformationSchema(ctx context.Context, tableNames ...string) ([]*desc.ColumnBasicInfo, error)

ListColumnsInformationSchema returns a list of basic columns information for the given table names or all.

Example 
if err := createTestConnectionSchema(); err != nil {
	handleExampleError(err)
	return
}

db, err := openEmptyTestConnection()
if err != nil {
	handleExampleError(err)
	return
}
defer db.Close()

columns, err := db.ListColumnsInformationSchema(context.Background())
if err != nil {
	handleExampleError(err)
	return
}

for _, column := range columns {
	fmt.Printf("%#+v\n", column)
}

Output:
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"id", OrdinalPosition:1, Description:"", Default:"gen_random_uuid()", DataType:0x2f, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"created_at", OrdinalPosition:2, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"updated_at", OrdinalPosition:3, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"blog_id", OrdinalPosition:4, Description:"", Default:"", DataType:0x2f, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"title", OrdinalPosition:5, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"photo_url", OrdinalPosition:6, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"source_url", OrdinalPosition:7, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"read_time_minutes", OrdinalPosition:8, Description:"", Default:"1", DataType:0x24, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"category", OrdinalPosition:9, Description:"", Default:"0", DataType:0x24, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"search_terms", OrdinalPosition:10, Description:"", Default:"", DataType:0xc, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"read_durations", OrdinalPosition:11, Description:"", Default:"", DataType:0x2, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"feature", OrdinalPosition:12, Description:"", Default:"", DataType:0x18, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"other_features", OrdinalPosition:13, Description:"", Default:"", DataType:0x18, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blog_posts", TableDescription:"", TableType:0x0, Name:"tags", OrdinalPosition:14, Description:"", Default:"", DataType:0x18, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blogs", TableDescription:"", TableType:0x0, Name:"id", OrdinalPosition:1, Description:"", Default:"gen_random_uuid()", DataType:0x2f, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blogs", TableDescription:"", TableType:0x0, Name:"created_at", OrdinalPosition:2, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blogs", TableDescription:"", TableType:0x0, Name:"updated_at", OrdinalPosition:3, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"blogs", TableDescription:"", TableType:0x0, Name:"name", OrdinalPosition:4, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"id", OrdinalPosition:1, Description:"", Default:"gen_random_uuid()", DataType:0x2f, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"created_at", OrdinalPosition:2, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"updated_at", OrdinalPosition:3, Description:"", Default:"clock_timestamp()", DataType:0x2b, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"cognito_user_id", OrdinalPosition:4, Description:"", Default:"", DataType:0x2f, DataTypeArgument:"", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"email", OrdinalPosition:5, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}
&desc.ColumnBasicInfo{TableName:"customers", TableDescription:"", TableType:0x0, Name:"name", OrdinalPosition:6, Description:"", Default:"", DataType:0xb, DataTypeArgument:"255", IsNullable:false, IsIdentity:false, IsGenerated:false}

func (*DB) ListConstraints 

func (db *DB) ListConstraints(ctx context.Context, tableNames ...string) ([]*desc.Constraint, error)

ListConstraints returns a list of constraint definitions in the database schema by querying the pg_constraint table and.

Example 
connString := getTestConnString()
schema := NewSchema()
db, err := Open(context.Background(), schema, connString)
if err != nil {
	handleExampleError(err)
	return
}
defer db.Close()

/*
	table_name	column_name	constraint_name	constraint_type	constraint_definition	index_type
	blog_posts		blog_posts_blog_id_fkey	i	CREATE INDEX blog_posts_blog_id_fkey ON public.blog_posts USING btree (blog_id)
	blog_posts	blog_id	blog_posts_blog_id_fkey	f	FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE DEFERRABLE
	blog_posts	id	blog_posts_pkey	p	PRIMARY KEY (id)	btree
	blog_posts	read_time_minutes	blog_posts_read_time_minutes_check	c	CHECK ((read_time_minutes > 0))
	blog_posts	source_url	uk_blog_post	u	UNIQUE (title, source_url)	btree
	blog_posts	title	uk_blog_post	u	UNIQUE (title, source_url)	btree
	blogs	id	blogs_pkey	p	PRIMARY KEY (id)	btree
	customers		customers_name_idx	i	CREATE INDEX customers_name_idx ON public.customers USING btree (name)
	customers	cognito_user_id	customer_unique_idx	u	UNIQUE (cognito_user_id, email)	btree
	customers	email	customer_unique_idx	u	UNIQUE (cognito_user_id, email)	btree
	customers	id	customers_pkey	p	PRIMARY KEY (id)	btree
*/
var expectedConstraints = []*desc.Constraint{
	{
		TableName:      "blog_posts",
		ColumnName:     "blog_id",
		ConstraintName: "blog_posts_blog_id_fkey",
		ConstraintType: desc.IndexConstraintType,
		IndexType:      desc.Btree,
	},
	{
		TableName:      "blog_posts",
		ColumnName:     "blog_id",
		ConstraintName: "blog_posts_blog_id_fkey",
		ConstraintType: desc.ForeignKeyConstraintType,
		ForeignKey: &desc.ForeignKeyConstraint{
			ColumnName:          "blog_id",
			ReferenceTableName:  "blogs",
			ReferenceColumnName: "id",
			OnDelete:            "CASCADE",
			Deferrable:          true,
		},
	},
	{
		TableName:      "blog_posts",
		ColumnName:     "id",
		ConstraintName: "blog_posts_pkey",
		ConstraintType: desc.PrimaryKeyConstraintType,
		IndexType:      desc.Btree,
	},
	{
		TableName:      "blog_posts",
		ColumnName:     "read_time_minutes",
		ConstraintName: "blog_posts_read_time_minutes_check",
		ConstraintType: desc.CheckConstraintType,
		Check: &desc.CheckConstraint{
			Expression: "read_time_minutes > 0",
		},
	},
	{
		TableName:      "blog_posts",
		ColumnName:     "source_url",
		ConstraintName: "uk_blog_post",
		ConstraintType: desc.UniqueConstraintType,
		IndexType:      desc.Btree,
		Unique: &desc.UniqueConstraint{
			Columns: []string{"title", "source_url"},
		},
	},
	{
		TableName:      "blog_posts",
		ColumnName:     "title",
		ConstraintName: "uk_blog_post",
		ConstraintType: desc.UniqueConstraintType,
		IndexType:      desc.Btree,
		Unique: &desc.UniqueConstraint{
			Columns: []string{"title", "source_url"},
		},
	},
	{
		TableName:      "blogs",
		ColumnName:     "id",
		ConstraintName: "blogs_pkey",
		ConstraintType: desc.PrimaryKeyConstraintType,
		IndexType:      desc.Btree,
	},
	{
		TableName:      "customers",
		ColumnName:     "name",
		ConstraintName: "customers_name_idx",
		ConstraintType: desc.IndexConstraintType,
		IndexType:      desc.Btree,
	},
	{
		TableName:      "customers",
		ColumnName:     "cognito_user_id",
		ConstraintName: "customer_unique_idx",
		ConstraintType: desc.UniqueConstraintType,
		IndexType:      desc.Btree,
		Unique: &desc.UniqueConstraint{
			Columns: []string{"cognito_user_id", "email"},
		},
	},
	{
		TableName:      "customers",
		ColumnName:     "email",
		ConstraintName: "customer_unique_idx",
		ConstraintType: desc.UniqueConstraintType,
		IndexType:      desc.Btree,
		Unique: &desc.UniqueConstraint{
			Columns: []string{"cognito_user_id", "email"},
		},
	},
	{
		TableName:      "customers",
		ColumnName:     "id",
		ConstraintName: "customers_pkey",
		ConstraintType: desc.PrimaryKeyConstraintType,
		IndexType:      desc.Btree,
	},
}

columns, err := db.ListConstraints(context.Background())
if err != nil {
	handleExampleError(err)
	return
}

for i, got := range columns {
	expected := expectedConstraints[i]
	if !reflect.DeepEqual(expected, got) {

		if expected.ForeignKey != nil && got.ForeignKey != nil {
			if !reflect.DeepEqual(expected.ForeignKey, got.ForeignKey) {
				fmt.Printf("expected foreign key:\n%#+v\nbut got:\n%#+v", expected.ForeignKey, got.ForeignKey)
			}
			continue
		}
		if expected.Unique != nil && got.Unique != nil {
			if !reflect.DeepEqual(expected.Unique, got.Unique) {
				fmt.Printf("expected unique:\n%#+v\nbut got:\n%#+v", expected.Unique, got.Unique)
			}
			continue
		}

		if expected.Check != nil && got.Check != nil {
			if !reflect.DeepEqual(expected.Check, got.Check) {
				fmt.Printf("expected check:\n%#+v\nbut got:\n%#+v", expected.Check, got.Check)
			}
			continue
		}

		fmt.Printf("expected:\n%#+v\nbut got:\n%#+v", expected, got)
	}
}

fmt.Println("OK")

Output:
OK

func (*DB) ListTables 

func (db *DB) ListTables(ctx context.Context, opts ListTablesOptions) ([]*desc.Table, error)

ListTables returns a list of converted table definitions from the remote database schema.

func (*DB) ListTriggers 

func (db *DB) ListTriggers(ctx context.Context) ([]*desc.Trigger, error)

ListTriggers returns a list of triggers in the database for a given set of tables The method takes a context and returns a slice of Trigger pointers, and an error if any.

func (*DB) Listen 

func (db *DB) Listen(ctx context.Context, channel string) (*Listener, error)

Listen listens for notifications on the given channel and returns a Listener instance.

Example

go test -coverprofile=cov go tool cover -html=cov 

db, err := openEmptyTestConnection()
if err != nil {
	handleExampleError(err)
	return
}
// defer db.Close()

const channel = "chat_db"

conn, err := db.Listen(context.Background(), channel)
if err != nil {
	fmt.Println(fmt.Errorf("listen: %w\n", err))
	return
}

go func() {
	// To just terminate this listener's connection and unlisten from the channel:
	defer conn.Close(context.Background())

	for {
		notification, err := conn.Accept(context.Background())
		if err != nil {
			fmt.Println(fmt.Errorf("accept: %w\n", err))
			return
		}

		fmt.Printf("channel: %s, payload: %s\n", notification.Channel, notification.Payload)
	}
}()

if err = db.Notify(context.Background(), channel, "hello"); err != nil {
	fmt.Println(fmt.Errorf("notify: hello: %w", err))
	return
}

if err = db.Notify(context.Background(), channel, "world"); err != nil {
	fmt.Println(fmt.Errorf("notify: world: %w", err))
	return
}

time.Sleep(5 * time.Second) // give it sometime to receive the notifications.

Output:
channel: chat_db, payload: hello
channel: chat_db, payload: world

func (*DB) Notify 

func (db *DB) Notify(ctx context.Context, channel string, payload any) error

Notify sends a notification using pg_notify to the database.

See the `Listen` package-level function too.

func (*DB) Query 

func (db *DB) Query(ctx context.Context, query string, args ...any) (Rows, error)

Query executes the given "query" with args. If there is an error the returned Rows will be returned in an error state.

func (*DB) QueryRow 

func (db *DB) QueryRow(ctx context.Context, query string, args ...any) Row

QueryRow is a convenience wrapper over QueryRow. Any error that occurs while querying is deferred until calling Scan on the returned Row. That Row will error with ErrNoRows if no rows are returned.

func (*DB) Rollback 

func (db *DB) Rollback(ctx context.Context) error

Rollback rolls back the current database transaction and returns any error that occurs.

func (*DB) SearchPath 

func (db *DB) SearchPath() string

func (*DB) Select 

func (db *DB) Select(ctx context.Context, scannerFunc func(Rows) error, query string, args ...any) error

Select executes a query that returns rows and calls the scanner function on them. It takes a context, a query string, a scanner function and a variadic list of arguments as parameters. It returns an error if the query fails, the scanner function returns an error or the rows have an error.

func (*DB) SelectByID 

func (db *DB) SelectByID(ctx context.Context, destPtr any, id any) error

SelectByID selects a row from a table by matching the primary key column with the given argument.

func (*DB) SelectByUsernameAndPassword 

func (db *DB) SelectByUsernameAndPassword(ctx context.Context, destPtr any, username, plainPassword string) error

SelectByUsernameAndPassword selects a row from a table by matching the username and password columns with the given arguments and scans the row into the destPtr variable.

func (*DB) Unlisten 

func (db *DB) Unlisten(ctx context.Context, channel string) error

Unlisten removes the given channel from the list of channels that the database is listening on. Available channels: - Any custom one - * (for all)

func (*DB) Update 

func (db *DB) Update(ctx context.Context, values ...any) (int64, error)

Update updates one or more values in the database by building and executing an SQL query based on the values and the table definition.

func (*DB) UpdateOnlyColumns 

func (db *DB) UpdateOnlyColumns(ctx context.Context, columnsToUpdate []string, values ...any) (int64, error)

UpdateOnlyColumns updates one or more values in the database by building and executing an SQL query based on the values and the table definition.

The columnsToUpdate parameter can be used to specify which columns should be updated.

func (*DB) Upsert 

func (db *DB) Upsert(ctx context.Context, forceOnConflictExpr string, values ...any) error

Upsert inserts or updates one or more values into the database by calling db.UpsertSingle for each value within a transaction.

func (*DB) UpsertSingle 

func (db *DB) UpsertSingle(ctx context.Context, value any, idPtr any, forceOnConflictExpr string) error

UpsertSingle inserts or updates a single value into the database by building and executing an SQL query based on the value and the table definition.

type DataType 

type DataType = desc.DataType

DataType is a type alias for desc.DataType.

type ListTablesOptions 

type ListTablesOptions struct {
	TableNames []string

	Filter desc.TableFilter // Filter allows to customize the StructName and its Column field types.
}

ListTableOptions are the options for listing tables.

type Listener 

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

Listener represents a postgres database LISTEN connection.

func (*Listener) Accept 

func (l *Listener) Accept(ctx context.Context) (*Notification, error)

Accept waits for a notification and returns it.

func (*Listener) Close 

func (l *Listener) Close(ctx context.Context) error

Close closes the listener connection.

type MapTypeFilter 

type MapTypeFilter map[string]any

MapTypeFilter is a map of expressions inputs text to field type. It's a TableFilter.

Example on LsitTableOptions of the ListTables method: 

Filter: pg.MapTypeFilter{
	"customer_profiles.fields.jsonb": entity.Fields{},
},

func (MapTypeFilter) FilterTable 

func (m MapTypeFilter) FilterTable(t *Table) bool

FilterTable implements the TableFilter interface.

type Notification 

type Notification = pgconn.Notification

Notification is a type alias of pgconn.Notification type.

type Repository 

type Repository[T any] struct {
	// contains filtered or unexported fields
}

Repository is a generic type that represents a repository for a specific type T.

func NewRepository 

func NewRepository[T any](db *DB) *Repository[T]

NewRepository creates and returns a new Repository instance for a given type T and a DB instance. It panics if the T was not registered to the schema.

Example 
package main

import (
	"context"
	"fmt"
)

// Repositories.

// CustomerRepository is a struct that wraps a generic Repository instance with the Customer type parameter.
// It provides methods for accessing and manipulating customer data in the database.
type CustomerRepository struct {
	*Repository[Customer]
}

// NewCustomerRepository creates and returns a new CustomerRepository instance with the given DB instance.
func NewCustomerRepository(db *DB) *CustomerRepository {
	return &CustomerRepository{
		Repository: NewRepository[Customer](db),
	}
}

// InTransaction overrides the pg Repository's InTransaction method to include the custom type of CustomerRepository.
// It takes a context and a function as arguments and executes the function within a database transaction,
// passing it a CustomerRepository instance that uses the transactional DB instance.
func (r *CustomerRepository) InTransaction(ctx context.Context, fn func(*CustomerRepository) error) (err error) {
	if r.DB().IsTransaction() {
		return fn(r)
	}

	return r.DB().InTransaction(ctx, func(db *DB) error {
		txRepository := NewCustomerRepository(db)
		return fn(txRepository)
	})
}

// Exists is a custom method that uses the pg repository's Database instance to execute a query and return a result.
// It takes a context and a cognitoUserID as arguments and checks if there is any customer row with that cognitoUserID in the database.
func (r *CustomerRepository) Exists(ctx context.Context, cognitoUserID string) (exists bool, err error) {
	// query := `SELECT EXISTS(SELECT 1 FROM customers WHERE cognito_user_id = $1)`
	// err = r.QueryRow(ctx, query, cognitoUserID).Scan(&exists)
	// OR:

	exists, err = r.Repository.Exists(ctx, Customer{CognitoUserID: cognitoUserID})
	return
}

// Registry is (optional) a struct that holds references to different repositories for accessing and manipulating data in the database.
// It has a db field that is a pointer to a DB instance, and a customers field that is a pointer to a CustomerRepository instance.
type Registry struct {
	db *DB

	customers *CustomerRepository
	blogs     *Repository[Blog]
	blogPosts *Repository[BlogPost]
}

// NewRegistry creates and returns a new Registry instance with the given DB instance.
// It also initializes the customers field with a new CustomerRepository instance that uses the same DB instance.
func NewRegistry(db *DB) *Registry {
	return &Registry{
		db: db,

		customers: NewCustomerRepository(db),
		blogs:     NewRepository[Blog](db),
		blogPosts: NewRepository[BlogPost](db),
	}
}

// InTransaction overrides the pg Repository's InTransaction method to include the custom type of Registry.
// It takes a context and a function as arguments and executes the function within a database transaction,
// passing it a Registry instance that uses the transactional DB instance.
func (r *Registry) InTransaction(ctx context.Context, fn func(*Registry) error) (err error) {
	if r.db.IsTransaction() {
		return fn(r)
	}

	return r.db.InTransaction(ctx, func(db *DB) error {
		txRegistry := NewRegistry(db)
		return fn(txRegistry)
	})
}

// Customers returns the CustomerRepository instance of the Registry.
func (r *Registry) Customers() *CustomerRepository {
	return r.customers
}

// Blogs returns the Repository instance of the Blog entity.
func (r *Registry) Blogs() *Repository[Blog] {
	return r.blogs
}

// BlogPosts returns the Repository instance of the BlogPost entity.
func (r *Registry) BlogPosts() *Repository[BlogPost] {
	return r.blogPosts
}

func main() {
	db, err := openTestConnection()
	if err != nil {
		handleExampleError(err)
		return
	}
	defer db.Close()

	registry := NewRegistry(db)       // Create a new Registry instance with the DB instance.
	customers := registry.Customers() // Get the CustomerRepository instance from the Registry.

	// Repository example code.
	customerToInsert := Customer{ // Create a Customer struct to be inserted into the database.
		CognitoUserID: "373f90eb-00ac-410f-9fe0-1a7058d090ba",
		Email:         "kataras2006@hotmail.com",
		Name:          "kataras",
	}

	err = customers.InsertSingle(context.Background(), customerToInsert, &customerToInsert.ID)
	if err != nil {
		handleExampleError(err)
		return
	}

	fmt.Println(customerToInsert.ID)
}

func (*Repository[T]) DB 

func (repo *Repository[T]) DB() *DB

DB returns the DB instance associated with the Repository instance.

func (*Repository[T]) Delete 

func (repo *Repository[T]) Delete(ctx context.Context, values ...T) (int64, error)

Delete deletes one or more values of type T from the database by their primary key values.

func (*Repository[T]) Exec 

func (repo *Repository[T]) Exec(ctx context.Context, query string, args ...any) (pgconn.CommandTag, error)

Exec executes a query that does not return rows and returns a command tag and an error.

func (*Repository[T]) Exists 

func (repo *Repository[T]) Exists(ctx context.Context, value T) (bool, error)

Exists returns true if a row exists in the table that matches the given value's non-zero fields or false otherwise.

func (*Repository[T]) InTransaction 

func (repo *Repository[T]) InTransaction(ctx context.Context, fn func(*Repository[T]) error) error

InTransaction runs a function within a database transaction and commits or rolls back depending on the error value returned by the function.

func (*Repository[T]) Insert 

func (repo *Repository[T]) Insert(ctx context.Context, values ...T) error

Insert inserts one or more values of type T into the database by calling repo.InsertSingle for each value within a transaction.

func (*Repository[T]) InsertSingle 

func (repo *Repository[T]) InsertSingle(ctx context.Context, value T, idPtr any) error

InsertSingle inserts a single value of type T into the database by calling repo.db.InsertSingle with the value and the idPtr.

If it is not null then the value is updated by its primary key value.

func (*Repository[T]) IsReadOnly 

func (repo *Repository[T]) IsReadOnly() bool

IsReadOnly returns true if the underline repository's table is read-only or false otherwise.

func (*Repository[T]) IsTransaction 

func (repo *Repository[T]) IsTransaction() bool

IsTransaction returns true if the underline database is already in a transaction or false otherwise.

func (*Repository[T]) Query 

func (repo *Repository[T]) Query(ctx context.Context, query string, args ...any) (Rows, error)

Query executes a query that returns multiple rows and returns them as a Rows instance and an error.

func (*Repository[T]) QueryRow 

func (repo *Repository[T]) QueryRow(ctx context.Context, query string, args ...any) Row

QueryRow executes a query that returns at most one row and returns it as a Row instance.

func (*Repository[T]) Select 

func (repo *Repository[T]) Select(ctx context.Context, query string, args ...any) ([]T, error)

Select executes a SQL query and returns a slice of values of type T that match the query results.

func (*Repository[T]) SelectByID 

func (repo *Repository[T]) SelectByID(ctx context.Context, id any) (T, error)

SelectByID selects a row from a table by matching the id column with the given argument and returns the row or ErrNoRows.

func (*Repository[T]) SelectByUsernameAndPassword 

func (repo *Repository[T]) SelectByUsernameAndPassword(ctx context.Context, username, plainPassword string) (T, error)

SelectByUsernameAndPassword selects a row from a table by matching the username and password columns with the given arguments and returns the row or ErrNoRows.

func (*Repository[T]) SelectSingle 

func (repo *Repository[T]) SelectSingle(ctx context.Context, query string, args ...any) (T, error)

SelectSingle executes a SQL query and returns a single value of type T that matches the query result.

func (*Repository[T]) Update 

func (repo *Repository[T]) Update(ctx context.Context, values ...T) (int64, error)

Update updates one or more values of type T in the database by their primary key values.

func (*Repository[T]) UpdateOnlyColumns 

func (repo *Repository[T]) UpdateOnlyColumns(ctx context.Context, columnsToUpdate []string, values ...T) (int64, error)

UpdateOnlyColumns updates one or more values of type T in the database by their primary key values.

The columnsToUpdate parameter can be used to specify which columns should be updated.

func (*Repository[T]) Upsert 

func (repo *Repository[T]) Upsert(ctx context.Context, forceOnConflictExpr string, values ...T) error

Upsert inserts or updates one or more values of type T into the database.

func (*Repository[T]) UpsertSingle 

func (repo *Repository[T]) UpsertSingle(ctx context.Context, forceOnConflictExpr string, value T, idPtr any) error

UpsertSingle inserts or updates a single value of type T into the database.

If idPtr is not null then the value is updated by its primary key value.

type Row 

type Row = pgx.Row

Row is a type alias for pgx.Row.

type Rows 

type Rows = pgx.Rows

Rows is a type alias for pgx.Rows.

type Schema 

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

Schema is a type that represents a schema for the database.

func NewSchema 

func NewSchema() *Schema

NewSchema creates and returns a new Schema with an initialized struct cache.

Example 
package main

import (
	"fmt"
	"time"
)

// Structs.

// BaseEntity is a struct that defines common fields for all entities in the database.
// It has an ID field of type uuid that is the primary key, and two timestamp fields
// for tracking the creation and update times of each row.
type BaseEntity struct {
	ID        string    `pg:"type=uuid,primary"`
	CreatedAt time.Time `pg:"type=timestamp,default=clock_timestamp()"`
	UpdatedAt time.Time `pg:"type=timestamp,default=clock_timestamp()"`
}

// Customer is a struct that represents a customer entity in the database.
// It embeds the BaseEntity struct and adds a CognitoUserID field of type uuid
// that is required and unique. It also specifies a conflict resolution strategy
// for the CognitoUserID field in case of duplicate values.
type Customer struct {
	BaseEntity
	// CognitoUserID string `pg:"type=uuid,unique,conflict=DO UPDATE SET cognito_user_id=EXCLUDED.cognito_user_id"`

	CognitoUserID string `pg:"type=uuid,unique_index=customer_unique_idx"`
	Email         string `pg:"type=varchar(255),unique_index=customer_unique_idx"`
	// ^ optional: unique to allow upsert by "email"-only column confliction instead of the unique_index.
	Name string `pg:"type=varchar(255),index=btree"`
}

// Blog is a struct that represents a blog entity in the database.
// It embeds the BaseEntity struct and has no other fields.
type Blog struct {
	BaseEntity

	Name string `pg:"type=varchar(255)"`
}

// BlogPost is a struct that represents a blog post entity in the database.
// It embeds the BaseEntity struct and adds several fields for the blog post details,
// such as BlogID, Title, PhotoURL, SourceURL, ReadTimeMinutes, and Category.
// The BlogID field is a foreign key that references the ID field of the blogs table,
// with cascade option for deletion and deferrable option for constraint checking.
// The Title and SourceURL fields are part of a unique index named uk_blog_post,
// which ensures that no two blog posts have the same title or source URL.
// The ReadTimeMinutes field is a smallint with a default value of 1 and a check constraint
// that ensures it is positive. The Category field is a smallint with a default value of 0.
type BlogPost struct {
	BaseEntity

	BlogID          string `pg:"type=uuid,index,ref=blogs(id cascade deferrable)"`
	Title           string `pg:"type=varchar(255),unique_index=uk_blog_post"`
	PhotoURL        string `pg:"type=varchar(255)"`
	SourceURL       string `pg:"type=varchar(255),unique_index=uk_blog_post"`
	ReadTimeMinutes int    `pg:"type=smallint,default=1,check=read_time_minutes > 0"`
	Category        int    `pg:"type=smallint,default=0"`

	SearchTerms   []string        `pg:"type=varchar[]"` // Test a slice of strings.
	ReadDurations []time.Duration `pg:"type=bigint[]"`  // Test a slice of time.Duration based on an int64.

	// Custom types.
	Feature       Feature  `pg:"type=jsonb"` // Test a JSON structure.
	OtherFeatures Features `pg:"type=jsonb"` // Test a JSON array of structures behind a custom type.
	Tags          []Tag    `pg:"type=jsonb"` // Test a JSON array of structures.
}

type Features []Feature

type Feature struct {
	IsFeatured bool `json:"is_featured"`
}

type Tag struct {
	Name  string `json:"name"`
	Value any    `json:"value"`
}

func main() {
	// Database code.
	schema := NewSchema()
	schema.MustRegister("customers", Customer{})  // Register the Customer struct as a table named "customers".
	schema.MustRegister("blogs", Blog{})          // Register the Blog struct as a table named "blogs".
	schema.MustRegister("blog_posts", BlogPost{}) // Register the BlogPost struct as a table named "blog_posts".

	fmt.Println("OK")
}

Output:
OK

func (*Schema) Get 

func (s *Schema) Get(typ reflect.Type) (*desc.Table, error)

Get takes a reflect.Type that represents a struct type and returns a pointer to a Table that represents the table definition for the database or an error if the type is not registered in the schema.

func (*Schema) GetByTableName 

func (s *Schema) GetByTableName(tableName string) (*desc.Table, error)

GetByTableName takes a table name as a string and returns a pointer to a Table that represents the table definition for the database or an error if the table name is not registered in the schema.

func (*Schema) HandlePassword 

func (s *Schema) HandlePassword(handler desc.PasswordHandler) *Schema

HandlePassword sets the password handler.

func (*Schema) HasColumnType 

func (s *Schema) HasColumnType(dataTypes ...desc.DataType) bool

HasColumnType takes a DataType that represents a data type for the database and returns true if any of the tables in the schema has a column with that data type.

func (*Schema) HasPassword 

func (s *Schema) HasPassword() bool

HasPassword reports whether the tables in the schema have a column with the password feature enabled.

func (*Schema) MustRegister 

func (s *Schema) MustRegister(tableName string, emptyStructValue any, opts ...TableFilterFunc) *Schema

MustRegister same as "Register" but it panics on errors and returns the Schema instance instead of the Table one.

func (*Schema) Register 

func (s *Schema) Register(tableName string, emptyStructValue any, opts ...TableFilterFunc) (*desc.Table, error)

Register registers a database model (a struct value) mapped to a specific database table name. Returns the generated Table definition.

func (*Schema) TableNames 

func (s *Schema) TableNames(types ...desc.TableType) []string

TableNames returns a slice of strings that represents all the table names in the schema.

func (*Schema) Tables 

func (s *Schema) Tables(types ...desc.TableType) []*desc.Table

Tables returns a slice of pointers to Table that represents all the table definitions in the schema sorted by their registered position.

type Table 

type Table = desc.Table

Table is a type alias for desc.Table.

type TableFilterFunc 

type TableFilterFunc = desc.TableFilterFunc

TableFilter is a type alias for desc.TableFilter.

Source Files

View all Source files

Directories

Path Synopsis
_examples
basic command
presenter command
view command

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.