How to Expose a Database Stored Procedure and a Function as a REST API

Appery.io API Express makes it easy to expose a SQL query as a REST API. You can as easily expose a database stored procedure or a database function as a REST API. The following two blogs posts will teach you how to do that:

Want to learn more about API Express? Check our the Appery.io YouTube channel for videos on how to integrate with external data sources using API Express.

 

Learn How to Perform Basic Database Operations: Create, Read, Update, and Delete

The Appery.io Backend Services consists of the following components:

  • Database – for storing any app data.
  • Server Code – for writing server-side app logic using JavaScript.
  • API Express – integrating with external systems and APIs.
  • Push Notifications – sending push messages to devices.
  • Web Hosting – publishing and hosting mobile web apps.

In this post, we will show you how a Server Code script integrates with the Database.

One of the most common questions we get is how to work with the database, how to create, edit and delete data. This makes sense as virtually any mobile app needs to store data in a database and perform these basic operations. To access the database, we are going to use Server Code script. Server Code allows writing app logic using JavaScript which will be executed on the server. For example, a script can access the database, send a Push Notification message, sort data and invoke an external REST API. We will start with the most basic operation: how to read data from the database.

The database collection used in this post looks like this:

crud_collection
Database collection.

This can be data collected from a form inside an app – a form that collects information about the user.

Reading Data

Server Code script has out-of-the-box API to access the database to perform all the basic operations. The following script reads all objects from the above collection.

var dbApiKey = "fe7c124b-f7c5-4764-9274-173b56a97102";
var result = Collection.query(dbApiKey, "Data");
Apperyio.response.success(result, "application/json");

One line 2, the script retrieves all the data.

One line 3, the script response is set. A Server Code script is invoked as a REST API. The code on this line defines the API response.

A script can be quickly tested from the Run tab where you can see the JSON data:

 

crud_test
Script testing.

 

Reading data from a database collection is fast and simple. Next, we are going to save data into the database.

Continue reading “Learn How to Perform Basic Database Operations: Create, Read, Update, and Delete”

Learn How to Connect Your App to an API Backend

This short video shows how to import backend services APIs into your app. Appery.io services consist of a cloud database, Server Code for custom app logic, and API Express for enterprise integration and service orchestration.

Our YouTube channel has many more short videos where you can learn how to build apps fast with Appery.io.

Moving App Logic to the Server: How to Save and Query an Appery.io Database from Server Code Script

The Appery.io Database provides storage for your mobile app data. You can store data such as users, orders, purchases, products or anything else. The Appery.io Database is a cloud NoSQL database and provides a very simple and elegant REST API to work with the database.

One of the most common cases for an enterprise mobile app is to create/save a new item/record and then redisplay the list in the app with the newly created item.

Accessing the database directly from the client is fine but requires two requests to the database.

  1. A request to save the new item/record into the database.
  2. Once the 1st request completes, on its success, a request to get the updated list to display in the app.

A better approach is to do both the create and list in a single request from the app. This is very simple to do from Appery.io Server Code script. The following script shows how to create a new record and then get the updated list:

var dbId = "cdaec951....d8";
var collectionName = "People"

var newName = request.get("name");
var newTitle = request.get("title");

// Save new object into the collection
Collection.createObject(dbId, collectionName, {
   "name": newName,
   "title": newTitle
});

// Retrieve updated data from the collection
var result = Collection.query(dbId, collectionName);
// Return the updated list (including the new object)
Apperyio.response.success(result, "application/json");

In this simple script you first create a new Person object (line 8) and then do another query to get the updated list of people (line 14). The script response returns the updated list of people. This script automatically has a REST API which is the invoked from the app.

What’s most important, there is only a single request from the app to the Server Code script. A single request is usually better than two requests. Also, you can add additional logic to the script without impacting the app. For example, you can send an email when a new person is added. Or, you can send  Push Notification when a new person is added. All this can be done without impacting the app. That’s nice.

If you want to learn more about Server Code, check out our YouTube channel Server Code playlist.

Here Is How to Secure a Cloud Database

The Appery.io Database is a cloud database for storing any app data. The app communicates with the cloud database via simple REST API.

To access the database, the database API key is added to each request.

What if you need extra security? Any database collection can be configured with extra security in a few seconds.

Screen Shot 2016-04-22 at 1.05.08 PM
Database collection security and permissions

