database models drivers

Database Models and Drivers

This chapter covers

  • Planning API features and mapping them to sources of data

  • Designing relational database models and document database collections

  • Using Node.js drivers for PostgreSQL and MongoDB

  • Seeding local databases with test data

In the first part of this book, you learned the fundamentals of the GraphQL "language" that clients of GraphQL APIs can use to query GraphQL services for data resources and instruct them to do mutations on data. It is now time to learn how to create these GraphQL services that can understand that language.

We are going to build a real data API for a real Web Application. I picked the name AZdev for it which is short for "A to Z" of developer resources. AZdev will be a searchable library of practical micro-documentations, errors and solutions, and general tips for software developers.

I am not a fan of useless abstract examples that are removed from practical realities. Let’s build something real (and useful).

Some familiarity with the Node.js runtime is needed from this chapter an on. You don’t need to be an expert in Node but if you have never worked with it before you will need to learn its basics before proceeding with this chapter. I wrote a detailed introduction about Node that can read at az.dev/node-intro.

I’ll be building the web User Interface for AZdev as a video course at az.dev/ui. By the time you are reading this, I’ll hopefully have managed to get that work done and the AZdev application will be active and usable! I am planning to host it at https://az.dev.

Please take a moment to explore that site to better understand the next section about the API requirements for AZdev.

1. Why AZdev

When software developers are performing their day-to-day tasks, they often need to lookup one particular thing. For example, how to compute the sum of an array of numbers in JavaScript. They are not really interested in scanning through pages and pages of documentations to find the simple code example they need. This is why at AZdev they’ll find an entry on "summing a JavaScript array of numbers" featuring multiple approaches on just that particular need.

AZdev is not a question-answer site. It is just a library of what developers usually need to lookup and a quick way for them to find exact, concise, and up-to-date approaches to handle what they need.

Here are some examples of entries I’d imagine finding on AZdev:

  • Get rid of only the unstaged changes since the last Git commit

  • Create a secure one-way hash for a text value (like a password) in Node

  • The syntax for a switch statement in LanguageX

  • A Linux command to lowercase names of all files in a directory

You can certainly find approaches to these needs by reading documentations and stack-overflows, but wouldn’t it be nice to have a site that features specific needs like these with their approaches right there without all the noise?

AZdev is something I have wished existed for as long as I remember. Let’s take the first step into making it happen. Let’s build an API for it!

2. The API Requirements for AZdev

To build a GraphQL API, you first need to figure out the sources of data the API service is going to use. GraphQL is not a storage engine! It’s just the interface to one (or many).

ch04 fig 01 gqlia
Figure 4. 1. An API server interfaces data-storage services

To make things interesting for the AZdev GraphQL API, we will make it work with two different storage services. We’ll use a relational database service to store transactional data and a document database service to store aggregate and dynamic data. A GraphQL schema can resolve data from many services, even in the same query!

It’s not at all unusual to have many sources of data in the same project. They don’t all have to be database services. A project can make use of a key-value cache service, get some data from other APIs, or even read data directly from files in the file system. A GraphQL schema can interface as many services as needed.

For the API vocabulary, we’ll name the model to represent a single main micro-documentation entry at AZdev as "Need" and we’ll name a single way or method on how that need can be met as "Approach". A Need can have multiple Approaches. An Approach belongs to a Need.

From now on, I will use the capitalized version of the words "need" and "approach" to refer to the Database/API entities. "We need a Needs table and we’ll approach the Approaches table with some constraints in mind."

