GuidesAPI ReferenceLatest UpdatesBlogVideosStart Building Apps

Database Connection

Setting up API Express database connection

Suggest Edits

API Express allows you to connect to a database and expose it via REST APIs. In order to do that, the first step is to create a database connection. With API Express you can connect to:

This page explains how to connect to both database types.

You will also find the specific examples of connecting to:

📘

A database connection is required if you want:

  • Generate REST APIs that talk to the database.
  • Use the SQL component in the Visual Service Creator.
  • Build offline apps.

Connecting to External Database

Setting up a database connection consists of two sections: the Database connection and Pool settings.

API Express support connecting to the following databases:

  • SQL Server.
  • Oracle.
  • Postgre SQL.
  • MySQL.

📘

How to Add API Express into Mobile Application

Check this video for detailed instructions on how to:

  • create a Database connection to a relational DB,
  • expose the relational DB data as a REST service and
  • use this functionality in an Appery.io mobile app:

Database Connection

To set up a database connection:

  1. Go to the API Express main page and click the Create new DB connection button.
  2. Enter a connection name and click the Create button.
  3. For Connection type select Relational database.
  4. Select a database type. Now, the following database types are supported: SQL Server, Oracle, PostgreSQL, and MySQL .
  5. Select Configuration edit mode that can be either BASIC or ADVANCED
    5.1 For the BASIC mode, you need to provide:

  • Host

  • Port

  • Database name

  • Database schema

  • Username

  • Password

  • Connection properties

    5.2 For the ADVANCED mode, you need to provide:

  • Database schema

  • Username

  • Password

  • Jdbc url

The Database schema value depends on the database type you use. Here is a quick guide.:

  • SQL Server – the predefined db schema is passed by default.
  • Oracle – there is no default schema and passing it is optional.
  • Postgre SQL – the predefined public schema is passed by default.
  • MySQL – the field is disabled and no schema is required.

For most cases BASIC mode is preferable.
It is recommended to use ADVANCED in exceptional cases; for example when it is needed to create a connection to Oracle database using the _tnsnames.ora _file then it is possible to connect to the database using the following jdbc url jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP)(HOST=hostA)(PORT= 1522))(ADDRESS=(PROTOCOL=TCP)(HOST=hostB)(PORT=1521)))(SOURCE_ROUTE=yes)(CONNECT_DATA=(SERVICE_NAME=DatabaseService))).

Pool Settings

The following values are recommended for Pool settings:

  • Max active – between 1 to 9.
  • Max idle – between 1 to 9.
  • Max wait – between 1 to 10000.

Testing Connection

It's a good idea to test the connection. Once you are done entering the connection information click Test to test it.

Exposing Your Database to API Express

If you use a real/white IP address on your server, you will use an external data source URL. Also, you can use a DNS (Domain Name Service) record for your external data source server. Ensure that your firewall is properly configured and allows internal connections to your server from Appery.io. Our application servers DNS are:

  • app5.appery.io
  • app6.appery.io
  • aex1.appery.io
  • aex2.appery.io
  • aex3.appery.io
  • aex4.appery.io

In some cases, you’ll have to provide IP addresses instead of domain names. For example, you can execute the nslookup command-line tool (Windows, Linux, Mac OS X) or whois-like services from the Internet (like ping.eu, etc) to see the actual IP addresses:

nslookup aex1.appery.io

Configuration for Hidden/Internal/Gray Database IP Address

If you use a hidden/internal/gray IP address on your server, you should make additional settings for using your own server. You can ask your IT to expose a real IP for your server or configure port forwarding. For example, if using a gateway with Red Hat/CentOS and IP MASQUERADE configured, for server port forwarding (standard port 443), you should run the following commands:

iptables -t nat -A PREROUTING -i $EXTERNAL_NETWORK_INTERFACE -p tcp -m tcp -d $EXTERNAL_GW_IP --dport 443 -j DNAT to-destination $WS_SERVER_IP:443
 
iptables-save | sudo tee /etc/sysconfig/iptables
 
service iptables restart

Relational Databases Supported and Data Types

API Express supports the following databases and data types:

MySQLSQL ServerOraclePostgreSQL
BIT
TINYINT
BOOLEAN
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DATE
DATETIME
TIMESTAMP
CHAR
VARCHAR
TINYBLOB
TINYTEXT
BLOB
TEXT
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
SET
BINARY
VARBINARY		TINYINT
SMALLINT
INT
NUMERIC
DECIMAL
SMALLMONEY
MONEY
REAL
BIT
CHAR
VARCHAR
NCHAR
NVCHAR
TEXT
IMAGE
DATETIME
SMALLDATETIME
BINARY
VARBINARY		NUMBER
SMALLINT
DOUBLE
CHAR
INT
DEC
NUMERIC
REAL
DECIMAL
VARCHAR2
VARCHAR
FLOAT
TIMESTAM
CLOB
BLOB
DATE		INT8
SMALLINT
TEXT
BOOLEAN
TIMESTAMP
SMALLSERIAL
BIGINT
DATE
BIGSERIAL
INTEGER
TIME
LONG
SERIAL
NUMERIC
DOUBLE_PRECISION
CHARACTER
REAL
CHARACTER_VARYING
VARCHAR

🚧

Connecting to a Database Instance

Today API Express doesn't support connecting to a database instance that has the following connection string: host\instancename. This is something we are planning to support in the future.

Connecting to Appery.io Database

API Express also supports connecting to the Appery.io Database in order to build offline apps with the platform database.

Database Connection

To use this option:

  1. Go to the API Express page and click Create a new DB connection.
  2. Set the Database type to Appery.io Database.
  3. Then, from the drop-down, select the needed database and database credentials. The username and password are coming from the Users collection in the Appery.io Database.

Testing Connection

It's a good idea to test the connection. Once you are done entering the connection information click Test to test it.

Updated 6 days ago

What’s Next