When you configure a collection with extra security, a user session token has to be included in every request. How to get the session token? A user (from Users collection) needs to do a login. When a login is successful, a session token is returned. This session token is then used with every request. Without the session, the collection can not be accessed.

Another layer of access can be set by checking or unchecking the Read/Write permission. For example, if you uncheck Write, then no request will be able to write anything into the collection, only read the data.

Both the security and Read/Write permissions can be overwritten by including a special master key – which acts as root access.

The Appery.io Database can be configured with a number of security and permissions options to give you the most flexibility when creating your mobile app.

What to learn more? Check out our YouTube channel for many short videos.

How to Do Multi-Object Update in MongoDB From Appery.io Server Code

The Appery.io Database runs on MongoDB database. The following screen shot shows a sample collection (table) with employees in the database.

Screen Shot 2016-04-19 at 5.01.31 PM.png
Custom database collection

You want to update two or more objects in a single request. That’s very simple to do from Appery.io Server Code. The following server script does multi-object update, it just one line of code:

var result = Collection.multiUpdateObject("367e9e77-cc1f-4c1f...",
    "People", '{"title":"Engineer"}', {"title":"Senior Engineer"});
  • The first argument is the database API key.
  • People is the collection name inside the database.
  • With {“title”:”Engineer”} you are searching for all records where the title is set to “Engineer” and update those objects to a title of “Senior Engineer“.
  • Collection is part of Server Code API that integrates with the database.

That’s it.

After you run the script, the database collection looks like this:

Screen Shot 2016-04-20 at 11.35.22 AM
Collection after the update

Appery.io Server Code provides a very nice integration with the Database that allows you to run and execute advanced app logic.

Learn more about Appery.io Database and Server Code from many short videos on our YouTube channel.

What is Appery.io Database?

Appery.io Database is a cloud database for storing any information or data for your app. For example, you can store app users, orders, purchases, pictures — or anything else that your app needs to store.

The database is running in the cloud, this means that the app connects to the database via REST API.

The database has two types of collections or tables: pre-defined and custom.

There are three pre-defined collections:

  • Users – for managing users. For example, you can add user login and registration to an app.
  • Files – for storing files.
  • Devices – device information which have registered to receive Push Notifications. This collection is used to send targeted Push Notifications. For example, send a message to all users who subscribed to get news about travel.

In addition to the pre-defined collections which are automatically available in each new database, you can create any number of custom collections (tables). Custom collections can hold any data.

A collection can have any number of columns to hold the data. The following column data types are supported:

  • String
  • Number
  • Boolean
  • Date
  • Array
  • Pointer – reference to another collection
  • Object
  • Geopoint – geolocation point expressed as latitude, longitude
  • File

The database is accessed via a REST API. An API key is required to access any database collection. Further security can be added by making a database secure – this means a user login will be required before a database can be accessed from the app. The user is kept in the Users collection.

In addition to using the built-in Users collection for user management, login into an app can also be accomplished via social networks: Facebook, Twitter and Google.

More security and permissions can be specified via ACL (Access Control List). Every new records/object created in the database can have specific access control information. For example, only the user who created this object can read and write this object. Everyone else can only read the object.

A special master key is also available which gives a root-like access to the database.

The Database has a built-in Versioning feature. It allows to create an instance (copy) of the database and restore it at any point.

The Database is nicely integrated with the Server Code component. Server Code provides elegant API to access the database from a server-side script.

We have a number of short videos on using the Database on our YouTube channel here and here.

Happy coding.

Getting Started with the Appery.io Cloud Database

This is our last webinar in our 2016 Webinar Series. This webinar covered the cloud database. By watching this video you will learn:

  • What is Appery.io cloud database and when to use it.
  • What are pre-defined collections.
  • What are custom collections.
  • How to use the database developer console, settings and features.
  • How to use the query console to test queries.
  • Using and accessing the database from a Server Code script.

Check all other webinar recordings.

How to Integrate with the Database from Server Code Script

This short video tutorial shows how to connect to the Appery.io database and retrieve all records from a collection using Server Code script (server-side JavasScript).

Watch other Appery.io Backend Services videos to learn how to create your app backend and integrate with external data sources.

How to Expose a SQL Database via REST API in 5 Minutes

This video shows how to use the Appery.io API Express visual editor to connect to a SQL database and expose it via a REST API.  The SQL component is used to connect and execute a custom SQL query.

Once a database is exposed via REST APIs you can easily build a mobile app that talks to the database.

Looking for more videos on how to build mobile app fast? Check out our YouTube channel.