It's always a joyful moment for me when I'm announcing another stable version of pgModeler. It symbolizes a personal reward for a huge effort made during longs twelve months. This new version brings a huge amount of new things while fixing lots of others, and elevates pgModeler to another level of maturity, quality, and reliability! This post is a compilation of all the information written during the release of alpha and beta versions of this development iteration, with some reviews. I like this approach because it gives readers the real dimension of how much work was put into the development of the product. So, with no more delay, let's see what's new!

Infinite canvas support

This feature allows objects to be moved to negative coordinates without forcing them to be locked at the origin point (0,0). This brings more flexibility when designing databases allowing the models to grow in any direction. Also, like previous versions, the canvas is automatically expanded when objects are moved beyond the current limits but now the expansion may occur on a negative coordinate. However, with the introduction of infinite canvas, some actions were created so users could also have full control over the canvas size. To change the dimensions of the canvas, click the icon on the model actions toolbar at the main window. The actions Expand ... are self-explanatory, but the action Reset geometry will cause the canvas to be resized enough so all objects can be contained in it, if the canvas is greater than the bounding rectangle of all objects, it'll be shrunk. The expansion factor used by the mentioned actions can be adjusted in the appearance settings, tab Canvas & objects.

Redesigned table editing form

Attending to lots of requests, the table, view, and foreign table editing forms were redesigned in such a way as to move the general attributes of these objects to a dedicated tab in the editing form. This allowed the tabs that store child objects like columns, constraints, triggers, etc., to be reallocated as well so the objects could be better displayed mainly on small screens.

Improved changelog visualization

The changelog visualization widget received a bunch of improvements that include an overview of the number of operations performed over the current database model and a way to make some deep inspection in the changelog entries. In that case, if the user clicks the Inpsect button it will be presented to a data grid with user-friendly information about all the actions taken in the model.

Resizable text boxes

This feature is one that I really wanted to implement earlier. Despite its simplicity, it has a considerable impact on how text boxes are created in the model. If you use text boxes to document tables and other aspects of the database model like me you already know that until pgModeler 1.0.0 these objects had a fixed size determined by the number of line breaks that the documentation text had. Relying on line breaks to determine the size of text boxes was pretty annoying because forced the user to create lines with fixed widths so the text box could have the desired width and height in the model, and depending on the size of the text this operation could be quite tedious.

So, finally, in 1.1.0, text boxes can be freely resized without the use of line breaks in the text. First of all, when creating a new text box just type the text the way you want and pgModeler will take care of the rest. The initial width of the object will be defined by the width of the text input in the text box editing form. Once the object is created, just click it in the canvas area (keeping the left mouse button pressed), hold Shift, and move the cursor to determine a new width for the text box. The only downside of this new feature is that you can't freely resize the text box in both dimensions (width and height) because the height is determined based on the width specified by the user during the resize movement as you can see in the animation below.

Views creation is now way simpler

Instead of that clumsy interface to configure views on previous versions, now the user can create this kind of object by using freely typed SQL commands with special placeholder variables enclosed by {} that we call references. Any reference in the typed SQL command that defines the view will be replaced by the referenced object's name, which can be columns, functions, procedures, tables, foreign tables, and views. Once the view is created, pgModeler will create relationships between it and the referenced tables (foreign tables, and views). Unfortunately, this feature has a drawback which is not being backward compatible with models designed in the previous version, which means if you have models containing views you'll need to use the pgmodeler-cli fix process to make the proper corrections.

The image below gives an idea of how the new view structure works. The view's SQL definition has two references named {ta} and {td} where, respectively, points to the tables public.table_a and schema_a.table_d. Also, in the code below, there are two other forms of references @{ta} and @{td} which are replaced by the respective reference aliases.

So once the view's SQL code is generated the referenced object names as well as the reference aliases are used, resulting in the code below. This is far better and quicker than configuring each part of the SELECT, FROM, WHERE portions of the command like it was until pgModeler 1.0.0.

