Connect to an internet database
2017-01-30

Creating your own database, connectivity, and maintenance can be a hassle. We surveyed the internet for the clearest, cleanest solution, and RestDB.io just gets it done. Model your information quickly. The data management application, schema and REST API are instantly available. With RestDB, you can collaborate and work with data in a workspace on your desktop, laptop or smartphone, quickly and securely accessing your data.

Here's how we did it.

RestDB Setup

We created a free trial account with RestDB.io, then created two tables for the example: car-make and car. The idea here is to have a simple relationship (car-make name in car) between the tables. We also entered a few rows in each table.

Car-make contents:

Car contents:

The last step before we leave the RestDB.io site is to pick up the API key.
You can get there in database settings, on a URL like

https://restdb.io/account/databases/[YOUR_DATABASE]/api

Script Setup

Now that we picked up the API key, we can enter it in a DragToCode connector.
A direct route is https://www.dragtocode.com/connectors_view?action=Database

Now we can grab a few blocks to select data from RestDB. Here's the script:

The blocks lay out like this:

  • a basic select block, retrieving the year 2016 rows (I leave the order socket blank)
  • get the first row (of the two) into the row variable
  • get the model name from the retrieved row


When these blocks are run, the log looks like this:

  • 2016 value type gets evaluated first, as part of the filter
  • Filter gets sent to RestDB select, returning two rows (JSON showing)
  • We store the JSON data
  • Grab the model from the first row

Variations on a theme

Being able to select is great, but you also want to add, delete, and update records. Inserting into a simple table looks something like this:

We're mapping the properties on the left with the values on the right in the first block, then inserting into a new block and returning the newly inserted ID number.

HOWEVER.

That's not how we set up the tables at the start of this post, and inserting this way will leave the make field agonizingly blank. The tables were set up with a relationship, so it's one step more complex.

We have to set the ID of the make as an object instead of just setting it's name. From there, it's the same process of mapping the properties on the left with the values on the right in the first block, then inserting into a new block and returning the newly inserted ID number.

Updating a record requires two things: the ID of the record you want to update and the data as an object. Below, we go through the steps to select the Corvette from RestDB, grab the first row from the reply, create the Impala data, and send the update back to RestDB:

Not surprising, delete is much the same as update, but we show off an "or" condition block in the selection. This time, we select either the newly-updated Impala or the Corvette for destruction, grabbing the first (and only) row, and send it to the digital scrap heap by ID number:

Conclusion

Maintaining your own database can be a hassle, and RestDB offers a great solution to in the database-as-a-service market. Give it a try and tell us about the great things you build!