Skip to main content
Version: 2023.3

Database Model

Pimcore tries to keep a clean and optimized database model for managing the data. Nevertheless, there are quite a lot of tables around and finding the correct data might be a bit challenging at the beginning.

Basically, there are two types of tables

  • Default tables with are created during install - for all sorts of data like assets, documents logs, versions, ...
  • Dynamically created tables during object data model configuration - mainly for all object related data.

Default Tables

These tables are created during Pimcore install and are always the same.

TableDescription
application_logsContains all application logs. For more information see Application Logger. Additionally, there might be application_logs_archive* tables for old logging entries.
assetsAssets (Images, etc.), with system metadata
assets_metadataAdditional user metadata (Metadata tab in the asset panel)
classesList of all object classes with ID and name. Everything else is stored in PHP configuration files.
classificationstore_collectionrelationsRelation Collection - Group for Classification Store
classificationstore_collectionsCollections of Classification Store
classificationstore_groupsGroups of Classification Store
classificationstore_keysKeys of Classification Store
classificationstore_relationsRelation Group - Key for Classification Store
classificationstore_storesStores of Classification Store
custom_layoutsDefinition of the custom layouts for object classes
dependenciesStores dependencies between elements such as objects, assets, documents
documentsList of all documents, folders, links, hardlinks, emails and snippets of the document area with meta- and config-data, relations
documents_editablesEditables of documents (data), in a serialized form
documents_emailExtra config data
documents_hardlinkExtra config data
documents_linkExtra config data
documents_pageExtra config data
documents_printpageExtra config data
documents_snippetExtra config data
documents_translationsRelation between same documents for different languages
edit_lockTracks which user opened which resource in the backend
element_workflow_stateKeeps track of workflow state for all elements
email_blocklistBlocklist for eMail-addresses
email_logLog for sent emails
glossaryWords to auto-link in texts. See Glossary
http_error_logHTTP error log
lock_keysStore for Locking functionality
notesNotes for elements
notes_dataAdditional data for notes
objectsList of all objects with metadata like id, class name, path, parent, ...
object_url_slugsURL Slug data
propertiesData from the properties tab
quantityvalue_unitsAvailable quantities for quantity value object data type
recyclebinStores metadata of deleted elements
redirectsStores redirects
schedule_tasksStores scheduled tasks
search_backend_dataStores the index for the backend search - is a InnoDb Table with fulltext capabilities
sitesStores sites
tagsStores available tags
tags_assignmentStores assignment of tags to elements
tmp_storePimcore internal tmp store
tracking_events
translations_adminBackend translations
translations_messagesFrontend translations
tree_locksLocks in the tree of Pimcore backend interface
usersBackend users
users_permission_definitionsList of globally assignable user permissions
users_workspaces_assetStores user access permissions for asset folder
users_workspaces_documentStores user access permissions for document folders
users_workspaces_objectStores user access permissions for object folders
uuidsstores Unique Identifiers - if enabled
versionsList of object/asset/document versions. Actual data is serialized and written to disk

Object Tables

These tables are created and modified dynamically during the configuration of the object data model. As a result, they look different on an every Pimcore installation depending on the data model.

Objects

As soon as a new object class is created in Pimcore, at least three tables are added to the database. The tables have a numerical suffix, denoting the number (id) of the object class: object_query_(id), object_relations_(id), object_store_(id) and an additional database view object_(id) which is a combination of object_query_(id) and objects.

Table / ViewDescription
object_(id) ViewDatabase view joining object_query_(id) and objects table
object_query_(id) TableUse this table to retrieve data incl. inherited data. Data types with relations are usually stored in a serialized form here, too. Pimcore Object-Lists work with this table.
object_relations_(id) TableContains data of fields with relations to objects, assets, etc.
object_store_(id) TableThis is the main data storage table of an object class. It contains all "flat" data without any relations or external dependencies.
objects TableContains an entry for each and every object in the system. The id field is an auto_increment and the source of the primary key for an object. Metadata about an object is stored in this table, too.

