Powerful database migrations focused on reliability
Home Page: https://nextras.org/migrations
License: Other
For more information read documentation.
Supported databases:
Supported DBALs:
./tests/*.ini files
cp tests/php.docker.ini tests/php.ini
cp tests/drivers.docker.ini tests/drivers.inidocker-compose up --detachtests/run-in-docker.sh php81 tests/run-integration.shBased on Clevis\Migration by Petr Procházka and further improved.
New BSD License. See full license.
Even though migrations should be immutable, in real life use there are often good reasons to change migrations.
There are many ways of solving this now:
The last option is actually pretty viable, but requires all users to implement it themselves (and nobody bothers, ending up fixing their databases manually).
Proposed feature: magic migration group patches that would contain file names in format oldHash-newHash.extension. Whenever OrderResolver finds hash collision, it would check if patch migration is defined. If so, it would prepend it to the migration queue and after executing it, it would automatically update migrations row with the newHash.
Those migrations would only be executed if both oldHash and newHash matched.
Different implementation could be executing a patches group (or any other name for that matter) prior to running "main" migrations. This is currently not possible due to the Existing migrations depend on unknown group foo exception.
and create new migrations ie. in year subfolder.
There is a 64 character limit of lock name in MySQL 5.7.5+. (See https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock)
If your database name is longer than 44 characters (lock name in nextras/migrations consists of "Nextras.Migrations-" and database name, which is 20 + up to 44 characters), command migrations:continue will fail because of incorrect lock name.
Problematic code: https://github.com/nextras/migrations/blob/master/src/Drivers/MySqlDriver.php#L69
Such command should show:
Hi, thank you for this great tool.
I started using this in Phalcon framework. I would like to share my integration with community (I will prepare PR).
Integration adds new task (extends Phalcon\Cli\Console - https://docs.phalconphp.com/en/3.4/application-cli) and usage will be quite similar to Symfony Console commands.
However I need to split classes in src/exceptions.php into separated files to comply with PSR-4 (when using Phalcon autoloader instead of composer's one - https://docs.phalconphp.com/en/3.4/loader).
Nette\DeprecatedException: Service migrations.structureDiffGenerator: Nette\DI\Definitions\ServiceDefinition::setDynamic() is deprecated, use $builder->addImportedDefinition(...) in /srv/tetraedr/vendor/nette/di/src/DI/Definitions/ServiceDefinition.php:158
#0 /srv/tetraedr/vendor/nextras/migrations/src/Bridges/NetteDI/MigrationsExtension.php(86): Nette\DI\Definitions\ServiceDefinition->setDynamic()
Hey,
I think i found a bug. In new project (Nette sandbox) I installed nextras/migrations, add configuration in config.neon and migrations folder. If i want execute migrations I get a error.
Error:
Uninitialized string offset: 0
Stack trace:
https://www.dropbox.com/s/pxv5moo3ud14pqh/exception--2019-06-22--22-53--1a67a46997.html?dl=0
Version: 3.1
Problem with regex probably ?
Thanks
$controller = new ConsoleController(
new MySqlDriver(
new NetteAdapter(
new Nette\Database\Connection(...)
)
)
);
$controller->addGroup(...)
$controller->addExtension(...)
$controller->run();Nextras Migrations
RESET
1 migration needs to be executed.
Nette\MemberAccessException: Call to undefined method Nette\Database\Drivers\MySqlDriver::formatBool(). in /app/vendor/nette/utils/src/Utils/ObjectHelpers.php:65
Stack trace:
#0 /app/vendor/nette/utils/src/Utils/SmartObject.php(42): Nette\Utils\ObjectHelpers::strictCall('Nette\\Database\\...', 'formatBool')
#1 /app/vendor/nextras/migrations/src/Bridges/NetteDatabase/NetteAdapter.php(59): Nette\Database\Drivers\MySqlDriver->__call('formatBool', Array)
#2 /app/vendor/nextras/migrations/src/Drivers/MySqlDriver.php(108): Nextras\Migrations\Bridges\NetteDatabase\NetteAdapter->escapeBool(false)
#3 /app/vendor/nextras/migrations/src/Engine/Runner.php(171): Nextras\Migrations\Drivers\MySqlDriver->insertMigration(Object(Nextras\Migrations\Entities\Migration))
#4 /app/vendor/nextras/migrations/src/Engine/Runner.php(127): Nextras\Migrations\Engine\Runner->execute(Object(Nextras\Migrations\Entities\File))
#5 /app/vendor/nextras/migrations/src/Controllers/ConsoleController.php(23): Nextras\Migrations\Engine\Runner->run('reset')
#6 /app/bin/db-migrate(13): Nextras\Migrations\Controllers\ConsoleController->run()
...
https://github.com/nette/database/releases/tag/v3.0.0
removed unused ISupplementalDriver::formatBool()
we are printing number of queries, but much more interesting and usable entry is the elapsed time.
Currently, if database does not have migrations table and you run migrations:continue, it creates migrations table and runs all your migrations. if your initial migrations isn't created correctly and you are in the middle of project, you can lost your data.
example of initial migrations (which may be an default output of some tools, i.e. Navicat):
DROP TABLE IF EXISTS `acl_acl`;
CREATE TABLE `acl_acl` (
...
)Why: To protect production environment from destructive behaviors
Solutions:
BC break but safer
config example:
enableResetCommand: true # default: false
Not BC break but less safer
config example:
disableResetCommand: true # default: false
This is caused by MySQLDump class from https://github.com/dg/MySQL-dump
(Signaly.cz are affected by this bug, local development loose invalidateAuthorizationCache functions)
I am getting this error on CI, because basic-data directory does not exist. I am only using structures.
Directory "/home/ubuntu/wallet/tests/../app/../migrations/basic-data" does not exist.
Could you please ignore directories which do not exist?
Customizing commands is quite involved now: https://gist.github.com/Mikulas/213604faa0b55e46588a
Multiple properties should be protected instead of private, such as BaseCommand:$dir and $driver. Container extension could have a configuration option for custom command classes, for example in dictionary format command-name: ClassName which would also allow defining new commands.
Hi,
Branch/commit: master - 5970edad7945214a3eb5e22f74caa1cd35303b4d.
I registered extension twice in my config.neon.
Each instance for separated database, with separated migrations dir.
And it was working with some 3.0@dev version.
Now I try to update to 3.1@dev version.
But migrations (dirs / Nextras\Migrations\Entities\Group) from each separated extension are mixed together. In each configuration (generated by extension) are present all groups.
Reason is probably in MigrationsExtension::createConfigurationDefinition.
It creates Nextras\Migrations\Configurations\Configuration with autowired constructor paramaterGroup[] $groups.
The solution would be to provide constructor parameters of Configuration directly without autoloading.
Version v3.1.0-rc2 is working.
Migrations of procedures/functions are not done as delta migrations, but as a drop & create (replace) statements. For this reason, it's really hard to track the changes - since there are many "copies" of the function and git diff doesn't visualize the different between these coppies.
I propose introducing support for a file, which may be modified and would be deployed (executed) if the hash changes. The usage of this behaviour would have only one restriction - the procedure/function cannot be used in migration. (In such case, the migration would have to "copy" the "old" function to custom temporary function.)
Initial state:
class Foo
{
/**
* @ORM\Column(type="text")
*/
private $content;
}php migration (2016-01-01.php)
assert($entityManager instanceof Doctrine\ORM\EntityManager);
foreach ($entityManager->getRepository(Foo::class)->findAll() as $foo) {
// ... queries db
}If we ever change the original model, for example by adding additional property
class Foo
{
/**
* @ORM\Column(type="text")
*/
private $content;
+ /**
+ * @ORM\Column(type="datetime_immutable")
+ */
+ private $createdAt;
}we will break the php migration, because Doctrine will query the database as
SELECT t0.content AS content, t0.created_at AS created_at -- ...
but the newly added column was not yet added to database when the php migration executes.
In other words, php migrations use latest meta data with Doctrine, because the model itself is not versioned (or at least the versions are not used for migration purposes).
I don't have a solution to propose, other than not using default Doctrine queries in php migrations. Even writing custom DQL/SQL queries cannot handle renamed columns etc.
The current implementation of DibiAdapter supports Dibi 2 and 3. However, to support the 2.x branch, it relies on non-namespaced Dibi classes, Dibi\Driver::escape() method and possibly other things that have been deprecated in 3.0 and will be removed in 4.0 which seems to have entered beta stage recently.
Isn't it time to drop Dibi 2.x support? What do you think?
I've found that travis fails on PHP 5.4/5.5.
According to travis blog, new default distribution (Ubuntu Xenial 16.04) does not support these old PHP versions.
It can be fixed by adding dist: trusty to travis.yml.
Question: should I prepare PR or are you planning to drop support for old PHP versions?
Currently it prints e.g. „Unknown type 'foo' given, expected on of 's', 'b' or 'd'.“ which no one can possibly understand.
src/Bridges/SymfonyConsole/CreateCommand.php:115
Question to any users of this library - how do you use it if you create branch per feature/bug?
My problematic scenario:
2016-05-02-migration.sql.2016-05-04-migration.sql.How do you solve this issue? From my point of view it might be good thing to be able to switch off sorting of migrations, but have ability to bind few together (declare dependency on others).
I have problem that our existing database tables have default value '0000-00-00 00:00:00' for datetime. Problem is that when altering table (for example adding new column 'test01' with type int) via migration script I get following error:
Invalid default value for 'field which is using datetime'
Is there an option to ignore 'default values' for datetime ?
According to mysql docu, it should be possible to use sql_mode 'ALLOW_INVALID_DATES'
It seems to be problem related to MysqliDriver. I am using Nextras\Dbal\Connection
I can execute sql script via adminer without any problem, bot not through migration script
This will ensure that merging branches will results in Git conflict when in both branches new migrations is added.
Partially related to discussion in #29
I have an idea about whitelisted database which will prevent of calling migration:reset command. Just for security reasons. What do you think?
try {
// mehotd run
} catch (Exception $e) {
}may be \Exception or use Exception and fix it in others method
Without installed Symfony\Console it crash on Bridges\SymfonyConsole\BaseCommand.php:22
Fatal Error Class 'Symfony\Component\Console\Command\Command' not found
Have somebody any idea why postgre prefixes string on Php 5.4 with e? e"string" (I am not sure if it's the letter e, but something like this).
Hi, is it possible execute SQL migration with variable?
For example:
SELECT @adminRoleId := id
FROM `user_role`
WHERE `name` = 'administrator';
INSERT INTO `user_role` (`parent_id`, `name`)
VALUES (@adminRoleId, 'faq');It will fail, because N/Migrations exec single queries from file.
Is there some workaround or solution?
I see things are happening now and then here, but no new version for a very long time. Any plan on releasing new version soon?
Hi,
I get this exception
it's solved if I add "symfony/console": "^3.2" manually to my composer, but IMHO this should NOT happen, and fix shouldn't be just installing console, but being able to work without it.
Any solutions?
The migrations:create command would instead of empty file create file with first line containing reference to parent migration, e.g. sth like
# parent = structures/2015-08-28-142150-init.sql/786340a54b68591563e983f60f82feccThis will allow us to detect when programmer forgot to „rebase migrations“. This would also probably required some tool to help with the rebasing etc.
Hi
When I try to run migrations with structure described down, I'm getting error because it's calling migrations in wrong order:
structures/2015-04-24-095312-init.sql
dummy-data/2015-07-14-153742-oauth2.sql
-error-
structures/2015-07-14-153742-oauth2.sql
Quickfix is to change dummy-data/2015-07-14-153742-oauth2.sql to time which is 1 second after file in structures, but this shows there is some other problem with ordering of migrations.
migrations
├── basic-data
├── dummy-data
│ ├── 2015-07-14-153742-oauth2.sql
├── structures
│ ├── 2015-04-24-095312-init.sql
│ └── 2015-07-14-153742-oauth2.sql
Implemented as session parameters
https://www.postgresql.org/docs/9.6/static/sql-set.html
https://dev.mysql.com/doc/refman/5.7/en/set-statement.html
We should avoid using constructor injection in Symfony command, because the command are not lazy loaded (Symfony Console design problem). Currently migrations + with all their dependencies need to be loaded every time any other unrelated command is executed.
Note that the solution should be ideally both Nette & Symfony compatible.
Currently you need to write something like
nextras.migrations:
dir: %appDir%/../migrations
services:
migrationsDbal: Nextras\Migrations\Bridges\NextrasDbal\NextrasAdapter
migrationsDriver: Nextras\Migrations\Drivers\PgSqlDriver(..., ..., my_schema)
The following unfortunately does not currently work
nextras.migrations:
dir: %appDir%/../migrations
driver: Nextras\Migrations\Drivers\PgSqlDriver(..., ..., my_schema)
dbal: nextras
If I run "Init SQL => Run structures + basic-data + dummy-data" it show me this (print sql for migrations) and db is empty:
If I run "Reset All tables, views and data will be destroyed! => Run structures + basic-data + dummy-data" it show me this and everythig is in db:
Version 3.0
Nette 2.3
Windows
Mysql
It must be safe and efficient to execute migrations in parallel (e.g. in tests). Snapshots should be OK since fb66f48 but the database-level locks do not work reliably. Probably because we drop the database =)
See Clevis/Migration@3883eff#diff-f081f57898133f3ea52ecbdad77a5660R63
zdravim, delam neco spatne nebo se opravdu pri prechodu v nextras/migrations z verze 2.3.1 na ^3.0 menil vypocet checksumu souboru? po staru koukam:
$file->checksum = md5_file($group->directory . '/' . $file->name);
po novu:md5(str_replace(["\r\n", "\r"], "\n", $content));takze par hashu mi tim padem nesedi...resp. par sedi, zbytek je v hajzlu 😟 budu muset ten str_replace v src vyhodit asi..nevim ale jak je to pak s composer update? nebude to porad prepisovat?
so consider this code:
DROP TRIGGER IF EXISTS stock_products_BEFORE_INSERT;
DELIMITER |
CREATE TRIGGER stock_products_BEFORE_INSERT BEFORE INSERT ON stock_products
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(255);
if new.purchase_price = 0 then
SET msg = concat('Cannot INSERT row: purchase price cannot be 0 - stock_product_id:', new.id);
SIGNAL SQLSTATE '45000'
set MESSAGE_TEXT= msg;
end if;
if new.purchase_price is null AND new.is_approved=1 then
SET msg = concat('Cannot INSERT row: purchase price cannot be NULL - stock_product_id:', new.id);
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = msg;
end if;
if new.original_purchase_price = 0 then
SET msg = concat('Cannot INSERT row: original purchase price cannot be 0 - stock_product_id:', new.id);
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = msg;
end if;
END |
running it in any SQL management tool like mysql workbench works perfectly well.
But it allways return error on migration and I couldn't be able to fix it.
I use dibi driver and allways get:
fatal error: Uncaught Dibi\DriverException: You have an error in your SQL syntax; check the manual that corresponds t
o your MariaDB server version for the right syntax to use near 'DELIMITER |
CREATE TRIGGER stock_products_BEFORE_INSERT BEFORE INSERT ON stock_' at line 1
Hello,
I just tried your v3 migrations with Doctrine DBAL and I ran into few problems:
The main one:
Query is returning string "1" and therefore it is going to throw error LockException('Unable to release a lock.'); from unlock method because in lock method is LockException('Unable to acquire a lock.'); thrown and that one is catched in run method of Runner.
After commit 1293b15 migrations not support LeanMapper\Connection and requires exact Dibi\Connection or Dibi\Connection.
It throws LogicException
Invalid argument, expected instance of Dibi\Connection or DibiConnection
See
migrations/src/Engine/Runner.php
Lines 98 to 104 in 93c3ba6
$this->driver->lock() required the schema to already existCurrently, Doctrine Adapter directly requires Doctrine Connection, which is not lazy; so every Symfony Command listing starts a database connection. (https://github.com/nextras/migrations/blob/master/src/Bridges/DoctrineDbal/DoctrineAdapter.php)
edit: I'm blind :) completelly different issue happened.
migrations table is used
We set foreign_key_checks = 0 and that's have some consequences.
Running migrations with ON DELETE RESTRICT produces table with ON DELETE NO ACTION. In MySQL they are identical, still, I see this as a bug.
Also, disabled foreign_key_check may lead to hard-to-catch errors and we should not enable it by default. A migration can enable it by itself. So, I suggest removing it.
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.