PgRoutiner

PgRoutiner - Database-First For .NET and PostgreSQL

PgRoutiner is a set of command-line tools for PostgreSQL databases and PostgreSQL .NET projects.

Using your .NET configuration project connection string (or custom-defined connection) - you can:

  1. Download native executable files (not dependent on any framework) for the latest version from the releases page.
  2. Set the appropriate path to the downloaded executable file.
  3. Type pgroutiner --info
  4. Note: working with native executables is many times faster, they very short startup time and they offer many times better user experience.

Table of Contents:

Installation

Download Binaries

This is the fastest and easiest way to get started with the PgRoutiner tool. The releases page contains downloadable executables that are not dependent on anything. No framework or docker is required, just plain old native executable.

This is actually, the preferable way of using the PgRoutiner tool. Native executables are very much optimized and have very short startup time and they offer many times better user experience.

Here are the steps:

  1. Download native executable files (not dependent on any framework) for the latest version from the releases page.
  2. Set the appropriate path to the downloaded executable file.
  3. Type pgroutiner --info to see f it works.

That is it.

.NET Tool

Requirements

Global .NET tool

To install a global tool (recommended):

$ dotnet tool install --global dotnet-pgroutiner
Tool 'dotnet-pgroutiner' (version '5.4.0') was successfully installed.

To update a global tool:

$ dotnet tool update --global dotnet-pgroutiner
Tool 'dotnet-pgroutiner' was successfully updated from version '5.0.7' to version '5.0.8'.

This will enable a global command line tool pgroutiner. Try typing pgroutiner --help.

Local .NET tool

To add a local tool to your project only you need to create a manifest file and add a tool without --global switch as described in this tutorial.

TL-DR:

1) Add .config directory to your project or solution.

2) Add dotnet-tools.json file to this directory with the following content:

{
  "version": 1,
  "isRoot": true,
  "tools": {
    "dotnet-pgroutiner": {
      "version": "5.0.6",
      "commands": [
        "pgroutiner"
      ]
    }
  }
}

Quick Start

1) Install pgroutiner (see the instructions above) 2) Open the terminal in your .NET project configured to use the PostgreSQL database. 3) Type pgroutiner --help to see available commands and switches (the list is long). 4) Type pgroutiner to define a new connection if you don’t have one - and to create the default configuration file for this dir. See more on Connection management 5) Type pgroutiner --info to see if can you connect to the database and to see other environment info. 6) Type pgroutiner --list to see list of all objects. 7) Type pgroutiner --ddl [object_name] to see the data definition language for the object from the second parameter. 8) Type pgroutiner --search [search expression] to search data definitions with search expression.

Connection Management

appsettings.json

{
  "ConnectionStrings": {
    "DvdRental": "Server=localhost;Db=dvdrental;Port=5432;User Id=postgres;Password=postgres;"
  }
}

~$ pgroutiner --info

Version:
 5.0.3.0

Executable dir:
 /home/vbilopav/.dotnet/tools

OS:
 Unix 5.10.102.1

Using configuration files:
 appsettings.Development.json
 appsettings.json

Using dir:
 /home/vbilopav/dev/dvdrental

Using settings:
 --info

Using connection DvdRental:
 Host=localhost;Database=dvdrental;Port=5432;Username=postgres  (PostgreSQL 13.8)

Using project file:
 dvdrental.csproj

pg_dump:
 pg_dump

pg_restore:
 /usr/lib/postgresql/13/bin/pg_restore

~$ pgroutiner

Connection server [localhost]:
Connection port [5432]: 
Connection database [postgres]: 
Connection user [postgres]:
Connection password [environment var.]:

~$ pgroutiner -c ConnectionString2 --info

~$ pgroutiner --connection ConnectionString2 --info

Connection server [localhost]:
Connection port [5432]:
Connection database [postgres]:
Connection user [postgres]:
Connection password:

~$ pgroutiner --connection "Server=localhost;Db=test;Port=5432;User Id=postgres;Password=postgres;" --info

{
  "ConnectionStrings": {
    "TestConnection": "postgresql://postgres:postgres@localhost:5432/test"
  }
}

