prisma migration shouldn't be in the production code ... instead it should have its own cli command

[kidusdev]

when we migrate prisma migration we migrate it like this

final supportDir = await getApplicationSupportDirectory();
  final database = join(supportDir.path, 'dev.db');

  prisma = PrismaClient(datasourceUrl: 'file:$database');

  final engine = switch (prisma.$engine) {
    LibraryEngine engine => engine,
    _ => null,
  };

// this applies the migration but it is hard coded in the application code and it will be also in the production package
// it our migration code shouldn't be in the production build right
  await engine?.applyMigrations(path: 'prisma/migrations');

of course we can create new file that runs from command line like this migration.dart

import 'dart:io';

import 'package:orm_app/prisma_client.dart';

void main(List<String> args) async {
  print("START");
  await engine?.applyMigrations(path: 'prisma/migrations');
  print("DONE");
  exit(0);
}

and run it in the cli using dart run migration.dart

but it throws this error
Error: Dart library 'dart:ui' is not available on this platform.

• it would be good if can migrate with code like this dart run orm_flutter:migrate

lets gather ideas and implement it

[medz]

@kidusdev Well, I have reason to think that you want to put your SQLite database file into assets.

But putting SQLite database file into assets will cause the data in the database to be lost after the user upgrades the App.

There is no big problem in running migration at the entry point, and it should be optimized by the developer whether to run migration. This pattern design is just a simple demonstration of how to upgrade the database without losing data after the user upgrades the App.

In addition, running migration at the entry point does not have much performance overhead, it only traverses assets and queries the records in the migration table.

Usually, you should only make a database version lock file, such as writing a lockfile in the support directory, using it to indicate whether migration needs to be run. If so, the App homepage should display the data migration medium occupancy page. When the value of the lock file record is the situation that you judge that migration is not needed, you do not need to call the applyMigrations function.

The reason why example is written in main is that it is just a simple demonstration of how to run migration. Of course, you also need to determine whether the database file exists. If it does not exist, you still need to run migration to create the database file.

[medz]

@kidusdev I have reason to believe that we should find the best migration timing plan. At least most apps I have seen will determine whether migration is needed when using local databases and then jump to the upgrade prompt page.

In addition, the engine design supports custom bundles, which means that dynamic migration plans can be implemented. For example, if there is a field error in the original migration, we cannot fix it by releasing a new version of the app, then we can use a remote http bundle to implement hot fixes for the database.

[kidusdev]

@medz, yeah you are right putting SQLite data into assets folder or running migration at the entry point might not be a good practice.

but we can make migration in both ways

• in development - we can use the cli like dart run orm_flutter:migrate and it takes the migration file from the project_root/prisma/migrations/* folder and run applyMigrations under the hood. its like a cli wrapper around applyMigrations.

• in production - we write a code to update the migrations file remotely then run applyMigrations.

• future recommendations - in my startup we are using orm and orm_flutter. when ever a new data created on the server the app downloads it then resets the database then fill it up with the new data. its over 100,000 records. we are using prisma.sales.deleteMany() to reset the database but if we add the npx prisma migrate reset to applyMigrations it would be easier.

[medz]

@kidusdev

in development - we can use the cli like dart run orm_flutter:migrate and it takes the migration file from the project_root/prisma/migrations/* folder and run applyMigrations under the hood. its like a cli wrapper around applyMigrations.

This is a good idea, just check if it is a development environment at the entry point

But there is a better solution, use prisma db push, or the migration command of Prisma CLI.

Then add the SQLite database file to the assets, but making an orm_flutter: migrate can help verify the availability of applyMigrations. I think this can create a new issue to mark it as a feature request.

when ever a new data created on the server the app downloads it then resets the database then fill it up with the new data. its over 100,000 records. we are using prisma.sales.deleteMany() to reset the database but if we add the npx prisma migrate reset to applyMigrations it would be easier.

I think in this case, it is better to pack the SQLite database file directly on the remote end and include the data you need to update. Then directly replace the local database file.

Because the purpose of prisma migrate reset is to reset the database and clear all data.

A better solution I think is to delete the database file instead of prisma migrate reset and then run migration. Because of their results.

But directly deleting the database and running migration is much more efficient than prisma migrate reset, which uses down SQL to invert the database.

[medz]

when ever a new data created on the server the app downloads it then resets the database then fill it up with the new data. its over 100,000 records. we are using prisma.sales.deleteMany() to reset the database but if we add the npx prisma migrate reset to applyMigrations it would be easier.

In addition, I think it is best practice to store data locally on demand. Pulling all the data at once seems to be a very unreasonable design.

[kidusdev]

Shorebird this repository works on codepush for flutter apps its like hot restart in production when you update your code in your developement environment and verify it, the new code reaches all your users phones with out any playstore updates. they also use this architecture you can apply the code push in cli or programatically inside your apps code. We will look forward to implement this capability for database migrations.

[medz]

@kidusdev It suddenly seems to me that what you want is a data migration solution, not a schema migration.