Features

Schema definition

pqt allows to programmatically define the database schema. That includes:

• Schemas
• Tables
• Columns
• Constraints
• Relationships
• Functions

Code generation

SQL

Database schema is generated by pqtsql package. It can be found in the output file at the very end, as a constant called SQL. It can be easily accessed through CLI for further processing by using go doc command. As an example:

go doc github.com/piotrkowalczuk/pqt/example/app/internal/model.SQL

It will produce output like:

const SQL = `
-- sql schema beginning
-- do not modify, generated by pqt

CREATE SCHEMA IF NOT EXISTS example;

CREATE OR REPLACE FUNCTION multiply(x BIGINT, y BIGINT) RETURNS BIGINT
    AS 'SELECT x * y'
    LANGUAGE SQL
    VOLATILE;

CREATE TABLE IF NOT EXISTS example.category (
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    id BIGSERIAL,
    name TEXT NOT NULL,
    parent_id BIGINT,
    updated_at TIMESTAMPTZ,
    
    CONSTRAINT "example.category_id_pkey" PRIMARY KEY (id),
    CONSTRAINT "example.category_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES example.category (id)
);

...

-- sql schema end
`
    / SQL ...

Everything in between comments -- sql schema beginning and -- sql schema end is a valid SQL code.

Golang

Building Blocks

• Composer - A builder-like object that keeps buffer and arguments but also tracks positional parameters.
• ErrorConstraint - Helper function that if possible, it extracts constraint from pq.Error. It make easy to build switch statements using generated constraints.
• <table-name>Entity - A struct that reflects single row within the database.
  • Prop - Returns pointer to property by given name if exists, otherwise false.
  • Props - Returns slice of pointers to properties that match given names (all if a not even single name is given).
• Scan<Entity>Rows - A helper function that scans *sql.Rows into []*<table-name>Entity.
• <table-name>Iterator - Interface returned by FindIter method. It is implemented by <table-name>Rows (that embeds sql.Rows).
• constants:
  • complete names
  • column names
  • constraints - The library generates exact names of each constraint and corresponding constant. It makes handling query errors using ErrorConstraint helper function easy.
• <table-name>Criteria - An object that can be passed to the Find method, it allows creating complex queries.
  • <table-name>Or - Utility function that joins Criteria using logical OR operator.
  • <table-name>And - Utility function that joins Criteria using logical AND operator.
• <table-name>Patch - A structure used by UpdateOneBy<primary-key> method to modify existing entity.

Data Access Layer

• <table-name>RepositoryBase - Data access layer that exposes API to manipulate entities:
  • Count - Returns number of entities for given entity type.
  • Find - Returns collection of entities that match given expression.
  • FindIter - Works like Find but returns an iterator.
  • Insert - Saves given entity in the database.
  • FindOneBy<primary-key> - Retrieves a single object. It performs a lookup by primary key.
  • FindOneBy<unique-key> - Retrieves a single object. It performs a lookup by unique key.
  • UpdateOneBy<primary-key> - Modifies a single object. It performs a lookup by primary key.
  • UpdateOneBy<unique-key> - Modifies a single object. It performs a lookup by unique key.
  • DeleteOneBy<primary-key> - Deletes single entity, it performs a lookup by primary key.
  • Tx - Instantiates <table-name>RepositoryBaseTx using given *sql.Tx.
  • BeginTx - Works like Tx. It starts a new transaction under the hood.
  • RunInTransaction - Executes user-defined function until transaction will succeed or it will exceed the number of possible attempts.
• <table-name>RepositoryBaseTx - A short-lived object that works in a similar way to base version. It executes all methods during a single transaction. Additionally, it implements:
  • Commit - Commits underlying transaction, repository instance is useless after transaction is done.
  • Rollback - Rollbacks underlying transaction, same like with Commit repository instance is useless after transaction is done.

Data Structures

• Nullable
  • NullByteaArray - Wrapper around pq.ByteaArray that follow sql package convention.
  • NullInt64Array - Wrapper around pq.Int64Array that follow sql package convention.
  • NullFloat64Array - Wrapper around pq.Float64Array that follow sql package convention.
  • NullStringArray - Wrapper around pq.StringArray that follow sql package convention.
  • NullBoolArray - Wrapper around pq.BoolArray that follow sql package convention.
• JSON
  • JSONArrayInt64 - Wrapper for []int64, it generates JSONB compatible array ([] instead of {})
  • JSONArrayFloat64 - Wrapper for []float64, it generates JSONB compatible array ([] instead of {})
  • JSONArrayString - Wrapper for []string, it generates JSONB compatible array ([] instead of {})