DEV Community

Assim EL HAMMOUTI
Assim EL HAMMOUTI

Posted on • Updated on

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)"
Enter fullscreen mode Exit fullscreen mode

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

brew update
brew install postgresql
brew services start postgresql
Enter fullscreen mode Exit fullscreen mode

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

postgres --version
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

TablePlus

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
  }
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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

"scripts": {
  "start": "nodemon"
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'))
Enter fullscreen mode Exit fullscreen mode

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!
  }
`
...
Enter fullscreen mode Exit fullscreen mode

Setup TypeORM:

As usual, we need to install some packages first :

yarn add typeorm reflect-metadata pg
yarn add @types/node -D
Enter fullscreen mode Exit fullscreen mode

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

...
"emitDecoratorMetadata": true,
"experimentalDecorators": true,
...
Enter fullscreen mode Exit fullscreen mode

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"]
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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.")
});
Enter fullscreen mode Exit fullscreen mode

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
  }
`;
...
Enter fullscreen mode Exit fullscreen mode

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)
    },
  },
}
...
Enter fullscreen mode Exit fullscreen mode

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

Mutation screenshot

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:

Mutation screenshot

Aaand we got the user back πŸŽ‰πŸŽ‰

Discussion (6)

Collapse
jimmymcbride profile image
Jimmy McBride • Edited on

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

Collapse
jimmymcbride profile image
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,
    },
  });
Collapse
karladler profile image
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.

Collapse
jimmymcbride profile image
Jimmy McBride

They do! :) Posted the answer above! πŸ”₯

Collapse
ruqianq profile image
ruqianq

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

Collapse
hypeofpipe profile image
Volodymyr V.

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