Connecting to Databases

RapidQL may be used for querying databases. Database queries and API queries may be combined to create advance data gathering logic.

Setup

To add a database connection to your rql instance, you need to add it's connection details in the RapidQL initialization

const RapidQL = require('RapidQL');
const rql = new RapidQL({
  PostgreSQL: {
    Sample: {
      user: '#############', // required
      database: '#########', // required
      password: '#########', // required
      host: '#############', // required
      port: '#############', // required
      max: 10, // optional - max connections
      idleTimeoutMillis: 30000 // optional - how long a client is allowed to remain idle before being closed
    }
  }
});

Once the RapidQL instance is connected to the DB, you may query it. The object you're querying will have the following schema

DBType.DBName.Schema.Table.Operation

• DBType : type of DB you're querying (PostgreSQL, MySQL, Redis, etc...)
• DBName : name you used when configuring the DB (you can be connected to multiple DBs of each type)
• Schema : schema you wish to work with
• Table : name of the table to be queried

🚧

PostgreSQL.Sample.public.users.select will query the Sample PostgreSQL DB (same sample we used in configuration above), and perform a select query on the users table in the public schema.

Select

The most basic way to perform select queries is by passing equality comparisons:

PostgreSQL.Sample.public.users.select(location: "US")

This will find all users where location is 'US'.

For more complex conditions use:

PostgreSQL.Sample.public.users.select(birthyear: {"<=": "1997"})

This will find users whose birth year is smaller than or equal to 1997. Using .select(location: "US") is shorthand for .select(location:{"=": "US"}) You can have multiple conditions, mixing between comparison styles:

PostgreSQL.Sample.public.users.select(location: 'US', birthyear: {"<=": "1997"})

Complex queries (SKIP, LIMIT, ORDER BY)

PostgreSQL.Sample.public.users.select(location: "US") is shorthand for PostgreSQL.Sample.public.users.select(WHERE: {"location": "US"}). Using the full syntax you may add skip, limit and order by clauses.

PostgreSQL.Sample.public.users.select(WHERE:{"location": "US"}, LIMIT:"3", SKIP:"1", ORDERBY: {birthyear: "DESC"})

Note case sensitivity.

Count

Count works just like select, only it returns the count value.

{
    PostgreSQL.GCELogs.public.blockcalls.count(LIMIT: "10", GROUPBY: "package", ORDERBY: {count: "DESC"}) {
        package,
        count
    }
}

Aggregate functions

Most databases also support aggregate function. The basic ones include:

• avg - averages over a field
• sum - sums all value in a field
• min - gets minimum value in a field
• max - gets maximum value in a field

The syntax for using aggregate functions is:

PostgreSQL.local.public.users.avg(FIELD:"birthyear") {
    birthyear
}

This query will go over all the rows in the users table and get the average birth year. You can also aggregate over rows matching a query, The following query will get the average birth year for rows where state="CA".

PostgreSQL.local.public.users.max(FIELD:"birthyear", state:"CA") {
    birthyear
}