protoc-gen-psql (PGP)

PGP is a protoc plugin to generate postgresql statements from protobuf files.

This project uses protoc-gen-star to ease code generation.

How to use it

See ./tests/asset.proto for example on how to use it.

Options

The plugin support the following options.

At file level

initialization: these strings will be gathered in a file called 00_init_<YOUR_PROTO_FILE>.pb.psql.
finalization: these strings will be gathered in a file called 99_final_<YOUR_PROTO_FILE>.pb.psql.

At message level

The tableType option defines what "type" of table will be contained in the message. Two values are valid:

DATA: This value indicates the other psql options set in the message are describing a SQL "data" table. If this option is present, every other valid psql options in the message will be gathered in a file called 10_tables_<YOUR_PROTO_FILE>.pb.psql.
RELATION: This value indicates the other psql options set in the message are describing a "relation" table. This kind of message should probably contains constraints. If this option is present, every other valid psql options in the message will be gathered in a file called 20_relations_<YOUR_PROTO_FILE>.pb.psql.

Other remaining options defines what statements will be contained in the table definition:

prefix: these strings will be set at the beginning of the creation table definition.
suffix: these strings will be set at the end of the creation table definition.
constraint: this option enable defining constraints on the table. This option will handle error if the constraint already exist in the schema.
disabled: this boolean indicates the message should be ignored. If other options are used in this message, they will be ignored.
relay_cascade_update: create a trigger after INSERT, DELETE and UPDATE operations on the source_foreign_key parent table to update the given field on destination foreign key parent tables:
- source_foreign_key: source foreign key
- destinations: List of destinations to update:
  - foreign_key: destination foreign key
  - field: field to update on parent table
  - value: value to set. The given value can be a function (ex: now())

At field level

column: let describe how the field should be represented in the schema.
auto_fill_on_update: create a trigger to update this field with the given value each time the row is updated. The given value can be a function (ex: now()).
cascade_update_on_related_table: create a trigger to update other fields in another table each time the row is updated. This option must be set on the foreign key of the table to update and can be set multiple times to update various fields. This option has 2 parameters to set:
- field: field to update in the parent table (the one referenced by the foreign key)
- value: value to set. The given value can be a function (ex: now()).

PSQL files naming convention

Generated files are prefixes with a number to enable executing the psql statements in the right order. Code consuming these files to create/modify a schema should read them following the numbering.

Use ALTER or not

Two modes are available and may be chosen by adding alter as a parameter in protoc command line like --psql_out="alter:."

(default) alter = false : The psql code is generated considering the initial schema is empty (no table, etc.). This mode is great for readability and to generate code for a new database.
alter = true : The psql code is generated to be applied on an existing database. ALTER statements are heavily used.

Tests

Do a make test-generate to view a code generated example.

To test the generate psql code, do a make test.

The directory tests contains several proto coverage multiple use cases.

Intrinsec/protoc-gen-psql