postgres

README

PostgreSQL Indexer

The PostgreSQL indexer can fully index the current state for all modules that implement cosmossdk.io/schema.HasModuleCodec. implement cosmossdk.io/schema.HasModuleCodec.

Table, Column and Enum Naming

ObjectTypes names are converted to table names prefixed with the module name and an underscore. i.e. the ObjectType foo in module bar will be stored in a table named bar_foo.

Column names are identical to field names. All identifiers are quoted with double quotes so that they are case-sensitive and won't clash with any reserved names.

Like, table names, enum types are prefixed with the module name and an underscore.

Schema Type Mapping

The mapping of cosmossdk.io/schema Kinds to PostgreSQL types is as follows:

Kind	PostgreSQL Type	Notes
StringKind	TEXT
BoolKind	BOOLEAN
BytesKind	BYTEA
Int8Kind	SMALLINT
Int16Kind	SMALLINT
Int32Kind	INTEGER
Int64Kind	BIGINT
Uint8Kind	SMALLINT
Uint16Kind	INTEGER
Uint32Kind	BIGINT
Uint64Kind	NUMERIC
Float32Kind	REAL
Float64Kind	DOUBLE PRECISION
IntegerStringKind	NUMERIC
DecimalStringKind	NUMERIC
JSONKind	JSONB
Bech32AddressKind	TEXT	addresses are converted to strings with the specified address prefix
TimeKind	BIGINT and TIMESTAMPTZ	time types are stored as two columns, one with the _nanos suffix with full nanoseconds precision, and another as a TIMESTAMPTZ generated column with microsecond precision
DurationKind	BIGINT	durations are stored as a single column in nanoseconds
EnumKind	<module_name>_<enum_name>	a custom enum type is created for each module prefixed with the module name it pertains to

Documentation

Overview

Example (CreateEnumTypeSql)

err := createEnumTypeSql(os.Stdout, "test", testdata.MyEnum)
if err != nil {
	panic(err)
}

Output:

CREATE TYPE "test_my_enum" AS ENUM ('a', 'b', 'c');

Example (ObjectIndexer_createTableSql_allKinds)

exampleCreateTable(testdata.AllKindsObject)

Output:

CREATE TABLE IF NOT EXISTS "test_all_kinds" (
	"id" BIGINT NOT NULL,
	"ts" TIMESTAMPTZ GENERATED ALWAYS AS (nanos_to_timestamptz("ts_nanos")) STORED,
	"ts_nanos" BIGINT NOT NULL,
	"string" TEXT NOT NULL,
	"bytes" BYTEA NOT NULL,
	"int8" SMALLINT NOT NULL,
	"uint8" SMALLINT NOT NULL,
	"int16" SMALLINT NOT NULL,
	"uint16" INTEGER NOT NULL,
	"int32" INTEGER NOT NULL,
	"uint32" BIGINT NOT NULL,
	"int64" BIGINT NOT NULL,
	"uint64" NUMERIC NOT NULL,
	"integer" NUMERIC NOT NULL,
	"decimal" NUMERIC NOT NULL,
	"bool" BOOLEAN NOT NULL,
	"time" TIMESTAMPTZ GENERATED ALWAYS AS (nanos_to_timestamptz("time_nanos")) STORED,
	"time_nanos" BIGINT NOT NULL,
	"duration" BIGINT NOT NULL,
	"float32" REAL NOT NULL,
	"float64" DOUBLE PRECISION NOT NULL,
	"address" TEXT NOT NULL,
	"enum" "test_my_enum" NOT NULL,
	"json" JSONB NOT NULL,
	PRIMARY KEY ("id", "ts_nanos")
);
GRANT SELECT ON TABLE "test_all_kinds" TO PUBLIC;

Example (ObjectIndexer_createTableSql_singleton)

exampleCreateTable(testdata.SingletonObject)

Output:

CREATE TABLE IF NOT EXISTS "test_singleton" (
	_id INTEGER NOT NULL CHECK (_id = 1),
	"foo" TEXT NOT NULL,
	"bar" INTEGER NULL,
	"an_enum" "test_my_enum" NOT NULL,
	PRIMARY KEY (_id)
);
GRANT SELECT ON TABLE "test_singleton" TO PUBLIC;

Example (ObjectIndexer_createTableSql_vote)

exampleCreateTable(testdata.VoteObject)

Output:

CREATE TABLE IF NOT EXISTS "test_vote" (
	"proposal" BIGINT NOT NULL,
	"address" TEXT NOT NULL,
	"vote" "test_vote_type" NOT NULL,
	_deleted BOOLEAN NOT NULL DEFAULT FALSE,
	PRIMARY KEY ("proposal", "address")
);
GRANT SELECT ON TABLE "test_vote" TO PUBLIC;

Example (ObjectIndexer_createTableSql_vote_no_retain_delete)

exampleCreateTableOpt(testdata.VoteObject, true)

Output:

CREATE TABLE IF NOT EXISTS "test_vote" (
	"proposal" BIGINT NOT NULL,
	"address" TEXT NOT NULL,
	"vote" "test_vote_type" NOT NULL,
	PRIMARY KEY ("proposal", "address")
);
GRANT SELECT ON TABLE "test_vote" TO PUBLIC;

Index

• type Config

Examples

• Package (CreateEnumTypeSql)
• Package (ObjectIndexer_createTableSql_allKinds)
• Package (ObjectIndexer_createTableSql_singleton)
• Package (ObjectIndexer_createTableSql_vote)
• Package (ObjectIndexer_createTableSql_vote_no_retain_delete)

Types

type Config

type Config struct {
	// DatabaseURL is the PostgreSQL connection URL to use to connect to the database.
	DatabaseURL string `json:"database_url"`

	// DatabaseDriver is the PostgreSQL database/sql driver to use. This defaults to "pgx".
	DatabaseDriver string `json:"database_driver"`

	// DisableRetainDeletions disables the retain deletions functionality even if it is set in an object type schema.
	DisableRetainDeletions bool `json:"disable_retain_deletions"`
}