Louis' Blog

All about software development for the web

Calgary Open Data GraphQL API

Facebook recently announced GraphQL, a query language for building APIs. The strength of this new language has to do with the fact that you can request all the required data in one query, instead of having to make multiple requests. Writing queries is intuitive and the structure of the results mirrors the structure of the query.

GraphiQL was also created to allow developers to interact with the API they’re developing. It has autocomplete and built in documentation which are incredibly useful.

I wanted to create an API using this new language to see how it works and explore the possibilities. You can interact with this API here

Building an API

The first step was creating a hello world query. First, GraphQLObjectType is used to create a Query, and the following is passed as a field:

1
2
3
4
hello: {
type: GraphQLString,
resolve: () => 'world'
}

The type field describes the type of result returned. It can be a basic type like string, int, or Boolean. It can also be a custom type, which we’ll see later.

The resolve field is a function which generates the result. In the example above, the result will always be “world”.

If we run the following query in GraphiQL:

1
2
3
query{
hello
}

We will get the following result:

1
2
3
4
5
{
"data": {
"hello": "world"
}
}

Passing Parameters to GraphQL

Then I tried to create something a little more dynamic, and I created an add function. By adding arguments to the query and resolve function, you can use those arguments in the generation of our results

The following example shows how we can pass two integers to an add function, and return the sum.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
add: {
type: GraphQLInt,
args:{
a:{
name:'a',
type: GraphQLInt
},
b:{
name:'b',
type: GraphQLInt
}
},
resolve: (root,{a,b}) => a+b
}

The type field again describes the type of result returned. In this case we’re now returning an integer.

The args field is an object with properties which can be passed to the resolve function via the query. In this case we’re passing two integers, a and b.

The resolve field again is a function which generates the result. In this example, the result is the sum of a and b. The parameters are passed as a single object.

If we run the following query in GraphiQL:

1
2
3
query{
add(a:3,b:7)
}

We will get the following result:

1
2
3
4
5
{
"data": {
"add": 10
}
}

Querying a PostgreSQL database in GraphQL

The previous examples were great for getting to know the basics, but if we want our API to be useful, it will have to inteact with data in more elaborate ways. Using some data from the City of Calgary Open Data Catalogue I created a PostgreSQL database, and used GraphQL to interact with it.

The Query definition is a bit longer, but follows the same basic structure. I simply added many fields allowing to filter the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
fireStations: {
type: new GraphQLList(fireStationType),
args: {
nameContains: {
name: 'nameContains',
type: GraphQLString
},
addressContains: {
name: 'addressContains',
type: GraphQLString
},
hasFireSupport: {
name: 'hasFireSupport',
type: GraphQLBoolean
},
hasMedRescue: {
name: 'hasMedRescue',
type: GraphQLBoolean
},
hasAquaRescue: {
name: 'hasAquaRescue',
type: GraphQLBoolean
},
hasHighAngRes: {
name: 'hasHighAngRes',
type: GraphQLBoolean
},
hasHazCond: {
name: 'hasHazCond',
type: GraphQLBoolean
},
hasAirportRes: {
name: 'hasAirportRes',
type: GraphQLBoolean
},
hasHeavyRes: {
name: 'hasHeavyRes',
type: GraphQLBoolean
},
hasPubSrvAsst: {
name: 'hasPubSrvAsst',
type: GraphQLBoolean
},
hasFalseAlarm: {
name: 'hasFalseAlarm',
type: GraphQLBoolean
},
hasHydBldInsp: {
name: 'hasHydBldInsp',
type: GraphQLBoolean
},
hasInvestigat: {
name: 'hasInvestigat',
type: GraphQLBoolean
},
hasH20_Safety: {
name: 'hasH20_Safety',
type: GraphQLBoolean
},
hasChemDrop: {
name: 'hasChemDrop',
type: GraphQLBoolean
},
hasSmokeDProg: {
name: 'hasSmokeDProg',
type: GraphQLBoolean
},
hasStnTours: {
name: 'hasStnTours',
type: GraphQLBoolean
},
hasSandWinter: {
name: 'hasSandWinter',
type: GraphQLBoolean
},
hasBulkWater: {
name: 'hasBulkWater',
type: GraphQLBoolean
}
},
resolve: resolvefireStations
}

