You probably are receiving many orders per day, so this copy process should be executed periodically to keep the database updated. For this reason, to be efficient, the process should load only the orders created since the last copy.
We are going to implement this copy process in a few minutes using YepCode and some of its features:
First, we'll create a new process using the Shopify orders to MySQL recipe. To do this, from the recipe page, click on Clone to YepCode button and then select your YepCode team. Click on Create and you'll be redirected to the process page.
In the process page you can see recipe source code. Looking along it you can see four blocks of comments, each one containing a TODO statement. Let's review all of them! From top to bottom:
You'll need to create a credential to be able to connect to Shopify API. So this TODO aims to create one.
As the comment says, you need to provide your Shopify access token by headers to be able to make requests to their API.
You can create the credentials from the right sidebar, by clicking on the add button in the credentials section. Then select the Axios credential and fill in the needed info.
Here you have an example of Shopify credential creation:
Once you have created it, replace the credential name in the source code, just under this block of comments. For this example, you should replace "your-shopify-credential-name" by "my-ecommerce-shopify".
You may want to customize your request to fetch only the orders you need. For now, you don't need to change anything here. This request will fetch all the orders. We are going to come here later.
The same as for Shopify, you need a MySQL credential to be able to connect to your MySQL.
Here you can see one example with the needed parameters for the MySQL credential.
As in the previous case, replace the credential name in the process source code. For this example, replace "your-mysql-credential-name" by "my-ecommerce-mysql".
You need to modify the insert statement with your table name and store the desired item properties in your query.
An example of the change in the code would be:
Now, after these changes, you'll be able to copy all of your orders data to our MySQL database each time the process is executed.
However, as we said, there will be new orders each day, and we don't want to re-copy all orders each time we want to insert the new ones.
For this reason, we'll turn this process into an incremental copy process, which copies only the orders generated since the last process execution date.
To fit this requirement, you need to store the copy's execution time and retrieve it in the next execution to fetch only the orders after that moment.
You could use an auxiliary control table in your database, but YepCode offers a more agile way to reach this. This is using the data store. As we said, it allows to CRUD key-value pairs in each execution of the copy process.
So, in each execution, we'll retrieve the stored date of the previous execution, keep it in a variable and then update the same entry with the current date. Translating it to code, it looks like the code below, which you can paste at the beginning of your process source code:
This is the moment when we come back to the second TODO, where it was not required to do changes before.
Now, you need to customize the Shopify API request to fetch only the orders created after the last execution date. For this, you need to add the "created_at_max" query parameter, and set its value with the variable created in the previous step. The resulting code would be:
And that's all, now you have configured an incremental copy process!!
You can run it manually, schedule it or execute it via webhooks! Each time the process is executed it will only copy the orders generated after the last execution!
You have an incremental copy process, which was implemented in a few minutes!If needed, you could continue iterating this process to fit more needs. For example, you could send an email, telegram message, etc. with some info about the process each time it is executed. For this case, that info could be the total order price sum of the copied orders. We've done this for Shopify orders to MySQL, but this can be done for any of the services and APIs you can find in our recipes page! You can browse there and find if there are more recipes which fit one need you have.
Thank you for reading :) and...