Using a GraphQL schema to generate your Hasura database
Tags: graphql, postgres, fauna, tutorial
Published: 2021-02-13 19:22:20

**tldr;** You have huge amount (30+) models/tables in a SDL-type graphql schema (like Fauna)? Use Prisma migrate to generate a postgres database (or update your schema) in Hasura, dump it, clean it, and track it all again in Hasura.

---

Say you have a (large) schema written in SDL'ish like this
```graphql
model Post {
  id        Int      @default(autoincrement()) @id
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}
model User { ... }
## 40+ other models!
```

that you would like to use to generate a new database/schema in [Hasura](https://hasura.io/docs/1.0/graphql/cloud/index.html) (i.e. the Postgres database). This is how we will do it:

1. Use **Prisma Migrate** to populate the Postgres Database
2. Use **Hasura metadata "reload"** so that Hasura gets back in sync with the postgres database
3. **Track** the tables and relationships in Hasura
4. Realize its not exactly what you want so dump it and clean it, and re-track it.

🎉 Voilà!

PS: I am not affiliated to Fauna, Prisma or Hasura.

---
OK let's do this...


###Getting Started

Make sure you have Hasura or [Hasura Cloud (its free)](https://hasura.io/docs/1.0/graphql/cloud/index.html) project running. In this example we use Hasura Cloud.

###1. Bring Prisma into to your project
This is just to use prisma migrate. This is based on [this start guide from prisma](https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch-typescript-postgres#create-database-tables-with-prisma-migrate).

- Create a folder called `prisma_migrate` or `prisma_stuff` anything so that the prisma init later doesn't mess up your project's tooling.
```console
$ mkdir prisma_migrate; cd prisma_migrate
```

- Install prisma and typescript 
```console
$ npm install prisma typescript ts-node @types/node --save-dev
```

- Add a `tsconfig.json` in your `prisma_migrate` folder
```json 
{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true
  }
}
```
- Initiatilize prisma to get the starter files you need. It will create a `prisma` directory inside your `prisma_generate` folder for you and put `schema.prisma` in there and also `.env` <-- this is why we created a sub folder, else that would override your own `.env`

```console
$ npx prisma init
```

---

###2. Point Prisma to your Postgres database
- Copy the `HASURA_GRAPHQL_DATABASE_URL` variable in your Hasura project settings and paste it in the `DATABASE_URL` variable of the `.env` file that Prisma generated
- Now if you want prisma to generate this into a new schema, just add a `?schema=myapp_test` that will create a new schema in your postgres and Hasura will pick it up later. (This avoids overrides of any existing shcema you have in Hasura), so your `.env` file looks something like this (see the end):
```js
DATABASE_URL="postgresql://postgres:XXXXX@potatoes.ce6gsvwmjbvx.us-east-2.rds.amazonaws.com:5432/postgres?schema=potatoes_dev"
```
*Notice that I am using AWS RDS to host my Postgres database, but you could use anything that Hasura supports.*

---

###3. Get your SDL schema
OK. We are ready to edit the `schema.prisma` file by adding our existing schema. This needs to be compatible with Prisma ([see prisma's guide for more](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model)). So if you have say a [Fauna DB SDL schema](https://docs.fauna.com/fauna/current/start/graphql#schema), make sure to adjust it (still quicker than writing your own SQL migration files in Hasura!)

```graphql
model Post {
  id        Int      @default(autoincrement()) @id
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}
model Profile {
  id     Int     @default(autoincrement()) @id
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}
model User {
  id      Int      @default(autoincrement()) @id
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}
```

---


###4. Generate the schema/database in postgres
Now that you have your schema in a happy Prisma SDL format you can run prisma migrate to leverage Prisma's magic in generating Postgres tables.
```
$ npx prisma migrate dev --name init --preview-feature
```
That will create the 3 tables in Postgres.

---

###5. Reload your Hasura metadata
You can now reload your schema in Hasura cloud using the cli or the console [learn more here](https://hasura.io/docs/1.0/graphql/core/migrations/manage-metadata.html#reloading-hasura-metadata).
![Reload metadata](https://hasura.io/docs/1.0/_images/metadata-reload1.png)

---

###6. Track all the new tables and relationships
Go to your new schema in the Hasura console track the new tables and relationships. [See how here](https://hasura.io/docs/1.0/graphql/core/schema/using-existing-database.html#step-1-track-tables-views).

#####Before
![image](https://dev-to-uploads.s3.amazonaws.com/i/1ez9hkuhnrshv4mj3bbq.png)

#####After
![image](https://dev-to-uploads.s3.amazonaws.com/i/5aehfx5zln3d0y128m7v.png)

####Track the relationships
![image](https://dev-to-uploads.s3.amazonaws.com/i/r3edv5856zr6xp35y92n.png)
 
---

🚀 You can now use GraphQL with all the queries and mutations ready! 


---

###7. Some gotchas

####7.1 Coming from Faunadb

* Missing `id` fields: if you are coming from Fauna, you will need to add your own `id` fields and `create_at` and `update_at` as Fauna generates the `_id` and `_ts` itself. I wrote a little script that parsed the .graphql file and injects it at the top of each model/type, something like this:
```js
lines.push("id           Int      @id @default(autoincrement())")
lines.push("created_at   DateTime @default(now())")
lines.push("updated_at   DateTime @updatedAt")
``` 

* Changing Long and Date/Time fields: Fauna uses Long, and DateTime. We need to change those, here is a handy script (assuming you are parsing your file and line is each line):
```js
if (line.includes(":")) {
    const regex = /([^:]*):\s*(\[?)([^@!\]#/]*)(\]?)!?/m;
    const subst = `$1\t\t$3$2$4? `;
    line = line.replace(regex, (m:any,g1:string,g2:string,g3:string,g4:string) => {
        if(g3.trim() == "Time") {
            return `${g1}\t\tDateTime${g2}${g4}? @db.Time() `
        } else if(g3.trim() == "Date") {
            return `${g1}\t\tDateTime${g2}${g4}? @db.Date `
        } else {
            g3 = g3.replace("Long","Int");
            return `${g1}\t\t${g3}${g2}${g4}? `
        }
    });
    // remove optional list (i.e. []?) not allowed in prisma
    line = line.replace("[]?","[]");
    return line;
  }
```

####7.2 Prisma don't like `uuid()`
* Prisma's `uuid()` function as a [`@default`](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference/#default) gets ignored by Hasura, so you will need to update the metadata in Hasura to use their `gen_random_uuid()` instead.
* Once you change the schema with a new function, running the prisma migrate command will cause Prisma Migrate to panic with a `✔ Drift detected: Your database schema is not in sync with your migration history.` message, forcing a full reset. So I suggest just using Prisma migrate for an initial port of your schema.

###8. Still having issues?
You might find it easier after doing the first prisma migrate to dump the schema to a `.sql` file and then do some batch editing to change things like `id INT` to `id uuid` etc., here is how:

####8.1 Dump the schema
Use Postman or curl to `POST` to the pd_dump of Hasura:
```js
// endpoint: POST https://xxxxxxx.hasura.app/v1alpha1/pg_dump
// body:
{
  "opts": ["-O", "-x", "--schema-only", "--schema", "potatoes_dev"],
  "clean_output": true
}
```
####8.2 Edit the .sql file
The above will return a dump of the schema, starting with `CREATE SCHEMA...`

You can edit the file to "replace all" for things like table names and relationships, just keep in mind the following:

1. When changing from `INT` to `UUID` for your table id:

* you no longer need the `CREATE SEQUENCE..` for those id columns,
* make sure to replace the foreign keys for example if the `Author` table has a `uuid` as id, then the `Post` table's `authorId` column needs to change from `authorId int` to `authorId uuid` in its `CREATE TABLE...` section

2. Prisma uses `A` and `B` for many-to-many relationships. I'd suggest replacing those with more readable names like `PostId` and `TagId` for the `_PostTag` relationship table.
```sql
-- Ugly and unreadable! --
CREATE TABLE "_PostTag" (
"A" integer NOT NULL,
"B" integer NOT NULL
)

-- Way better --
CREATE TABLE "_PostTag" (
"postId" integer NOT NULL,
"tagId" integer NOT NULL
)
```

####8.3 Delete the schema and run the edited .sql file
You can now delete the schema from within the Hasura console, and run the above sql file from either the console or using any Postgres admin tool like [pgAdmin](https://www.pgadmin.org/). pgAdmin gives great feedback incase you didn't do a proper clean up or rename of items in our sql file.

####8.4 Reload the schema in Hasura
Refresh the Hasura console and notice the schema is there, track the tables and relationsips as described in 6. above