Skip to main content

Node-RED tutorials

Node-RED tutorials for FairCom DB

Tutorial

Description

Tables in Node-RED

Tutorial for using the NodeRED driver for the JSON DB API to create tables and manage data.

Node-RED tutorials for FairCom DB

Node-RED
tutorials
FairCom DB
JSON DB API

How to work with tables in Node-RED

You can work with tables in Node-RED by either following a prebuilt flow or creating a custom flow.

Prebuilt flow

This tutorial shows how to use FairCom DB with Node-RED to create tables, store data, and retrieve data.

The basic structure of the demo is a series of inject nodes configured to send different JSON objects to an HTTPS request node. The response is converted to JSON and sent to a debug node for viewing in the debug tab. These JSON objects contain configuration instructions and data telling FairCom DB what to do. You can edit the inject nodes and open the JSON editor to see the included JSON.

Prerequisites

  • FairCom DB must be installed and running on the same machine that hosts the Node-RED instance.

  • Node-RED installed and running.

Steps

  1. Import flows.json into a Node-RED flow by copying and pasting the json code.

    TutorialNodes.png

  2. Click the createSession inject node to connect to FairCom DB and establish a session.

    An auth token is stored in the global context by the Store Auth Token function node for reuse in the next steps. You should see info about the returned auth token in the debug tab.

  3. Click the createTable inject node.

    This will send JSON describing an "athlete" table and the fields it should include. FairCom DB will create this table. You should see a result object including errorCode: 0 in the debug tab.

  4. Click the createIndex ranking inject node.

    This will send JSON describing an index to add to the table. This index will order records by the ranking field. You should see a result object including errorCode: 0 in the debug tab.

  5. Click the createIndex earnings inject node.

    This will send JSON describing another index to add to the table. This index will ordr records by the earnings field. You should see a result object including errorCode: 0 in the debug tab.

  6. Click the createIndex name inject node.

    This will send JSON describing another index to add to the table. This index ill order records by the name field. The data will be ordered descending, case insensitive, and will filter out records that have "livedpast2000" unset. You should see a result object including errorCode: 0 in the debug tab.

  7. Click the insertRecords inject node.

    This will send JSON describing 6 records of data to be inserted into the table. You should see a result object including errorCode: 0 in the debug tab.

  8. Click the getRecordsByTable inject node.

    This will send JSON requesting records from the table including a table filter that will only include records where ranking is less than or equal to 3. You should see a result object that includes table rows inside a data array. There should be 3 rows that meet the criteria.

  9. Click the getRecordsInKeyRange name inject node.

    This will send JSON requesting records from the table sorted by the name_livedpast2000 index where the indexed value is greater than or equal to "P". You should see a result object that includes table rows inside a data array. There should be 2 rows that meet the criteria.

  10. Click the getRecordsInKeyRange earnings inject node.

    This will send JSON requesting records from the table sorted by the earnings index where the indexed value is less than 2,000,000. You should see a result object that includes table rows inside a data array. There should be 2 rows that meet the criteria.

  11. Click the getRecordsInKeyRange ranking inject node.

    This will send JSON requesting records from teh table sorted by the ranking index where the indexed value is less than or equal to 3. You should see a result object that includes table rows inside a data array. There should be 3 rows that meet the criteria.

  12. Click the getRecordsUsingSQL inject node.

    This will send JSON describing a simple SQL query on the table. you should see a result object that includes table rows inside a data array. There should be 6 rows that meet the criteria.

  13. There is a deleteTable inject node. If you click this, the table will be deleted.

Create your own flow