~$ pgroutiner --connection "postgresql://postgres:postgres@localhost:5432/test" --info

Configuration Management

{
  "ConnectionStrings": {
    "TestConnection": "postgresql://postgres:postgres@localhost:5432/test"
  },
  "PgRoutiner": {
    // pgroutiner settings
  }
}

You don't seem to be using any available command-line commands and PgRoutiner configuration seems to be missing.
Would you like to create a custom settings file "appsettings.PgRoutiner.json" with your current values?
This settings configuration file can be used to change settings for this directory without using a command-line.
Create "appsettings.PgRoutiner.json" in this dir [Y/N]?

Working With Database

List database objects

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --list
SCHEMA public
EXTENSION plpgsql
TYPE mpaa_rating
DOMAIN public.year
TABLE public.actor
VIEW public.actor_info
VIEW public.customer_list
...

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner -l
SCHEMA public
EXTENSION plpgsql
TYPE mpaa_rating
DOMAIN public.year
TABLE public.actor
VIEW public.actor_info
VIEW public.customer_list
...

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner -ls
SCHEMA public
EXTENSION plpgsql
TYPE mpaa_rating
DOMAIN public.year
TABLE public.actor
VIEW public.actor_info
VIEW public.customer_list
...

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --list film
VIEW public.film_list
VIEW public.nicer_but_slower_film_list
VIEW public.sales_by_film_category
TABLE public.film_actor
TABLE public.film_category
TABLE public.film
SEQ public.film_film_id_seq
FUNCTION public.film_in_stock(integer, integer)
FUNCTION public.film_not_in_stock(integer, integer)
vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --list actor
TABLE public.actor
VIEW public.actor_info
TABLE public.film_actor
SEQ public.actor_actor_id_seq
vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

View object definitions

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner -ddl actor
--
-- Table: public.actor
--
CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL PRIMARY KEY,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

ALTER TABLE public.actor OWNER TO postgres;
CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name);
CREATE TRIGGER last_updated BEFORE UPDATE ON public.actor FOR EACH ROW EXECUTE FUNCTION public.last_updated();


vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner -ddl film
--
-- Table: public.film
--
CREATE TABLE public.film (
    film_id integer DEFAULT nextval('public.film_film_id_seq'::regclass) NOT NULL PRIMARY KEY,
    title character varying(255) NOT NULL,
    description text,
    release_year public.year,
    language_id smallint NOT NULL,
    rental_duration smallint DEFAULT 3 NOT NULL,
    rental_rate numeric(4,2) DEFAULT 4.99 NOT NULL,
    length smallint,
    replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL,
    rating public.mpaa_rating DEFAULT 'G'::public.mpaa_rating,
    last_update timestamp without time zone DEFAULT now() NOT NULL,
    special_features text[],
    fulltext tsvector NOT NULL,
    FOREIGN KEY (language_id) REFERENCES public.language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

ALTER TABLE public.film OWNER TO postgres;
CREATE INDEX film_fulltext_idx ON public.film USING gist (fulltext);
CREATE INDEX idx_fk_language_id ON public.film USING btree (language_id);
CREATE INDEX idx_title ON public.film USING btree (title);
CREATE TRIGGER film_fulltext_trigger BEFORE INSERT OR UPDATE ON public.film FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description');
CREATE TRIGGER last_updated BEFORE UPDATE ON public.film FOR EACH ROW EXECUTE FUNCTION public.last_updated();


vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --ddl film_in_stock
--
-- Function: public.film_in_stock
--
CREATE FUNCTION public.film_in_stock(
    p_film_id integer,
    p_store_id integer,
    OUT p_film_count integer
)
RETURNS SETOF integer
LANGUAGE sql
AS $_$
     SELECT inventory_id
     FROM inventory
     WHERE film_id = $1
     AND store_id = $2
     AND inventory_in_stock(inventory_id);
$_$;

ALTER FUNCTION public.film_in_stock(p_film_id integer, p_store_id integer, OUT p_film_count integer) OWNER TO postgres;


vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --ddl "actor;film_in_stock"
--
-- Table: public.actor
--
CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL PRIMARY KEY,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

ALTER TABLE public.actor OWNER TO postgres;
CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name);
CREATE TRIGGER last_updated BEFORE UPDATE ON public.actor FOR EACH ROW EXECUTE FUNCTION public.last_updated();


