Setup PostgreSQL with TypeORM in GraphQL Tutorial

NOTE: This is a cross-post from my newsletter. I publish each email on my blog one week after it’s sent. Subscribe to get more content like this earlier right in your inbox 💌!

Hi ! In this post, I'Il show you how I to setup PostgreSQL with TypeORM in a GraphQL server. This is the backend setup that I usually use for my backend's side projects.

This tutorial is focused on MacOS environment 🍏. I might write other tutorials later for Windows and Linux.

MacOS Postgres Setup

The easiest way to do it is by using Homebrew, which is a MacOS package manager.

To install Homebrew, run this command in the terminal:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Once the installation is over, we need to install the postgresql package:

brew update
brew install postgresql
brew services start postgresql

To make sure that everything got installed, the next command should return the version of the postgresql installed:

postgres --version

Create the Database

Now that we have PostgreSQL installed, we need to create a database that we will connect to. We'Il name our database graphqldb.

Run the next command in the terminal:

createdb graphqldb

This will create a new database for us. To connect to it for testing purposes, you can use TablePlus(Free).

To test if the database exists, create a new connection on TablePlus, give it a name, in my case I named it GraphqlDB, and give it a database name, which is graphqldb (the name of the database that we created earlier), then click on Test. If All the feilds turn green then the database is created and we're ready to connect to it.

Setup the project using Typescript

Now let's create a new project using typescript, to do that we need to install some dependencies.

First let's create a new project folder:

mkdir graphql-proj
cd graphql-proj

now that we're inside the project folder, we need to install typescript as a dev dependency and create a tsconfig file:

yarn init -y
yarn add typescript nodemon ts-node @types/node -D

Create a new file, tsconfig.json and paste the following code into it:

{
  "compilerOptions": {
    "lib": ["es5", "es6", "esnext", "dom"],
    "target": "es5",
    "module": "commonjs",
    "moduleResolution": "node",
    "outDir": "./build",
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "sourceMap": true
  }
}

We'il use nodemon so that we don't have to restart the server manually to test our changes. Create a nodemon.json file and paste the following code into it:

{
  "watch": ["src"],
  "ext": "ts",
  "exec": "ts-node ./src/index.ts"
}

Last but not least, add the following section to your package.json:

"scripts": {
  "start": "nodemon"
}

Create the GraphQL Server

For our GraphQL server, we'Il use graphql-yoga which is a pretty handly all-in-one package for creating GraphQL servers.

Now, add this package graphql-yoga:

yarn add graphql-yoga

To have a quick running graphql server, just copy the sample from graphql-yoga Quickstart section.

import { GraphQLServer } from 'graphql-yoga'

const typeDefs = `
  type Query {
    hello(name: String): String!
  }
`

const resolvers = {
  Query: {
    hello: (_, { name }) => `Hello ${name || 'World'}`,
  },
}

const server = new GraphQLServer({ typeDefs, resolvers })
server.start(() => console.log('Server is running on localhost:4000'))

Let's add a User type to the graphql schema so that later we can add/edit/delete users from our database:

...
const typeDefs = `
  type User {
    id: ID!
    name: String!
    email: String!
  }
  type Query {
    hello(name: String): String!
    user(id: ID!): User!
  }
`
...

Setup TypeORM:

As usual, we need to install some packages first :

yarn add typeorm reflect-metadata pg
yarn add @types/node -D

Once the installation finishes, add these two lines to your tsconfig.json:

...
"emitDecoratorMetadata": true,
"experimentalDecorators": true,
...

Create a ormconfig.json file to put our database configuration in it:

{
  "type": "postgres",
  "host": "localhost",
  "port": 5432,
  "database": "graphqldb",
  "synchronize": true,
  "logging": true,
  "entities": ["src/entities/**/*.ts"]
}

As you see in the config, we need to create a folder entities and put our entites into it.

In our case we will create a simple User.ts entity:

import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from 'typeorm'

@Entity()
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  name: string

  @Column()
  email: string
}

This entity has the same attributes as the GraphQL User type we created earlier.

Now inside index.ts we need to create the database connection before we start our server:

...

const server = new GraphQLServer({ typeDefs, resolvers });

createConnection().then(() => {
  server.start(() => console.log("Server is running on localhost:4000"));
}).catch(() => {
  console.log("Couldn't connect to the database.")
});

Create addUser Mutation

To test if the setup is as good as we want it to be, let's create a mutation that adds a user to the database.

First we need to update our schema:

...
const typeDefs = `
  type User {
    id: ID!
    name: String!
    email: String!
  }
  type Query {
    hello(name: String): String!
    user(id: ID!): User!
  }
  type Mutation {
    addUser(name: String!, email: String!): User
  }
`;
...

Now we need to resolve the addUser mutation and the user query, we'Il need to use TypeORM's getRepository (I'Il write another blogpost to details TypeORM ):

...
const resolvers = {
  Query: {
    hello: (_, { name }) => `Hello ${name || 'World'}`,
    // this is the user resolver
    user: (_, { id }) => {
      return getRepository(User).findOne(id)
    },
  },
  Mutation: {
    // this is the addUser resolver
    addUser: (_, { name, email }) => {
      const user = new User()
      user.email = email
      user.name = name
      return getRepository(User).save(user)
    },
  },
}
...

Let's add a user now using this mutation. Go to http://localhost:4000 and add a user:

As you can see on the right, we created a user with id 1.

Let's try to query the user we just added using his Id:

Aaand we got the user back 🎉🎉

Comments

[Jimmy McBride]

Solved: Answer in reply below

I have a TypeORM project I am trying to deploy to Heroku. Heroku's Postgres addon just gives me one database URL. That URL locally on my end would look like: postgres://user_name:password@localhost:5432/project_table

TypeORM spreads those things out a bit, though. In knex I can just do:

require("dotenv").config();

module.exports = {
  development: {
    client: "pg",
    // 🔻 Points to our local Postgresql database
    connection: process.env.DATABASE_URL,
  }
};

But I don't know how to consolidate that down just one variable in the ormconfig.ts. Any ideas? I'm really loving using TypeORM so far and would love to get it hosted! Lol

[Jimmy McBride]

This is how I have my TypeORM set up that works exactly the way I wanted it:

createConnection({
    name: "default",
    type: "postgres",
    url: process.env.DATABASE_URL,
    synchronize: true,
    logging: true,
    entities: ["src/entity/*.*"],
    extra: {
      ssl: process.env.SSL || false,
    },
  });

[Karl Adler]

You can extract all data you need from the connection string. This module may help a lot, but it's also manually not that hard.

npmjs.com/package/pg-connection-st...

But, yeah would be really cool if typeorm supports this by default somehow.

[Jimmy McBride]

They do! :) Posted the answer above! 🔥

[ruqianq]

Great article! One thing you might need to mention is the createConnection function is imported from typeorm

[Volodymyr V.]

Thx a lot! That it is a super useful tutorial for beginners!