This tool is now considered deprecated. Use contentful-database-importer instead.

Migrate content from a relational database to contentful.com.

This tool allows you to fetch content from your database system and prepare it for the import.

gem install database-exporter

This will install the database-exporter executable on your system.

Once you installed the Gem and created the settings.yml file, you can invoke the tool using:

database-exporter --config-file settings.yml --action

1. Create a YAML file with the required parameters (e.g. settings.yml):

   #PATH to all data, this will create a folder in your current working directory
   data_dir: PATH_TO_ALL_DATA
   
   #Connecting to the database
   adapter: postgres
   host: localhost
   database: database_name
   user: username
   password: username
   
   # Extract data from models:
   mapped:
     tables:
     - :table_name_1
     - :table_name_2
     - :table_name_3
     - :table_name_4
   
   ## MAPPING ##
   mapping_dir: example_data/mapping.json
   contentful_structure_dir: example_data/contentful_structure.json
   
   ## CONVERT
   content_model_json: example_data/contentful_model.json
   converted_model_dir: example_data/contentful_structure.json

2. Create the contentful_structure.json file: First you should create a content model using the Contentful web application. Then you can download the content model using the content management api and use it as the schema for your imports.

    curl -X GET \
         -H 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
         'https://api.contentful.com/spaces/SPACE_ID/content_types' > contentful_model.json

It will create a contentful_model.json file, which you need to transform into the contentful_structure.json file using:

```bash
database-exporter --config-file settings.yml --convert-content-model-to-json
```

The converted content model will be saved as a JSON file in the `converted_model_dir` path.

3. Create content types files: Based on the contentful_structure.json file, create content types json files, which represents your contentful structure, use:

   database-exporter --config-file settings.yml --create-content-model-from-json

   It will extract your content types and store it as a separate JSON file in data_dir/collections directory.

4. After filling in the required parameters to connect to the database, the tables we want to fetch the content from need to be specified. You can skip joining table names, if you do not want to map them to a separate content type.

   database-exporter --config-file settings.yml --list-tables

It will create the table_names.json file with the names of all tables contained in database.

Example:

```javascript
[
  "schema_migrations",
  "skills",
  "comments",
  "images",
  "job_add_skills",
  "users",
  "job_adds",
  "profiles"
]
```

5. Extract data from the database: Create the mapping.json file with the mapped the structure of your database.

   Example structure for user table.

     "Users": {
       "content_type": "User",
       "type": "entry",
       "fields": {
       },
       "links": {
       }
     }

Important: Model names should be the camelized version of the table name, e.g.: my_table should be included as MyTable in the mapping.json file

After defining the structure for each table you want to extract in the JSON file, use:

```bash
database-exporter --config-file settings.yml --extract-to-json
```

This will extract data from tables and store it as JSON. The data_dir/entries directory will be created with subdirectories that represent the data from each table. The sub-directories name depends on the content_type parameter used in the mapping.json file.

6. Mapping data to content types: The mapping.json file contains the structure of your database. All the relationships between the models need to be specified there. A description of how to build those relationships can be found here.

   To begin the mapping procedure, use:

   database-exporter --config-file settings.yml --prepare-json

It will change the structure of files in the entries directory. If the mapping has been done correctly, you can proceed to import the data into Contentful.

7. Use the contentful-importer to import the content to contentful.com

You need to create a configuration file and fill in the following information:

#PATH to all data
data_dir: PATH_TO_ALL_DATA

#Connecting to a database
adapter: postgres
host: localhost
database: database_name
user: username
password: password

# Extract data from models:
mapped:
  tables:
  - :table_name_1
  - :table_name_2
  - :table_name_3

# Mapping
mapping_dir: PATH_TO_MAPPING_FILE/mapping.json
contentful_structure_dir: PATH_TO_CONTENTFUL_STURCTURE_FILE/contentful_structure.json

# Convert
content_model_json: PATH_TO_CONTENT_MODEL/contentful_model.json
converted_model_dir: PATH_TO_CONVERTED_CONTENT_MODEL_FILE/contentful_structure.json

To display all actions use the -h option:

database-exporter -h

This action will create a JSON file including all table names from your database and write them to data_dir/table_names.json. The table names are needed to extract the content from the database.

In the settings.yml file, you need to define the table names that should be exported from the database.

The recommended way to get the table names, is using --list-tables.

After specifying the table names you want to extract in your settings, run the --extract-to-json command. This will save each object from the database into its own JSON file, ready to be transformed and imported.

Path to JSON data: data_dir/entries/content_type_name_defined_in_mapping_json_file

Prepares the generated JSON files so they can be imported to Contentful.

