kysely
kysely copied to clipboard
kysely-org
Migrations can be modified without notice
Description:
I noticed migrator stores only the migration name into db. Therefore if there's any changes in migrations, you wouldn't notice it in environments where you have ran them already.
Expected behaviour:
Migrator throws an error if the current migration contents do not match already ran migrations.
Possible solution:
This could be potentially solved by adding a checksum of the migration to the migration table and verifying it while running migrations.
I have used Liquibase in the past, but now in a project I am migrating away from it to Kysely. While I understand the benefits of integrity checks, especially at scale, I am against them or, at least, in favor of having the option to ignore or reset the checks. I have the following reasons:
- The integrity checks ensure only integrity between migration scripts and the metadata of scripts that have already run. If someone modifies data or the structure of data directly, you are still out of luck.
- I assume that past scripts are modified intentionally. For example, if database vendors introduce breaking changes and migrations stop working on newer database versions in integration tests. In my current case, I want to switch to Kysely migrations by running an empty migration and then adding the entire structure from a mysqldump to this migration. Such changes can/should/must be verified in a code review anyway.
- During development, I often modify the database structure manually (especially indexes) and then adjust the migration file accordingly. If the migration has already run, which is almost always the case, then I can't down-up to get a consistent state because the checks say
no. - The checks introduce additional complexity to the code of Kysely.
Therefore, please do not take my reaction to the feature request personally, but as a vote.
I completely agree with @Bessonov! We could add the check behind an option (off by default) but that would be tricky since we'd have to migrate the migration tables. I don't think there's a way to do that in a way that'd work on all possible dialects (internal and 3rd party). We'd have to add that to the dialects, making them more complex.
I'm curious what the use case would even be for this? Kysely's value to me as a programmer is to help me avoid unexpected things, but when I change my migration that is absolutely an expected change
IMO these are the most important reasons for having integrity checks on your database migrations.
- Ensuring consistency
Checksums ensure that the migration scripts remain unchanged from development to deployment. Any modifications to the scripts after they have been executed in a given environment will be detected, ensuring that all environments (development, testing, staging, production) remain consistent. I.e. Helps in preventing schema drift if scripts are altered or not applied uniformly.
- Audit trails
Having a reliable checksum system helps maintain an audit trail of all changes applied to the database. This can be crucial for compliance and auditing purposes, as it provides a verifiable history of migrations.
- Collaboration
In a team environment, multiple developers might work on the database schema simultaneously. Checksums help ensure that everyone is working with the same version of the migration scripts, reducing conflicts and integration issues.
- Reliable rollbacks
This is not so important for me personally as I usually prefer forward-only migration model. However, if a migration fails or causes issues, checksums can help ensure that rollbacks are executed correctly by verifying that the scripts being rolled back are the same as the ones initially run.
To summarize, I think it's all about maintaining high standards of database integrity, ensuring reliable deployments and minimizing risks associated with schema changes.
I strongly agree with @niklas-e, I'm using liquibase for a quite big project and sometimes the integrity checks on the migrations saved my day. If you introduce utilities like changelog sync you can solve the issues mentioned by @Bessonov.
@niklas-e In fact, none of the mentioned issues are resolved by checking the execution integrity. It doesn't prevent any schema drifts. Go into your database, drop a random column, and it wouldn't be detected by the "integrity checks" done by Liquibase. The only thing it can detect is if the migration files are different from the past migrations.
If you want an integrity check, then the tool must check the migration files against the actual database schema (again, not just the checksums table!). I see that Liquibase Pro could be a solution. However, this is an entirely different league and not what you are asking for in this issue.
If you need auditing, then, by definition, a client-side database migration tool isn't the right tool for that. Either it must be an inherent property of the database, or you must ensure that it can't be faked by developers and use, for example, Audit Triggers with appropriate permissions. Of course, there are possible workarounds. However, you can also apply workarounds to ensure that old files are unmodified, and you don't even need Kysely for that. See below.
I strongly agree with @niklas-e. I'm using Liquibase for a quite big project and sometimes the integrity checks on the migrations saved my day. If you introduce utilities like changelog sync, you can solve the issues mentioned by @Bessonov.
@GioPat No, they don't. Again, until you compare the actual schema with the migration files, your gain is nearly nonexistent. Not tested, but probably, I think the following should do the same for you as checksum checks:
if [ $(git diff main..HEAD --name-only --diff-filter=MD | grep '.*\.sql$' | wc -l) -gt 0 ]; then
echo Integrity check failed
exit 1
fi
Adjust it to your needs. Thanks me later :smile:
You are right, they are not a standalone solution which solves everything, and I didn't claim them to be such. However I still think they are very useful.
You are implying database schema can be changed by other means than migration files. That is not the case in many environments.
Hey 👋
The migrator we offer is a reference design. For most people it is more than enough.
Keep in mind, Kysely is not a framework. You can copy the migrator to your project and adjust it to meet your requirements, or patch it (pnpm's patch command or the patch-package library).
kysely-ctl allows passing a custom migrator too.
I have been using kysely for years and now for new project have decided to try Kysely migrations. So I am here with the same issue. Early I have just used flyway but I like idea to have migrator as part of app.
Honesty I don't understand people who use migrations without checksums. In real development for even relatively small project you are asking for very seriously troubles.
I use vide-node for development and vite as bundler for node.js application. Its work fine but I have figured out it's just impossible to implement mature migration system as part of kysely for this case. I believe it should be implemented as vite plugin and so the issue can't be resolved within kysely project.
Honesty I don't understand people who use migrations without checksums. In real development for even relatively small project you are asking for very seriously troubles.
Because everyone on the team knows not to modify migrations and if they do they have a very very very good reason.
Because everyone on the team knows not to modify migrations
It's not funny. Maybe you are lucky man with very good team. But even in such case...
When I am working on new version than I repeat the same loop: edit new migration, edit code, edit tests and run tests against the test DB. It's very easy to forget that I have changed current migration and haven't change the test db. Without checksums it can be hard to figure out what is wrong with my code. It's annoying.
I consider Flyway as almost perfect solution in the field. But by reasons of deployment I don't want to use it in current project. It's strange for me that I can't find good solution in JS-world. So I will probably do it by myself. It's not big deal.
In my opinion Kyseky is a ideal sql-builder but chunk about migrations is not. It's a pity. For sake of good design I believe migrations should be removed from Kysely in a separate project. It's will open up opportunities to have several different implementations with different ways of thought.
When I am working on new version than I repeat the same loop: edit new migration, edit code, edit tests and run tests against the test DB. It's very easy to forget that I have changed current migration and haven't change the test db.
This is an issue to prevent migrations from being modified without notice. If you change the migration, you should know that you changed the migration without the computer telling you. I don't get how this would help the desync you describe.
In my opinion Kyseky is a ideal sql-builder but chunk about migrations is not. It's a pity. For sake of good design I believe migrations should be removed from Kysely in a separate project.
Migrations aren't a part of the kysely project. Kysely offers a DSL for executing SQL queries freuquently associated with migrations and also a Migrator helper, however the actual migration script you are imagining is only an example script, not a part of the kysely codebase. You are free to modify the example script as you see fit.