Anyone can browse AZdev and find Needs and Approaches. Logged-in users can add new Needs and Approaches and they can also up-vote or down-vote Approaches.

  1. AZdev’s main entries for both Needs and Approaches will be stored in a relational database. I picked PostgreSQL for that. We’ll also store the main User records in PostgreSQL. A relational database like PostgreSQL is great for, well, relations! A Need has many Approaches and is defined by a User.

    Why PostgreSQL: PostgreSQL is a scalable open-source object-relational database that’s free to use and easy to setup. It offers an impressive set of advanced features that will be handy when you need them. It is currently the most popular choice among open-source relational databases.

  2. Extra data elements on Approaches like number of votes, explanations, warnings, or general notes will be stored in a document-oriented database. I picked MongoDB for that. A document-oriented database like MongoDB is "schemaless", which makes it a good fit for this type of "dynamic" data. An Approach might have a warning or an explanation associated with it, and it might have other data elements that we’re not aware of at the moment. Maybe at some point we’ll decide to add some performance metrics on Approaches or add a list of related links to them. We don’t need to modify a database "schema" for that. We can just instruct MongoDB to store these new data elements.

    Why MongoDB: MongoDB is also the most popular open-source document-oriented (NOSQL) database. The company behind it (MongoDB Inc) offers a community edition that is free to use and available on the 3 major operating systems.

Please note that in this book we will not be covering the installation of PostgreSQL or MongoDB. If you want to follow along with what we’ll do in the book (and you should!) please pause here and go install both services locally in your operating system or sign-up for "hosted" alternatives to use these services.

Please also note that this book will not be a proper source for you to learn these database services. However, the concepts we are going to use in these services will be briefly explained and all the commands and queries related to them will be provided as we progress through the API. I’ll also provide some seed data to help us practically test the API features we add.

All the commands in this book will be for Linux. They will also work on a Mac machine because macOS is a Linux-based OS. On Windows, you’ll have to find the native equivalent of these commands or you can spare yourself a lot of trouble and use the "Windows subsystem for Linux". If that is not an option, you can also run a Linux machine in a virtual hardware environment like VirtualBox.

If developing natively on Windows is your only option, I recommend using PowerShell instead of the CMD command. Most of Linux Bash shell commands work with Powershell.

We are using Node.js in this book and in general, Windows is not the best option when it comes to working with Node-based applications. Node was originally designed for Linux and many of its internal optimizations depend on the native APIs of Linux. Windows-support for Node was started a few years after Node was first released and there are active efforts into making it "better" but it will never be as good as Node for Linux.

Running Node on Windows natively is still an option but it’s just one that’ll give you a lot of trouble. Only develop natively on Windows if you plan to actually host your production Node applications on Windows servers.

2.1. Queries and Mutations

On the main page of AZdev, users will see a list of "featured" Needs and they can navigate to them. The GraphQL API will need to provide a public "featuredNeeds" query for that.

Listing 4. 1. Query #1: featuredNeeds
query GetFeaturedNeeds {
  featuredNeeds {
    # Info for "Need"
  }
}

Also on the main page, users can use a search form to search all listed public Needs (and Approaches, really). The GraphQL API will need to provide a "searchNeeds" query for that. This API endpoint will need to normalize the search query to have it return better results. For example, searching for either "array" or "arrays" should return results matching either words.

ch04 fig 02 gqlia
Figure 4. 2. Mock of AZdev main landing page
Listing 4. 2. Query #2: searchNeeds
query SearchAllNeeds {
  searchNeeds(
    # A Search Term
  ) {
    # Info for "Search"
    needs {
      # Info for "Need"
    }
  }
}

Once on the page for a single Need, users can see the list of Approaches sorted by their popularity (using their votes). The GraphQL API will need to provide a single query to get data about one Need and the list of its Approaches including their dynamic data elements. We’ll name this query "oneNeed". This is probably the most interesting query in the API because it’ll have to use both PostgreSQL and MongoDB in one go!

Listing 4. 3. Query #3: oneNeed
query GetOneNeedData {
  oneNeed(
    # Input to identify "Need"
  ) {
    # Info for "Need"
    approaches {
      # Info for "Approach"
    }
  }
}

To add content to AZdev (including voting on Approaches), a guest has to create an account (or login) to the application. This will require the API to host a Users table to store users' credentials. Passwords in that table will need to be one-way hashed. The GraphQL API will need to provide mutations to "createAccount" and "authorizeAccount".

Listing 4. 4. Mutation #1: createAccount
mutation CreateUserAccount {
  createAccount(
    # Input for "User"
  ) {
    # Info for "User"
  }
}
Listing 4. 5. Mutation #2: authorizeAccount
mutation AuthorizeUserAccount {
  autorizeAccount(
    # Input for "User"
  ) {
    # Info for "User"
  }
}

The "authorizeAccount" mutation will enable users to perform queries and mutations that are specific to a single user. These single-user operations are:

  • A "createNeed" mutation to create a new Need entry. Note that because this is not a Q/A site, this same mutation will have to include one Approach. Users can also create private Needs that are not to be included in the search (unless the User who is searching owns them).

    Listing 4. 6. Mutation #3: createNeed
    mutation CreateNeed {
      createNeed(
        # Input to identify "User"
        # Input for "Need"
        # Input for "Approach"
      ) {
        # Info for "Need"
      }
    }
  • An "updateNeed" mutation to update the data of a Need. This could be helpful to fix a typo in the Need or for future improvements of the Need text. The creator of the Need will be allowed to edit that Need for 5 minutes after creating it. After that, only "Admin" users can edit the Need. We’ll maintain a Boolean flag in the Users table to check if a user is an admin.

    Listing 4. 7. Mutation #4: updateNeed
    mutation UpdateNeed {
      updateNeed(
        # Input to identify "User"
        # Input for "Need"
      ) {
        # Info for "Need"
      }
    }
  • A "createApproach" mutation to add a new Approach to a Need. This will be an interesting mutation because it has to accept a list of dynamic data elements about the Approach.

    Listing 4. 8. Mutation #5: createApproach
    mutation CreateApproach {
      createApproach(
        # Input to identify "User"
        # Input for "Approach"
      ) {
        # Info for "Approach"
      }
    }
  • A "voteApproach" mutation to vote a Need’s Approach either up or down. This mutation needs to return the new votes count on that approach.

    Listing 4. 9. Mutation #6: voteApproach
    mutation CreateApproach {
      createApproach(
        # Input to identify "User"
        # Input for "Vote"
      ) {
        # Info for new vote count
      }
    }
  • A "myNeeds" query to return a list of all Needs for the logged in user, including their private ones.

    Listing 4. 10. Query #4: myNeeds
    query GetMyNeeds {
      myNeeds(
        # Input to identify "User"
      ) {
        # Info for "Need"
      }
    }

For the list of Approaches on a single Need, the GraphQL API will also provide a subscription operation to monitor the list for new data. This way the web page that’s open on a Need entry can be automatically updated when new Approaches are posted to that Need.

Listing 4. 11. Subscription #1: approachesForNeed
subscription GetOneNeedApproaches {
  approachesForNeed(
    # Input to identify "Need"
  ) {
    # Info for "Approach"
  }
}

How does all that sound to you? Let’s make it happen!

There is a lot more we can do for AZdev and I might end up adding more features to the API after this book but we need to keep things simple and manageable here. You can explore the AZdev current production GraphQL API at az.dev/api and see what other queries, mutations, and subscriptions I ended up adding to AZdev.

3. Designing the PostgreSQL Database Models

Let’s start with the main database models that are to be hosted in PostgreSQL.

You need to create a local PostgreSQL database and connect to it before running any commands in psql (or any other PostgreSQL interfaces). Creating a database, connecting to it, and running commands on it will be different based on your OS or service. Go ahead and do that now before proceeding with this chapter.

For the rest of this section, I’ll be assuming that you are in a PostgreSQL interface (like psql) and you can run SQL statements and commands on a database.

First, it’s a good idea to create a "schema" in a PostgreSQL database to host an application’s tables rather than create them in the "public" schema. This way you can use the same PostgreSQL database to host data for many applications. This is especially helpful if you have a group of schemas that are related to each other.

To create a PostgreSQL schema, you can use this command:

CREATE SCHEMA azdev;

3.1. The Users Model

The Users table will have a record for each registered user. A registered user can optionally have a first and last name. They have to have a unique email and a hashed password. A user can also be an admin.

Here’s a SQL statement you can use to create the Users table:

Listing 4. 12. The Users Table | az.dev/gia
CREATE TABLE azdev.users (
  id serial PRIMARY KEY,

  email text NOT NULL UNIQUE,
  hashed_password text NOT NULL,
  hashed_api_key text,
  first_name text,
  last_name text,
  is_admin boolean NOT NULL DEFAULT false,

  created_at timestamp without time zone NOT NULL
    DEFAULT (now() at time zone 'utc'),

  CHECK (lower(email) = email)
);