Create session

  1. Drag an https request node into a flow.

  2. Double-click the request node to edit it.

    NodeEditor.png

    1. Select POST from the Method dropdown menu.

    2. Enter https://localhost:8443/api in the URL textbox.

    3. Check the Enable secure (SSL/TLS) connection checkbox.

    4. Create a new TLS Configuration.

      • Uncheck the Verify server certificate.

      • Create certificates for your FairCom Edge server and configure them.

    5. Click Done to finish configuring the node and to close the node editor.

  3. Drag a json node into the flow.

  4. Connect the output of the https request node to the input of the json node.

    Note

    The FairCom Edge server returns info in JSON format. The json node will translate the response into a native JSON object.

  5. Drag an inject node into the flow.

  6. Connect the output of the inject flow to the input of the https request node.

  7. Double-click the inject node to edit it.

    1. Enter createSession in the Name textbox.

    2. Enter {} JSON in the msg.payload textbox.

    3. Click ... to open the JSON editor.

    4. Paste the following JSON in the JSON editor textbox:

      { "api": "admin", "action": "createSession", "params": { "username": "ADMIN", "password": "ADMIN" }
}

    5. Click Done to close the JSON editor.

    6. Click Done so that now the inject node will send a JSON object that requests a new session from the FairCom Edge server.

      Note

      The response will include an authtoken.

  8. Drag a function node into the flow.

    Note

    We will configure this node to save the authtoken for use in other nodes.

  9. Connect the output of the json node to the input of the function node.

  10. Double-click the function node to edit it.

    1. Paste the following code in the On Message code editor textbox:

      var authToken = msg.payload.result.authToken;
node.status("Got token:"+authToken);
flow.set("tutorial_authToken", authToken);
return msg;

    2. Enter Store Auth Token in the Name textbox.

    3. Click Done to finish configuring the node and to close the node editor.

      Note

      This node will parse the JSON response object and grab the returned authtoken then store it in a flow variable called "tutorial_authToken".

  11. Click Deploy to save the flow and make it ready for use.

  12. Click the corresponding createSession node button to send the request for a new session to the FairCom Edge server.

    Note

    The authtoken for this session will be stored in a flow variable. You can inspect the flow variable via the Context Data tab or add a debug node connected to the output of the json node to see it.

Create a table & index

Create table

  1. Drag an inject node into the flow.

  2. Double-click the inject node to edit it.

    1. Enter createTable in the Name textbox.

    2. Enter {} JSON in the msg.payload textbox.

    3. Click ... to open the JSON editor.

    4. Paste the following JSON in the JSON editor textbox:

      { "api": "db", "authToken": "replaceWithRealToken", "action": "createTable", "params": { "databaseName": "ctreeSQL", "tableName": "athlete", "fields": [ { "name": "name", "type": "varchar", "length": 30 }, { "name": "ranking", "type": "smallint", "nullable": false }, { "name": "birthDate", "type": "date" }, { "name": "playerNumber", "type": "number", "length": 32, "scale": 6 }, { "name": "livedPast2000", "type": "bit" }, { "name": "earnings", "type": "money", "length": 32, "scale": 4 }, { "name": "favoriteSaying", "type": "varchar", "length": 500 } ] }
}

    5. Click Done to close the JSON editor.

    6. Click Done on the inject node.

  3. Drag a function node into the flow.

  4. Connect the output of the createTable inject node to the input of the function mode.

  5. Double-click the function node to edit it.

    1. Enter Add auth token in the Name textbox.

    2. Paste the following code in the On Message code editor textbox:

      var authToken = flow.get("tutorial_authToken")||"BadToken";
msg.payload.authToken = authToken;
return msg;

    3. Click Done to finish configuring the node and to close the node editor.

      Note

      This node will now replace the "authToken" property of the JSON object it receives with the real auth token stored in a flow variable.

  6. Copy the http request node and paste a duplicate of it into the flow.

  7. Connect the output of the Add auth token node to the input of the request node.

  8. Drag in a JSON node and connect the output from the request node to the input of the JSON node.

  9. Drag a debug node into the flow.

  10. Connect the output of the JSON node to the input of the debug node.

    Note

    This will send the results of the request to the debug tab where it is easily inspected.

  11. Click Deploy.

  12. Click the corresponding createTable node button to send the request.

  13. Observe the results in the debug tab in a JSON payload object and an empty table in the FairCom Edge server.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

