PgRoutiner High-Level Concept

PgRoutiner is a command-line set of tools that work seamlessly with your NET project configuration in JSON files.

.NET projects usually have some configuration files, for example, appsettings.json or appsettings.Development.json or both. And usually, those files will contain a workable connection string. For example, they might look something like this:

{
  "ConnectionStrings": {
    "MyConnection": "Server=localhost;Db=dvdrental;Port=5432;User Id=postgres;Password=postgres;"
  },
  // the rest of your configuration
}

{
  "ConnectionStrings": {
    "MyConnection": "Server=localhost;Db=dvdrental;Port=5432;User Id=postgres;Password=postgres;"
  },
  // the rest of your configuration
}

There is not one, single reason to enter the connection parameters again, when you have them, right there. All you have to do now is type the pgroutiner command:

Now, what is PgRoutiner telling us in that screenshot above is that it has found a viable connection string in the current configuration - but it didn't find any PgRoutiner configuration section. And since we didn't issue any command-line command - it offered us to create a default PgRoutiner configuration file.

This question wouldn't happen if we issued any viable command - or - if the PgRoutiner configuration already exists (in any of the configuration files):

{
  "ConnectionStrings": {
    "MyConnection": "Server=localhost;Db=dvdrental;Port=5432;User Id=postgres;Password=postgres;"
  },
  "PgRoutiner": {
    // PgRoutiner configuration goes here
  }
}

{
  "ConnectionStrings": {
    "MyConnection": "Server=localhost;Db=dvdrental;Port=5432;User Id=postgres;Password=postgres;"
  },
  "PgRoutiner": {
    // PgRoutiner configuration goes here
  }
}

The default PgRoutiner configuration file is interesting because it shows all available commands and options, together with helpful command line shortcuts in comment headers. You can see the latest default configuration file here: PgRoutiner Default Configuration

Now, of course, if the configuration with the connection string isn't found, the user will be prompted to enter the database parameters:

To see more details on connection and configuration management, see the Connection Management Section and Configuration Management Section in the readme file.

Basic Concept

The basic concept is to use command prompt switches to override anything defined in configuration settings.

For example, the JSON configuration defines a value "SkipUpdateReferences": false. We can use any of these command-line switches - to set this value to true:

• pgroutiner --SkipUpdateReferences - switch starts with --, the name is the same as configuration name.
• pgroutiner --skipupdatereferences - switch starts with --, the name is the same as configuration name, case insensitive.
• pgroutiner --skip-update-references - switch starts with --, the name is the same as configuration name, kebab case supported.

Since this is a boolean switch, it is not necessary to set the value explicitly. Value is automatically set to true since the switch is included.

However, some switches require a value as the following argument:

Many important switches also have a shorthand. For example, the switch --list that lists available objects in the database, has two shorthand aliases -ls or just -l:

Of course, you don't need to even have a valid configuration to work with PgRoutiner. The connection information can be supplied through either:

• Command line switches --connection, -conn or -c to set either the connection string or the connection name from the configuration.
• Interactive command line input as shown on the screenshot above.

To repeat:

• For more information on working with connection, see the Connection Management in the readme file.

• For more information on working with configuration files, see the Configuration Management in the readme file.

• To see all available commands and options, see the latest Default Configuration.

Features

Features fall into three major categories:

• Database Management.
• Code Generation
• Database Documentation

Database Management

• List and search database objects.
• Search database object definitions.
• View database object definitions.
• Run psql tool or psql commands
• Execute scripts from files or entire directories (via psql tool)
• Generate INSERT script for a table, multiple tables or for the custom SQL query.
• Backup and restore helpers.
• Create schema files.
• Create data files for all tables or selected tables.
• Create individual files for each database object.

Code Generation

• Create C# database connection extension data-access methods for any PostgreSQL functions or stored procedures.
• Create database unit test projects and unit tests for every generated C# data-access extension method.
• Create C# or TypeScript models from tables or queries.
• Create PostgreSQL CRUD functions for tables, that can include:
  • Create functions.
  • Create returning functions.
  • Create and on conflict do nothing functions.
  • Create and on conflict update functions.
  • Create returning and on conflict do nothing functions.
  • Create returning and on conflict update functions.
  • Read by keys functions.
  • Read by all functions.
  • Read by page functions.
  • Update functions.
  • Update returning functions.
  • Delete by keys functions.
  • Delete by keys returning functions.