Note how I added an "id" field with a "serial" type and a "PRIMARY KEY" constraint. The "serial" type will automatically fill this field using a sequence of numbers (which gets automatically created and managed for this table). We need a unique and none-null "id" field in all tables. It’ll be used for referential integrity constraints (making sure records reference existing records).

The "email" field is also UNIQUE and NOT NULL, making it practically another PRIMARY KEY. If you want to use the email as a PRIMARY KEY (and that’s not too bad of an idea), you just need to make sure any referential integrity constraints are updated correctly if the user decides to "update" their email. PostgreSQL has some advanced features to help with that.

I also added a "created_at" field that will be automatically populated by PostgreSQL itself. We’ll also do that to all tables. Both of the "id" and "created_at" fields will not be mutated by the GraphQL API. We can just read them if we need to.

KEEP IT SIMPLE! I find it a lot easier to store date-time values without time zone information and always store UTC values in them. Life is too short to deal with time-zoned date-time values and their conversions.

The CHECK constraint on the "email" field validates that emails will always be stored in their lowercase form. This is a good practice to do for fields that are unique regardless of their case. I’ve learned that the hard way.

The "hashed_api_key" field is needed to authenticate requests to the GraphQL API after a user logs in. Because HTTP APIs are stateless, instead of having the user send over their password with every GraphQL operation, once they log in successfully we can give them a unique API "key" for them to use in subsequent GraphQL requests and the server will use that key to identify the logged in user. The API key is like a temporary password. It can be renewed per session, for example, and we could come up with a way to invalidate it after a certain time.

There will always be more things you can do to make an API more secure. For example, you can text the temporary API key to a phone number instead of communicating it back to the logged-in user in the same GraphQL request. For the purposes of this book, we’ll keep things simple but still go with the practical minimum. For example, don’t ever store plain-text passwords or api keys in your database!

3.2. The Needs/Approaches Models

The Needs table will have a record for each Need entry that’s submitted to AZdev. A Need entry is just a body of text but let’s also add an optional "tags" field to this table to make the Need entries more discoverable when searching and browsing.

A tag can be something like "git", "javascript", "command", "code", etc. An entry can have many tags that will be stored in the database as a comma-separated value. However, when it’s time for us to expose this field to the API users, we’ll return it to them as an array!

A Need (or Approach) has to belong to a User because only logged-in users can submit new entries. For that, we can use a FOREIGN KEY constraint to validate the map between a Need and a User. We’ll need to do the same for Approaches as well.

Let’s also add an "is_public" field to this table to allow users to have their own private entries that are not featured to the public. This is handy for users to keep a reference of things they need in their private projects. It will also make things a bit more challenging for us to implement.

Here’s a SQL statement you can use to create the Needs table.

Listing 4. 13. The Needs table | az.dev/gia
CREATE TABLE azdev.needs (
  id serial PRIMARY KEY,

  body text NOT NULL,
  tags text,
  user_id integer NOT NULL,
  is_public boolean NOT NULL,
  is_featured boolean NOT NULL DEFAULT FALSE,

  created_at timestamp without time zone NOT NULL
    DEFAULT (now() at time zone 'utc'),

  FOREIGN KEY (user_id) REFERENCES azdev.users,
  CHECK (is_public = true OR is_featured = false)
);
Challenge: What is the CHECK constraint on the Needs table validating?

The Approaches table will have a record for each approach submitted on a Need entry. An Approach entry is just a body of text. Each Approach record has to be stored under a valid User record and mapped to a Need record. This table will need two FOREIGN KEY constraints.

Here’s the SQL statement that we need:

Listing 4. 14. The Approaches table | az.dev/gia
CREATE TABLE azdev.approaches (
  id serial PRIMARY KEY,

  body text NOT NULL,
  user_id integer NOT NULL,
  need_id integer NOT NULL,

  created_at timestamp without time zone NOT NULL
    DEFAULT (now() at time zone 'utc'),

  FOREIGN KEY (user_id) REFERENCES azdev.users,
  FOREIGN KEY (need_id) REFERENCES azdev.needs
);
ch04 fig 03 gqlia
Figure 4. 3. The UML diagram for the 3 tables in PostgreSQL

