Using a GraphQL schema to generate your Hasura database

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

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 (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) 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.

• Create a folder called prisma_migrate or prisma_stuff anything so that the prisma init later doesn't mess up your project's tooling.

$ mkdir prisma_migrate; cd prisma_migrate

• Install prisma and typescript

$ npm install prisma typescript ts-node @types/node --save-dev

• Add a tsconfig.json in your prisma_migrate folder

{
  "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

$ 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):

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). So if you have say a Fauna DB SDL schema, make sure to adjust it (still quicker than writing your own SQL migration files in Hasura!)

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.

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.

Before

After

Track the relationships

🚀 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:

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):

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 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:

// 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

1. 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.

-- 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. 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