The type field now returns a list of objects, and those objects are a custom type fireStationType. The type is defined as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
const fireStationType = new GraphQLObjectType({
name: 'FireStation',
description: 'A Fire Station',
fields: () => ({
name: {
type: GraphQLString,
description: 'Station Name',
},
address: {
type: GraphQLString,
description: 'Station Address'
},
firesupp: {
type: GraphQLBoolean,
description: 'Fire Support'
},
med_rescue: {
type: GraphQLBoolean,
description: 'Medical Rescue'
},
aquarescue: {
type: GraphQLBoolean,
description: 'Water Rescue'
},
highangres: {
type: GraphQLBoolean,
description: 'High Ang Rescue'
},
hazcond: {
type: GraphQLBoolean,
description: 'Hazardous Conditions'
},
airportres: {
type: GraphQLBoolean,
description: 'Airport Rescue'
},
heavyres: {
type: GraphQLBoolean,
description: 'Heavy Rescue'
},
pubsrvasst: {
type: GraphQLBoolean,
description: 'Public Service Assistance'
},
falsealarm: {
type: GraphQLBoolean,
description: 'false Alarm'
},
hydbldinsp: {
type: GraphQLBoolean,
description: 'Hydro Building Inspection'
},
investigat: {
type: GraphQLBoolean,
description: 'Investigation'
},
h2o_safety: {
type: GraphQLBoolean,
description: 'Water Safety'
},
chemdrop: {
type: GraphQLBoolean,
description: 'Chemicals Drop Off'
},
smokedprog: {
type: GraphQLBoolean,
description: 'Smoke Detector Program'
},
stntours: {
type: GraphQLBoolean,
description: 'Station Tours'
},
sandwinter: {
type: GraphQLBoolean,
description: 'Sand Winter'
},
bulkwater: {
type: GraphQLBoolean,
description: 'Bulk Water'
}
})
});

The resolve field again is a function, which is defined as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
function resolvefireStations(rootValue, args ){
return new Promise(function(resolve, reject) {
var query = 'SELECT * from fire_stations WHERE 1=1'
if(args.nameContains){
query += ' AND name like \'%'+args.nameContains+'%\'';
}
if(args.addressContains){
query += ' AND address like \'%'+args.addressContains+'%\'';
}
if(args.addressContains){
query += ' AND address like \'%'+args.addressContains+'%\'';
}
if(typeof(args.hasFireSupport) === 'boolean'){
query += ' AND FireSupp = '+args.hasFireSupport;
}
if(typeof(args.hasMedRescue) === 'boolean'){
query += ' AND Med_Rescue = '+args.hasMedRescue;
}
if(typeof(args.hasAquaRescue) === 'boolean'){
query += ' AND AquaRescue = '+args.hasAquaRescue;
}
if(typeof(args.hasHighAngRes) === 'boolean'){
query += ' AND HighAngRes = '+args.hasHighAngRes;
}
if(typeof(args.hasHazCond) === 'boolean'){
query += ' AND HazCond = '+args.hasHazCond;
}
if(typeof(args.hasAirportRes) === 'boolean'){
query += ' AND AirportRes = '+args.hasAirportRes;
}
if(typeof(args.hasHeavyRes) === 'boolean'){
query += ' AND HeavyRes = '+args.hasHeavyRes;
}
if(typeof(args.hasPubSrvAsst) === 'boolean'){
query += ' AND PubSrvAsst = '+args.hasPubSrvAsst;
}
if(typeof(args.hasFalseAlarm) === 'boolean'){
query += ' AND FalseAlarm = '+args.hasFalseAlarm;
}
if(typeof(args.hasHydBldInsp) === 'boolean'){
query += ' AND HydBldInsp = '+args.hasHydBldInsp;
}
if(typeof(args.hasInvestigat) === 'boolean'){
query += ' AND Investigat = '+args.hasInvestigat;
}
if(typeof(args.hasH20_Safety) === 'boolean'){
query += ' AND H20_Safety = '+args.hasH20_Safety;
}
if(typeof(args.hasChemDrop) === 'boolean'){
query += ' AND Chem_Drop = '+args.hasChemDrop;
}
if(typeof(args.hasSmokeDProg) === 'boolean'){
query += ' AND SmokeDProg = '+args.hasSmokeDProg;
}
if(typeof(args.hasStnTours) === 'boolean'){
query += ' AND StnTours = '+args.hasStnTours;
}
if(typeof(args.hasSandWinter) === 'boolean'){
query += ' AND SandWinter = '+args.hasSandWinter;
}
if(typeof(args.hasBulkWater) === 'boolean'){
query += ' AND BulkWater = '+args.hasBulkWater;
}
query += ';';
db.connect(function(err, client, done) {
if (err) {reject(err);return;};
client
.query(query, function (err, result) {
done();
if (err) {reject(err);return;}
resolve(result.rows);
});
});
}).then(function(res) {
return res;
}, function(error) {
console.error("Database Error:", error);
return null;
});
}

The resolvefireStations function builds a SQL query based on the arguments passed in the GraphQL query. The database is queried for the results, which are returned as a promise.

If you’d like to obtain a listing of all Calgary fire stations with chemical drop off services, you could run:

1
2
3
4
5
6
query{
fireStations(hasChemDrop:true){
name
address
}
}