v1.2.0 Summary Chapter 6: Advanced tools 6. Introduction 6.13. Command line interface
You're browsing the documentation of a version that is still under development, the features described here may change or even be removed.

6.13. Command line interface

The pgModeler command-line interface, or simply pgmodeler-cli, encapsulates the most important pgModeler operations like export, import, diff, and other auxiliary features. The idea behind the CLI is to enable the user to perform a set of operations over a model or database without the need to do the tasks in the graphical interface. This approach can be handy when dealing with software deployment, for instance.

To exemplify a software deployment using the CLI, let's say you need to export a model to a database server during the installation of Java-based software through the Apache Ant tool. You can easily integrate the pgmodeler-cli command to execute the export process taking a database model file as an input parameter in the middle of the deployment process like this:pgmodeler-cli -ed -if demo.dbm -ca local-db.

The command-line interface has dozens of parameters that can be combined to optimize operations to be executed. One important thing to observe is that the CLI accepts short and long parameters and they can appear in any order. The options must be separated from their values (when accepted) by a blank space. All options are described below:

pgModeler command line interface.
Version 1.2.0-beta - 20241029.7fe6f444e Qt 6.8.0
PostgreSQL Database Modeler Project - pgmodeler.io
Copyright 2006-2024 Raphael Araújo e Silva 

Usage: pgmodeler-cli [OPTIONS]
This CLI tool provides several operations over models and databases without the need to perform them
 in the pgModeler's graphical interface. All available options are described below.

Operation mode options: 
  -ef, --export-to-file             Exports the input model to SQL script file(s).
  -ep, --export-to-png              Exports the input model to a PNG image.
  -es, --export-to-svg              Exports the input model to a SVG file.
  -ec, --export-to-dict             Exports the input model to a data dictionary in HTML format.
  -ed, --export-to-dbms             Exports the input model directly to a PostgreSQL server.
  -lc, --list-conns                 Lists the available connections in file connections.conf.
  -im, --import-db                  Import a database to an output file.
  -df, --diff                       Compares a model and a database or two databases generating the SQL script to sync the latter in relation to the first.
  -fm, --fix-model                  Tries to fix the structure of the input model file to make it loadable again.
  -cc, --create-configs             Creates the pgModeler's configuration folder and files in the user's local storage.
  -mt, --dbm-mime-type [ACTION]     Handles the DBM file association to pgModeler binaries. The ACTION can be [install | uninstall].
  -h, --help                        Shows this help menu.

General options: 
  -if, --input [FILE]               Input model file (.dbm). This is mandatory for export and model fix operations.
  -id, --input-db [DBNAME]          Input database name. This is mandatory for import operation.
  -of, --output [FILE|DIRECTORY]    Output file or directory. This is mandatory for fixing models or exporting to SQL, HTML, PNG, or SVG.
  -v, --pgsql-ver                   Force the PostgreSQL syntax to the specified version when generating SQL code. The version string must be in the form of [major].[minor], e.g., 17.0.
  -s, --silent                      Silent execution. Only critical messages and errors are shown during the process.

SQL file export options: 
  -sp, --split                      The database model SQL code is split into several files, one per object.
  -ds, --dependencies               Includes the object's dependencies SQL code in the generated file. (Only for split mode)
  -cs, --children                   Includes the object's children SQL code in the generated file. (Only for split mode)
  -gt, --group-by-type              Instead of creating a separate SQL file per object, groups the SQL code of all objects of the same type in a single file. (Only for split mode)
  -gd, --gen-drop-script            Create a separate script containing the DROP commands to destroy objects in the database.

PNG and SVG export options: 
  -sg, --show-grid                  Draws the grid in the exported image.
  -sl, --show-delimiters            Draws the page delimiters in the exported image.
  -pp, --page-by-page               Each page will be exported in a separated image. (Only for PNG images)
  -oc, --override-bg-color          Don't use the original canvas color in the exported image, instead, a white background is used. (Only for PNG images)
  -zf, --zoom [FACTOR]              Applies a zoom (in percent) before export to an image. Accepted zoom interval: 10-500 (Only for PNG images)

