Issues with running migrations programatically

[revmischa]

I'm trying to figure out how to run prisma migrations using AWS Lambda. I am not using the Serverless framework, I am using this template (why) and trying to adapt it to Prisma.

I don't really understand how I can run migrations. My idea is to create a lambda function that runs the migrations programatically. I don't really see an API for doing so. I tried to cram the CLI migration script into a NodejsFunction CDK construct but I have encountered a problem doing that. When I run the script I get:

{
  "errorType": "Runtime.ImportModuleError",
  "errorMessage": "Error: Cannot find module '../package.json'\nRequire stack:\n- /var/task/index.js\n- /var/runtime/UserFunction.js\n- /var/runtime/index.js",
  "trace": [
    "Runtime.ImportModuleError: Error: Cannot find module '../package.json'",
    "Require stack:",
    "- /var/task/index.js",
    "- /var/runtime/UserFunction.js",
    "- /var/runtime/index.js",
    "    at _loadUserApp (/var/runtime/UserFunction.js:100:13)",
    "    at Object.module.exports.load (/var/runtime/UserFunction.js:140:17)",
    "    at Object.<anonymous> (/var/runtime/index.js:43:30)",
    "    at Module._compile (internal/modules/cjs/loader.js:1068:30)",
    "    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1097:10)",
    "    at Module.load (internal/modules/cjs/loader.js:933:32)",
    "    at Function.Module._load (internal/modules/cjs/loader.js:768:16)",
    "    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)",
    "    at internal/main/run_main_module.js:17:47"
  ]
}

Looking through @prisma/migrate I see a number of places where it tries to require ../package.json. This does not work with JavaScript bundling because all dependencies are bundled into a single file, there is no node_modules directory structure. AWS's official NodeJsFunction uses esbuild to bundle everything into an index.js.

Questions:

• How should I be running DB migrations in a serverless (not Serverless) environment?
• Can I run migrations programmatically without needing a shell?
• Can @prisma/migrate not explode on import if it's part of a bundle and doesn't have a ../package.json file in the local path?

Migration script:

This is what I hacked together:

import { Migrate } from "@prisma/migrate"
import { ensureDatabaseExists } from "@prisma/migrate/dist/utils/ensureDatabaseExists"
import { printFilesFromMigrationIds } from "@prisma/migrate/dist/utils/printFiles"
// import { getSchemaPath } from "@prisma/sdk"
import chalk from "chalk"

export const handler = async (): Promise<string> => {
  const schemaPath = "./prisma/schema.prisma"
  console.log("path", schemaPath)

  const migrate = new Migrate()
  const wasDbCreated = await ensureDatabaseExists("apply", true, schemaPath)
  if (wasDbCreated) {
    console.info() // empty line
    console.info(wasDbCreated)
  }

  const diagnoseResult = await migrate.diagnoseMigrationHistory({
    optInToShadowDatabase: false,
  })
  const listMigrationDirectoriesResult = await migrate.listMigrationDirectories()

  if (listMigrationDirectoriesResult.migrations.length > 0) {
    const migrations = listMigrationDirectoriesResult.migrations
    console.info(`${migrations.length} migration${migrations.length > 1 ? "s" : ""} found in prisma/migrations`)
  } else {
    console.info(`No migration found in prisma/migrations`)
  }

  const editedMigrationNames = diagnoseResult.editedMigrationNames
  if (editedMigrationNames.length > 0) {
    console.info(
      `${chalk.yellow("WARNING The following migrations have been modified since they were applied:")}
${editedMigrationNames.join("\n")}`
    )
  }

  const { appliedMigrationNames: migrationIds } = await migrate.applyMigrations()

  migrate.stop()

  console.info() // empty line
  if (migrationIds.length === 0) {
    return chalk.greenBright(`No pending migrations to apply.`)
  } else {
    return `The following migration${migrationIds.length > 1 ? "s" : ""} have been applied:\n\n${chalk(
      printFilesFromMigrationIds("migrations", migrationIds, {
        "migration.sql": "",
      })
    )}
      
${chalk.greenBright("All migrations have been successfully applied.")}`
  }
}

And the CDK construct:

export class MigrationScript extends Construct {
  constructor(scope: Construct, id: string, { vpc, db }: DatabaseScriptProps) {
    super(scope, id)

    // migrate script
    const func = new NodejsFunction(this, "migrate", {
      timeout: Duration.seconds(20), // todo: config
      memorySize: 2048,
      projectRoot: `${__dirname}/..`,
      environment: {
        NODE_OPTIONS: "--enable-source-maps",
      },
      runtime: Runtime.NODEJS_14_X,
      bundling: {
        sourceMap: true,
        commandHooks: {
          beforeInstall: () => [],
          afterBundling: () => [],
          beforeBundling: (inputDir: string, outputDir: string): string[] => {
            return [
              // need to copy over migration files
              `cp -r "${__dirname}/../../../../repo/prisma" "${outputDir}"`,
              `cp -r "${__dirname}/../package.json" "${outputDir}"`,
            ]
          },
        },
      },
      vpc,
    })

    // allow DB access
    db.connections.allowDefaultPortFrom(func)
  }
}

[ryands17]

Currently we do not support running migrate programmatically yet. You can use exec to run prisma migrate and that would give you the same output.

As for bundling, I would suggest keeping prisma and @prisma/client both in dependencies in your package.json so that they are bundled and you can add this to bundling to make sure that they are not packaged by esbuild and stay as node_modules.

bundling: {
  sourceMap: true,
  nodeModules: ['@prisma/client', 'prisma']
}

Then running prisma migrate via exec should work.

[revmischa]

I did see that but I assumed bundling the engine would increase my function size and I plan to have dozens of functions so would prefer to simply use the layer. Is my assumption correct?

[janpio]

Kinda. If you have many functions, it might make sense to put the full generated Client into a layer and use that across these functions. But splitting generated Client and the used Engine apart introduces a high risk of incompatibility and so on that will be hard to impossible to debug. I would really try to keep it the code and Engine together on one level - either function, or layer, but not cross boundaries.

[revmischa]

Okay thanks I take your point. I wonder how hard it would be to make a CDK construct that generates this layer automatically as part of the stack? I think it'd require the right combination of tsconfig/package.json/bundling props but should be possible

[janpio]

That I do not know much about honestly. Layers and CDK are quite advanced, so everyone seems to have very specific requirements that make it hard to generalize the solutions. If you get to one, please make sure to share it. Having a simple way to put your generated Client into a layer that is used across functions sounds like an awesome improvement in that regard.

[revmischa]

Definitely, I'm doing my best to generalize and share my solutions in jetbridge/jetkit-cdk#13

[revmischa]

One unfortunate side effect of using npx prisma generate in the bundle hook is that it runs for every function I'm deploying, and I plan to have a lot of functions.
In a perfect world it would generate it once and bundle only that generated node module with my function with everything else living in the layer.
I'm working on adding this all to https://github.com/jetbridge/freezy-backend/pull/21

[janpio]

Quick question parallel to our "figuring technical things out" comment thread above:

Why do you want to run Migrations via a serverless functions? Usually our users do that via the Prisma CLI in a build step that happens on CI or on their local machine. Do you have a use case where this would not work?

[revmischa]

Why do you want to run Migrations via a serverless functions? Usually our users do that via the Prisma CLI in a build step that happens on CI or on their local machine. Do you have a use case where this would not work?

I need to run the migrations against my Aurora Serverless database. Since there is no data API support (would make my life a lot easier) I need to run them somewhere with network access to the database in my VPC. Is there a better way to achieve this? I use GitHub actions, I'm not clear how I would run migrations from there or my local machine.

[janpio]

Network access via VPC is a very valid answer here.

I do not think we have a better answer than to use a bastion host inside the VPC, connect to that one via SSH and then run the commands from there. (Might still be less complexity that running Migrations via serverless directly)

[revmischa]

I've got it set up now in JetKit so that all you have to do in your stack is:

import { DatabaseMigrationScript } from "@jetkit/cdk"

new DatabaseMigrationScript(this, "MigrationScript", {
        vpc,
        db,
        prismaPath: `${__dirname}/../../repo/prisma`,
      })

And it constructs a lambda that runs migrations for you and returns the output as a string. You can invoke the lambda however you want.