The Liferay Database Schema

A brief explanation of the Liferay Database

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.

Blogs

Thanks Sam! This diagram is really useful when diagnosing issues during the upgrade or building queries to extract some data. Olaf, I agree the Liferay API is the only way to modify the data however having some knowledge about how the data is stored is crucial to solving complex issues, especially for community users with no official Liferay support.

I encourage everyone to add more tables and build the whole database diagram!  

Hi,

i agree that one should not mess with the DB, however in my case i work in SQL and do not have nowledge of Java, but must report some audits and statistics about users, etc.

So, to me, it is most useful to know something about the underlying table relastionships.

Regards