ER modeling has been around for several decades and has proven to be an effective method for conceptualizing data. ER models consist of entities, relationships, and attributes, which are graphically represented in an ER diagram through different shapes. Over the years different ER variations, notations, and extensions appeared, requiring a certain level of expertise to efficiently create valid and meaningful models. Numerous ER modeling tools are available that simplify the process of creating models and integrating them with databases. However, often the tools are inflexible, proprietary, or lack modern modeling functionality, which are the concrete limitations bigER tries to solve.
bigER is a free and open-source ER modeling tool supporting hybrid, textual– and graphical editing, multiple notations, and SQL code generation. The tool is available as an extension for Visual Studio Code, which is currently one of the most popular source-code editors, especially in the context of web development. VS Code is based on Node.js and Electron, offering high extensibility with its extension API and fully incorporates the Language Server Protocol (LSP). bigER internally relies on the LSP to provide powerful language support and extends the protocol with graphical language features, making the tool one of the first of its kind. Furthermore, the concrete technology choices make the tool available in a modern web-based environment with high reusability for other platforms that also support the LSP (e.g., Eclipse, Theia, Atom). See the list of tools supporting the LSP.
In this blog post, we highlight the key features of bigER and look into the technical realization of the tool.
Modeling with bigER
The extension can be simply installed from the Extension tab in VS Code by searching for “bigER”. Once the installation is finished, the extension is ready for use.
ER models are stored in simple text files ending in .erd. The easiest way to get started with a new model is by using the New Sample ER Model command from the VS Code command palette. As seen in the image below, this creates a basic model with two entities and a relationship, showcasing basic concepts of the language.
Hybrid modeling
bigER supports hybrid (or blended) modeling, with the ability to use both, the textual- and the graphical editor for the creation and modification of ER models. Besides clear usability improvements, this hybrid modeling approach also improves cross-communication between stakeholders, e.g., business analysts with less technical experience can initially create an ER diagram by using the graphical editor, while a database engineer, who is well accustomed to textual languages, refines the model in the textual editor, taking advantage of efficiency and stability.
The textual language offers rich-text editing support, enabled through the Language Server Protocol (LSP). Examples of textual language features are shown in the images below, including hover information (left), finding definitions of references (middle), and quick fixes for validation errors (right).
The diagram is automatically laid out and fully synchronized with the textual model, even on selection of elements. Actions on the left side of the toolbar allow creating elements, enabling the code generator, or changing the notation. When hovering over the diagram additional information is shown together with buttons to rename or delete the elements. All graphical editing actions are of course also immediately reflected in the textual model. Actions on the right side of the toolbar allow refreshing the diagram, collapsing/expanding all entities, or centering the view.
SQL Code Generation
Another key feature of bigER is SQL code generation from the created ER models to integrate with existing databases. The generator can be enabled in the toolbar or by textually specifying generate=sql in the model header. Once the code generator is enabled and the specified ER model is valid, a new file containing database tables with foreign key constraints is automatically generated.
The GIF below showcases various modeling functionalities, including SQL code generation with the generated output towards the end. The ER model featured in the image is an example of a university database, available on GitHub for reference.
Notations
A recent extension to the tool is multi-notation support, which can be changed similarly to the code generator. The main difference between the notations is how relationships and cardinality/multiplicity in the edges are represented. When switching to a specific notation and the value for the cardinality is invalid, the error and correct usage is displayed in the textual editor, diagram and problems view. The image below demonstrates this behavior.
The table below shows various examples of the currently supported notations in the bigER modeling tool.
| Notation | Example |
|---|---|
| Min-Max (Default) |  |
| Chen |  |
| Bachman |  |
| Crow’s Foot |  |
| UML |  |
Improved Edge Routing
Besides multi-notation support, another recent improvement to the tool is the improved routing of edges in the diagram. By using a Libavoid edge router from the sprotty-routing-libavoid package, the layout of the diagram is completely automated and includes advanced mechanisms such as obstacle avoidance. Furthermore, the edges are now orthogonal instead of the previously used poly line edges. A comparison is shown in the images below, with edge routing before (left) and after (right) the improvements.
Technical Realization
The bigER modeling tool is based on the architecture of the Language Server Protocol with a client and a server. The server is realized as a language server with Xtext that communicates textual language features (LSP messages) to the VS Code extension. The diagramming framework Sprotty is used to further enhance the server with graphical language features, including a diagram generator and a layout engine based on the Eclipse Layout Kernel (ELK). The client-side consists of the VS Code extension with a language client to communicate with the server, and integrates Sprotty for VS Code to connect and render the diagram in a web view. The web view contains the toolbar and diagram-specific components for Sprotty, e.g., SVG views and CSS styling for rendering the model elements, or actions and commands for handling user interaction on the diagram.
The architecture of the bigER modeling tool is described in more detail in the thesis “Developing Sprotty-based Modeling Tools for VS Code”.
Outlook
Currently, we are working on adding any remaining ER (including EER) concepts to the tool, while also working on improving the multi-notation functionality. The main goal is to fully support all ER modeling concepts and consistently reflect them in different notations. Another focus is import- and export functionality, which also includes enhancements to the current behavior of the code generator. Naturally, current ongoing work also includes bug fixes and implementing tests.
Try it
The bigER VS Code extension can be freely downloaded from the VS Code Marketplace. The code is open-source available in the GitHub repository which also contains a Wiki with more information about usage and development.
We are actively working on new releases. Contributions to the project or bug reports are always welcome!
Contact
Philipp-Lorenz Glaser [email protected]
Dominik Bork [email protected]
TU Wien, Business Informatics Group
I would like to know whether developer can figure out circular relationship for the ER diagram? Also, the foreign key indicator, what symbol(s) can we use for the presentation? Mnay thanks.