Here we are a bit closer to the stable version by announcing the first beta of 1.1.0! Despite the development of this version was not that long I could solve two of the main problems that had been bothering me for a long time. The first one was the complicated interface to create views, and the other was related to data type handled by extensions. I'm quite satisfied with the result and I hope you, dear reader, like it too.

Before starting to detail what's new, I want to emphasize that this is the last version in this development cycle to receive new major features. From now on I'll work only on bug fixes until the launching of the stable version, maybe small improvements can be added here and there but nothing that will change considerably the tool's usage compared to this version. That's it, so now, let's see what pgModeler 1.1.0-beta brings us!

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 (yeah, views!). Once the view is created, pgModeler will create relationships between it and the referenced tables (foreign tables, and views). Of course, not everything is roses, the feature is not 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.1.0-alpha1.

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 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-beta, 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.

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, note that invalid variable names or variables without a path assigned are 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 =

Several other improvements and bug fixes

General improvements and bug fixes were made all over the code as part of the constant search for the overall tool's stability and reliability. Below, some of them are described:

• 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.
• Fixed settings storing for the grid options in MainWindow.
• Fix a crash that was happening only on Windows.
• 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.

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

That's all folks! If you want to check the whole list of bug fixes and improvements in this version, please, refer to the CHANGELOG.md file. Also, leave your thoughts about this new version in the comments section, and don't forget that any bug and feature request can be registered on GitHub, these kind of feedback helps a lot in the enhancement of the tool! Finally, follow pgModeler on X, Mastodon, Bluesky or join the telegram channel @pgmodeler for first-hand news about the project!

See you next time! ;)