Getting a Create Script from Prisma Schema

TL;DR: Use this package: prisma-sql-gen

Prisma provides a way to create a database from your schema using the following command:
npx prisma migrate dev.
However, this method has the following limitations:

• It uses migrations and performs checks, which makes it slow when the application has a large migration history.
• It requires a shell command to run, complicating app startup logic.

In my case, the migration folder contained approximately 100 migrations, with an additional 3-5 migrations being added monthly. With this amount, creating the database from scratch took nearly 10 minutes, which was a significant portion of the pipeline execution time. Prisma is the tool of choice for Node.js when dealing with databases, similar to Entity Framework for .NET or NHibernate for Java.

Now, imagine every team developing backend applications in Node.js having such a migration folder. What a waste of computational resources! Another concern is the use of npx. In modern Kubernetes environments, most developers equip Node.js apps with shell scripts, which will require npx and the Prisma CLI to be installed in the image. This increases the attack surface of the app and makes the image heavier.

Ideally, migration checks and database initialization should be handled in the app’s entry point. Fortunately, Prisma exposes its internal package used by the npx CLI:
@prisma/migrate.

Internally, it uses JSON-RPC to communicate with the core Prisma tool, written in Rust:
prisma-engines. You can also extract the raw create script using the CLI. The command that prints a large SQL script to the console is:
npx prisma migrate diff --from-empty --to-schema-datasource prisma/schema.prisma --script

All we need to do is find the correct command and intercept the JSON response from the Rust core. After exploring the migrate package:
prisma/migrate, I created the following snippet:

import { SchemaEngine } from '@prisma/migrate'
import { resolve } from 'path'

// Inherit the internal engine class
class GeneratorEngine extends SchemaEngine {
  public createScript: string
  constructor() {
    super({ projectDir: process.cwd(), schemaPath: '' })
    this.createScript = ''
    // Assign a custom handler to the JSON RPC response handler. Prisma's original handler is more complex, but we only need it for a single command.
    this['handleResponse'] = (response: string) => {
      try {
        this.createScript = JSON.parse(response).params.content
      } catch (error) {
        throw `Invalid response: ${error}, response ${response}`
      } finally {
        this.stop()
      }
    }
  }
}

// Wrap with an empty try-catch block, because the engine works with dummy handlers
export const schemaToScript = async (schemaPath: string = 'prisma/schema.prisma') => {
  const engine = new GeneratorEngine()
  try {
    await engine.migrateDiff({
      script: true,
      from: { tag: 'empty' },
      to: { tag: 'schemaDatamodel', schema: resolve(schemaPath) },
    })
  } catch (error) {}

  return engine.createScript
}