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
}
Updated about 6 years ago