README

What is Nasgrate?

Nasgrate is a console utility that let you organise database schema migration process at a consistent and easy way. It supports mysql, mssql, postgresql, oracle and other databases (you can find informaton here )

The key features:

• native SQL syntaxes for migrations
• automatically generates migrations based on saved database states (you don't need to write migrations yourself, for MySQL database only, but I plan to add support for PostgreSQL, MS SQL and Oracle databases later).
• user-friendly interface to view saved migrations

Requirements

Nasgrate is only supported by PHP 5.3.0 and up with PDO extension.

Use Docker (preferred)

The easiest way to use Nasgrate is to use Docker.

You can build your own container using Dockerfile or use Docker Hub image:

$ docker run -it --rm -v $(pwd)/data:/usr/src/nasgrate/data  \
-e DATABASE_DRIVER=mysql  \
-e DATABASE_HOST=host.docker.internal  \
-e DATABASE_NAME=[database name]  \
-e DATABASE_USER=[database user]  \
-e DATABASE_PASSWORD=[database password]  \
-e DATABASE_PORT=[database port]  \
-e VERSION_TABLE_NAME=__migrationVersions  \
-e DIR_MIGRATION=data/migrations  \
-e DIR_DBSTATE=data/dbstate  \
-e DEFAULT_DESCRIPTION_MESSAGE='Created by CURRENT_USER, CURRENT_DATE'  \
-e CURRENT_USER=[your name] \
dlevsha/nasgrate generate MyFirstMigration

You need to change variables for your own

$(pwd)/data will contain your migration files and current database state

You can also use .env file (please see .env.example). In this case you can use one line command

$ docker run -it --rm -v $(pwd)/data:/usr/src/nasgrate/data --env-file=.env dlevsha/nasgrate generate MyFirstMigration

Please remember:

• if you want to connect to your local database, use special docker variable host.docker.internal (for Mac and Windows user).
• if you want to use Nasgrate with your existing docker network (for example created with docker-compose) you need to connect to container inside your docker network. To do this first run docker network ls command and find you network name.

NETWORK ID     NAME             DRIVER    SCOPE
27feae2bb848   bridge           bridge    local
6ef8cb27a7fd   docker_default   bridge    local
d2f3d581bf31   host             host      local
318fd5030260   none             null      local

For example, I use docker_default network inside my application and you need to add --net=docker_default parameter to you command

The second thing - you need to know your database container IP in order to connect to it.

Please run docker exec [your database container name] cat /etc/hosts. Usually the last line will show the IP address

127.0.0.1	localhost
::1	localhost ip6-localhost ip6-loopback
fe00::0	ip6-localnet
ff00::0	ip6-mcastprefix
ff02::1	ip6-allnodes
ff02::2	ip6-allrouters
172.25.0.3	980280f59bd3

In my case IP is 172.25.0.3 and my command will be

$ docker run -it --rm -v $(pwd)/data:/usr/src/nasgrate/data \ 
--net=docker_default  \
-e DATABASE_DRIVER=mysql  \
-e DATABASE_HOST=172.25.0.3  \
-e DATABASE_NAME=[database name]  \
-e DATABASE_USER=[database user]  \
-e DATABASE_PASSWORD=[database password]  \
-e DATABASE_PORT=[database port]  \
-e VERSION_TABLE_NAME=__migrationVersions  \
-e DIR_MIGRATION=data/migrations  \
-e DIR_DBSTATE=data/dbstate  \
-e DEFAULT_DESCRIPTION_MESSAGE='Created by CURRENT_USER, CURRENT_DATE'  \
-e CURRENT_USER=[your name] \
dlevsha/nasgrate generate MyFirstMigration

You can run this line to obtain current ip

docker exec [mysql container name] cat /etc/hosts | tail -n 1 | cut -d$'\t' -f 1

You can also run bin/migration.sh script (please change params inside it)

./migration.sh help

Another option is you can view all transactions (executed and non-executed) via web interface. Just run command

$ docker run -it --rm -v $(pwd)/data:/usr/src/nasgrate/data \ 
-e DATABASE_DRIVER=mysql  \
-e DATABASE_HOST=host.docker.internal  \
-e DATABASE_NAME=[database name]  \
-e DATABASE_USER=[database user]  \
-e DATABASE_PASSWORD=[database password]  \
-e DATABASE_PORT=[database port]  \
-e VERSION_TABLE_NAME=__migrationVersions  \
-e DIR_MIGRATION=data/migrations  \
-e DIR_DBSTATE=data/dbstate  \
-e DEFAULT_DESCRIPTION_MESSAGE='Created by CURRENT_USER, CURRENT_DATE'  \
-e CURRENT_USER=[your name] \
-p 9001:9000 \
--entrypoint php \
dlevsha/nasgrate -S localhost:9000  

or (if you use .env file)

docker run -it --rm -v $(pwd)/data:/usr/src/nasgrate/data --env-file=docker/nasgrate/.env -p 9001:9000 --entrypoint php dlevsha/nasgrate -S 0.0.0.0:9000 

and type in your browser http://localhost:9001/app/.

You'll see your migrations

Installation

Clone project from GitHub

$ git clone https://github.com/dlevsha/nasgrate.git
$ cd nasgrate

or use composer

$ composer require dlevsha/nasgrate	

Rename .env.example to .env and change your settings:

# [Primary connection params]
# possible drivers: 'mysql' - MySQL database, 'sqlsrv' - MS SQL Server and SQL Azure databases
# 'mssql' - FreeTDS, 'pgsql' - PostgreSQL, 'oci' - Oracle
DATABASE_DRIVER=mysql
# you can use special variable 'host.docker.internal' for docker
DATABASE_HOST=127.0.0.1
DATABASE_NAME=testdb
DATABASE_USER=testuser
DATABASE_PASSWORD=testdbpass
DATABASE_PORT=3306

# [Migration params]
VERSION_TABLE_NAME=__migrationVersions
FILE_EXTENSION=sql
DIR_MIGRATION=DIR_ROOT/data/migrations
DEFAULT_DESCRIPTION_MESSAGE=Created by CURRENT_USER, CURRENT_DATE

# [Database version control]
DIR_DBSTATE=DIR_ROOT/data/dbstate
# possible values - file / database
VERSION_CONTROL_STRATEGY=file


# --------------------------------------------------------------------
# This params need only if you use second database as data source
# to compare database structure. Please read documentation.
#[Secondary connection params]
DATABASE_HOST_SECONDARY=localhost
DATABASE_NAME_SECONDARY=test
DATABASE_USER_SECONDARY=root
DATABASE_PASSWORD_SECONDARY=
DATABASE_PORT_SECONDARY=

[Primary connection params] section describes connection settings

DATABASE_DRIVER - set one of the drivers which is supported by PHP PDO extension

• mysql - MySQL database
• sqlsrv - MS SQL Server and SQL Azure databases
• mssql - FreeTDS
• pgsql - PostgreSQL
• oci - Oracle

You can find more information at official PHP PDO documentation

DATABASE_HOST - database host name or IP

DATABASE_NAME - database name

DATABASE_USER and DATABASE_PASSWORD - login and password to access your database

DATABASE_PORT - the port number where the database server is listening (not required if using standard port)

Next section [Migration params] describes how the script stores information about migrations

VERSION_TABLE_NAME - name of a table, where migration script stores service information

FILE_EXTENSION - migration file extension (by default sql)

DIR_MIGRATION - where script stores migration files. By default it stores it inside migrations directory.

If you plan to share your migrations between team members or servers using version control system (git for example) you need to move this directory to your project folder and change this path.

For example if you have project in /var/www/project/ and plan to store migrations in /var/www/project/service/scripts/migrations directory, you need to change DIR_MIGRATION to

DIR_MIGRATION = /var/www/project/service/scripts/migrations

DEFAULT_DESCRIPTION_MESSAGE - each migration has its own description.

By default a message looks like Created by CURRENT_USER, CURRENT_DATE, where CURRENT_USER and CURRENT_DATE - is a predefined constant which is changed to user name and current date respectively. So this message becomes Created by dlevsha, 2020-12-21 17:53:41 in my case.

Next section [Database version control] describes version control settings. The most powerful feature of this script is ability to track database changes and automatically create diff file which contains all database changes between migrations.

VERSION_CONTROL_STRATEGY - describes which strategy you use to store database changes. There are two possible values - file and database.

If you have two databases (prod and test for example ) and you want to generate diff file which describes differences between databases, your choice will be database and you need to feel next section [Secondary connection params] which describes connection settings to reference database.

Or you can set file value and script will automatically save database state each time you create migration (in this case you do not need to feel [Secondary connection params] section).

You can check your settings by simply running

$ php bin/nasgrate

and you are to see the help page describing base commands

Nasgrate is a console utility that let you organise database schema migration process at a consistent and easy way.
It supports mysql, mssql, postgresql, oracle (you can find informaton here http://php.net/manual/en/pdo.drivers.php)

Usage:
  php nasgrate [command] [options]

Command:
  status     - displays migration status
  generate   - creates new migration (migration file)
  diff       - save current database state and create migration with database schema diff
  up:show    - displays (but not executes) SQL-query, executed by migration update
  down:show  - displays (but not executes) SQL-query, executed by migration revert
  up         - executes migration update
  down       - executes migration revert
  help       - shows this help page

If you use Linux or MacOS for your convenience you can setup nasgrate script

Go to console and run

$ chmod +x bin/nasgrate

Now you can run Nasgrate by simply typing

$ ./bin/nasgrate	

Lets check your database connection settings

$ ./bin/nasgrate status

If all is ok you will see

Last Migration ID:  no migrations
Available Migrations: No actual migrations

If you have a connection problem you'll see an error description. For example:

DATABASE ERROR :: SQLSTATE[HY000] [1049] Unknown database 'test2'

Documentation

Create migration

Every time you create migration - you create .sql file having at least two sections: -- UP -- and -- DOWN --.

-- UP -- section contains SQL-queries that are used to update exist database schema. For example:

CREATE TABLE test (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DOWN -- method conatains SQL-queries that are used to revert database schema. For example:

DROP TABLE test

Let's create our first migration

$ ./bin/nasgrate generate CreateTestMigration

and it will display

Generate new migration ID: 20200821112753_CreateTestMigration
Please edit file: /migrations/20200821112753_CreateTestMigration.sql

By default migration will be placed in migrations directory. You can change this location in .environment file at DIR_MIGRATION param.

If you look closely you'll see that migration ID is a timestamp:

20200821112753 -> 2020-08-21 11:27:53

The created file looks like

-- Skip: no
-- Name: Test
-- Date: 01.12.2020 20:28:08
-- Description: Created by dlevsha, 2020-12-01 20:28:08

-- UP --

-- DOWN --

Skip: - if migration needs to be skiped. Possible values yes|no. Default: no. Sometimes you need to skip certain migration for any reason. You can do this by setting Skip: to yes.

Name: - your migration name

Date: - the date when the file was created

Description: - describes current migration

-- UP -- and -- DOWN -- section contains SQL-expressions. You can add as many sql queries as you want. Each sql query needs to be at a new line. Each sql query at -- UP -- section needs to have mirrow sql query at -- DOWN -- section.

For example:

-- Skip: no
-- Name: Test
-- Date: 01.12.2020 20:28:08
-- Description: The first migration. Created by dlevsha, 2020-12-01 20:28:08

-- UP --
CREATE TABLE test (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE test2 (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DOWN --
DROP TABLE test;
DROP TABLE test2;

Create migration (database schema diff) automatically

You can automaticaly create database schema diff, so you don't need describe changes in database.

If you don't know what database schema is, read this StackOverflow answer: what is purpose of database schema?

Each time you create new migration, Nasgrate saves current database schema state in a special file at dbstate directory. This file contain snapshot of you database schema as a php serialized array (tables, rows name and type, indexes and etc.).

When you change you database schema later, you can compare it with saved state and automatically create new migration with all database changes.

Another option is if you have two databases (prod and test for example), you make changes in test database and want to create new migration which contains all changes, the script can automatically do it.

You can use prefered database tools to modify database schema (for example Sequel Pro or phpMyAdmin) and no need to remember what you have changed in the database since the last migration.

By default script use file strategy to track changes in your database. If you want to compare changes in two databases using one of them as a standart - change VERSION_CONTROL_STRATEGY in .environment file to database and fill [Secondary connection params] section.

Let me give you an example

Suppose you add a new table at your database using Sequel Pro:

Run

$ ./bin/nasgrate diff AddNewTable

and it will display (in my case)

Generate new migration ID: 20201223133618
Please edit file: /migrations/20201223133618_AddNewTable.sql
This migration marked as executed

When you look at 20201223133618_AddNewTable.sql you will see that this file already has -- UP -- and -- DOWN -- sections with SQL-queries.

-- Skip: no
-- Name: AddNewTable
-- Date: 23.12.2020 13:36:18
-- Description: Created by dlevsha, 2020-12-23 13:36:18

-- UP --

CREATE TABLE `test` (
    `id` int(11) unsigned NOT NULL  auto_increment,
    `name` varchar(200)  DEFAULT NULL ,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- DOWN --

DROP TABLE IF EXISTS `test`;

Suppose you decide to change name field to VARCHAR(255) and add index for name field using the program.

Run

$ ./bin/nasgrate generate ChangeMyTestTable diff

display

Generate new migration ID: 20201223135246
Please edit file: /migrations/20201223135246_ChangeMyTestTable.sql
This migration marked as executed	

and create automatically

-- Skip: no
-- Name: ChangeMyTestTable
-- Date: 23.12.2020 13:52:46
-- Description: Created by dlevsha, 2020-12-23 13:52:46

-- UP --

ALTER TABLE `test` CHANGE `name` `name` varchar(255)  DEFAULT NULL;

ALTER TABLE `test` ADD KEY `name` (`name`);


-- DOWN --

ALTER TABLE `test` CHANGE `name` `name` varchar(200)  DEFAULT NULL;

ALTER TABLE `test` DROP  KEY `name`;

View migrations list

Before we run our first migation let's view query at our migration

$ ./bin/nasgrate up:show

and it will display

Migration :: 20200821112753_CreateTestMigration
Description: The first migration. Created by dlevsha, 2020-12-01 20:28:08 
	
CREATE TABLE test (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
	
CREATE TABLE test2 (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We can see each query which will be executed during migration process.

Another option is you can view all transactions (executed and non-executed) via web interface. Just run command

php -S localhost:9000 

inside script directory and type in your browser http://localhost:9000/app/.

You'll see your migrations

Update database schema (run migration)

If all is ok let's run migration.

./bin/nasgrate up

and it will display

Migration :: 20200821112753_CreateTestMigration
	
CREATE TABLE test (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
	
	
CREATE TABLE test2 (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
	
... complete

If you look at your database you will see three tables.

__migrationVersions
test
test2

__migrationVersions is a service table created by migration script. It contains an executed migration ID. If you want to change the name of this table edit VERSION_TABLE_NAME constant at .environment file. Never remove this table or you will loose you migration information.

test and test2 are the tables created through migration process.

If you want to update database schema before a certain migration you need to set this migration ID as an argument

$ ./bin/nasgrate up:run 20200821132420

Revert database schema

If something goes wrong and you want to rollback your changes you need to use revert process. Before you run this update you need to know migration ID to which you want to apply revert database schema process.

You can display all migration IDs at your database by runing

$ ./bin/nasgrate list

or using web-interface described above and it will display

Migration list:
 - [26.08.2020 19:39:39] 20200826193939_CreateFirstMigration - new
 - [26.08.2020 19:30:33] 20200826193033_New_Table_Test - executed

You see that you have two migrations at your database. Migration 20200821112753 is already executed, 20200826193939_CreateFirstMigration is not executed.

Let's imagine you want to revert 20200821112753_CreateFirstMigration migration.

$ ./bin/nasgrate down:show 20200821112753

or

$ ./bin/nasgrate down:show 20200821112753_CreateFirstMigration

and it will display

Migration :: 20200821112753_CreateFirstMigration
Description: The first migration. Created by dlevsha, 2020-08-21 11:27:53

DROP TABLE test
DROP TABLE test2	

Lets run revert process

$ ./bin/nasgrate down:run 20200821112753_CreateFirstMigration

and it will display

Migration :: 20200821112753_CreateFirstMigration

DROP TABLE test
DROP TABLE test2

... complete	

If you look at your database you can see that test and test2 tables were removed.

Run again list command

$ ./bin/nasgrate list

and it will display

Migration list:
 - [26.08.2020 19:39:39] 20200826193939_CreateFirstMigration - new
 - [26.08.2020 19:30:33] 20200826193033_New_Table_Test - new

LICENSE

Copyright (c) 2021, Levsha Dmitry

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.