Did you notice how I’ve used many database constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and FOREIGN KEY? These database constraints will help future developers (including you) understand the design decisions you’re making today and they’ll be the last-standing guard if a user (or a program) tries to insert invalid data to the database. When it comes to data integrity, spare no layers! The least you can do is have the database validate it. Don’t skip that. You should also add more layers to give users of your API more meaningful error messages when they attempt to insert invalid data. We’ll do some data validation in the GraphQL layer as well.

4. Designing the MongoDB Models

Let’s now design the sources of data that are to be hosted in MongoDB. Before doing that, we need to create a new database for AZdev.

In MongoDB, there is no "schema" concept to group related database entities. You just create a database for that purpose. You actually don’t need to "create a database". You just "use" it and MongoDB will automatically create the currently-used database the first time you insert any data to it.

For the instructions in this section, I am going to assume that you’re in the mongo CLI tool.

$ mongo

I’ll use the $ sign to mean that this is a "command" to be executed in a "terminal". The $ sign is not part of the command.

Once in a mongo session, you can run the following command to "use" a new database.

use azdev

Note that this will not create the azdev database yet.

A "Model" is represented with a "Collection" object in MongoDB and, just like the database itself, you don’t need to create a collection to be able to store documents. MongoDB will accept requests to store any data in any form or shape whether a collection for it existed before or not. For new types of documents, MongoDB will automatically create new collections.

The flexibility in document databases is great but it also might be a source of big problems. A simple typo might lead to having a brand new (wrong) collection in the database. Be careful with that. With flexibility comes great responsibilities!

4.1. The Approaches Collection

You can create empty collections in MongoDB if you want and you can also restrict the privileges of a database user to only do certain actions on certain collections in certain databases! I think that’s a great way to validate that data will be stored in its intended locations. I’ll skip the privileges part here to keep things simple, but let’s plan the collection for the extra dynamic data elements that we want to support on Approaches.

MongoDB supports performing some data validation when inserting (or updating) documents in collections. This is useful when there are certain fields in your documents that cannot be empty, have to have a certain "type", or even have a certain structure. For an Approach entry to have extra data elements in MongoDB, we need to associate its MongoDB record with its PostgreSQL "ID" be able to do the mapping between the two sources. We are also planning to support sorting Approaches based on their vote count, which means an Approach document must have a field for vote count!

We’ll use MongoDB schema validation to make sure we at least have both of these fields for each Approach document. Here’s the MongoDB command you can use to create the "approaches" collection and define its "validator":

Listing 4. 15. The "approaches" collection | az.dev/gia
db.createCollection("approaches", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: [ "pgId", "voteCount" ],
      properties: {
        pgId: {
          bsonType: "int",
          description: "must be an integer and is required"
        },
        voteCount : {
          bsonType: "int",
          description: "must be an integer and is required"
        }
      }
    }
  }
})

The command above will create an approaches collection and because it’s the first thing we’re creating in the currently-used azdev database it’ll also create that database as well. You can verify that with the show dbs command. The show collections command should report back: approaches.

ch04 fig 04 gqlia
Figure 4. 4. The UML diagram for the Approches collection
Note how I used camel-case for MongoDB fields and under-score for PostgreSQL fields. I like camel-case but the under-score style is the common convention in PostgreSQL and it will add some challenges for us down the road as we should normalize everything in the API. The convention for GraphQL APIs it to use camel-case for all fields.
Think of adding more documents to the azdev database. For example, maybe store vote records and track who voted what and when. I’ll leave that as an exercise for you if you want to expand the scope of this API and make it more challenging. However, I’ll just keep a single MongoDB collection for the version of the API that we’re building in this book.

5. Node.js Drivers for PostgreSQL and MongoDB

For the GraphQL runtime service to be able to communicate with databases like PostgreSQL and MongoDB, it needs a driver. We’ll use Node’s "pg" package and "mongodb" package for that purpose. These are not the only packages that can be used as drivers. They are however the most popular ones in the Node’s ecosystem.

