We get requests at Liferay for an ER diagram for the database regularly. We do not have an ER diagram, nor are there any intentions to create one for the database. One of the most important reasons behind this decision is that any CRUD operations to the database simply SHOULD NEVER be made by anything except the Liferay API. Making manual changes in a Liferay database violates support guidelines and prevents Liferay Support from assisting with issues caused by doing so. Additionally, when using Liferay, the database should be considered a black box that must not be changed outside of API usage. However, there are times when verifying what data is present can be helpful, especially in the scope of upgrades. Often while upgrading, there will be errors present that seemingly have no connection to the data at hand, but upon closer inspection, we find these two entities are linked. 

To help get started with being able to verify what data is in the database, it is essential to know some names of tables in the database do not always correlate directly with the front end names for Liferay content. With that in mind, I’d like to start by identifying some of the more common tables we see issues with during upgrades and which tables relate to which entities. While the list is by no means exhaustive, and the tables listed often correspond to multiple objects, here are a few more easily confused tables and entity names.

• Web Content -> JournalArticle (and other tables starting with journal)

• Documents and Media -> DLFileEntry (and other tables starting with DL)

• Forms -> DDM There are many different DDM tables, and most have some relation to Forms; however, any web content structures and templates are stored in DDMStructureas well.

• Sites -> Group_

• Pages -> Layout

• portlet/widget preferences -> portletPreferences

In Liferay, we do not use foreign keys in our database, but we have identifiers that determine what data is related to other tables. In most entity tables, there are always two columns present, classNameId, and classPK. ClassNameId represents a value in the className_ table and can help narrow down the related entity in another table. ClassPK represents the primary identifier of the other table. For example, a classPK in the AssetEntry table with a classNameId referencing the value of “com.liferay.journal.model.JournalArticle” will use the resourcePrimKey column for of the JournalArticle table. This approach helps Liferay use tables for multiple entity types when needed, while the robust API updates the necessary tables without causing issues or forgetting to update a table. The main reason Liferay chose this approach is to allow compatibility with all of our supported databases without having to implement connectors for each vendor, which helps users choose the infrastructure that works best for them instead of being confined into a specific set up.

I’d like to finish up by offering the start of an ER diagram with the invitation to contribute more of the many Liferay tables to this diagram. Note, only the main fields and relationships are represented. There are many more columns and connections for these tables. If you would like to add more, please do so here.