Like previous versions, views can deduce their columns from the referenced objects (relations and columns), but in this version, you need to hint to pgModeler that a referenced object is a column provider. In that case, when configuring a reference in the References tab make sure to check the option Use column(s). Based on that, when creating the graphical representation of the view in the model, pgModeler will get each flagged object's columns and create a copy of them in the view.

The image above shows something that was not possible in previous versions: a view referencing another view. This is a bonus feature that we gained by creating a new way to construct views. To achieve a similar result, just create as many as needed references to views when designing a new view. The only downside of this feature is that you can't reference individual view columns like in tables or foreign tables, so, if you check Use column(s) flag in a reference to a view, all columns of that object will be copied to the new view.

Extensions can now have multiple child data types

In previous releases, pgModeler had a special flag in the extension's editing form labeled "Handles data type". That flag served to inform pgModeler that the extension needed to be used as a data type, for example, creating an extension named "hstore" and checking the mentioned flag, would create the type "hstore" making it usable in the columns, function parameters, and so on. The problem with this approach is that if an extension installed more than one data type in the database, it was needed to make some workarounds to have a second type available for the database modeling process. So, in pgModeler 1.1.0, the "Handles data type" flag was ditched and now the user can specify a free number of data types that the extension handles. pgModeler will take care of the data types when adding, removing, or editing the extension. Models that use the old extension format can be fixed by using the pgmodeler-cli model fix process.

Enhanced SQL tool

The SQL tool was one of the most worked components of pgModeler during the past months. Since the database modeling has been working pretty well for quite some time, I decided to put into practice my desire to make the SQL tool more feature-complete. There are still more things to be created, but for now, that's what we've got:

• Improved code completion: A long-awaited feature is finally arriving pgModeler: the code completion based on living database object names. From now on, in the SQL execution widget, it will be possible to list schema, table (and column), sequence, and function names in the middle of the INSERT, DELETE, TRUNCATE, UPDATE, ALTER, and DROP commands. This feature also considers table aliases and lists the correct columns. It also works with non-schema qualified object names, but in that case, public and pg_catalog will be used as the default search path when retrieving object names during completion. It is worth mentioning that this feature is still experimental despite the good results on different kinds of SQL commands. Anyway, this is another feature that will improve even more the overall experience when managing databases in the built-in SQL tool.

