Alternative migration client for prisma ORM
npm install prisma-rays
prisma rays is a schema migration management tool built for prisma ORM.
It is meant to be used as a drop in replacement to the builtin prisma migrate cli.
Providing many management improvements and a more intuitive api.
- Documentation
- Why to use Prisma Rays
- Getting started
- Prisma rays workflow
- Configuration
- Usage
- How it works
- migration.js
- Prisma Rays vs Prisma Migrate
- Known limits and missing features
- Going back to prisma migrate
- Troubleshooting
- Upgrade from 1.x
Fair question, the wonderful devs on prisma migrate have made a great job on the builtin migration tool.
In fact, Prisma Rays uses prisma migrate under the hood to generate its own migrations, so you don't have to worry about differences between schema parsers.
However, prisma migrate is littered with all kind of counterintuitive behaviours and lack support for some flows which can be useful.
See a feel list of differences between the two in the Prisma Rays vs Prisma Migrate section
Prisma Rays is heavily inspired by the UX given by the Django framework builtin migration tool.
#### prerequisites
- prisma cli installed on your project
- prisma client installed on your project
- existing postgres/mysql/sqlite database (other relational databases might also be supported but were not tested against at the moment)
- prisma.schema file with database connection url provided from .env file
- if using the auto-generated shadow database, your user credentials to the database should have the appropriate permissions for shadow database creation
#### Installation
1. Install package npm i prisma-rays
You may also install as global package instead of using npx
2. in your project's root directory run npx rays init
3. Open the generated raysconfig.js file and update it according to your project's setup (see Configuration section for details).
if your project's database is brand new (i.e has no tables), make sure your prisma schema contain at least one model and run npx rays push
If your project does not have existing migrations created from prisma migrate you can opt in to prisma rays
by running npx rays prepare. Otherwise, see Adding to existing projects
#### Adding to existing projects
1. make sure your database is currently at the starting state that fits your project.
2. remove all folders in your migrations directory, only keep the migration_lock.toml file.
3. run npx rays prepare
With prisma rays your typical workflow will look like this:
1. Modify your prisma schema file
2. generate migrations based on changes using makemigrations command
3. repeat steps 1 + 2 until you're ready to apply them.
4. When you wish to apply the generated migrations run the migrate command
5. push migration files to version control
In production, your workflow should typically be to simply apply your migrations after you've pulled the changes from version control.
Prisma Rays has a single configuration file raysconfig.js
#### Configuration options
Option | Values | description
--- | --- | ---
migrationsDir | string | A path to your prisma migrations directory
schemaPath | string | A path to your prisma schema file
databaseUrl | string | A connection url to your database (this is the same as value set in your .env file)
shadowDatabaseName | string / null | The name of your shadow database if you wish to use predefined one instead of auto-create on each make-migration process. Must be accessible using the same credentials and schema as your database
verboseLogging | boolean | Whether to enable verbose logging by default (instead of requiring --log flag)
#### Basic configuration
For most setups you only need to set your migrationsDir and schemaPath and databaseUrl.
#### Configuring to work with cloud hosted / fixed shadow database
Prisma Rays (and the underlying Prisma Migrate) uses shadow database to generate migration files based on schema changes without affecting your database.
Using Prisma Rays require 2 separate shadow databases - one for prisma rays and another for prisma migrate.
With the basic configuration those databases are automatically created and dropped when creating migrations.
There are however, cases where you might what to override this behaviour and specify your own shadow databases:
- You don't have the appropriate permissions to create and drop databases.
- Your database is hosted on a cloud service (which does not normally support creating and dropping database instances)
- You use Prisma Rays migration generating in your CI system (for example Prisma Rays tests run on CI)
It's important to note that shadow databases only play a role when creating migrations (as part of prepare or makemigration).
if you only need apply/revert migrations you do not need this special setup.
When overriding the the shadow database behavior, instead of creating and dropping the shadow database, both Prisma Rays and Prisma Migrate
simply drop all the tables in them and reuse them.
Configuration
1. In your raysconfig.js file update the shadowDatabaseName property to match the name of your shadow database to be used by prisma rays.
This database must be accessible using the same credentials as your database. For example:
``
databaseUrl='postgresql://user:password@dbhost:5432/mydb?schema=public'
shadowDatabaseName='mydb_rays_shadow'
Shadow database url will be: postgresql://user:password@dbhost:5432/mydb_rays_shadow?schema=public
2. configure shadow database for Prisma Migrate by setting shadowDatabaseUrl in your schema. read more on prisma migrate docs
`
datasource db {
provider = "postgresql"
url = "postgresql://user:password@dbhost:5432/mydb?schema=public"
shadowDatabaseUrl = "postgresql://user:password@dbhost:5432/mydb_prisma_shadow?schema=public"
}
This database must be different from shadow database set for prisma rays
Optional global cli options with any command:
Option | Values | description
--- | --- | ---
log | None | Run command with verbose logging.
conf | File path | path to your raysconfig file.
help | None | prints the help chapter on the specific command.
#### init
npx rays init
Setup prisma rays for your project, creating an initial raysconfig.js file
init is only ever required once in the entire lifespan of a project
#### prepare
npx rays prepare
Options:
Option | Values | Required | description
--- | --- | --- | ---
y | None | No | approve database reset
Initialize the migration system against the current existing database.
Using this function require to clear the database during the process.
This function works by looking at the current database and update the prisma schema accordingly.
if you have an existing prisma schema you wish to end up with run npx prisma db push before running this command
Prepare is only ever required once in the entire lifespan of a project
example usage:
npx rays prepare --y
#### makemigration
npx rays makemigration --name
Options:
Option | Values | Required | description
--- | --- | --- | ---
name | String | yes | suffix to give to the created migration.
blank | None | no | allow the creation of a blank migration if no changes detected in the schema.
autoresolve | None | no | Auto confirm migration generation warnings.
Create a migration based on your recent schema changes without applying it to your database.
You can use this function at any time as you like and any database state.
This function works by:
1. creating a shadow database and applying all the available migrations to it.
2. compare the shadow database schema against your current prisma schema and generate the necessary migration
3. create the revert migration and create a migration.js file with both up and down migrations
4. drop the shadow database
example usage:
create a migration suffixed by myFirstMigration:
npx rays makemigration --name myFirstMigration
create a migration or blank if no changes, suffixed by myFirstMigration:
npx rays makemigration --name myFirstMigration --blank
#### migrate
npx rays migrate
Options:
Option | Values | Required | description
--- | --- | --- | ---
name | String | no | Target migration to reach (if not given all up migrations are applied).
fake | None | no | Change the migration state without applying the schema changes to the database.
Apply migrations to your database. If migration name option is given, the database will be migrated to this migration
regardless of the direction (i.e up/down) it's found at. Otherwise the topmost migration is used as end target.
You may use the fake migration option to only mark the migration as applied/reverted without actually effecting the database structure.
This is useful for solving sync issues or error recovery.
Each migration step is being wrapped in transaction which is either committed or rolled back when the migration step is done.
This function works by:
1. finding the migration end target (uses the last migration if non given)
2. get applied migrations list from database (piggybacking on prisma migrate's migration listing table)migration.js
3. determine required migration direction and steps
4. if fake migration option not given - load each migration step script and run the up/down functions, wrapped in transaction sql syntax.prisma migrate
5. update each migration step in 's migration listing table (either insert or remove from it)
6. replaces your prisma schema file with schema file associated with the last successful migration step.
example usage:
apply all migrations:
npx rays migrate
mark all un-applied migrations as applied without running them:
npx rays migrate --fake
apply migrations up/down to myFirstMigration_20211109182020:
npx rays migrate --name myFirstMigration_20211109182020
mark un-applied migrations up/down to myFirstMigration_20211109182020 as applied/reverted without running them:
npx rays migrate --name myFirstMigration_20211109182020 --fake
#### push
npx rays push
Options:
Option | Values | Required | description
--- | --- | --- | ---
y | None | No | approve database reset
Reset your database to the current state of your schema, this mechanism does not use migrations api and instead
rebuild the database based on the schema.
This command usually required for new projects which never applied any schema to it
example usage:
npx rays push --y
#### status
npx rays status
log the migration and schema status against the database structure
Prisma rays does not re-invent the wheel, it uses the same functionality as prisma migrate does but wrap the experience
in a tighter package.
It uses two dedicated / auto-generated shadow databases to achieve the desired outcome (will be referred to as rays_shadow and prisma_shadow).
When creating migrations (either as part of makemigrations or prepare), prisma rays configure prisma migrate to treat the rays_shadow db as the
working database instead of your real working database, this allows prisma rays to avoid making changes / resetting your working database.
When applying/reverting migrations, prisma rays uses different engines (pg, mysql2, mssql, sqlite3) depending on your database provider
to execute migration queries and then mark the migration as applied/reverted using prisma migrate.
Prisma Rays work with javascript files to manage migrations. Each migration file (a.k.a step)
exports an array of operation tuples:
Each tuple contain two functions:
- up (0) - run during forward migration
- down (1) - run during backward migration
you can add additional operation tuples to perform different actions over your database with one exception:
- do not change the database structure or modify the generated sql calls in the migration script.
why ? because those bits of code must be aligned with the generated migration.sql which prisma migrate depends on.
You can of course step in between operations to perform your own logic such as changing the data of your models and so on.
For data related operations. your up and down operations receive a client api object which can be used to interact with the
database during the migration process.
Because of this, migration.js files doesn't even need to run any structure changes sql at all. you can use a blank migration
to apply database wide data manipulation.
in addition to the migration script, prisma rays also create a copy of each migration step schema, so it can be reverted to at any time.
here are some examples of typical migration scripts (written as helper functions for readability :
`javascript`
module.exports = [
[createUsersTable, dropUsersTable],
[createPostsTable, dropPostsTable],
[createConstraint, dropConstraint],
]
`javascript`
module.exports = [
[setUserNameDefaultValue, replaceDefaultUserNameValueWithNull],
[makeUserNameNonNull, makeUserNameNullable],
]
#### Client API
The migration functions are given a client object which is connected to the database within the migration transaction.
The client api is as follows:
`typescript`
interface IDatabaseClientApi {
query: (query: string, params?: any[]) => Promise
execute: (query: string, params?: any[]) => Promise
}
use query for command to SELECT data from your database, the result is an array of objects matching your query (i.e rows)
use execute for commands that you do not expect to get result back for such as INSERT and UPDATE
both functions accept a query string and an optional array of arguments to be safely escaped into the resulting query.
example usage
query data with hard coded values (not recommended):
const rows = await query('SELECT * FROM users WHERE firstname = "John" AND lastname = "Doe"')
query data with parameters - use the :? markup as a parameter insertion point (useful when your parameters derive from unknown source such as user generated content in order to avoid SQL injection):
const rows = await query('SELECT * FROM users WHERE firstname = :? AND lastname = :?', ['John', 'Doe'])
WARNING: Do not build query string in runtime yourself based on uncontrolled data source (such as user provided data), doing so will expose your database do SQL injection
and potential catastrophe
for example DO NOT DO THIS:
`
const fname = ...
const lname = ...
const rows = await query('SELECT * FROM users WHERE firstname = "' + fname + '" AND lastname = " + lname + "')
#### Creating migrations
In prisma migrate, attempting to create multiple migrations without applying any of them is not supported. if you attempt
to create another migration while you have an un-applied migration pending it will by applied first.
In prisma rays, creating migrations is completely separated from applying the migration, so you can create as many of those as you
want.
#### Migration format
prisma migrate only supports an SQL file as your migration. This might impose some limits on what you can do with migrations.
prisma rays on the other hand uses a plain js file as your migration, so you can use it to perform complex data manipulations
and easily resolve data related issues during schema changes.
For example assume you're adding a new non-null column to an existing table and need to provide a default value,
with prisma rays you can just populate a value in your migration file before the sql which create the non-null constraint.prisma migrate overcome this issue (in development) by offering you to reset the database
Because prisma rays uses prisma migrate under the hood, you will still see migration.sql file created. This file is only kept to support prisma migrate usage by prisma rays but its not being used by it directly.
#### Revert migration
prisma migrate does not support reverting applied migrations.
prisma rays support reverting applied migrations at any depth since it keeps a copy of the prisma schema every time it creates
a migration.
#### Applying migrations
prisma migrate is applying all migrations, without using transactions and only in one direction (e.g up), if your migration fail halfway the database is left in undetermined state until manually fixed
prisma rays can apply as many migration steps as you wish in both directions to bring your database to the desired state. Each migration step is being run inside a transaction and is being rolled back on errors
#### So many logs
Currently, even with verbose logging option turned off the you will still see every one of prisma migrate console logsprisma rays
when running commands. Annoying, I know.
#### Databases support
At the moment prisma rays only supports postgresql & mysql, (2 out of 3 relational databases prisma migration supports).
This is due to some raw db queries used internally to perform the different functions.
If you're interested in helping with this issue feel free to submit a pull request, adding your engine file
If you're unhappy with Prisma Rays or simply want to go back to the built in prisma migrate tool its easy to do so.
1. run npx rays migrate to bring your database to the latest version
2. In the migrations folder, for each migration directory delete all the files except for the migration.sql
3. if installed locally, uninstall prisma rays with npm uninstall prisma-rays
#### Error: P4001 The introspected database was empty while running rays prepare
your database is empty so it cannot be used to generate the schema.
Update your prisma schema to an initial point you want to support and run npx rays push.
Then run npx rays prepare again
#### Warning: Migration operations for up and down have different amount of operations
This warning comes up after prisma rays break down the sql generated from prisma migrate into separate sql statements &
the amount of statements in the up migration is different from the down migration. This means that the generated
migration script will have miss-aligned tuple commands in it which makes the migration non-reversible until manually fixed.
This is more common with sqlite database where some table operations require dropping the whole table and recreate it in multiple statements
rather than apply a single alter table statement
for example consider the following change:
up migration - just add a column with default value
``sql`
ALTER TABLE "User" ADD COLUMN "lastname" TEXT DEFAULT 'Doe';
down migration - in sql this change will require recreating the table with the additional column, populate it and replace the original table with the newly created one:
`sql`
CREATE TABLE "new_Users" ...;
INSERT INTO "new_Users" ...;
DROP TABLE "Users";
ALTER TABLE "new_Users" RENAME TO "Users";
as a result of the difference in operation count prisma rays will generate this migration script
`javascript`
module.exports = [
[addColumnLastName, createTableNewUsers],
[noop, insertIntoNewUsers],
[noop, dropTableUsers],
[noop, renameNewUsersTableToUsers],
]
As you can see those script operations are not aligned - the down operation is not the reverse of the up operation in the tuple.
This is where manual fixing is required to allow this operation to be reversible. In this case its enough to convert the multistep process into a single step process like so:
`javascript`
module.exports = [
[
addColumnLastName,
async(...args) => {
await createTableNewUsers(...args)
await insertIntoNewUsers(...args)
await dropTableUsers(...args)
await renameNewUsersTableToUsers(...args)
}
],
]
This kind of fix is called grouping and what makemigration does when given the --autoresolve flag (or via runtime prompt). Depending on your use case this sort of fix might not be enough/appropriate.
You should always review created migration script when this sort of warning is showing to ensure reversibility of your migrations
If you wish to keep your migrations from v1 of prisma rays you can manually convert them to v2 format by following this process:
Make your module.exports be an array, each item in the array should be an array with two items in it.
take each execute call from your up script, wrap it in a function with similar signature as the up function and put it as the first item
of the inner array in the module exports (create as many arrays as you need to fit all your operations)
take each execute call from your down script, wrap it in a function with similar signature as the down function and put it as the second item
of the inner array in the module exports - fit the down execute function to the down function so the two items are forward and reverse operations
on the same database entity.
It might be easier to just show a comparison of the two formats to understand the difference:
v1.x format
`javascript+A
const up = async ({ client }) => {
await client.execute()+B
await client.execute()
}
const down = async ({ client }) => {
await client.execute(-B)-A
await client.execute()
}
module.exports = { up, down }
`
v2.x format
`javascript+A
module.exports = [
[
// up and down changes to A
async ({ client }) => { await client.execute() },-A
async ({ client }) => { await client.execute() }+B
],
[
// up and down changes to B
async ({ client }) => { await client.execute() },-B
async ({ client }) => { await client.execute() }``
]
]