To change the name of a field in the database to a new one in the content type, we need to add a new mapping for that field:

 "fields": {
             "model_name": "new_api_contentful_field_name",
             "model_name": "new_api_contentful_field_name",
             "model_name": "new_api_contentful_field_name"
         },

The following relational associations behave similar to the Active Record associations.

The belongs_to method should only be used if this table contains the foreign key. If the other table contains the foreign key, then you should use has_one instead.

At the beginning and we are looking for type and id of the linked object in file contentful_structure.json. It's very important to maintain consistency for the content type names in mapping.json and contentful_structure.json. The next step is to check if the object has defined a foreign key itself. After that an object with type and ID is created.

Example:

    "Comments": {
        "content_type": "Comments",
        "type": "entry",
        "fields": {
        },
        "links": {
           "belongs_to": [
                          {
                              "relation_to": "ModelName",
                              "foreign_id": "model_foreign_id"
                          }
                      ]
        }
    }

It will assign the associated object and save its ID (model_name + id) in the JSON file.

Result:

{
  "id": "model_name_ID",
  ...
  "job_add_id": {
    "type": "Entry",
    "id": "model_name_3"
  },
}

The has_one method should be used if the other table contains the foreign key. If the current table contains the foreign key, then you should use belongs_to instead.

At the beginning the tool builds a helper file which contains the primary id as key and the foreign id as values. This file lives in data_dir/helpers.

After that we modify only those files whose ID is located in the helper file as a key. Value is written as a Hash value.

Example:

"Users": {
 "content_type": "Users",
 "type": "entry",
 "fields": {
  ...
 },
 "links": {
     "has_one": [
         {
             "relation_to": "ModelName",
             "primary_id": "primary_key_name"
         }
     ]
 }
}

Result:

It will assign the associated object, save his ID (model_name + id) in JSON file.

...
"model_name": {
    "type": "profiles",
    "id": "content_type_id_3"
}

The resulting file will be generated in a similar way as for the has_one relation. At the beginning the tool builds a helper file which contains the primary id as key and the foreign id as values. This file lives in data_dir/helpers.

After that we modify only those files whose ID is located in the helper file as a key. Related objects are written always as an Array.

Example:

"ModelName": {
...
},
"links": {
    "many": [
                {
                    "relation_to": "related_model_name",
                    "primary_id": "primary_key_name"
                }
            ],
        }
}

It will assign the associated objects, save its ID (model_name + id) in JSON file.

Result:

{
  "id": "content_type_id",
  "comments": [
    {
      "type": "related_content_type_name",
      "id": "related_model_name_id"
    },
    {
      "type": "related_content_type_name",
      "id": "related_model_name_id"
    },
    {
      "type": "related_content_type_name",
      "id": "related_model_name_id"
    },
    {
      "type": "related_content_type_name",
      "id": "related_model_name_id"
    }
  ]
}

The resulting file will be generated in a similar way as for the has_one relation. After that we modify only those files whose ID is located in the helper file as a key. Related objects are written always as an Array.

Attributes:

relation_to: Name of the related model, defined in the mapping.json file as a key.
primary_id: Name of the primary key located in the joining table.
foreign_id: Name of the foreign key, located in the joining table. The object with this ID will be added to the mapped object.
through: Name of the joining model.

Example:

"ModelName": {
    ...
    "links": {
        "many_through": [
            {
                "relation_to": "related_model_name",
                "primary_id": "primary_key_name",
                "foreign_id": "foreign_key_name",
                "through": "join_table_name"
            }
        ]
    }
}

It will map the join table and save the objects IDs in the current model.

Result:

  "content_type_name": [
    {
      "type": "content_type_name",
      "id": "related_model_foreign_id"
    },
    {
      "type": "content_type_name",
      "id": "related_model_foreign_id"
    },
    {
      "type": "content_type_name",
      "id": "related_model_foreign_id"
    }
  ]

aggregate_belongs allows to fetch a value from a related model. To add the value, the table must have the foreign_id to the related table. Through this key the object is found and the related data is extracted.

Attributes:

relation_to: Name of the related model, defined in the mapping.json file as a key.
primary_id: Name of the primary key in the model.
field: Name of the attribute, which you want to add.
save_as: Name of the attribute whose value is assigned.

Example:

"links": {
    "aggregate_belongs": [
        {
            "relation_to": "related_model_name",
            "primary_id": "primary_key_name",
            "field": "aggregated_field_name",
            "save_as": "name_of_field"
        }
    ]
}

Result:

{
  "id": "model_name_id",
   "name_of_field": "aggregated_value"
}

It will save the value with the key of the related model. To add has_one value, the table must have the primary_id of the related table.

