Prisma Migrate with AWS Aurora Serverless
Tags: prisma, migrate, aurora, serverless
Published: 2021-02-02 15:17:23

## Introduction

In the previous post, we saw how we could deploy a GraphQL API using AWS AppSync and Prisma. Querying the database with Prisma Client worked great, but there was a minor issue with the overall workflow: We had to create the tables manually in the RDS Query Editor and sync them _manually_ with the Prisma model in `schema.prisma`.

In this post, we will look at how we can use [Prisma Migrate](https://www.prisma.io/docs/concepts/components/prisma-migrate) to create our tables with migrations. These migrations then can be directly run on any other setup with a single command.

## Before we move forward...

I am assuming that you have read the previous post in this series. We will be adding snippets to the current application that we have and deploy it. It's not necessary to deploy the application beforehand for this and so you would be good to go by just cloning the repo below and following along.

{% github ryands17/graphql-api-cdk-serverless-postgres no-readme %}

## Setup

In this section, we will see how to make our Aurora Serverless database accessible on our local machine via an EC2 instance and SSH.

### Intro

Amazon Aurora PostgreSQL Serverless databases are not accessible locally. They can only be accessed from the VPC in which they are deployed. So to add the table to our database locally, we will use a [Jump Box](https://en.wikipedia.org/wiki/Jump_server) (basically an EC2 instance) to forward our local connection to our remote database in the VPC.

### Creating an SSH key

For this we first need to create an SSH key. So in the AWS Console, navigate to EC2 and select the **Key Pairs** section from the left hand side of the menu. We will see a UI like this where we need to select the **Create key pair** option.

![Create a Key Pair](https://dev-to-uploads.s3.amazonaws.com/i/x6su4fb9mwg26ed7of01.png)

I have already one named `"prisma"` which I will be using for the rest of this walkthrough.

Next, let's add the name of this key to our `cdk.context.json` file like this:

```json
"keyName": "prisma"
```

### Adding CDK resources

Now let's add some snippets to our CDK file.

The very first snippet will tell AWS to fetch us the latest Ubuntu Server:

```ts
// appsync-cdk-rds-stack.ts

const ami = new ec2.LookupMachineImage({
  name: 'ubuntu/images/hvm-ssd/ubuntu-focal-20.04-amd64-server-*',
  filters: { 'virtualization-type': ['hvm'] },
  // Canonical AWS Account ID
  owners: ['099720109477'],
})
```

This will search for the latest Ubuntu image (20.04) which we will be using for our EC2 instance and set it to the `ami` variable.

The next snippet is for allowing SSH access to our EC2 instance and also to make sure that it can access our database.

```ts
// appsync-cdk-rds-stack.ts

const publicSg = new ec2.SecurityGroup(this, 'public-sg', {
  vpc,
  securityGroupName: 'public-sg',
})
publicSg.addIngressRule(
  ec2.Peer.anyIpv4(),
  ec2.Port.tcp(22),
  'allow SSH access'
)

privateSg.addIngressRule(
  publicSg,
  ec2.Port.tcp(5432),
  'allow Aurora Serverless Postgres access'
)
```

We have created a public security group so that we can port forward our database locally via the EC2 instance, which is why we have also opened port **22** on our public security group.

We have also given access to port **5432** which is our database port to our public SG from our private SG so that we can successfully access the database on our local machine.

### Creating our EC2 instance (Jump Box)

This last snippet is for creating our EC2 instance assigining all the above parameters:

```ts
// appsync-cdk-rds-stack.ts

new ec2.Instance(this, 'jump-box', {
  vpc,
  securityGroup: publicSg,
  vpcSubnets: { subnetType: ec2.SubnetType.PUBLIC },
  instanceType: ec2.InstanceType.of(
    ec2.InstanceClass.T2,
    ec2.InstanceSize.MICRO
  ),
  machineImage: ec2.MachineImage.genericLinux({
    [this.region]: ami.getImage(this).imageId,
  }),
  keyName: this.node.tryGetContext('keyName'),
})
```

Finally, we create our EC2 instance where we assign it our vpc, public security group and subnets. We have selected a **t2.micro** instance here to save costs as we just need to port forward our database. Finally, we have assigned the AMI's `imageId` to the `machineImage` which means we will be using Ubuntu 20.04 as our OS.

### Deploying to AWS

We're done with the setup so let's deploy our current stack using the following command:

```
yarn deploy
```

### SSH forwarding

After deploying, all our resources including our EC2 instance will be created. Now we shall move on to SSH forwarding. The basic command used will like this:

```sh
ssh -N -L 5432:aurora-serverless-hostname:5432 ubuntu@ipAddress -i keyName.pem -v
```

This command forwards our instance locally to port **5432** so now we can access our Serverless database as if it were present locally i.e. `localhost`.

By looking at the above command, we need the host name of the newly created database and the IP address of our EC2 instance.

The hostname for our database can be obtained by visiting Secrets Manager from the AWS Console and selecting the secret created.

![Retrieve database secret](https://dev-to-uploads.s3.amazonaws.com/i/gg77rd5d1j0x3pvgcfxx.png)

The IP address of the EC2 Instance can be obtained from the instances menu in the EC2 section of the AWS Console

![IP Address of the EC2 instance](https://dev-to-uploads.s3.amazonaws.com/i/3jkhsqhg5o0e37m6pzy1.png)

Now, we can run the SSH command specified above and replace the placeholder values with the ones we found from the steps above. After running this command you will see something like this in the console, which means that our port forwarding setup has been successfully completed.

Let's leave this running as is and move on to the next section.

## Creating and applying migrations with Prisma Migrate

Finally we have setup port forwarding so let's move on to creating the table by implementing our migrations with the Prisma CLI.

Currently our `schema.prisma` file look something like this:

```groovy
generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DB_URL")
}

model Post {
  id      String  @id
  title   String?
  content String?

  @@map("posts")
}
```

Looking at the [schema.prisma](https://github.com/ryands17/graphql-api-cdk-serverless-postgres/blob/main/lambda-fns/prisma/schema.prisma) file we have in our `lambda-fns/prisma` directory, we can see that we already have the model `Post` and our database URL in the `datasource` block which would need to be specified via the `DB_URL` environment variable.

So let's create an `.env` file in the `lambda-fns` directory and the placeholder values would look something like this:

```config
DB_URL="postgresql://username:password@localhost:5432/BlogDB"
```

In the above snippet, we only need to replace the `username` and `password` which can be obtained in the same place we fetched the database host i.e. from our secret created in Secrets Manager.

![Retrieve database secret](https://dev-to-uploads.s3.amazonaws.com/i/gg77rd5d1j0x3pvgcfxx.png)

Let's run `yarn db:save`. This command invokes the Prisma CLI's `prisma migrate dev --create-only` command that will create our migration stored in an SQL file. Which will look something like this after you accept the defaults in the prompt:

![Migration command successful](https://dev-to-uploads.s3.amazonaws.com/i/uwd6z4oxmna5nvepleqg.png)

And the created SQL migration:

```sql
-- CreateTable
CREATE TABLE "posts" (
    "id" TEXT NOT NULL,
    "title" TEXT,
    "content" TEXT,

    PRIMARY KEY ("id")
);
```

Now to create our table in the database, let's run `yarn db:migrate` which will again invoke Prisma CLI's `prisma migrate dev` which will run the SQL migrations created onto the database and our table will be created.

![Migration file created](https://dev-to-uploads.s3.amazonaws.com/i/prolssg50k0tugo0zaj1.png)

We can verify this by going to RDS in the AWS Console and selecting **Query Editor** from the left menu.

Selecting our secrets and entering the database name, we can now execute the following query:

```sql
select * from "posts"
```

And this will give us the following output:

![Query runs successfully](https://dev-to-uploads.s3.amazonaws.com/i/xymtipa1th15ecoc7zyz.png)

Yayy! We can finally see the database created with the migrations via Prisma Migrate! You can now play around with the AppSync API in a similar manner as the previous post.

## Conclusion

In this post, we created our database migrations with [Prisma Migrate](https://www.prisma.io/docs/concepts/components/prisma-migrate) and applied them on our Aurora Serverless database using an EC2 instance with SSH port forwarding that helps connect to our database which is not publicly accessible otherwise.

Here's the link to the repo again for those who want to dive in.

{% github ryands17/graphql-api-cdk-serverless-postgres no-readme %}

Thanks a lot for reading this post, and if you have implemented this stack, please **DO NOT FORGET TO DELETE THIS STACK** via

```
yarn cdk destroy
```