Create index

  1. Add an Inject node and connect the output of the inject node to the input of the Add auth token node from above.

  2. Enter createIndex in the Name textbox.

  3. Enter {} JSON in the msg.payload textbox.

  4. Click ... to open the JSON editor.

  5. Paste the following JSON in the JSON editor textbox:

    { "api": "db", "authToken": "replaceWithRealToken", "action": "createIndex", "params": { "databaseName": "ctreeSQL", "tableName": "athlete", "indexName": "ranking", "fields": [ { "name": "ranking" } ], "waitToBeLoaded": true }
}

  6. Click Done to close the JSON editor.

  7. Click Done on the inject node.

  8. Click Deploy to configure the inject node to send a JSON object to the FairCom Edge server instructing it to add an index to the created table.

  9. Click the corresponding createIndex node button to send the request.

  10. Observe the results in the debug tab in a response object and a new index on ranking in the created table.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

Add & query records

Add Records
Figure 1. Flow of nodes
Flow of nodes


  1. Drag an inject node into the flow.

  2. Connect the output of the inject node to the input of the Add auth token node that the createTable node feeds into.

  3. Double-click the inject node to edit it.

    1. Enter insertRocords in the Name textbox.

    2. Enter {} JSON in the msg.payload textbox.

    3. Click ... to open the JSON editor.

    4. Paste the following JSON into the JSON editor textbox:

      "api": "db", "authToken": "<valid authToken>", "action": "insertRecords", "params": { "databaseName": "ctreeSQL", "tableName": "athlete", "dataFormat": "objects", "sourceData": [ { "name": "Michael Jordan", "ranking": 1, "birthDate": "19630217", "playerNumber": 23, "livedPast2000": true, "earnings": 1700000000, "favoriteSaying": "There is no 'i' in team but there is in win." }, { "name": "Babe Ruth", "ranking": 2, "birthDate": "18950206", "playerNumber": 3, "livedPast2000": false, "earnings": 800000, "favoriteSaying": "Every strike brings me closer to the next home run." }, { "name": "Muhammad Ali", "ranking": 3, "birthDate": "19420117", "playerNumber": 1, "livedPast2000": true, "earnings": 60000000, "favoriteSaying": "Float like a butterfly, sting like a bee." }, { "name": "Pele", "ranking": 4, "birthDate": "19401023", "playerNumber": 10, "livedPast2000": true, "earnings": 115000000, "favoriteSaying": "Everything is practice." }, { "name": "Wayne Gretzky", "ranking": 5, "birthDate": "19610126", "playerNumber": 99, "livedPast2000": true, "earnings": 1720000, "favoriteSaying": "You miss 100 percent of the shots you never take." }, { "name": "Michael Schumacher", "ranking": 6, "birthDate": "19690103", "playerNumber": 1, "livedPast2000": true, "earnings": 990000000, "favoriteSaying": "Once something is a passion, the motivation is there." } ] }
}

    5. Click Done to close the JSON editor.

    6. Click Done to save the inject node.

  4. Click Deploy to configure the inject node to send a JSON object to the FairCom Edge server instructing it to insert 6 records into the table we created.

  5. Click the corresponding createIndex node button to send the request.

  6. Observe the results in the debug tab.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

Query data
Figure 2. Flow of nodes
Flow of nodes


  1. Drag an inject node into the flow.

  2. Connect the output of the inject node to the input of the Add auth token node that the createTable node feeds into.

  3. Double-click the inject node to edit it.

    1. Enter getRecordsStartingAtKey in the Name textbox.

    2. Enter {} JSON in the msg.payload textbox.

    3. Click ... to open the JSON editor.

    4. Paste the following JSON in the JSON editor textbox:

      { "api": "db", "authToken": "replaceWithRealToken", "action": "getRecordsStartingAtKey", "params": { "databaseName": "ctreeSQL", "tableName": "athlete", "indexFilter": { "indexName": "ranking", "operator": ">=", "indexFields": [ { "fieldName": "ranking", "value": 3 } ] }, "reverseOrder": false }
}

    5. Click Done to close the JSON editor.

    6. Click Done to save the inject node.

  4. Click Deploy to configure the inject node to send a JSON object to the FairCom Edge server instructing it to return records where "ranking" is equal to or greater than three.

  5. Click the corresponding createIndex node button to send the request.

  6. Observe the results in the debug tab.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

  7. Click the "msg.payload" object in the debug tab.

  8. Expand the "result" property.

  9. Expand the "data" property to view the list of four objects (numbered 0-3).

  10. Expand the 0 object to view the first row returned by the query.

    QueryData.png