--
-- Function: public.film_in_stock
--
CREATE FUNCTION public.film_in_stock(
    p_film_id integer,
    p_store_id integer,
    OUT p_film_count integer
)
RETURNS SETOF integer
LANGUAGE sql
AS $_$
     SELECT inventory_id
     FROM inventory
     WHERE film_id = $1
     AND store_id = $2
     AND inventory_in_stock(inventory_id);
$_$;

ALTER FUNCTION public.film_in_stock(p_film_id integer, p_store_id integer, OUT p_film_count integer) OWNER TO postgres;


vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

Search definitions by search expression

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --search "select public.group_concat"
--
-- View: public.actor_info
--
CREATE VIEW public.actor_info AS
 SELECT a.actor_id,
    a.first_name,
    a.last_name,
    public.group_concat(DISTINCT (((c.name)::text || ': '::text) || ( SELECT public.group_concat((f.title)::text) AS group_concat
           FROM ((public.film f
             JOIN public.film_category fc_1 ON ((f.film_id = fc_1.film_id)))
             JOIN public.film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
          WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
          GROUP BY fa_1.actor_id))) AS film_info
   FROM (((public.actor a
     LEFT JOIN public.film_actor fa ON ((a.actor_id = fa.actor_id)))
     LEFT JOIN public.film_category fc ON ((fa.film_id = fc.film_id)))
     LEFT JOIN public.category c ON ((fc.category_id = c.category_id)))
  GROUP BY a.actor_id, a.first_name, a.last_name;

ALTER TABLE public.actor_info OWNER TO postgres;


vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$

vbilopav@DESKTOP-O3A6QK2:~/dev/dvdrental$ pgroutiner --search smallint
--
-- Table: public.store
--
CREATE TABLE public.store (
    store_id integer DEFAULT nextval('public.store_store_id_seq'::regclass) NOT NULL PRIMARY KEY,
    manager_staff_id smallint NOT NULL,
    address_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL,
    FOREIGN KEY (address_id) REFERENCES public.address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (manager_staff_id) REFERENCES public.staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

ALTER TABLE public.store OWNER TO postgres;
CREATE UNIQUE INDEX idx_unq_manager_staff_id ON public.store USING btree (manager_staff_id);
CREATE TRIGGER last_updated BEFORE UPDATE ON public.store FOR EACH ROW EXECUTE FUNCTION public.last_updated();


--
-- Table: public.address
--
CREATE TABLE public.address (
    address_id integer DEFAULT nextval('public.address_address_id_seq'::regclass) NOT NULL PRIMARY KEY,
    address character varying(50) NOT NULL,
    address2 character varying(50),
    district character varying(20) NOT NULL,
    city_id smallint NOT NULL,
    postal_code character varying(10),
    phone character varying(20) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL,
    CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city(city_id)
);

...

Dump data from tables or queries as inserts

Backup and restore

Open PSQL tool

Execute PSQL commands and scripts

Generating Scripts

Generating Documentation

Generating Code

Troubleshooting

Depending on the command, this tool will start external processes with PostgreSQL client tools like psql, pg_dump, or pg_restore.

That means, that PostgreSQL client tools must be installed on the system. PostgreSQL client tools will be installed by default with every PostgreSQL installation.

If you don’t want a server, but only client tools:

When pgroutiner calls an external tool, it will first try to call the default alias psql or pg_dump. Then, if the version of the tool doesn’t match the version from the connection it will try to locate the executable on the default location:

Those paths are PostgreSQL installs binaries by default.

When PgRoutiner sees the version mismatch it will prompt a warning and fallback to that path with an appropriate version number.

This behavior can be avoided by settings PgDumpFallback and PsqlFallback settings values respectively.

Support

This is open-source software developed and maintained freely without any compensation whatsoever.

License

Copyright (c) Vedran Bilopavlović - VB Consulting and VB Software 2020 This source code is licensed under the MIT license.

This site is open source. Improve this page.