Data dictionary export options: 
  -sp, --split                      The data dictionaries are generated in separated files inside the specified output directory.
  -ni, --no-index                   Avoids the generation of the index that is used to help navigate through the data dictionary.
  -md, --markdown                   Generates a data dictionary in Markdown format (.md) instead of the HTML format.

DBMS export options: 
  -ir, --ignore-duplicates          Ignores errors related to duplicate objects that eventually exist in the server.
  -ic, --ignore-error-codes [CODES] Ignores additional errors by their codes. A comma-separated list of alphanumeric codes should be provided.
  -dd, --drop-database              Drop the database before executing an export process.
  -ff, --force                      Forces the termination of all connections to the target database before dropping it. This option is ignored when exporting to PostgreSQL 12 or below.
  -do, --drop-objects               Runs the DROP commands attached to objects in which SQL code is enabled.
  -sm, --simulate                   Simulates an export process by executing all steps but undoing any modification in the end.
  -tn, --use-tmp-names              Generates temporary names for database, roles, and tablespaces when in simulation mode.
  -nt, --non-transactional          Run the export process in a non-transactional mode where changes are not rolled back in case of errors.

Connection options: 
  -ca, --conn-alias [ALIAS]         Connection configuration alias to be used.
  -H, --host [HOST]                 PostgreSQL host in which a task will operate.
  -p, --port [PORT]                 PostgreSQL host listening port.
  -u, --user [USER]                 PostgreSQL username.
  -w, --passwd [PASSWORD]           PostgreSQL user password.
  -D, --initial-db [DBNAME]         Connection's initial database.

Database import options: 
  -ie, --ignore-errors              Ignores all errors and tries to create as many as possible objects.
  -is, --import-sys-objs            Imports built-in system objects. This option causes the model to bloat due to the importing of unneeded objects.
  -ix, --import-ext-objs            Imports extension objects. This option causes the model to bloat due to the importing of unneeded objects.
  -cl, --comments-as-aliases        Use objects comments as aliases. This option is takes effect on objects graphically represented in the database model.
  -fo, --filter-objects [FILTER]    Makes the import process retrieve only those objects matching the filter(s). The FILTER must be in the form type:pattern:mode.
  -om, --only-matching              Makes only objects matching the provided filter(s) to be imported. Those not matching filter(s) are discarded.
  -mn, --match-by-name              Makes the objects matching to be performed over their names instead of their signature ([schema].[name]).
  -fc, --force-children [OBJECTS]   Forces the importing of children objects related to tables/views/foreign tables matched by the filter(s). The OBJECTS is a comma-separated list types.
  -d, --debug-mode                  Runs the import in debug mode printing all queries executed on the server.

Diff options: 
  -ct, --compare-to [DBNAME]        The database used in the comparison. All the SQL code generated is applied to it.
  -pd, --partial                    Switches to the partial diff operation. A set of object filters should be provided using the import option --filter-objects.
  -ff, --force                      Forces a full diff if the provided filters were not able to retrieve objects for a partial diff operation.
  -st, --start-date                 Matches all database model objects in which the modification date starts on the specified date. (Only for partial diff)
  -et, --end-date                   Matches all database model objects in which the modification date ends on the specified date. (Only for partial diff)
  -sd, --save                       Saves the generated diff code to the output file.
  -ad, --apply                      Applies the generated diff code on the database server.
  -np, --no-preview                 Don't preview the generated diff code before applying it to the server.
  -dc, --drop-cluster-objs          Drop cluster-level objects like roles and tablespaces.
  -rv, --revoke-perms               Revokes permissions already set on the database. New permissions configured in the input model are still applied.
  -dm, --drop-missing               Drops missing objects. Generates DROP commands for objects that are present in the input model but not in the compared database.
  -fd, --force-drop-cols            Forces the drop of missing columns and constraints. Causes only columns and constraints to be dropped, other missing objects aren't removed.
  -rn, --rename-db                  Renames the destination database when the names of the involved databases are different.
  -nd, --no-cascade                 Don't drop objects in cascade mode.
  -ns, --no-sequence-reuse          Don't reuse sequences on serial columns. Drop the old sequence assigned to a serial column and creates a new one.
  -ru, --recreate-unmod             Recreates the unmodifiable objects. These objects are the ones that can't be changed via the ALTER command.
  -rm, --replace-mod                Replaces modifiable objects. These objects are the ones that supports CREATE OR REPLACE command.

