Skip to main content

MongoDB vs. SQL implementation differences in Strapi v3

This documentation explains the key structural differences to take into account when migrating data from MongoDB to SQL in the context of a Strapi v3 project. It should be used as a reference when migrating data locally (see MongoDB to SQL migration in Strapi v3).

Model settings

The model.settings.json files, used to define models in Strapi v3, include parameters that are handled differently by MongoDB and SQL databases.

Naming conventions

Table/collection names

Table in SQL databases, equivalent to collection in MongoDB, are created with the name defined in the collectionName option of the model.settings.json file.

When switching from MongoDB to SQL, one SQL table is created per MongoDB collection, and new SQL tables are created for relations.

Column/field names

Columns in SQL, equivalent to fields in MongoDB, are created with the names defined in the attributes option of the model.setting.json file.

An example attribute_a defined in model.settings.json would be stored like the following in MongoDB and SQL databases:

// model.settings.json
{
"attributes": {
"attribute_a": {
"type": "string"
}
}
}
MongoDB
{
"_id": ObjectId("1")
"attribute_a": "abcd"
}
SQL
{
"id": 1
"attribute_a": "abcd"
}

Timestamps

If the timestamps option is defined in the model.settings.js file, no migration is required, the properties will be the same in MongoDB and SQL databases.

If no timestamps option is set, the defaults should be migrated, using lower snake case in SQL databases:

Field name in MongoDBField name in SQL databases
createdAtcreated_at
updatedAtupdated_at

Relations

note

Custom column names for relations can't be used in both MongoDB and SQL databases. No specific migrations are needed for this case and custom column names can be considered as if they were not used.

In Strapi, relations between models are defined in the attributes section of the model.settings.json files.

The following section explains how each type of relation is declared in the model attributes and gives an example of how the model attributes are reflected in the MongoDB and SQL databases:

In MongoDB, the id of a oneWay relation is in the document and is named after the property in the model.settings.json file.

In SQL databases, the oneWay relation is a column in the row and is named after the property in the model.settings.json file.

Models:

// model A
{
"attributes": {
"one_way": {
"model": "test-a"
}
}
}

// model B
{
"attributes": {
// no attributes on the other side
}
}
MongoDB
// model A
{
"_id": ObjectId("1"),
"one_way": ObjectId("1")
}

// model B
{
"_id": ObjectId("1")
}
SQL
// model A
{
"id": 1,
"one_way": 1
}

// model B
{
"id": 1
}

SQL join table names

The name for the SQL join table used in manyToMany and manyWay relations is generated based on the collectionName property, the attributes of the relation, and the type of the relation:

  • manyToMany relations have the join table follow this naming pattern: {}_{}

  • manyWay relations have the join table follow this naming pattern: {collectionName}__${snakeCase(attributeName)}, like in the following example:

    // With the following model A:
    {
    "collectionName": "table_a",
    "attributes": {
    "myManyWay": {
    // ...
    }
    }
    }

    // The SQL join table name will be:
    "table_a__my_many_way"

Components & Dynamic zones

In both MongoDB and SQL databases, components have their own collection and are links to their parent.

In MongoDB, the links are done via an array of objects stored in the parent. Even non-repeatable components are listed in an array. Each object from this array has 2 properties:

  • ref targets a specific component
  • kind targets a specific collection

In SQL databases, the links are done with a SQL join table. The table name is generated following this pattern: {collectionName}_components, where collectionName is in the parent model. SQL tables for components include the following elements:

NameTypeDescription
component_typeColumnUses the collectionName and not the globalId property
fieldColumnShould be equal to the attribute name
orderColumnShould go from 1 to x, matching the order in the MongoDB array
component_idForeign keyTargets the component table
{singular(collectionName)}_idForeign keyTargets the parent table
Example of a component definition in model settings, MongoDB and SQL databases in Strapi v3

Models:

// model A
{
"attributes": {
"compo": {
"type": "component"
"repeatable": true|false
}
}
}

// Component
{
"attributes": {}
}

Mongo:

// model A
{
"_id": ObjectId("1"),
"compo": [
{
"_id": ObjectId("xxx"), // this id doesn't matter
"kind": "CompoGlobalId", // to be converted to collectionName before creating the join in SQL
"ref": ObjectId("1") // actual id of the component
}
]
}

// Component
{
"_id": ObjectId("1"),
}

SQL:

// model A
{
"id": 1,
}

// Component
{
"id": 1,
}

// A_components
{
"id": 1,
"field": "compo",
"order": 1,
"component_type": "compos",
"component_id": 1,
"a_id": 1
}

Media

Media are stored the same way in MongoDB and in SQL. However, the links created between media and entries are stored differently:

In MongoDB, media links are stored on both sides of the relation. The related property is an array of objects targeting the related entries in the media collection, called upload_file. Each object has 3 properties:

  • ref targets a specific media
  • kind targets a specific collection
  • field targets a specific attribute

MongoDB also includes a property in the entries, named like the media attributes of the models, which is either an array or a single ObjectId targeting the media(s).

In SQL databases, an upload_file_morph join table is created, with the following elements:

NameTypeDescription
upload_file_idForeign keyTargets the media
related_idColumnTargets the entry
related_type
Example of media definition in model settings, MongoDB, and SQL databases in Strapi v3
**Models:**
// model A
{
"attributes": {
"pictures": {
"plugin": "upload",
"collection": "file", // multiple files
"via": "related",

}
}
}

// model B
{
"attributes": {
"cover": {
"plugin": "upload",
"model": "file", // single file
"via": "related",
}
}
}

MongoDB:

// model A
{
"_id": ObjectId("1"),
"pictures": [
ObjectId("1"),
]
}

// model B
{
"_id": ObjectId("1"),
"cover": ObjectId("1")
}

// upload_file
{
"_id": ObjectId("1"),
// ...
"related": [
{
"_id": ObjectId("1"), // this id doesn't matter
"kind": "GlobalIdOfA", // needs to be converted to collectionName for SQL
"ref": ObjectId("1"), // id of the A entry
"field": "pictures", // field in A to which the media is linked
},
{
"_id": ObjectId("2"), // this id doesn't matter
"kind": "GlobalIdOfB", // needs to be converted to collectionName for SQL
"ref": ObjectId("1"), // id of the B entry
"field": "cover", // field in B to which the media is linked
}
]
}

SQL:

// model A
{
"id": 1,
}

// model B
{
"_id": 1,
}

// upload_file
{
"id": 1,
}

// upload_file_morph
[
{
"id": 1, // this id doesn't matter
"upload
"related_type": "collectionNameofA", // collectionName of A
"related_id": 1, // id of the A entry
"field": "pictures", // field in A to which the media is linked
"order": 1,
},
{
"id": 2, // this id doesn't matter
"related_type": "collectionNameofB", // needs to be converted to collectionName for SQL
"related_id": 1, // id of the B entry
"field": "cover", // field in B to which the media is linked
"order": 1
}
]

Scalar attributes

There are no structural changes in the scalar attributes between MongoDB and SQL databases.

The only differences to take into account are the following:

  • time stores milliseconds.
  • json is an object in MongoDB. Make sure to stringify it if necessary in the SQL database you target (SQLite or MySQL < 5.6).

Attributes created by Strapi

With the exception of timestamps, attributes created by Strapi are the same in Mongo and SQL databases. This includes the following attributes:

  • published_at
  • created_by
  • updated_by
  • locale

localizations is a manyWay relation (see relations).

Custom use cases

The following table highlights some specific uses cases and their possible resolution:

Use caseResolution
Custom id typesCustom ID types are only used in SQL. No migration is required since the feature is not supported in MongoDB.
Custom indexingCustom indexing is not a supported feature. Equivalent indexes must be created in SQL manually.
Custom join table namesCustom join table names should be taken into account when migrating the relations to find the right table name (see SQL join table names).
Custom DB queriesMigrate to v3 SQL then to Strapi v4, and finally migrate the custom queries with the Query Engine of Strapi v4.