• Automatic concatenation characters removal: When pasting SQL code coming from external IDEs in SQL execution using Ctrl+Shift+V, pgModeler will try to remove unneeded string concatenation characters (e.g. " ' + .) in the clipboard text before inserting it into the input field. This is useful for quickly testing a SQL code written in other languages' source code using concatenated strings.

• Enclosing characters highlighting: In any field where it's possible to type SQL code, the characters(), [], {} are now highlighted to indicate the correct balance of those characters avoiding syntax errors when running the typed commands. The feature also indicates unbalanced characters by changing the background to red.

• Improved CSV support: The results of the DML commands executed in the SQL execution widget as well as the data displayed in the data manipulation form can be now exported to CSV format by activating the action Export > CSV File.

• Enable large data truncation in the results grid: To display large data (e.g. bytea) in the results grid without degrading the performance an option was added to general settings that toggle the truncation of column data that exceeds a certain limit. The truncated data can be fully visualized by double-clicking the desired cell in the result grid.

• Data type icons in data grid: This is a small aesthetical enhancement but it helps a lot to give users the idea of which data type a certain column has. Basically, an icon of the type category is displayed at the left of the column name. This change affects the result grids in the SQL execution widget as well as the data manipulation form.

UI improvements

This release also brings more UI improvements, adjusting those features introduced by 1.0.x or including new ones as described below:

• Improved UI color management: In 1.0.x, pgModeler couldn't correctly follow the system's color set (dark/light) forcing the user to manually adjust the color settings for the syntax highlight. Now, in 1.1.0, selecting System default UI theme in Appearance settings makes pgModeler properly configure the UI element colors as well as the source code highlight settings according to the current system's color schema.

• Drag & drop support: pgModeler now supports the drag & drop of .dbm files selected in the file manager directly into the tool's main window to load models. You can drag and drop files into the Welcome or Design views which causes pgModeler to toggle the file loading mode taking as input files those held over the tool's window.

• Disable objects' shadows: Database object shadow element can be now deactivated in the general settings, at behavior options, giving an overall performance boost in heavy-populated models since fewer objects per scene need to be handled.

• New InkSaver theme for optimized printing: For those who constantly need to print database models designed in pgModeler, there was an important improvement in this version: the InkSaver theme. As the name suggests, this theme was designed to have a limited color set (actually black & white) and can be useful when printing models to be used in academic papers or any other type of printed documentation. Currently, this theme does not replace tag object colors, schema rectangle colors, and relationship line colors. If you have a model in which you want to use the InkSaver theme you have to make some adjustments.

pgModeler is now unbelievably faster!

One of the most annoying things on pgModeler for me all these years was the speed of the operations like object searching, model validation, and, mainly, database model file loading for big database models. So, during the development of this version, I decided to face the challenge of improving these three operations, so I delved into the internals of the tool looking for the major bottlenecks. After finding and selecting the more problematic ones, I took the path of rewriting some mechanics instead of trying to fix them.

The two main bottlenecks that degraded the speed of the mentioned operations were the objects' name validation/formatting as well as the retrieval of objects' dependencies and references. Those seemed simple operations that I even could imagine that they were making pgModeler struggle to handle big models. For the objects' name formatting and validation, I decided to create an internal name cache to avoid calling those procedures repeatedly. A simple solution that brought a surprisingly good result.

For the objects' dependencies and reference handling, I completely ditched the routines written for that purpose and created something infinitely simpler. Instead of calling every time a procedure that runs countless loops and recursive calls, I just made the objects store internally which other objects are their references and dependencies. Those changes made models that were loading/validating in several minutes to be processed in a few seconds. I still have some other bottlenecks to solve, but with those two already removed, pgModeler has now an amazing performance. To give an idea, I used three of the heavier models I have in my development environment, real-world cases kindly handed over by some users. The results obtained were the following:

Those results were retrieved on openSuse Leap 15.5 powered by an Intel Core i7@4.7GHz with 32 GB RAM. But on the latest macOS running on a Mac Mini M2, the results were surprisingly ~20% better!

Improved tool's executables relocation

pgModeler already has some mechanisms to customize the paths associated with the assets and executables once installed in the system. One of them is the environment variables, but sometimes the user doesn't want or even has no privileges to change environment variables in the system. An example of usage for this file is when the user moves the installation folder to another place and the tool fails to start or isn't properly finding the resources needed during the runtime, so creating the file in the right place may solve the problem without the need to change system environment variables or even reinstalling pgModeler.

Thinking of that, this new version introduces a special configuration file called pgmpaths.conf, in which the goal is to configure the paths where the pgModeler main executable as well as the auxiliary tools can find all the needed folders, assets, and configuration files. This file must be created in the same folder as a pgModeler's executable and must be filled with lines in the format variable = path. The variable refers to one of the available environment variables understood by pgModeler and the path is a relative or absolute path to the resource associated with the environment variable. Below we have an example of pgmpaths.conf file. Invalid variable names or variables without a path assigned are completely ignored.

PGMODELER_SCHEMAS_PATH = ../share/pgmodeler/schemas
PGMODELER_TMPL_CONF_PATH = ../share/pgmodeler/conf
PGMODELER_LANG_PATH = ../share/pgmodeler/lang
PGMODELER_PLUGINS_PATH = ../lib/pgmodeler/plugins
PGMODELER_SAMPLES_PATH = ../share/pgmodeler/samples
PGMODELER_CONF_PATH =
PGMODELER_TMP_PATH =
PGMODELER_CH_PATH =
PGMODELER_CLI_PATH =
PGMODELER_SE_PATH =
PGMODELER_PATH =

Support for plugins in CLI tool

The command-line tool now can have extra features implemented through plugins like in GUI. This opens a wide range of possibilities making the CLI an even more powerful tool that can be integrated into your development/deployment pipeline. Refer to pgModeler docs to see how to create your own features for the command-line tool. The CLI tool now has dedicated options to handle plugins. Also, if you have one or more plugins, they will have their options listed right below the Plug-ins options section so the user can get help on how a specific plugin can be used.

pgModeler command line interface.
Version 1.1.0 - 20240222.c5b78cbad Qt 6.6.2
PostgreSQL Database Modeler Project - pgmodeler.io
Copyright 2006-2024 Raphael Araújo e Silva 

...

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

Split model plug-in options: 
  -ls, --load-sdbm                  The input database model must be loaded as split model file (.sdbm)

...

Split model load plugin

This is the first official plugin for CLI that is bundled in the paid version of pgModeler. It allows one to load split database models (.sdbm) in the CLI and perform any operations available like it was a common (single) database model file (.dbm). This plugin comes as a complementary feature for the split model plugin in GUI that allows the saving of database model files in separate files for improved version control via Git, SVN, and other similar tools. Below we have an example of the usage of the split model load plugin in CLI:

$> ./pgmodeler-cli --load-sdbm -ep -if ~/demo_split/dbmodel.sdbm -of ~/demo_split/dbmodel.png

pgModeler command line interface.
Version 1.1.0 - 20240222.c5b78cbad Qt 6.6.2
PostgreSQL Database Modeler Project - pgmodeler.io
Copyright 2006-2024 Raphael Araújo e Silva 

Parsing input split model: /home/raphael/demo_split/dbmodel.sdbm
Temporary single model created: /home/raphael/.config/pgmodeler-1.0/tmp/dbmodelixdGJr.dbm

Starting model export...
Loading input file: /home/raphael/.config/pgmodeler-1.0/tmp/dbmodelixdGJr.dbm
Export to PNG image: /home/raphael/demo_split/dbmodel.png
[90%] Rendering objects to page 1/1.
[100%] Output image `/home/raphael/demo_split/dbmodel.png' successfully written.
Export successfully ended!

Removing temporary single model.
Flushing used memory...
Done!

SQL session plugin

Another feature that I wanted to see in pgModeler, mainly because it was constantly affecting me, was a way to avoid losing the typed SQL commands and the currently browsed databases in Manage view. So I decided to create a plugin available in the paid version of the tool that allows one to restore the previous session in database management view. This plugin, before exiting the tool, saves in a dedicated configuration file all details about the currently opened SQL execution tabs and the typed commands as well as the browsed databases. After running pgModeler again the user just needs to click one button to restore the previous session instead of going through the tedious task of browsing each database and loading all previously saved commands in the SQL execution widget (if that was the case). This is a simple enhancement that brings a huge productivity impact!

Backup utility plugin

This version introduces the backup utility plugin, available in the paid version of the tool, which implements a user-friendly interface for the commands pg_dump, pg_dumpall, pg_restore, and psql. This extra feature was developed mainly focused on attending to those users less comfortable with command-line tools, making it possible to dump and restore databases without leaving pgModeler's GUI. Of course, advanced users are welcome to use the plugin and help to improve it!

In a nutshell, besides configuring backup tools' parameters with a simple form, it allows the creation of presets per backup tool for different needs, it also has some facilities that automate the backup file name generation by using a default backup folder and name patterns. Since the plugin reuses the configured connections information passing them to each backup utility command, make sure to use credentials that can log in to the server and perform a dump or restore, or the processes will fail.

The image below shows the pg_dump section of the plugin. The options there are user-friendly labels for each command-line option so, in case of doubts, you can always refer the official PostgreSQL docs to obtain detailed information about them. Specifically for Dump and Dump all sections, selecting a connection and a database name in the Databasegroup at the top-left portion of the form, will trigger the automatic output filename generation according to the parameters defined in the Settings tab. If now the automatic name generation parameter is defined, then you have to manually specify the output filename.

The backup plugin settings section is quite simple and demands almost no user interaction in a normal initialization. In the Backup utilities paths, the plugin tries to locate the needed PostgreSQL backup utilities and assign them to each selector. If one or more executables can't be found the related selector will display a warning sign so the user can fix the problem. In the File name patterns, is where the automatic name generation feature is configured. The first two fields, pg_dumpand pg_dumpall are the name patterns used in Dump and Dump all sections. In those two fields, hitting Ctrl+Space shows a popup with the available tokens used by the patterns to create the output file names. The Default folder selector is a valid path to a folder where all the backups will be saved. Finally, the Automatic names generation option toggles the whole process of generating a name based upon the options configured in the group, while the option Disambiguate conflicting names will avoid the generation of names that somehow may overwrite a backup file already preset in the output folder.

Once you have tweaked all the desired options in the backup utility tab, just hit Run to start the selected backup tool. Once the process is started, all the output will be displayed in the Output tab like the image below.

Other changes and improvements

• Now during object copy/paste or duplication, in case of name conflict, the user is asked to type a new name for each conflicting object.
• Added the ability to restore the scene rect from the database model file.
• Adding support to expand the canvas rectangle via menu actions in the main window.
• Removed the confirmation dialog related to copying dependencies during the copy operation. Now the copy of an object's dependencies is triggered by the shortcut Ctrl+Shift+C (copy selection and dependencies) or Ctrl+Shift+X (cut selection and dependencies).
• Minor improvement in the tree expanding state and vertical scrollbar position restoration in the model objects widget.
• Added support for inksaver color theme which uses only black and white colors for models that are commonly used for printing.
• Added support for using object comments as aliases in database import.
• pgModeler now asks the user about closing SQL execution tabs that contain possibly unsaved typed commands.
• Added support for remembering decisions on the alerts regarding unsaved models/open SQL tabs.
• Added a basic form to inspect the changelog's XML code.
• Added missing multirange types.
• Improved the relationship point addition and selection via mouse clicks.
• The "dot" grid mode is now the default in the appearance.conf file due to better drawing performance.
• Improved the scene background (grid, delimiter, limits) drawing speed for big models.
• Improved the objects' filtering in reverse engineering by introducing an "any" filter type.
• Data manipulation form now shows a confirmation message before closing when items are pending save.
• Added support for overriding the canvas' background color when exporting the model to PNG.
• The "Display unique results" option on objects' dependencies & references dialog is now checked by default.
• Adjusted the CSV pasting in the table data editor.
• Adjusted the extension's attributes display in the database explorer to list types related to an extension.
• The code completion widget now resizes according to the displayed items' width.
• The code completion will not display a "no items found" popup if no element is found matching the word at the cursor's position.
• Adjusted the reverse engineering process so relationships can be created from the link between two views.
• Minor change in reverse engineering to avoid importing extension child types into the model since the extension itself, when imported, already creates the types.
• Minor adjustment in connections configuration form in such a way to reload connections where they are used only when the user applies new connection settings on the form or restores original connections by clicking "Cancel".
• Minor improvement on the object IDs swapping form in such a way as to preserve the sorting parameters after swapping object IDs.
• Minor change in the operation list widget in such a way as to use icons instead of labels for operation list size and current history position.
• Refactored signal/slot connections that needed exception handling all over the tool avoiding crashing the application when an exception is raised (macOS only).
• Minor adjustment in model creation to avoid marking it as invalidated even if it's new and empty.
• Minor adjustment in code completion widget to include sequences when listing living database objects.

Bug fixes

• Fixed the plugins' loading process by ignoring the ones that don't implement the correct interfaces (PgModelerCliPlugin or PgModelerGuiPlugin).
• Fixed a bug in the model load process in the main window that was not registering the loaded file as a recent model.
• Fixed a malformed diff code when adding a column to a partitioned table.
• Minor fix in objects scene in order to draw the canvas background in the correct color defined in the appearance settings.
• Fixed a bug that was preventing tables and schemas from being updated graphically when adding/removing relationships in some circumstances.
• Minor fix in graphical objects tooltips.
• Fixed the trigger's catalog query to avoid referencing the tgparentoid field when importing from PostgreSQL 12 or below.
• Fixed the PNG export process in GUI and CLI to handle infinite canvas.
• Fixed a bug in model fix form that was not locating pgmodeler-cli on Windows.
• Fixed a bug in the object filter widget in diff form that was setting wrong object types when doing a partial diff using filters generated from the changelog.
• Minor fix in code completion in order to list columns of tables non-schema qualified. In that case, pg_catalog and public will be used as default schemas.
• Columns marked as PK are now restored when handling them in the table editing form.
• Minor fix in the object finder widget to avoid disconnecting a null selection which could lead to crashes.
• Minor fix in the database model widget to hide the new object overlay when moving a selection of objects in the design area.
• Minor fix in the object removal routine in a model widget that was not erasing an object in case it shared the same name as other objects in the same schema.
• Minor fix in the object addition routine to validate the layer of the object being added. If one or more layers are invalid the object will be moved to the default layer 0.
• Minor fix in pgmodeler-cli when extracting the objects' XML code during model file structure repair in order to restore correctly the layers name/count.
• Fixed a bug in partial reverse engineering that was not correctly importing functions in some specific conditions.
• Fixed a bug in partial reverse engineering that was not importing some objects' dependencies correctly.
• Fixed a bug in the appearance configuration widget that was not updating the example model colors when changing the UI theme.
• Fixed a crash when double-clicking the overview widget.
• Fixed the data dictionary schema files for tables and views.
• Fixed a bug in the database model that was causing FK relationships of a hidden layer to be displayed after loading the model.
• Fixed a bug in the scene move action that was causing the grid to not be displayed after a panning/wheel move.
• Fixed settings storing for the grid options in the main window.
• Fixed a bug in the generation of diff commands for identity columns.
• Fixed a bug in list widget items painting that was causing the rendering of artifacts sometimes.
• Fixed a bug in pgmodeler-cli that was aborting the fix process during the parsing of the model changelog.
• Fixed a crash when trying to load an invalid model from the recent model's menu.
• Fixed sample model structure to the new view's format.
• Fixed several bugs in the code completion widget when completing code using live database object names.
• Fixed the catalog query operator.sch so argument data types can have the schema name prepended.
• Fixed a crash when trying to display function/procedure/operator source code that references a domain in the parameters.
• Fixed the catalog queries for function and procedure to correctly format parameters' data type signatures.
• Fixed a problem in object IDs swapping widget that was not capturing arrow key press on macOS.
• Fixed a bug in the importing of partitioned table indexes which was affecting the results produced by the diff process.
• Fixed a bug in the code completion widget that was crashing the application when trying to list columns of an alias related to a non-schema-qualified table.
• Fixed a bug in reverse engineering related to the importing of partition tables hierarchy.
• Fixed a bug in diff that was generating false-positive results for columns with the same user-defined type.
• Fixed a bug in the line numbers widget, within source code input fields, that was not aligning the line numbers with their respective lines when the font size was different from the default.
• Fixed a broken build related to the XML parser that was using an xmlError * reference instead of a const xmlError * reference.
• Minor fix in code completion widget to avoid the displaying of the widget in a position that extrapolates the screen limits.
• Minor fix in the icon positioning in the list and tree widget items.

Let's support pgModeler?

If you like the work that is being done to create a quality database design tool, please become our sponsor on GitHub. Any open-source project needs financial support to keep the development alive, and this is not different with pgModeler. Go ahead, be a supporter in one of the offered sponsor tiers, and receive rewards for being a friend of an open-source project! :D

Finally, since this version has a long list of changes and the majority of them are not described here, please, take a look at the CHANGELOG.md file to get all details about them. Also, don't forget to leave your feedback below in the comments section, any suggestions, criticisms, and compliments are quite important to keep the development active. If you stumble on a bug or want to request a new feature, please, register a ticket on GitHub! For project news and updates, follow pgModeler on X, Mastodon, Bluesky or join the telegram channel @pgmodeler.

Now, I'll give a small pause on the development and work on updating the docs with the new features that pgModeler 1.1.0 brought! Anyway, if you have questions about this version or doubts about how to use the tool all communications channels are open, don't hesitate to contact me! ;)

Enjoy pgModeler.

See you soon! :)