Let’s start from complete scratch. Create a new directory to host the GraphQL API for AZdev.

$ mkdir azdev-api
$ cd azdev-api
All the commands in the rest of this chapter will be executed from inside this new azdev-api directory.
From this point on, I will be assuming that you have a modern Node.js installed in your OS. If you don’t have Node or if you have an old version of it (anything less than 10.x), please download the latset from nodejs.org and use that.

5.1. Installing Dependencies

To make a directory into a Node project, we need to create a package.json file to host meta-information about the project and document its dependencies. We can use the npm init command to generate this file:

$ npm init -y
This command has an interactive mode if you run it without the -y flag. You can use that interactive mode to seed the file with different information than what the command can detect on its own. The -y flag is for "YES" and it’ll start the file with the smart default that can be detected by the command.

Open up the generated package.json file in your editor and add a "type" property to the json content, give that property a value of "module":

  {
    "name": "azdev-api-app",
    "version": "1.0.0",
    "type": "module",

    ...
  }

This step is needed because we will be using JavaScript ESM modules in this project and Node supports a different type of modules (named commonjs). We have to tell Node which type this project will be using.

You will need to run Node version 12.x or higher for the JavaScript ESM modules to work natively. Alternatively, you can install the "esm" package from npm and alias the node command as node -r esm (and don’t add the type property to package.json in that case).

With a package.json file in the root of this project, we can now document the project dependencies. We need 5 dependencies and we can get them all using the npm install command:

$ npm install express graphql express-graphql pg mongodb

The express package will help us run a web server to host the GraphQL service. The graphql package is for GraphQL.js, the JavaScript implementation of GraphQL. It’ll take care of things like validating GraphQL operations and executing them.

To work with a GraphQL runtime, we need an interface. This is where the express-graphql package comes in handy. It has an HTTP listener function that can interface a GraphQL schema and is designed to use with a middleware-based Web framework like Express.

The pg and mongodb packages are the database drivers. They expose JavaScript APIs to execute operations for PostgreSQL and MongoDB. We’ll need to configure them to connect to these database services. Let’s start by testing these drivers on the local database schemas that we designed so far.

5.2. Environment Variables

Create a directory under azdev-api to host the files of the project:

$ mkdir lib

Under lib, create a file named config.js. This is the file that’ll define any configurable variables the API is going to use. You need to be able to run the GraphQL API service with different configurations in production environments. This is why the config.js will define its variables by reading from the process "environment". Here’s a starting point for config.js:

Listing 4. 16. lib/config.js | az.dev/gia
export const pgConfig = {
  database: process.env.PG_DATABASE,
};

export const mongoConfig = {
  database: process.env.MONGO_DATABASE,
  url: process.env.MONGO_URL,
};

When it’s time to run this service in production, we’ll define different process environment variables. For local development, create an .env file somewhere and seed it with the following lines:

Listing 4. 17. .env | az.dev/gia
export PG_DATABASE="YOUR_PG_DEV_DB_NAME_HERE"

export MONGO_DATABASE="azdev"
export MONGO_URL="mongodb://localhost:27017"

This .env file should be ignored by your source control tools. You’ll have a different .env file (or something similar) for your production environment.

To get the process environment variables defined, you can use the source command:

$ source .env

You’ll need to do that before running the service. Don’t forget it!

In production (and some local environments), you’ll also have to specify a USER and PASSWORD to be able to connect to databases. Figure out if you need that in your local environment. For most local environments, leaving these out will get you connected to a local user that matches your operating system logged-in user.

5.3. Connecting To Databases

Before a client can execute commands on a database and retrieve data out of it, it needs to "connect" to it. There are many ways to connect to both PostgreSQL and MongoDB from the Node drivers. We can do a one-time connection per sql statement in the pg driver but the better way is to have the driver manage a pool of open connections and reuse these connections as needed. Both drivers support this mode.

ch04 fig 05 gqlia
Figure 4. 5. Keeping pools of open connections to databases

Here are the files we need to manage a connections pool for both PostgreSQL and MongoDB:

