Adding Postgres to a Node app on DO's App Platform
Tags: dohackathon
Published: 2020-12-20 07:20:49

Source code available at https://github.com/ardentink/adr-api

# Backstory

I'm participating in [Digital Ocean's App Platform (DOAP) Hackathon](https://dev.to/devteam/announcing-the-digitalocean-app-platform-hackathon-on-dev-2i1k) and am building a graphQL server using:
* node
* postgres
* typescript
* graphql
* apollo-server
* type-graphql
* type-orm

In my last two posts I mostly rambled 😬 but talked about getting a basic node server deployed on DOAP. However it was just the graphQL server without a database. Today I need to add a persistence layer. I'll be using [TypeORM](https://typeorm.io/) and Postgres.

I created a [PR](https://github.com/ardentink/adr-api/pull/1) for all my changes in the hopes that you could see exactly where my code was before and after this process. I actually ran into some issues with the deploy after committing though so it might be easier to look at [this diff](https://github.com/ardentink/adr-api/compare/37b98e497da34ce4cf2651b5f4297501356b636b...669fe890c6c543330fb9772f375d0624d4ef7db8) instead.

The main steps were:
* Install Postgres locally
* Add TypeORM and DB migrations
* Create a Postgres component in DOAP
* Add a Job component in DOAP to run migrations

# Get Postgres working locally

There are a couple ways to get Postgres working locally including installing it directly on my machine. My company uses Docker though and I know that will make things easier in the long run so I'm going to do that instead.

I'm using a mac (thanks [SimpleNexus](https://simplenexus.com/sn/careers/)) so the best way to do this is to install the docker desktop app. You can download it directly from https://www.docker.com/products/docker-desktop, or through [Homebrew](https://brew.sh/) with `brew cask install docker`. Then start it up with `open -a Docker`.

Once Docker is running we need to start up our DB container. By looking at the [dockerhub page for Postgres](https://hub.docker.com/_/postgres) we find that the DB name, username, and password can all be set with environment variables. To do that we create a .env file and set the necessary variables there.

Then we can use docker-compose to pass all those variables to docker run: `docker-compose up -d {your container_name here}`.

And just like that we have Postgres running on our local machine.

# Installing TypeORM

Getting [TypeORM](https://typeorm.io/#/) working locally was pretty straightforward. I mostly just followed the guide on their website. I did hit some "gotchas" though so that's what I'll highlight here.

Because I'm using typescript the ormconfig.json file didn't work for me. There is a whole page on their site dedicated to using different configuration sources. I ended using a [typeorm.js file](https://typeorm.io/#/using-ormconfig/using-ormconfigjs). This allowed me to change my folder locations dynamically based on my my environment.

I don't know if it was how I'm using ts-node to run my files, but basically I wanted my local cli to run the typescript migrations in the src folder and production to run the compiled js migrations in my dist folder. That way I didn't have to run my build step every time I wanted to migrate my local db.

The other gotcha was that if you want UUIDs to be generated by the DB you need to add in the `uuid-ossp` extension. This just requires creating a DB migration with the following SQL statement: `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";`.

After that I updated my model with the entity and column tags, to connect it to my db. Then I ran my migrations and fired things up and everything worked.

# Adding Postgres to DOAP

Very first we need to create a Postgres component:

![Screenshot of DOAP's "Add Component" button](https://dev-to-uploads.s3.amazonaws.com/i/azf6s6tnu68obn7nspzx.png)

After selecting your tier and clicking "Create and Attach" DOAP will start provisioning your DB. Once it's fully provisioned DOAP shows you the connection credentials:

![Screenshot of DB Component connection credentials](https://dev-to-uploads.s3.amazonaws.com/i/bnv1vys6d2mvgmvgxgcn.png)

We need to get those connection details to our main app now as environment variables. Now you may be tempted to type these out, but DOAP actually has a neat concept called [bindable variables](https://www.digitalocean.com/docs/app-platform/how-to/use-environment-variables/#using-bindable-variables-within-environment-variables). These allow you to references variables in other DOAP components without typing raw strings:

![Screenshot of bindable variables in use](https://dev-to-uploads.s3.amazonaws.com/i/40wlpoad35dbqod8ja5h.png)

And with those all entered we should be good to deploy all our new code!

Chances are though that at this point you will get an error when your new code rolls out. Why? The database doesn't know about our models because we haven't run the migrations on our server.

# Create a Migration Runner

Well that's an easy fix. We just create a Job component and tell it to run the database migrations before each deploy. You can run migrations in TypeORM with the provided command: `typeorm migration:run`. I ended up adding this as the "migrate" script in my package.json. Then I had my new Job component use `npm run migrate` as my run command.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8r3ggg0ub4m66qgz9jm4.png)

And after the container re-builds with those changes... more errors 😢:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/szs1ypatwrkdnupkhbq0.png)

🤦🏻‍♂️ I forgot to add all my environment variables to the new container. Once again, this was quickly resolved by adding those.

And... we hit one last error:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/j1u8frl0n1xjxp2rb0rg.png)

This time it was complaining about a self-signed certificate. This actually took a little bit of googling to figure out but in the end I ended up on looking at these two issues:
* https://github.com/typeorm/typeorm/issues/278
* https://github.com/brianc/node-postgres/issues/2009

I'm still not sure if my fix is ok in production or not, but I ended up telling it to not warn about self-signed certs by  setting `rejectUnauthorized` to `false` in my ormconfig.js:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8x9n4ho4fkez4cwzfkly.png)

# Conclusion

All in all, adding Posgres and TypeORM was a pretty straightforward process. Definitely better than writing a complex docker-compose or Kubernetes yaml file. I did hit a few more errors than I expected but nothing too bad. Hopefully though this will help you get past any errors you encounter. Good luck!