Prisma
In the course of using prisma
I have developed the following helper commands in package.json
:
"scripts": { "migrate": "env-cmd -f .env-cmdrc -e default,dev npx prisma migrate dev", "migrate:create-only": "env-cmd -f .env-cmdrc -e default,dev npx prisma migrate dev --create-only", "migrate:resolve": "env-cmd -f .env-cmdrc -e default,dev npx prisma migrate resolve --applied", "migrate:deploy:uat": "env-cmd -f .env-cmdrc -e default,uat npx prisma migrate deploy", "migrate:deploy:poc": "env-cmd -f .env-cmdrc -e default,poc npx prisma migrate deploy", "migrate:deploy:prod": "env-cmd -f .env-cmdrc -e default,prod npx prisma migrate deploy", "migrate-resolve:uat": "env-cmd -f .env-cmdrc -e default,uat npx prisma migrate resolve --applied", "migrate-resolve:poc": "env-cmd -f .env-cmdrc -e default,poc npx prisma migrate resolve --applied", "migrate-resolve:prod": "env-cmd -f .env-cmdrc -e default,prod npx prisma migrate resolve --applied", }
-
migrate
We only runmigration dev
in local (or dev) developement to execute table migration and obtain migration file. -
migrate-create-only
Used when auto generated sql-migration script (generated from prisma) fails to persist data.We keep updating the
schema.prisma
,yarn migrate-create-only
to obtain faulty migration script, correct it, andyarn migrate
. -
migrate:deploy:uat
Apply all migration scripts to production server. The scripts are well tested in local development. -
migrate-resolve:uat
Used when we have manaully updated the table (due to incorrect procedures).We record the changes in migration file and run
migrate-resolve:uat <migration-name>
to indicate that the changes has been applied.
SQL Migration Scripts
Rename a Table
ALTER TABLE "RoomIssue" RENAME TO "MessagesSession";
Change the Type of a Column
Simple Case
ALTER TABLE "Message" ALTER COLUMN "createdAt" SET DATA TYPE DOUBLE PRECISION;
Type-Casting, Conditional (via Regex) Casting, with Error Handling
For example, our createdAt
is a string recording the unix timstamp started from 1970 in ms
. We:
- cast non-empty
"1234"
string to1234
. - cast empty string
""
to0
, hereCAST("" as numeric)
will makePGSQL
panic.
ALTER TABLE "Session" ADD COLUMN "temp_column" DECIMAL(14, 0); UPDATE "Session" SET "temp_column" = CASE WHEN "createdAt" ~ '^[0-9]+$' THEN CAST("createdAt" AS numeric) ELSE 0 END; ALTER TABLE "Session" DROP COLUMN "createdAt"; ALTER TABLE "Session" RENAME COLUMN "temp_column" TO "createdAt";
COALESCE, the PostgreSQL version of IFNULL
Examples:
SELECT COALESCE(1, 2);
resolves to 1
, and
SELECT COALESCE (NULL, 2 , 1);
resolved to 2
.