When restore of query tables is necessary (for what ever reason) calling DataObject\Concrete::disableDirtyDetection(); and saving all data objects of class will do the trick. When not disabling dirty detection, there might be data missing in query table.

Simple Data Field Types

Following is an overview of how different object data types are stored in the database. This overview might not be complete. This overview might be a useful starting point when querying object data with object lists.

Text

Table: object_store_(id)

NameData TypeDefaultComment
Inputvarchar(255)NULL/
TextarealongtextNULL/
wysiwyglongtextNULLText with HTML-tags
passwordvarchar(255)NULLPassword - as hash
Number

Table: object_store_(id)

NameData TypeDefaultComment
Numberdouble/decimal(64,3)NULLDatatype depends on selected precision
SliderdoubleNULL/
Date

Table: object_store_(id)

NameData TypeDefaultComment
Datebigint(20)NULL< 1970 = negative Timestamp
Date & Timebigint(20)NULL< 1970 = negative Timestamp
Timevarchar(5)NULLString - e.g.: "12:00"
Select

Table: object_store_(id)

NameData TypeDefaultComment
Selectvarchar(255)NULLSelected value
Uservarchar(255)NULLPimcore User-ID
Countryvarchar(255)NULLCountry code
Languagevarchar(255)NULLLanguage code
MultiselectiontextNULLString, selected values, separated by ","
Countries (Multiselect)textNULLString, selected language-codes, separated by ","
Languages (Multiselect)textNULLString, selected language-codes, separated by ","
Relations

Table: object_relations_(id) & object_meta_data_(id)

  • Data fields of relation types are stored in extra tables
  • Data fields are not stored in distinct columns, but as rows, whereas the field name is in an extra column fieldname
  • The column type specifies the type of the linked resource (Object, Document, Asset)
  • The columns src_id and dest_id define the relation / the link between the objects.
  • Column index is used to specify the order of the relations
  • Columns ownertype, ownername and position are used when relations are within field collections, localized fields, object bricks, etc.
  • The data type Advanced Many-To-One Object Relation stores the extra data in a table object_meta_data_(id) - the column column specifies the name of the meta item and data stores the value
Structured
NameComment
TableTable data is stored as a string - serialized.
Structured TableEach table cell is stored distinctively; schema: (fieldname)__(row key)#(column key)
Field-Collectionssee special data fields later
Objectbrickssee special data fields later
Localized Fieldssee special data fields later
Geographic

Table: object_store_(id)

NameData TypeDefaultComment
Geographic PointdoubleNULLCreates two columns: ‘(name)__longitude’ and ‘(name)__latitude’
Geographic BoundsdoubleNULLCreates four columns: ‘(name)__NElongitude’, ‘(name)__NElatitude’, ‘(name)__SWlongitude’ und ‘(name)__SWlatitude’
Geographic PolygonlongtextNULLSerialized geo-data
Other
NameData TypeDefaultComment
Imageint(11)NULLID of the image asset
Image Advancedint(11), textNULLCreates a column (name)__image(int) for the image assets id and the column (name)__hotspots(text). Hotspots are stored serialized.
VideotextNULLSerialized data
Checkboxtinyint(1)NULLBoolean value (1 = true)
LinktextNULLSerialized data

Special Data Fields

Objectbricks
Table/ViewPurpose
object_brick_query_(id) TableAnalog to object_query_(id)
object_brick_store_(id) TableMain data storage
Localized fields
Table/ViewPurpose
object_localized_(id)_(language-code) ViewA database view per language, combining regular and localized data fields
object_localized_data_(id) TableStores localized field data
object_localized_query_(id)_(language-code) TableAnalog to object_query_(id)
Field Collections
Table/ViewPurpose
object_collection_(collection-name)_(object-id)Stores data of the field collections fields and the order (index)