Manually Perform a Data Migration

Data migrations are essential when updating your data model to ensure consistency and integrity. This tutorial walks you through manually performing a data migration using Onyx Database's Stream API.

Steps to Manually Perform a Data Migration (Onyx Cloud Database)

1

Declare the Schema including the Account and Invoice Tables

Use the provided JSON schema to declare your entities in the Onyx Cloud Database.

Define your data model using the JSON schema. This includes specifying tables, attributes, identifiers, and relationships.

1{
2  "tables": [
3    {
4      "name": "Account",
5      "identifier": {
6        "name": "accountId",
7        "generator": "Sequence",
8        "type": "Long"
9      },
10      "partition": "",
11      "attributes": [
12        {
13          "name": "accountId",
14          "type": "Long",
15          "maxSize": null,
16          "isNullable": false
17        },
18        {
19          "name": "accountHolderName",
20          "type": "String",
21          "maxSize": 255,
22          "isNullable": false
23        },
24        {
25          "name": "accountName",
26          "type": "String",
27          "maxSize": 255,
28          "isNullable": true
29        }
30      ],
31      "relationships": [
32        {
33          "name": "invoices",
34          "inverse": "account",
35          "inverseClass": "Invoice",
36          "type": "OneToMany",
37          "fetchPolicy": "Lazy",
38          "cascadePolicy": "None"
39        }
40      ],
41      "indexes": []
42    },
43    {
44      "name": "Invoice",
45      "identifier": {
46        "name": "invoiceId",
47        "generator": "Sequence",
48        "type": "Long"
49      },
50      "partition": "",
51      "attributes": [
52        {
53          "name": "invoiceId",
54          "type": "Long",
55          "maxSize": null,
56          "isNullable": false
57        },
58        {
59          "name": "invoiceDate",
60          "type": "Date",
61          "maxSize": null,
62          "isNullable": true
63        },
64        {
65          "name": "dueDate",
66          "type": "Date",
67          "maxSize": null,
68          "isNullable": true
69        },
70        {
71          "name": "amount",
72          "type": "Double",
73          "maxSize": null,
74          "isNullable": true
75        },
76        {
77          "name": "notes",
78          "type": "String",
79          "maxSize": 500,
80          "isNullable": true
81        }
82      ],
83      "relationships": [
84        {
85          "name": "payments",
86          "inverse": "invoice",
87          "inverseClass": "Payment",
88          "type": "OneToOne",
89          "fetchPolicy": "Eager",
90          "cascadePolicy": "Save"
91        },
92        {
93          "name": "account",
94          "inverse": "invoices",
95          "inverseClass": "Account",
96          "type": "ManyToOne",
97          "fetchPolicy": "Lazy",
98          "cascadePolicy": "None"
99        }
100      ],
101      "indexes": []
102    },
103    {
104      "name": "Payment",
105      "identifier": {
106        "name": "paymentId",
107        "generator": "Sequence",
108        "type": "Long"
109      },
110      "partition": "",
111      "attributes": [
112        {
113          "name": "paymentId",
114          "type": "Long",
115          "maxSize": null,
116          "isNullable": false
117        },
118        {
119          "name": "amount",
120          "type": "Double",
121          "maxSize": null,
122          "isNullable": true
123        }
124      ],
125      "relationships": [
126        {
127          "name": "invoice",
128          "inverse": "payments",
129          "inverseClass": "Invoice",
130          "type": "ManyToOne",
131          "fetchPolicy": "Lazy",
132          "cascadePolicy": "None"
133        }
134      ],
135      "indexes": []
136    }
137  ],
138  "revisionDescription": "Initial Schema with updated Account and Invoice entities"
139}
140
  • Ensure your schema defines all required attributes, including types, sizes, and nullability.
  • The identifier field is mandatory for each table and must include a unique name and a supported generator type (e.g., Sequence or UUID).
  • Relationships and indexes are optional but improve performance and enforce data integrity.
2

Create a Script to Seed Some Test Data

Write a JavaScript script to populate the cloud database with initial test data. This will help in verifying the migration process.
1const account = {
2  accountHolderName: "Timbob's Lawn Care",
3  accountName: "Timbob's Lawn Care"
4};
5
6const savedAccount = await db.save("Account", account);
7
8const marchLawnInvoice = {
9  invoiceDate: new Date("2016-03-01"),
10  dueDate: new Date("2016-04-01"),
11  notes: "Why did we need to mow your lawn. It's basically a dirt field.",
12  amount: 44.32,
13  account: { accountId: savedAccount.accountId }
14};
15
16const savedMarchInvoice = await db.save("Invoice", marchLawnInvoice);
17
18const aprilLawnInvoice = {
19  invoiceDate: new Date("2016-03-01"),
20  dueDate: new Date("2016-04-01"),
21  notes: "It's April, your lawn should be growing by now.",
22  amount: 44.32,
23  account: { accountId: savedAccount.accountId }
24};
25
26const savedAprilInvoice = await db.save("Invoice", aprilLawnInvoice);
27
28const marchLawnCarePayment = {
29  amount: 44.32,
30  invoice: { invoiceId: savedMarchInvoice.invoiceId }
31};
32
33await db.save("Payment", marchLawnCarePayment);
34
  • Ensure all required properties are populated before saving the entity.
  • Use appropriate types for each attribute as defined in the schema (e.g., String, Date, Long).
  • If an entity with the same identifier already exists, Onyx Cloud Database will update the record instead of inserting a new one.
Prior to running this script, ensure the database is deleted so that we can start with a clean slate.
If the script runs successfully you have created an account with several invoices.
3

Define a Query to Stream

The Query specifies what records to iterate through. The Stream API requires a Query. This Query is designed to pull all Accounts.

Create a query to fetch all Account entities that need to be migrated.

1db.from("Account")
2
4

Use the Stream API with async Callbacks

The Stream API allows for asynchronous processing of query results using a callback function. This updated example demonstrates how to use an async callback with the stream method to process each record sequentially.

Within the callback, the latest Invoice is fetched for each Account to update the balanceDue. The updated Invoice is then saved back to the database.

1const stream = await db.from('Account')
2    .onItem(async (mapValue, action) => {
3        if (mapValue.balanceDue !== undefined) {
4            const balanceDue = mapValue.balanceDue;
5            const accountId = mapValue.accountId;
6
7            const invoices = await db.from('Invoice')
8                .where(notNull('invoiceId'))
9                .and(eq('account.accountId', accountId))
10                .orderBy(desc('invoiceDate'))
11                .limit(1)
12                .list();
13
14            const latestInvoice = invoices.length > 0 ? invoices[0] : null;
15
16            if (latestInvoice) {
17                latestInvoice.amount = balanceDue;
18                await db.save('Invoice', latestInvoice);
19            }
20        }
21    })
22    .stream(true, false);
23

Important Notes

  • Onyx does not differentiate between insert and update operations. If an entity with the same primary key exists, it will be updated.
  • For better performance, consider batching save operations where applicable.

Troubleshooting

  • Stream Canceled: The stream may unexpectedly terminate before it has iterated through all of the items. This can be prevented by ensuring you have a strong reference to the stream handle.
  • Invalid Identifier: Verify that the primary key has the correct data type.
  • Null Attribute: Check that all non-nullable attributes are assigned values before saving the entity.

Next Steps

Now that you know how to manually perform a data migration, you can explore advanced topics: