@freemework/sql.postgres

Freemework's PostgreSQL Facade

Version table

CREATE TABLE "__dbVersion" (
	"version" VARCHAR(64) NOT NULL PRIMARY KEY,
	"utc_deployed_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT (now() AT TIME ZONE 'utc'),
	"log" TEXT NOT NULL
)

NOTE: Name of table may be overridden via migration's opts

Connection URL

Format

postgres://[${user}[:${password}]]@${host}[:${port}]/${databaseName}[?app=${applicationName}&schema=${defaultSchema}]
postgres+ssl://[${user}[:${password}]]@${host}[:${port}]/${databaseName}[?app=${applicationName}&schema=${defaultSchema}]

TBD: Example URL for unix socket

Examples

Localhost

postgres://localhost:5432/postgres

Remote PostgreSQL server my_pg_host with SSL prefer mode (no certificate validation, just for encryption)

postgres+ssl://my_pg_host:5432/postgres

Note: For full SSL mode you need to pass opts.ssl programmatically. Passing certificates via URL does not supported.

Development Notes

1. Make sure your have PostgreSQL instance or launch it as Docker container

   docker run --name postgres --rm --interactive --publish 5432:5432 theanurin/devel.postgres-13

2. Launch tests via VSCode's launch configuration or via command line

   TEST_DB_URL="postgres://devadmin@localhost:5432/devdb" npm run test

3. Look inside DB to see SQL objects generated by tests. For the purpose you may use VSCode's plugins provided by Matheus Teixeira: SQLTools and SQLTools PostgreSQL/Cockroach Driver