Listing 4. 18. lib/db-clients/pg.js | az.dev/gia
import { Pool } from 'pg';
import { pgConfig } from '../config';

export default async () => {
  const pool = new Pool(pgConfig);
  console.log('Connected to PostgreSQL...');
  pool.on('error', err => {
    console.error('Unexpected PG client error', err);
    process.exit(-1);
  });
  return {
    pgdb: pool,
    pgClose: async () => await pool.end(),
  };
};
Listing 4. 19. lib/db-clients/mongo.js | az.dev/gia
import { MongoClient } from 'mongodb';
import { mongoConfig } from '../config';

export default async () => {
  const client = new MongoClient(mongoConfig.url);
  try {
    await client.connect();
    console.info('Connected to MongoDB...');
    return {
      mdb: client.db(mongoConfig.database),
      mongoClose: () => client.close(),
    };
  } catch (err) {
    console.error('Error in MongoDB Client', err);
  }
};
Drivers change their API often! Please check az.dev/gia to for the latest text of the above 2 files in case these API changed.

Put these new files under lib/db-clients. The next script is going to use them to connect to the databases and load some seed data in them.

The code in the above 2 files is just my preferred method of connecting to these databases. I make use of async/await for the MongoClient but you can also use a .then/.catch syntax or even use callbacks if you want.

Note how I made both files export an async function although that was not needed for the PostgreSQL pool. I also made both functions return an object with similar purposes. I find this kind of normalization helpful for entities in the project that are meant to do similar things.

5.4. Loading Seed Data

Having test-data in all tables and collections is a great way to get us started and enable us to test the GraphQL queries before dealing with the GraphQL mutations! Make sure you get some realistic test data in all the database tables one way or another.

Don’t use short or random data when you’re seeding your test database. Try to have production-quality data even for testing. You’re not building an API to work with Foos or Bars. This is why the seed script I prepared features real Need entries with real content that I’ll be entering on the production site once its ready.

The tables and collections we’ve designed require valid data. You’ll have to insert a user record first and then use its id to insert a Need record. You need to have an "id" for an Approach before instructing MongoDB to add any dynamic data elements to it. This means we need a write followed by a read for each record we insert to PostgreSQL.

We can simplify the script we need here by picking our own "id" values instead of having to read them after each insert but PostgreSQL has a way to do both write and read operations in one statement! This is beyond the scope of this book but the seed script I’ve prepared makes use of this powerful technique.

This script and its associated data elements are too big to include in here. You can find them both at this book’s code helper link: az.dev/gia. Use this link to find the text for both seed-data/index.js and seed-data/data.js. Add these files to your project and then execute the following command:

Listing 4. 20. Loading the seed data
$ node seed-data/index.js

If everything runs successfully, you should have 5 Need entries with their Approaches and some extra dynamic data elements in MongoDB for each Approach. Use the following SQL queries to see the data in PostgresQL:

Listing 4. 21. In "psql": queries to read data | az.dev/gia
SELECT * FROM azdev.users;

SELECT * FROM azdev.needs;

SELECT * FROM azdev.approaches;

For the data in MongoDB, you can use this find command to see it:

Listing 4. 22. In "mongo": command to read the approaches data | az.dev/gia
db.approaches.find({});

6. Summary

In this chapter, we prepared for building a real-data GraphQL API by creating and seeding the sources of data which the API will be communicating with. Here are the main things we learned:

  • An API server is an interface to one or many data sources. GraphQL is not a storage engine, it’s just a runtime that can power an API server.

  • An API server can talk to many "types" of data services. Data can be quered from databases, cache services, other APIs, files, etc.

  • Relational databases (like PostgreSQL) are a good fit to store relations and well-defined constrained data. Document databases (like MongoDB) are great for dynamic data structures.

  • The first step to design a GraphQL API is to draft a list of operations that will theoratically satisfy the needs of tha application that you’re building. Operations include queries, mutations, and subscriptions.

  • Draft GraphQL operations can be used to design the tables and collections in databases.

  • You should utilize the powerful data integrity constraints and schema validators that are natively offered by database services.

  • Seed your test databases with realistic production-like data to make your tests relevant and useful.