Database Documentation

Database documentation, or database dictionary, means the automatic creation of a nicely formatted markdown readme file that can be stored together with the source code. This automatically created document contains:

• Tables with every column, together with column properties.
• Comment on every table and every column.
• Hashtag links.
• Navigation links between relations.

For example:

Table public.company_reviews

Company reviews made by people.

Column		Type	Nullable	Default	Comment
#id	PK	uuid	NO	gen_random_uuid()
#company_id	FK ➝ companies.id, IDX	uuid	NO		company reviewed
#person_id	FK ➝ people.id	uuid	YES		person reviewer
#review	CHECK (review IS NOT NULL OR score IS NOT NULL)	character varying	YES		written review by a person
#score	CHECK (score IS NULL OR score > 0 AND score <= 5), CHECK (review IS NOT NULL OR score IS NOT NULL)	smallint	YES		score 1-5
#created_at		timestamp with time zone	NO	now()
#modified_at		timestamp with time zone	NO	now()
#created_by	FK ➝ users.id	uuid	NO	'00000000-0000-0000-0000-000000000000'::uuid

Additionally, generated markdown may include:

• All other possible database objects, together with comments (functions, procedures, enums, custom types, etc).
• Table and index statistics.
• Links to other generated source code files in the repository such as object scripts and data-access C# code.

You can see the working example here in this demo repository: PDD.Database

PostgreSQL First Development

The concept and methodology promoted with the PgRoutiner are the Database First Development, or sometimes called PostgreSQL First Development.

What does it mean? A couple of things...

1) Use Any Tool At Your Disposal

You can use any tool at your disposal to develop a project on your local or development PostgreSQL instance. Such as:

• psql
• pgAdmin
• DBeaver
• JetBrains DataGrip
• Microsoft Data Studio
• etc, hundreds or thousands of others, any will do, it doesn't matter...

2) Configure PgRoutiner

Configure PgRoutiner in your project to generate data-access code for PostgreSQL functions and procedures. Depending on your settings, the generated code typically looks like this (simplified):

// pgroutiner auto-generated code
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;
using dvdrental.Models;

namespace dvdrental.Extensions;

public static class PgRoutineRewardsReport
{
    public const string Name = "public.rewards_report";
    public const string Query = $"select customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active from {Name}($1, $2)";

    /// <summary>
    /// Executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IEnumerable of Customer instances</returns>
    public static IEnumerable<Customer> RewardsReport(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }
}

// pgroutiner auto-generated code
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;
using dvdrental.Models;

namespace dvdrental.Extensions;

public static class PgRoutineRewardsReport
{
    public const string Name = "public.rewards_report";
    public const string Query = $"select customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active from {Name}($1, $2)";

    /// <summary>
    /// Executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IEnumerable of Customer instances</returns>
    public static IEnumerable<Customer> RewardsReport(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }
}

3) Static Type Checking

Enable static type checking for your database in your project by running PgRoutiner on every pre-build event. For example in your project file:

<ItemDefinitionGroup>
  <PreBuildEvent>
    <Command>pgroutiner</Command>
    <Message>Running code generation with ogroutiner...</Message>
  </PreBuildEvent>
</ItemDefinitionGroup>

<PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
  <PreBuildEventUseInBuild>true</PreBuildEventUseInBuild>
</PropertyGroup>

<ItemDefinitionGroup>
  <PreBuildEvent>
    <Command>pgroutiner</Command>
    <Message>Running code generation with ogroutiner...</Message>
  </PreBuildEvent>
</ItemDefinitionGroup>

<PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
  <PreBuildEventUseInBuild>true</PreBuildEventUseInBuild>
</PropertyGroup>

In case something has changed in your database, either in a table or a function (names, types, etc) - the build will fail.

And then, you can enjoy benefits of the static type-checking in your application for your PostgreSQL database.