Attributes:

relation_to: Name of the related model, defined in the mapping.json file as a key.
primary_id: Name of the primary key in the model.
field: Name of the attribute, which you want to add.
save_as: Name of the attribute whose value is assigned.

Example:

"links": {
    "aggregate_has_one": [
        {
          "primary_id": "primary_id",
          "relation_to": "related_model_name",
          "field": "name_of_field_to_aggregate",
          "save_as": "save_as_field_name"
        }
    ]
}

Result:

{
  "id": "model_name_id",
   "name_of_field": "aggregated_value"
}

It will save the value with the key of the related table. To add the has_many value, the related table must have the primary_id of the related model. This will create a new attribute in the model with the type Array.

Example:

"links": {
    "aggregate_many": [
        {
          "primary_id": "primary_id",
          "relation_to": "related_model_name",
          "field": "name_of_field_to_aggregate",
          "save_as": "save_as_field_name"
        }
    ]
}

Result:

{
"id": "model_name_id",
"name_of_field": [
    "aggregated_value1",
    "aggregated_value2",
    "aggregated_value3",
    "aggregated_value4"
    ]
}

It will save the value with the key of the related model. To add the has_many, through value, you need to define the join model which contains the primary_id and foreign_id. Through the foreign_id the desired object can be found.

Attributes:

relation_to: Name of related model, defined in  mapping.json file as a key.
primary_id: Name of primary key located in joining table.
foreign_id: Name of foreign key, located in joining table. Object with this ID will be added mapped object.
through: Name of joining model.

Example:

"links": {
    "aggregate_through": [
        {
           "relation_to": "related_model_name",
           "primary_id": "primary_key_name",
           "foreign_id": "foreign_key_name",
           "through": "join_table_name",
           "field": '"name_of_field_to_aggregate",
           "save_as": "save_as_field_name"
        }
    ]
}

Result:

{
"id": "model_name_id",
 "name_of_field": ["aggregated_value1",
                   "aggregated_value2",
                   "aggregated_value3",
                   "aggregated_value4"
                   ]
}

In many cases you will want to use names for your fields that are not the same as the Content Type name. For example, a User may have a Manager, and the Manager itself is a User.

For these cases you can use the :maps_to property.

Example:

"User": {
    ...
    "links": {
        "belongs_to": [
            {
                "maps_to': "Manager",
                "relation_to": "User",
                "primary_id": "id",
            }
        ]
    }
}

This will work for :belongs_to, :many and :many_through relations.

This file represents our Contentful structure, it defines the remote data types and how they are formed.

Example:

{
    "Comments": {
        "id": "comment",
        "description": "",
        "displayField": "title",
        "fields": {
            "title": "Text",
            "content": "Text"
        }
    },
    "JobAdd": {
        "id": "job_add",
        "description": "Add new job form",
        "displayField": "name",
        "fields": {
            "name": "Text",
            "specification": "Text",
            "Images": {
                "id": "image",
                "link_type": "Asset"
            },
            "Comments": {
                "id": "comments",
                "link_type": "Array",
                "type": "Entry"
            },
            "Skills": {
                "id": "skills",
                "link_type": "Array",
                "type": "Entry"
            }
        }
    }

They keys "Images", "Comments", "Skills" are the equivalent of the content types IDs specified in the file mapping.json.

Example:

"SkillsTableName": {
    "content_type": "Skills",
    "type": "entry",
    "fields": { ... }

IMPORTANT

To create any relationship between tables, we must remember that the content names given in the mapping.json file, must be equal with names in the contentful_structure.json file.

To be able to extract any content you need to create a settings.yml file and define all needed parameters.

Assuming we are going to work with a MySQL, SQLite or PostgreSQL database we need to setup the credentials: Following is the example of connecting to a MySQL database test_import.

adapter: mysql2
user: username
host: localhost
database: test_import
password: secret_password

Available Adapters

PostgreSQL => postgres
MySQL => mysql2
SQlite => sqlite

Before we can start exporting the data from the database, the to be used tables need to be specified. The fastest way to get the names is using the --list-tables action.

Add those to the settings.yml file in the following manner:

mapped:
   tables:

Example:

mapped:
tables:
 - :example_1
 - :example_2
 - :example_3
 - :example_4

There is no need to specify the names of a join table unless you want to save them as a separate content types.

• JSON file with mapping structure that defines relations between models.

mapping_dir: example_path/mapping.json

• JSON file with contentful structure

contentful_structure_dir: contentful_import_files/contentful_structure.json

• Dump JSON file with content types from content model:

converted_model_dir: contentful_import_files/contentful_structure.json