Enable HTTP on port 8080

This FairCom tutorial uses HTTP to communicate with the server on port 8080. Because HTTP is insecure, this port is typically disabled.

Do the following to enable the HTTP protocol on port 8080:

  1. Shut down the FairCom server.

  2. Edit the services.json file located in the <faircom>/config folder.

  3. Find the listener service named "http8080".

  4. Change "enabled":false to "enabled":true.

  5. Restart the FairCom server.

The modified listener configuration object in services.json should look something like this:

{
 "serviceName": "http8080",
 "description": "Port 8080 using insecure HTTP protocol...",
 "port": 8080,
 "protocol": "http",
 "enabled": true
}

flows.json

[
    {
        "id": "ea0150321fed15cd",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "createSession",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"admin\",\"action\":\"createSession\",\"params\":{\"username\":\"ADMIN\",\"password\":\"ADMIN\"}}",
        "payloadType": "json",
        "x": 110,
        "y": 100,
        "wires": [
            [
                "c1b8bb6fd8253247"
            ]
        ]
    },
    {
        "id": "c1b8bb6fd8253247",
        "type": "http request",
        "z": "653bc040748e5959",
        "name": "Edge Server",
        "method": "POST",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "https://localhost:8443/api",
        "tls": "a3d976ad2c50f845",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 510,
        "y": 100,
        "wires": [
            [
                "99f7c73dc5f79572"
            ]
        ]
    },
    {
        "id": "99f7c73dc5f79572",
        "type": "json",
        "z": "653bc040748e5959",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 650,
        "y": 100,
        "wires": [
            [
                "62fcac0c8e6e79a9",
                "1658e30be3d4f4ec"
            ]
        ]
    },
    {
        "id": "62fcac0c8e6e79a9",
        "type": "debug",
        "z": "653bc040748e5959",
        "name": "Session Response",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 810,
        "y": 60,
        "wires": []
    },
    {
        "id": "1658e30be3d4f4ec",
        "type": "function",
        "z": "653bc040748e5959",
        "name": "Store Auth Token",
        "func": "var authToken = msg.payload.result.authToken;\nnode.warn(\"Got token:\"+authToken);\nglobal.set(\"localhost_authToken\", authToken);\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 810,
        "y": 100,
        "wires": [
            []
        ]
    },
    {
        "id": "fe16dae5c7750aa3",
        "type": "function",
        "z": "653bc040748e5959",
        "name": "Add Auth Token",
        "func": "var authToken = global.get(\"localhost_authToken\")||\"BadToken\";\n//node.warn(\"Loaded token:\"+authToken);\nmsg.payload.authToken = authToken;\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 340,
        "y": 200,
        "wires": [
            [
                "28f974174095d14f"
            ]
        ]
    },
    {
        "id": "28f974174095d14f",
        "type": "http request",
        "z": "653bc040748e5959",
        "name": "Edge Server",
        "method": "POST",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "https://localhost:8443/api",
        "tls": "a3d976ad2c50f845",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 510,
        "y": 200,
        "wires": [
            [
                "05cdc0b50788bc62"
            ]
        ]
    },
    {
        "id": "05cdc0b50788bc62",
        "type": "json",
        "z": "653bc040748e5959",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 650,
        "y": 200,
        "wires": [
            [
                "479bab1ccc153c90"
            ]
        ]
    },
    {
        "id": "479bab1ccc153c90",
        "type": "debug",
        "z": "653bc040748e5959",
        "name": "Action Response",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 810,
        "y": 200,
        "wires": []
    },
    {
        "id": "b4d81caf11d57f7b",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "createTable",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"createTable\",\"authToken\":\"replaceWithValidToken\",\"requestId\":\"00000001\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"fields\":[{\"name\":\"name\",\"type\":\"varchar\",\"length\":30},{\"name\":\"ranking\",\"type\":\"smallint\",\"nullable\":false},{\"name\":\"birthDate\",\"type\":\"date\"},{\"name\":\"playerNumber\",\"type\":\"number\",\"length\":32,\"scale\":6},{\"name\":\"livedPast2000\",\"type\":\"bit\"},{\"name\":\"earnings\",\"type\":\"money\",\"length\":32,\"scale\":4},{\"name\":\"favoriteSaying\",\"type\":\"varchar\",\"length\":500}]}}",
        "payloadType": "json",
        "x": 110,
        "y": 200,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "edd0dfbf7d2ff842",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "createIndex ranking",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"createIndex\",\"authToken\":\"replaceWithValidToken\",\"apiVersion\":\"1.0\",\"requestId\":\"00000002\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"indexName\":\"ranking\",\"fields\":[{\"name\":\"ranking\"}],\"waitToBeLoaded\":true}}",
        "payloadType": "json",
        "x": 130,
        "y": 240,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "ef05eb6822a7e617",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "createIndex earnings",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"createIndex\",\"authToken\":\"replaceWithValidToken\",\"apiVersion\":\"1.0\",\"requestId\":\"00000003\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"indexName\":\"earnings\",\"fields\":[{\"name\":\"earnings\"}],\"waitToBeLoaded\":true}}",
        "payloadType": "json",
        "x": 140,
        "y": 280,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "3621f8accd75c4eb",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "createIndex name",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"createIndex\",\"authToken\":\"replaceWithValidToken\",\"apiVersion\":\"1.0\",\"requestId\":\"00000004\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"indexName\":\"name_livedpast2000\",\"fields\":[{\"name\":\"name\",\"caseInsensitive\":true,\"sortDescending\":true,\"reverseCompare\":false}],\"conditionalExpression\":\"livedpast2000 == 1\",\"unique\":false,\"immutableKeys\":false,\"waitToBeLoaded\":true,\"filename\":\"admin_athlete_name_livedpast2000\",\"collectStats\":true,\"compression\":\"auto\"},\"responseOptions\":{\"binaryFormat\":\"hex\",\"dataFormat\":\"objects\",\"numberFormat\":\"string\"},\"debug\":\"none\"}",
        "payloadType": "json",
        "x": 130,
        "y": 320,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "bcd6998317d66643",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "insertRecords",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"insertRecords\",\"authToken\":\"replaceWithValidToken\",\"apiVersion\":\"1.0\",\"requestId\":\"00000005\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"dataFormat\":\"objects\",\"sourceData\":[{\"name\":\"Michael Jordan\",\"ranking\":1,\"birthDate\":\"19630217\",\"playerNumber\":23,\"livedPast2000\":true,\"earnings\":1700000000,\"favoriteSaying\":\"There is no 'i' in team but there is in win.\"},{\"name\":\"Babe Ruth\",\"ranking\":2,\"birthDate\":\"18950206\",\"playerNumber\":3,\"livedPast2000\":false,\"earnings\":800000,\"favoriteSaying\":\"Every strike brings me closer to the next home run.\"},{\"name\":\"Muhammad Ali\",\"ranking\":3,\"birthDate\":\"19420117\",\"playerNumber\":1,\"livedPast2000\":true,\"earnings\":60000000,\"favoriteSaying\":\"Float like a butterfly, sting like a bee.\"},{\"name\":\"Pele\",\"ranking\":4,\"birthDate\":\"19401023\",\"playerNumber\":10,\"livedPast2000\":true,\"earnings\":115000000,\"favoriteSaying\":\"Everything is practice.\"},{\"name\":\"Wayne Gretzky\",\"ranking\":5,\"birthDate\":\"19610126\",\"playerNumber\":99,\"livedPast2000\":true,\"earnings\":1720000,\"favoriteSaying\":\"You miss 100 percent of the shots you never take.\"},{\"name\":\"Michael Schumacher\",\"ranking\":6,\"birthDate\":\"19690103\",\"playerNumber\":1,\"livedPast2000\":true,\"earnings\":990000000,\"favoriteSaying\":\"Once something is a passion, the motivation is there.\"}]}}",
        "payloadType": "json",
        "x": 110,
        "y": 360,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "d3ae7be80bcaca40",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "getRecordsByTable",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"getRecordsByTable\",\"authToken\":\"replaceWithValidToken\",\"requestId\":\"00000006\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"tableFilter\":\"ranking <= 3\"}}",
        "payloadType": "json",
        "x": 130,
        "y": 400,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "ed1c53207d21f60e",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "getRecordsInKeyRange name",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"getRecordsInKeyRange\",\"authToken\":\"replaceWithValidToken\",\"requestId\":\"00000007\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"indexFilter\":{\"indexName\":\"name_livedpast2000\",\"indexFieldFilters\":[{\"fieldName\":\"name\",\"operator\":\">=\",\"value\":\"P\"}]}},\"debug\":\"none\"}",
        "payloadType": "json",
        "x": 160,
        "y": 440,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "ba40a0816a9a329c",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "getRecordsInKeyRange earnings",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"getRecordsInKeyRange\",\"authToken\":\"replaceWithValidToken\",\"apiVersion\":\"1.0\",\"requestId\":\"00000008\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableName\":\"athlete\",\"indexFilter\":{\"indexName\":\"earnings\",\"indexFieldFilters\":[{\"fieldName\":\"earnings\",\"operator\":\"<\",\"value\":2000000}]}}}",
        "payloadType": "json",
        "x": 170,
        "y": 480,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "abe9187206188927",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "getRecordsInKeyRange ranking",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"getRecordsInKeyRange\",\"authToken\":\"replaceWithValidToken\",\"requestId\":\"00000009\",\"apiVersion\":\"1.0\",\"params\":{\"databaseName\":\"ctreeSQL\",\"ownerName\":\"admin\",\"tableName\":\"athlete\",\"tableFilter\":\"name < \\\"W\\\"\",\"indexFilter\":{\"indexName\":\"ranking\",\"indexFieldFilters\":[{\"fieldName\":\"ranking\",\"operator\":\"<=\",\"value\":3}]},\"returnCursor\":false,\"reverseOrder\":false,\"skipRecords\":0,\"maxRecords\":20},\"responseOptions\":{\"binaryFormat\":\"hex\",\"dataFormat\":\"objects\",\"numberFormat\":\"string\",\"includeFields\":[\"name\",\"ranking\"],\"excludeFields\":[]},\"debug\":\"none\"}",
        "payloadType": "json",
        "x": 170,
        "y": 520,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "eedb3bf1c2a35d8b",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "getRecordsUsingSQL",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"getRecordsUsingSQL\",\"authToken\":\"replaceWithValidToken\",\"params\":{\"databaseName\":\"ctreeSQL\",\"sql\":\"SELECT * from athlete\"},\"requestId\":\"00000010\"}",
        "payloadType": "json",
        "x": 140,
        "y": 560,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "c2a2e3314397a923",
        "type": "inject",
        "z": "653bc040748e5959",
        "name": "deleteTable",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"api\":\"db\",\"action\":\"deleteTables\",\"authToken\":\"replaceWithValidToken\",\"params\":{\"databaseName\":\"ctreeSQL\",\"tableNames\":[\"athlete\"]},\"requestId\":\"00000011\"}",
        "payloadType": "json",
        "x": 110,
        "y": 600,
        "wires": [
            [
                "fe16dae5c7750aa3"
            ]
        ]
    },
    {
        "id": "181337a1c5aa3391",
        "type": "comment",
        "z": "653bc040748e5959",
        "name": "Instructions",
        "info": "This example flow assumes you have FairCom\nEdge installed and running on the same\nmachine that is hosting this NodeRED instance.\nIf you wish to use FairCom Edge on another\ndevice, adjust both HTTP Request nodes.\nThey are named \"Edge Server\".\n\nTo use this tutorial, click the inject\nbuttons once each in order. The results will\nbe shown in the debug tab.\n\nFor all of the actions that retrieve data,\n(that start with get...) expand the json\nobject returned. Look in result->data for the\nrows returned from the database.",
        "x": 90,
        "y": 40,
        "wires": []
    },
    {
        "id": "a3d976ad2c50f845",
        "type": "tls-config",
        "name": "Edge tls",
        "cert": "",
        "key": "",
        "ca": "",
        "certname": "",
        "keyname": "",
        "caname": "",
        "servername": "",
        "verifyservercert": false,
        "alpnprotocol": ""
    }
]

Use Node-RED with FairCom DB to create tables, store data, and retrieve data

Node-REDFairCom DBcreate tablesstore dataretrieve datatutorial