Model fix options: 
  -ft, --fix-tries [NUMBER]         Model fix tries. When reaching the maximum count, the invalid objects will be discarded.

File association options: 
  -sw, --system-wide                The file association to DBM files will be applied on a system-wide level instead of to the current user only.
  -ff, --force                      Forces the mime type installation or uninstallation. 

Config files creation options: 
  -mo, --missing-only               Copies only missing configuration files to the user's local storage.
  -ff, --force                      Forces the recreation of all configuration files. This option implies the backup of the current settings.

Plugins options: 
  -lp, --list-plugins               List the available plugins.
  -ip, --ignore-faulty              Ignore the errors of plugins that failed to be loaded.

SDBM export plug-in options: 
  -xs, --export-sdbm                Runs the export operation over a split database model (.sdbm)

SDBM fix plug-in options: 
  -fs, --fix-sdbm                   Runs the database model fix operation over a split model file (.sdbm)


** When exporting to SQL file in split mode and none of the options --dependencies, --children, and --group-by-type
   are specified then the generated files will be named in such a way to reflect the correct creation order.

** The FILTER value in the --filter-objects option has the form type:pattern:mode. 
   * The section `type' is the type of object to be filtered and accepts the following values (invalid types ignored): 
     > any, schema, table, view, aggregate, cast, collation, constraint
     > conversion, domain, eventtrigger, extension, foreigndatawrapper, foreignserver, foreigntable, function
     > index, language, opclass, operator, opfamily, policy, procedure, role
     > rule, sequence, tablespace, transform, trigger, usermapping

   * The special type `any' allows writing a single filter that applies to all object types.

   * The section `pattern' is the text pattern that is matched against the objects' names.

   * The section `mode' is the way the pattern is matched. This one accepts two values: 
     > `wildcard' causes the pattern to be used as a wildcard string while matching objects' names.
     > `regexp' causes the pattern to be treated as a Perl-like regular expression while matching objects' names.

   * The option --force-children takes effect only when used with --only-matching and will avoid discarding children of matched tables.
     Other tables eventually imported that are dependencies of the matched objects will have their children discarded.
     The comma-separated list of table children objects accepts the values:
     > constraint, index, policy, rule, trigger
     > Use the special keyword `all' to force all children objects.

   * NOTES: all comparisons during the filtering process are case-insensitive.
     Using the filtering options may cause the importing of additional objects due to the automatic dependency resolution.

** The diff process allows the usage of all options related to the import operation.
   It also accepts the following export operation options: `--ignore-duplicates', `--ignore-error-codes' and `--non-transactional'

** The partial diff operation will always force the options --only-matching and --force-children = all for more reliable results.
   * The options --start-date and --end-date accept the ISO8601 date/time format: yyyy-MM-dd hh:mm:ss

** When running the diff using two databases (--input-db and --compare-to) there's the option to specify two separated connections/aliases.
   If only one connection is set, then it will be used to import the input database as well as retrieve the database used in the comparison.
   A second connection can be specified by appending a 1 to any connection configuration parameter listed above.
   This causes the connection to be associated to --compare-to exclusively.

Operation mode options

Parameter Description
-ef, --export-to-file Export the input model to a sql script file.
-ep, --export-to-png Export the input model to a png image.
-es, --export-to-svg Export the input model to an svg file.
-ec, --export-to-dict Export the input model to a data directory in HTML format.
-ed, --export-to-dbms Export the input model directly to a PostgreSQL server.
-lc, --list-conns List available connections in file connections.conf.
-im, --import-db Import a database to an output file.
-df, --diff Compares a model and a database or two databases generating the SQL script to sync the latter in relation to the first.
-fm, --fix-model Try to fix the structure of the input model file in order to make it loadable again.
-cc, --create-configs Create the pgModeler's configuration folder and files in the user's local storage.
-mt, --dbm-mime-type [ACTION] Handles the file association to .dbm files. The ACTION can be install or uninstall.
-h, --help Show this help menu.

General options

Parameter Description
-if, --input [FILE] Input model file (.dbm). This is mandatory for export and fix operations.
-id, --input-db [DBNAME] Input database name. This is mandatory for import operation.
-of, --output [FILE] Output file. This is mandatory for fixing model or exporting to file, png or svg.
-v, --pgsql-ver Force the PostgreSQL version syntax when generating SQL code.
-s, --silent Silent execution. Only critical messages and errors are shown during the process.

SQL file export options

Parameter Description
-sp, --split The SQL file is generated per object. The files will be named in such a way to reflect the correct creation order of the objects.
-ds, --dependencies Includes the object's dependencies SQL code in the generated file. (Only for split mode).
-cs, --children Includes the object's children SQL code in the generated file. (Only for split mode).

PNG and SVG export options

Parameter Description
-sg, --show-grid Draws the grid in the exported image.
-sl, --show-delimiters Draws the page delimiters in the exported image.
-pp, --page-by-page Each page will be exported in a separate png image. (Only for PNG images)
-oc, --override-bg-color Don't use the original canvas color in the exported image, instead, a white background is used. (Only for PNG images)
-zf, --zoom [FACTOR] Applies a zoom (in percent) before export to a png image. Accepted zoom interval: 5-500 (Only for PNG images)

Data dictionary export options

Parameter Description
-sp, --split The data dictionaries are generated in separate files inside the selected output directory.
-ni, --no-index Avoids the generation of the index that is used to help navigate through the data dictionary.

DBMS export options

Parameter Description
-ir, --ignore-duplicates Ignores errors related to duplicated objects that eventually exist in the server.
-ic, --ignore-error-codes [CODES] Ignores additional errors by their codes. A comma-separated list of alphanumeric codes should be provided.
-dd, --drop-database Drop the database before executing an export process.
-do, --drop-objects Runs the DROP commands attached to SQL-enabled objects.
-sm, --simulate Simulates an export process by executing all steps but undoing any modification in the end.
-tn, --use-tmp-names Generates temporary names for database, roles, and tablespaces when in simulation mode.
-nt, --non-transactional Run the export process in a non-transactional mode where changes are not rolled back in case of errors.

Connection options

Parameter Description
-ca, --conn-alias [ALIAS] Connection configuration alias to be used.
-H, --host [HOST] PostgreSQL host in which a task will operate.
-p, --port [PORT] PostgreSQL host listening port.
-u, --user [USER] PostgreSQL username.
-w, --passwd [PASSWORD] PostgreSQL user password.
-D, --initial-db [DBNAME] Connection's initial database.

Database import options

Parameter Description
-ie, --ignore-errors Ignore all errors and try to create as many as possible objects.
-is, --import-sys-objs Import system built-in objects. This option causes the model to bloat due to the importing of unneeded objects.
-ix, --import-ext-objs Import extension objects. This option causes the model to bloat due to the importing of unneeded objects.
-cl, --comments-as-aliases Use objects comments as aliases. This option takes effect on objects graphically represented in the database model.
-fo, --filter-objects [FILTER] Causes the import process to import only those objects matching the filter(s). The FILTER should be in the form type:pattern:mode.
-om, --only-matching Causes only objects matching the provided filter(s) to be imported. Those not matching filter(s) are discarded.
-mn, --match-by-name Causes the objects matching to be performed over their names instead of their signature ([schema].[name]).
-fc, --force-children [OBJECTS] Forces the importing of children objects related to tables/views/foreign tables matched by the filter(s). The OBJECTS is a comma-separated list of types.
-d, --debug-mode Run import in debug mode printing all queries executed in the server.

Diff options

Parameter Description
-ct, --compare-to [DBNAME] The database used in the comparison. All the SQL code generated is applied to it.
-pd, --partial Toggles the partial diff operation. A set of objects filters should be provided using the import option --filter-objects.
-ff, --force Forces a full diff if the provided filters were not able to retrieve objects for a partial diff operation.
-st, --start-date Matches all database model objects in which modification date starts on the specified date. (Only for partial diff)
-et, --end-date Matches all database model objects in which modification date ends in the specified date. (Only for partial diff)
-sd, --save Save the generated diff code to the output file.
-ad, --apply Apply the generated diff code on the database server.
-np, --no-preview Don't preview the generated diff code when applying it to the server.
-dc, --drop-cluster-objs Drop cluster level objects like roles and tablespaces.
-rv, --revoke-perms Revoke permissions already set on the database. New permissions configured in the input model are still applied.
-dm, --drop-missing Drop missing objects. Generates DROP commands for objects that are present in the input model but not in the compared database.
-fd, --force-drop-cols Force the drop of missing columns and constraints. Causes only columns and constraints to be dropped, other missing objects aren't removed.
-rn, --rename-db Rename the destination database when the names of the involved databases are different.
-nd, --no-cascade Don't drop or truncate objects in cascade mode.
-ns, --no-sequence-reuse Don't reuse sequences on serial columns. Drop the old sequence assigned to a serial column and creates a new one.
-ru, --recreate-unmod Recreates the unmodifiable objects. These objects are the ones that can't be changed via the ALTER command.
-rm, --replace-mod Replaces modifiable objects. These objects are the ones that support CREATE OR REPLACE command.

Model fix options

Parameter Description
-ft, --fix-tries [NUMBER] Model fix tries. When reaching the maximum count the invalid objects will be discarded.

File association options:

Parameter Description
-sw, --system-wide The file association to .dbm files will be applied at a system-wide level instead of to the current user.
-ff, --force Forces the mime type install or uninstall.

Config files creation options:

Parameter Description
-mo, --missing-only Copy only missing configuration files to the user's local storage.
-ff, --force Forces the recreation of all configuration files. This option implies the backup of the current settings.

Plug-ins options

Parameter Description
-lp, --list-plugins List the available plug-ins.
-ip, --ignore-faulty Ignore the errors of plug-ins that failed to be loaded.


Despite the usefulness of the command line interface there are two important limitations in the tool which need to be described. The first one is that only one operation is supported per execution, in other words, the parameters --export-to-file, --export-to-png, --export-to-svg, --export-to-dbms, --import-db, --diff, --fix-model and --dbm-mime-type can't appear in the same command call. The second limitation is that in any operation that produces an output file based upon the input model you are obligated to specify the input and output files and these files should have different names or the process will be aborted.

6.13.1. Command line examples

Below we have some command samples, an important note to be given is that on any operation that involves the connection to a database server, you can either use the parameter --conn-alias alone or specify the entire connection configuration using the rest of the connection options (see above).

  1. Export a model to the server using a connection alias:
    pgmodeler-cli --export-to-dbms --input demo.dbm --conn-alias local-db

  2. Export a model to the server using the connections options:
    pgmodeler-cli --export-to-dbms --input demo.dbm --host localhost --user myuser --passwd mypass --initial-db mydb

  3. Export a model to PNG file applying a zoom factor of 200%:
    pgmodeler-cli --export-to-png --input demo.dbm --output demo.png --zoom 200

  4. Export a model to SQL script forcing the generation of code to PostgreSQL 9.6:
    pgmodeler-cli --export-to-file --input demo.dbm --output demo.sql --pgsql-ver 9.6

  5. Import a database ignoring errors and in debug mode:
    pgmodeler-cli --import-db --input-db demodb --output demo_imported.dbm --conn-alias local-db --ignore-errors --debug-mode

  6. Diff a model and a database applying the changes on the server:
    pgmodeler-cli --diff --input demo.dbm --compare-to demodb --conn-alias local-db --apply-diff

  7. Diff two databases living on different servers using connection aliases:
    pgmodeler-cli --diff --input-db pagila --compare-to demodb --conn-alias local-db --conn-alias1 remote-db --apply-diff

  8. Diff two databases living on different servers using connection parameters and saving the diff to a file:
    pgmodeler-cli --diff --input-db pagila --compare-to demodb --output diff.sql -H localhost -u postgres -w postgres -D postgres -H1 localhost -u1 postgres -w1 postgres -D1 postgres --save-diff

  9. Perform a partial diff by using objects modified in the model since June, 1st:
    pgmodeler-cli --diff --partial --input my_model.dbm --compare-to my_database --conn-alias local-db --start-date 2020-06-01 --apply

  10. Perform a partial diff between two databases and filter all tables and their children residing in the schema schema_a:
    pgmodeler-cli --diff --partial --input-db my_dev_db --compare-to my_prod_db --conn-alias local-db --apply --filter-objects "table:schema_a.*:wildcard" --force-children all


Nov 1, 2024 at 09:10