Importing Data

{
  "users": [...],
  "records": [...]
}

General Information

Here are a couple of details that you need to know before importing data:

• Deploy your Database Tables before importing.
• The system will change the fields and keep existing relations when importing data. If your relations are defined in properties, you must first describe them in Database Table files.
• If you test your imports and need to clean Instance data, use the command. Note that this command will work only on staging Instances.
• When you run import twice, it will try to match the data from the first import without removing existing changes. Example:
  1. You have a record with properties: ,
  2. First import adds new with JSON:
  3. User sets attributes of , so has the following properties:
  4. The second import with the same file changes the object to:
• Import will only do basic validations, such as the existence of properties and type validation. It doesn't check any of the validations defined on forms.

Data Import with the command line interface (CLI)

With insites-cli data, you can export/import Instance data (information entered by the users).

The Insites CLI tool provides the command for uploading and importing data from a JSON file or ZIP archive with CSV files. It accepts one option:

--path (short: -p): A path to a JSON or ZIP file to be imported

Under the hood, the CLI uses the GraphQL import API described in the next section. However, using the CLI provides some significant benefits:

• Uploading files from the disk
• Leveraging the CLI's authentication mechanism (i.e. you don't need to send your authentication token manually).

Input format

The required data format is a JSON file with users and records keys that have an array of maps. Each collection must be in the same format as a corresponding GraphQL mutation. Under the hood, import is a set of mutations: and . When importing data using the CLI, the file should contain data in a format that matches the following structure:

{
  "users": [<UserImport>],
  "records": [<CustomizationImport>],
}

Example

Example data import file

{
  "users": [
    {
      "id": 1000,
      "email": "[email protected]",
      "first_name": "Tom",
      "slug": "tom"
    },
    {
      "id": 1001,
      "email": "[email protected]",
      "first_name": "mike",
      "slug": "mike",
      "created_at": "2010-10-10 10:00:00",
      "profiles": [
        {
          "type_name": "student",
          "id": 2001,
          "properties": {
            "age": 21
          },
          "attachments": [
            {
              "name": "document",
              "url": "files/document.pdf"
            }
          ],
          "images": [
            {
              "name": "avatar",
              "url": "files/avatar.png"
            }
          ]
        }
      ]
    }
  ],
  "records": [
    {
      "id": 1,
      "user_id": 1000,
      "type_name": "house",
      "customizable_id": 1000,
      "customizable_type": "User",
      "properties": {
        "color": "red"
      }
    },
    {
      "id": 2,
      "user_id": 1001,
      "type_name": "house",
      "customizable_id": 1001,
      "customizable_type": "User",
      "properties": {
        "color": "green",
        "buyer_id": 1000,
        "beach_house_id": 1
      },
      "addresses": [
        {
          "name": "foo_address",
          "address": "Warsaw",
          "postcode": "01-001"
        }
      ]
    },
    {
      "id": 3,
      "user_id": 1001,
      "type_name": "house",
      "properties": {
        "buyer_id": 1000,
        "beach_house_id": 2
      },
      "attachments": [
        {
          "name": "driver_license",
          "url": "files/license.pdf"
        }
      ],
      "images": [
        {
          "name": "headshot",
          "url": "files/headshot.png"
        }
      ]
    }
  ]
}

Note: When creates a user, it applies and , but it ignores . Write profiles in a second step, for example with the mutation.

Note: Only a bcrypt hash works as . Any other value breaks the password check for that person. To move passwords from another platform, see Move People and Their Passwords to Insites.

To import the data from a file, run the following command in the terminal:

insites-cli data import --path=data.json staging-instance

Data Import with GraphQL mutations

It is possible to import data using GraphQL mutations. This method gives you more flexibility and even allows you to create your import tool as a website feature. Please remember to handle authentication properly.

There are two main mutations for importing: and . You can create a page that will import sent data.

app/graphql/import_data.graphql

mutation import_data(
    $users: [UserImport!]!,
    $records: [CustomizationImport!]!
  ) {
  import_users(users: $users) { ids }
  import_models(models: $records) { ids }
}

app/views/pages/import_data.json.liquid

---
method: post
---
{% graphql res = "import_data",
  users: context.params.users,
  records: context.params.records
%}

{{ res }}

curl -d @transformed.json 
  -H "Content-Type: application/json" 
  -X POST https://example.com/import_data.json

Data Import with raw import API

Our CLI uses the HTTP API to import a data file. You can use it in your own tool.
Please note that you need to provide your authentication token in the HTTP Authorization header of the request.

curl -F "import[data][email protected]" 
  -H "Authorization: Token [YOUR API TOKEN]" 
  -X POST https://example.com/api/app_builder/imports

Data import using CSV files

It is possible to import data using CSV files — this is the most straightforward and performant way of data import.

insites-cli data import --zip --path=archive.zip staging-instance

CSV data format

Inside the ZIP archive, there should be CSV files with data to be imported. For example:

records.csv
users.csv
user_profiles.csv

Some reminders:

• Before import, your Tables need to be deployed.
• CSV files only support comma-separated values. If in doubt, it might be a good idea to use a linter like CSVlint to check the file.
• A well-formatted file has a header with names of columns available for the given type and one or more rows of data.

Example file for Records

id,user_id,created_at,updated_at,properties,model_schema
1,,2020-03-13 11:22:54.825836,2020-03-17 05:32:41.477453,"{""name"": ""Buy milk"", ""done"": ""false""}",todo
2,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Do code reviews"", ""done"": ""true""}",todo
3,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Fix a tpyo"", ""done"": ""false""}",todo

This file will import Records matching the Table for 'todo'. If you notice, there's a typo in the object with id=3. To fix it, you can edit the file like this:

id,user_id,created_at,updated_at,properties,model_schema
1,,2020-03-13 11:22:54.825836,2020-03-17 05:32:41.477453,"{""name"": ""Buy milk"", ""done"": ""false""}",todo
2,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Do code reviews"", ""done"": ""true""}",todo
3,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Fix a typo"", ""done"": ""true""}",todo

And now, importing will update the data of matching rows. Even if you add new rows, the system will only add these new rows; the system will not delete existing rows. Additionally, not all columns are required, so the code in the example below will work as well:

properties,table
"{""name"": ""Buy milk"", ""done"": ""false""}",todo
"{""name"": ""Do code reviews"", ""done"": ""true""}",todo

Example file for Users

id,email,encrypted_password,created_at,updated_at,name,authentication_token,slug,time_zone,first_name,middle_name,last_name,external_id,properties
1,[email protected],abcdefghijaklmnoprst111,2020-03-11 05:44:13.302432,2020-03-11 05:44:13.302432,[email protected],xyz123fhfhfh,api-example-com,,John,A,Smith,af56f347-ecdd-4636-8cf6-76aa58f89b61,{}

It is important to note that will only work if you import data exported from our system through other means. So setting passwords this way will not work: they will be saved, but the system will not accept them when checking through the application code. It would be unsafe to set passwords this way, so it's better to use a random value here and require users to change their passwords after migration.

Example file for UserProfiles

id,user_id,proile_type,created_at,updated_at,properties,instance_profile_type_name
1,2,"buyer",2020-03-11 05:44:13.302432,2020-03-11 05:44:13.302432,"{""name"": ""Our Services""}","modules/menu/buyer"