Accessing data from databases

Learn how to access data stored in databases.

info

If you want to try this example, you can deploy the following endpoint:

API over MySQL database

Search a dataset on airports, optionally filtering by IATA code.

Overview

Sample usage:

/api/mysql[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/mysql?code=LGA

Code

main(code: string = null) =
    let
        airports = MySQL.InferAndRead(
            "raw",
            "airports",
            host = "example-mysql.raw-labs.com",
            username = "**************",
            password = "**************"
        )
    in
        Collection.Filter(airports, (x) -> Nullable.IsNull(code) or x.iata_faa == code)

API over PostgreSQL database

Search a dataset on airports, optionally filtering by IATA code.

Overview

Sample usage:

/api/postgresql[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/postgresql?code=LGA

Code

main(code: string = null) =
    let
        airports = PostgreSQL.InferAndRead(
            "raw",
            "example",
            "airports",
            host = "example-psql.raw-labs.com",
            username = "**************",
            password = "**************"
        )
    in
        Collection.Filter(airports, (x) -> Nullable.IsNull(code) or x.iata_faa == code)

API over Oracle database

Search a dataset on airports, optionally filtering by IATA code.

Overview

Sample usage:

/api/oracle[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/oracle?code=LGA

Code

main(code: string = null) =
    let
        airports = Oracle.InferAndRead(
            "orcl",
            "example",
            "airports",
            host = "example-oracle.raw-labs.com",
            username = "**************",
            password = "**************"
        )
    in
        Collection.Filter(airports, (x) -> Nullable.IsNull(code) or x.IATA_FAA == code)

API over Microsoft SQL Server

Search a dataset on airports, optionally filtering by IATA code.

Overview

Sample usage:

/api/sqlserver[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/sqlserver?code=LGA

Code

main(code: string = null) =
    let
        airports = SQLServer.InferAndRead(
            "raw",
            "example",
            "airports",
            host = "example-mssql.raw-labs.com",
            username = "**************",
            password = "**************"
        )
    in
        Collection.Filter(airports, (x) -> Nullable.IsNull(code) or x.iata_faa == code)

Quick Summary

• Use the package corresponding for the database desired. Refer to the MySQL, PostgreSQL, Oracle, SQL Server, Snowflake and Redshift packages.
• Use the Read functions for reading tables and the Query functions for passing down SQL query strings in the vendor-specific format.
• Use the InferAndRead or InferAndQuery functions for detecting the schema automatically.
• If you registered the database credentials in the platform, the database name argument should become the credential name.

Introduction

Snapi includes built-in support for reading data stored on MySQL, PostgreSQL, Oracle, SQL Server, Snowflake and Redshift.

Reading Tables vs Executing Queries

The database packages include two types of functions:

• functions that return the contents of a table;
• functions that allow you to specify a query in the native SQL syntax of the underlying system.

For instance, functions which are called Read receive the table name. The following code reads a table called table01 from a MySQL database called db01:

MySQL.InferAndRead("db01", "table01", host = "...", username = "...", password = "...")

You can also specify a query in the native SQL syntax of the underlying system. This is particularly useful if you want to use vendor-specific SQL extensions. These functions, which are called Query, receive the database name and the query string instead. For instance, the following code does a MySQL query to a MySQL database called db01:

MySQL.InferAndQuery("db01", "SELECT * FROM table01", host = "...", username = "...", password = "...")

Schema Detection

The system can either auto-detect the schema of the data or the user can specify it manually as an argument. The methods that auto-detect the schema are named InferAndRead (e.g. MySQL.InferAndRead or MySQL.InferAndQuery) while the methods that receive the type from the user are named Read (e.g. MySQL.Read or MySQL.Read).

The detection of the schema occurs prior to the execution of the program. This means the program is slightly slower to execute as a separate execution happens in advance to detect the schema. Moreover, if the location cannot be determined prior to the execution (e.g. the location is not statically defined in the program but is received as an external argument), then this auto-detection of the format cannot be used and the user must specify the type of the data.

Examples

This example reads MySQL table from a given URL. The type of the data in the MySQL table is not specified, so we use the MySQL.InferAndRead:

MySQL.InferAndRead("db01", "table01", host = "...", username = "...", password = "...")

If the data type is known or cannot be inferred, we use MySQL.Read instead as in:

MySQL.Read("db01", "table01", type collection(record(name: string, age: int))), host = "...", username = "...", password = "...")

If the database credentials are registered in the platform, then the database name should correspond to the credential name. In this case, we do not need to specify host/username/password or any other connection properties as those are defined in the credentials, e.g:

MySQL.InferAndRead("cred01", "table01")

.. where "cred01" is the name of the credential registered in the platform.