iTranslated by AI
Prisma Migrations on Cloud Build Using Secret Manager
I had the opportunity to look into migrations using Google Cloud Build, and I decided to try it out with Prisma. This is a record of that process.
Key Points
- With Prisma, the flow is to create migration files using development commands and apply them to the production database using production commands.
- To execute production commands from Cloud Build, you need to connect to Cloud SQL via the Cloud SQL Proxy. Secret Manager is used here.
- In Cloud Build, you can call a container image that wraps the App Engine environment, so we will use that to execute the migration commands.
Version Information
| Name | Version |
|---|---|
| Prisma | 3.8.1 |
| Google Cloud SQL PostgreSQL | 14 |
Generating Prisma Migration Files in a Local Environment
Prisma creates migration files using development commands. Let's try it out and follow the flow.
Installing the Prisma CLI
First, we'll start with the Prisma CLI. Let's proceed while looking at the official documentation.
mkdir google-cloud-prisma-migrate
cd google-cloud-prisma-migrate
yarn init --yes
yarn add -D prisma typescript ts-node @types/node
Next, prepare tsconfig.json. Prisma automatically generates useful TypeScript types from the database schema information. Since you will be using TypeScript as a set, prepare tsconfig.json as well.
{
"compilerOptions": {
"sourceMap": true,
"outDir": "dist",
"strict": true,
"lib": ["esnext"],
"esModuleInterop": true
}
}
Initialize it.
yarn prisma init
✔ Your Prisma schema was created at prisma/schema.prisma
You can now open it in your favorite editor.
At this point, it only creates the configuration file for connecting to the database, so the database does not need to be running.
Connecting to the Local Database
yarn prisma init should have generated schema.prisma. You will use this file as the source of truth to connect to the database and generate migration files. You should see a description like the following:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
It can be seen that the connection uses the environment variable DATABASE_URL. Let's use docker compose for the local database.
version: "3"
volumes:
db-data:
services:
db:
image: postgres:14
container_name: google-cloud-prisma-migrate-db
volumes:
- db-data:/var/lib/postgresql/google_cloud_prisma_migrate_db_development/data
ports:
- "5432:5432"
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
Start it up.
docker compose up
Next, set the environment variable DATABASE_URL. While the official documentation suggests using .env, I will use direnv because it's easier to prepare.
pwd
> google-cloud-prisma-migrate
direnv edit .
The .envrc edit screen should appear.
export DATABASE_URL="postgresql://postgres:password@localhost:5432/google_cloud_prisma_migrate_db_development?schema=public"
After saving, run direnv allow in the terminal. Now, DATABASE_URL is set in the google-cloud-prisma-migrate directory. Preparation is complete.
Generating Migration Files
Once you are ready to connect to the DB, edit the schema file. Add table information to the schema.prisma file, which I mentioned earlier would be the "source of truth". For this example, let's assume a blog site and create a table to store article information.
+model Post {
+ id String @id @default(uuid())
+ title String
+ emoji String?
+ published Boolean? @default(false)
+ publishDate DateTime? @map("publish_date")
+ like Int @default(0)
+ createdAt DateTime @default(now()) @map("created_at")
+ updatedAt DateTime @updatedAt @map("updated_at")
+
+ @@map("posts")
+}
By default, table names are in PascalCase and column names are in camelCase. Since I wanted to match the familiar Rails model structure as much as possible, I used @@map to change the names. For more details, please refer to the Schema Reference.
Now, run the development migration command.
yarn prisma migrate dev --name init
This command performs two tasks:
- Generates
prisma/migrations/20220127104449_init/migration.sql. - Executes the migration on the local database (the destination connected via
DATABASE_URL).
The migration file will be referenced as a diff when applying it to production. Therefore, it should be subject to version control with Git or similar tools. If you access the local DB with an SQL viewer, you can see that the database and table have been generated.
The work on the database for the local environment is now complete. While we're at it, let's briefly touch on the _prisma_migrations table that was created along with posts. Those who have worked with Ruby on Rails might have an idea. According to the documentation, it is used for the following check purposes:
- Whether a migration has been executed against the database
- Whether an applied migration has been deleted
- Whether an applied migration has been modified
So, it seems that keeping the migration history helps maintain consistency.
Running Migrations from Cloud Build to Cloud SQL
Now for the main topic. We will execute migrations from Cloud Build against PostgreSQL set up in Cloud SQL. The tasks are as follows:
- Create
cloudbuild.ymland aDockerfile - Create Cloud SQL and Artifact Registry using Terraform
- Execute the Cloud Build trigger
Create cloudbuild.yml and a Dockerfile
First, let's check the method for Prisma migrations in a production environment.
In production and testing environments, use the migrate deploy command to apply migrations:
npx prisma migrate deploy
So it seems it can be applied by running the prisma migrate deploy command. Simple enough.
Next, let's research how to perform DB migrations with Google Cloud Build.
[cloud build migration]
Oh...? It looks like others have struggled a bit here. However, the use of Cloud SQL Proxy seems to be a common point. Since I know that I can use prisma migrate deploy once I can connect to the DB, I'll change my search terms.
[cloud build sql proxy]
I want a more specific example. DB migrations usually bring Rails to mind, so I'll search along those lines.
[cloud build rails migration]
The cloudbuild.yaml file performs not only the typical image build steps (creating a container image and pushing it to Container Registry) but also the Rails database migration. This requires access to the database, which is performed using the app-engine-exec-wrapper (a helper for the Cloud SQL Auth proxy).
This is it. It's a brief paragraph titled "Automation with Cloud Build," but I think it deserves some emphasis! This is exactly it. This description.
- id: "apply migrations"
name: "gcr.io/google-appengine/exec-wrapper"
entrypoint: "bash"
args:
[
"-c",
"/buildstep/execute.sh -i gcr.io/${PROJECT_ID}/${_SERVICE_NAME} -s ${PROJECT_ID}:${_REGION}:${_INSTANCE_NAME} -e RAILS_MASTER_KEY=$$RAILS_KEY -- bundle exec rails db:migrate"
]
secretEnv: ["RAILS_KEY"]
There are two elements I want to dig into:
- What is
gcr.io/google-appengine/exec-wrapper? - What is
secretEnv: ["RAILS_KEY"]?
What is gcr.io/google-appengine/exec-wrapper?
It is said to be a wrapper for the App Engine Flexible Environment. It seems that people at Google Cloud prepared it because they thought it would be useful to have such an image for migration purposes.
This is a wrapper Docker image that sets up an environment similar to the Google App Engine Flexible Environment, suitable for running scripts and maintenance tasks provided by an application deployed to App Engine. In particular, it ensures a suitable Cloud SQL Proxy is running in the environment.
Its driving use case is running production database migrations for Ruby on Rails applications, but it is also useful for Django applications, and we expect similar uses for other languages and frameworks.
Looking at the usage examples in the README:
steps:
- name: "gcr.io/google-appengine/exec-wrapper"
args: ["-i", "gcr.io/my-project/appengine/some-long-name",
"-e", "ENV_VARIABLE_1=value1", "-e", "ENV_2=value2",
"-s", "my-project:us-central1:my_cloudsql_instance",
"--", "bundle", "exec", "rake", "db:migrate"]
It looks like this:
-
-i: Specifies the execution image. In the case of this article, it seems we will prepare a container image that can execute Prisma. -
-e: Specifies environment variables. If using Prisma, we would likely specifyDATABASE_URLhere. -
-s: Specifies the Cloud SQL instance. - Followed by the migration command.
While it primarily assumes Ruby on Rails, since you specify a container image, any environment should work. Initially, I had the impression that it would be difficult until I found this wrapper, but using gcr.io/google-appengine/exec-wrapper makes it easy to create a situation where you are connected to Cloud SQL.
What is secretEnv: ["RAILS_KEY"]?
This is another point to clarify. Like RAILS_KEY, there are situations where sensitive information is required during migration execution. In the example using Prisma, DATABASE_URL would likely include the DB username and password, so DATABASE_URL constitutes sensitive information. The following article is helpful for handling secrets in Cloud Build.
To extract the points:
To reference a secret, you must specify bash as the entrypoint.
The args must start with -c. The string following -c becomes the execution command.
The secret name must be prefixed with $$.
https://cloud.google.com/build/docs/securing-builds/use-secrets#configuring_builds_to_access_the_secret_from
This is why it's written in the same way as the Google Cloud Rails tutorial to combine gcr.io/google-appengine/exec-wrapper with Secret Manager.
From what I understand so far, the cloudbuild.yml output would look something like this:
steps:
- id: "apply-migrations"
name: "gcr.io/google-appengine/exec-wrapper"
entrypoint: "bash"
args:
- "-c"
- "/buildstep/execute.sh -i [Container image that can use Prisma] -e DATABASE_URL=$$DATABASE_URL -s $_CLOUDSQL_INSTANCE_FULL_NAME -- yarn prisma migrate deploy"
secretEnv: ["DATABASE_URL"]
dir: "google-cloud-prisma-migrate"
timeout: 2000s
substitutions:
_CLOUDSQL_INSTANCE_FULL_NAME: "Cloud SQL instance name (like my-project:us-central1:my_cloudsql_instance)"
_ARTIFACT_REPOSITORY_IMAGE_NAME: "Image name in Artifact Registry"
availableSecrets:
secretManager:
- versionName: projects/$PROJECT_ID/secrets/DATABASE_URL/versions/latest
env: DATABASE_URL
Since I realized an image is needed, I'll create a Docker image that can execute the migrations.
Creating the Dockerfile
In this example, we aren't running the application itself, so it's fine as long as the Prisma CLI can be executed. Without overthinking it, I'll define it as follows:
# syntax=docker/dockerfile:1
FROM node:16
# Because Prisma CLI was installed as devDependencies
ENV NODE_ENV=development
# Required for migration
RUN apt-get -qy update
RUN apt-get -qy install openssl
WORKDIR /app
COPY package.json ./
COPY yarn.lock ./
COPY prisma ./prisma
RUN yarn install
COPY . .
CMD ["yarn", "prisma", "migrate", "deploy"]
The task of creating this image will also be performed in cloudbuild.yml (described later).
Create Cloud SQL and Artifact Registry with Terraform
Create the resources on Google Cloud. We'll set up Cloud SQL, but let me explain why Artifact Registry is necessary. The App Engine wrapper image, gcr.io/google-appengine/exec-wrapper, requires an image to execute commands. Originally, this wrapper seems to assume Cloud Run applications and is provided as a means to execute arbitrary operation commands (like migrations or Rake tasks). Although we don't plan to run an application this time, we will create an image. Consequently, an Artifact Registry repository is needed as a place to store that image so it can be read from Cloud Build.
Preparation (Leaving it to another article)
Preparation is required to manage Google Cloud resources with Terraform. I have summarized this in another article, so please proceed until you are ready for plan.
Cloud SQL Definition
In addition to Artifact Registry, we also need Cloud SQL. Let's define the module.
terraform {
variable "target_region" {
description = "Region to deploy"
type = string
default = "us-central1"
}
# https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database
resource "google_sql_database_instance" "prisma-migrate" {
name = "prisma-migrate"
database_version = "POSTGRES_14"
region = var.target_region
deletion_protection = false # Setting to false because we want to delete it after testing
settings {
tier = "db-f1-micro"
availability_type = "REGIONAL"
disk_size = "20"
disk_type = "PD_SSD"
ip_configuration {
ipv4_enabled = "true"
}
}
}
resource "google_sql_database" "google-cloud-prisma-migrate-db" {
name = "google_cloud_prisma_migrate_db"
instance = google_sql_database_instance.prisma-migrate.name
}
# ref: https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#attributes-reference
output "prisma_migrate_connection_name" {
value = google_sql_database_instance.prisma-migrate.connection_name
}
As a side note, although the us-central1 region is quite cost-effective, it can still be expensive, so don't forget to delete it later.
Cloud Build Definition
This resource is for executing builds using cloudbuild.yml. To be precise, since the build itself uses cloudbuild.yml, the resource we create with Terraform is a Cloud Build trigger.
variable "github_owner" {}
variable "github_app_repo_name" {}
variable "region" {}
variable "gcp_project_id" {}
variable "app_name" {}
variable "cloudsql_instance_full_name" {}
resource "google_cloudbuild_trigger" "deploy-migrate" {
name = "deploy-migrate"
description = "Execute prisma deploy"
github {
owner = var.github_owner
name = var.github_app_repo_name
push {
branch = "^main$"
}
}
included_files = ["google-cloud-prisma-migrate/**"]
filename = "google-cloud-prisma-migrate/cloudbuild.yml"
substitutions = {
_CLOUDSQL_INSTANCE_FULL_NAME = var.cloudsql_instance_full_name
_ARTIFACT_REPOSITORY_IMAGE_NAME = "${var.region}-docker.pkg.dev/${var.gcp_project_id}/backend/${var.app_name}"
}
}
We are passing the Cloud SQL instance name and the image name in Artifact Registry via substitutions.
Updating main.tf
After defining the modules, call them from main.tf.
terraform {
required_version = "~> 1.0.0"
backend "gcs" {
+ # Create a dedicated state management file to avoid conflicts with other test resources
+ prefix = "tfstate/google-cloud-prisma-migrate"
}
}
## project ##
provider "google" {
project = var.gcp_project_id
region = var.primary_region
}
# Artifact Registry repository to store images
module "artifact-registry" {
source = "./modules/artifact-registry"
gcp_project_id = var.gcp_project_id
artifact_registry_location = var.primary_region
}
+# Cloud SQL
+module "cloud-sql" {
+ source = "./modules/cloud-sql"
+ target_region = var.primary_region
+}
+
+# Cloud Build to execute migrations
+module "cloud-build" {
+ source = "./modules/cloud-build"
+ gcp_project_id = var.gcp_project_id
+ region = var.primary_region
+ cloudsql_instance_full_name = module.cloud-sql.prisma_migrate_connection_name
+ app_name = "google-cloud-prisma-migrte"
+ github_owner = "cm-wada-yusuke"
+ github_app_repo_name = "gql-nest-prisma-training"
+}
There are a few missing variables such as gcp_project_id. Since these are sensitive pieces of information that depend on the user, they cannot be subject to version control. Each user should create their own terraform.tfvars.
# https://www.terraform.io/cli/config/environment-variables
primary_region = "us-central1" # This region is cheap and safe for verification purposes
gcp_project_id = "your-project-id"
The creation of Terraform files is now complete.
Mapping the GitHub Repository Before Applying
When creating Cloud Build with a GitHub trigger, you must connect the GitHub repository in advance. Connect it from the Cloud Build screen as shown below:
Applying
Once you have completed the above, you can apply the changes.
./tf.sh init # Loading the defined modules
./tf.sh apply
... (It takes quite a while for Cloud SQL deployment)
It's fine if the Artifact Registry repository, Cloud SQL, and Cloud Build trigger are created. If you are told that the Google Cloud admin API is required, enable it as instructed and run it again.
Updating cloudbuild.yml
Even now, the Cloud Build trigger can be executed, but it will fail because cloudbuild.yml is incomplete. Since the ambiguous parts have been clarified, let's complete cloudbuild.yml.
- Build the container image
- Push to Artifact Registry
- Execute the migration using the pushed image
Reflect this flow in the build file.
steps:
- id: build
name: "docker"
args:
- build
- --file=Dockerfile
- "--tag=$_ARTIFACT_REPOSITORY_IMAGE_NAME:latest"
- --cache-from=$_ARTIFACT_REPOSITORY_IMAGE_NAME:latest
- .
dir: "google-cloud-prisma-migrate"
- id: push
name: "docker"
args:
- push
- --all-tags
- $_ARTIFACT_REPOSITORY_IMAGE_NAME
dir: "google-cloud-prisma-migrate"
waitFor: ["build"]
- id: "apply-migrations"
name: "gcr.io/google-appengine/exec-wrapper"
entrypoint: "bash"
args:
- "-c"
- "/buildstep/execute.sh -i $_ARTIFACT_REPOSITORY_IMAGE_NAME:latest -e DATABASE_URL=$$PRISMA_MIGRATE_DATABASE_URL -s $_CLOUDSQL_INSTANCE_FULL_NAME -- yarn prisma migrate deploy"
secretEnv: ["PRISMA_MIGRATE_DATABASE_URL"]
dir: "google-cloud-prisma-migrate"
waitFor: ["push"]
timeout: 2000s
substitutions:
_CLOUDSQL_INSTANCE_FULL_NAME: by-terraform
_ARTIFACT_REPOSITORY_IMAGE_NAME: by-terraform
availableSecrets:
secretManager:
- versionName: projects/$PROJECT_ID/secrets/PRISMA_MIGRATE_DATABASE_URL/versions/latest
env: PRISMA_MIGRATE_DATABASE_URL
# Display information about the generated image in the build results
# https://cloud.google.com/build/docs/building/build-containers
images:
- $_ARTIFACT_REPOSITORY_IMAGE_NAME:latest
Registering DATABASE_URL in Secret Manager
Register PRISMA_MIGRATE_DATABASE_URL, which is required for migration. There are various ways to register it, but since it's sensitive information, I think it's safer to do it manually. Register it from the console.
- Name:
PRISMA_MIGRATE_DATABASE_URL - Secret Value:
postgres://postgres:<postgres user password>@localhost:5432/google_cloud_prisma_migrate_db?host=<Cloud SQL instance name>- Example:
postgres://postgres:password@localhost:5432/google_cloud_prisma_migrate_db?host=/cloudsql/sample-project:us-central1:prisma-migrate
- Example:
The DB user password can be changed from the Cloud SQL console, so set a new password and create this secret.
Running the Migration
Since we created the Cloud Build trigger to respond to pushes to the main branch, push your changes. This should trigger the Cloud Build process.
After completion, it's successful if you can see text output in the build logs indicating that the migration was executed.
Connecting Locally with Cloud SQL Proxy
Connect from your local environment to verify. Create a docker compose file.
version: "3"
# Proxy to access the DB in the Cloud SQL environment
services:
prisma-migrate-db:
image: "gcr.io/cloudsql-docker/gce-proxy:latest"
container_name: prisma-migrate-db
command:
[
"/cloud_sql_proxy",
"-instances=$CLOUDSQL_PROXY_CONNECTION_NAME=tcp:0.0.0.0:5432",
"-credential_file=/gcp-key.google-cloud-prisma-migrate.json",
]
volumes:
- type: bind # Error if file does not exist
source: "~/.config/gcloud/application_default_credentials.json"
target: "/gcp-key.google-cloud-prisma-migrate.json"
ports:
- "45432:5432" # Port changed to avoid conflict with local DB
Run direnv edit . and add CLOUDSQL_PROXY_CONNECTION_NAME. The value to set is the Cloud SQL connection name, a string like your-project-name:us-central1:prisma-migrate. Then, start it with docker compose.
docker compose -f docker-compose.sql-proxy.yml up
While this Docker container is running, you can configure your SQL client as if you were connecting to a local DB. In this case, the database URL is postgresql://localhost:45432/google_cloud_prisma_migrate_db. You need to specify the username and password from Cloud SQL.
You can see that the migration has been executed on the database in Cloud SQL, and tables and columns have been created.
Cleanup
Cloud SQL instances are quite expensive, so let's clean them up once verification is complete.
./tf.sh destroy
That's all for the process.
Conclusion
By gathering the wisdom of our predecessors, we were able to execute Prisma migrations from Cloud Build. Once set up, the subsequent flow is the same:
- Development work such as
yarn prisma migrate dev --name modifyin the local environment. - Merge or push to the main branch.
- The Cloud Build trigger runs, and
yarn prisma migrate deployis executed. - All unapplied migrations are applied to Cloud SQL.
The initial setup is a bit of work, but once it's done, it's convenient because the local DB and Cloud SQL are automatically synchronized. I hope this helps you when running migrations with Cloud Build.
Source Code
It is published on GitHub. I think it is fine, but if by any chance it contains sensitive information, please let me know privately...
Discussion