· 8 years ago · Dec 07, 2017, 11:50 AM
1- [Creating A Migration](#creating-a-migration)
2 + [Generating a migration](#generating-a-migration)
3 - [Directives](#directives)
4 + [Running Migrations](#running-migrations)
5 + [Rollback Migrations](#rollback-migrations)
6 + [Redoing Migrations](#redoing-migrations)
7 + [Migration Status](#migration-status)
8 + [Migration Version](#migration-version)
9 + [Complex Statements](#complex-statements)
10
11# Creating A Migration
12
13
14Amber makes it easy to interact with your database. Amber supports `Postgres`, `MySql` and `Sqlite`.
15
16Edit the database setting for your current environment by editing `{project_name}/config/environments/{current_environment}.yml` file. Amber looks at the `database_url` key for the default database connection string.
17
18### Generating a migration
19
20Amber uses the `micrate` shard for migrations. Lets start by creating a new migration file `amber g migration create_posts` this will create a new SQL file called `db/migrations/20171204192501874_create_posts.sql` that contains the following:
21
22```
23-- +micrate Up
24-- SQL in section 'Up' is executed when this migration is applied
25
26
27-- +micrate Down
28-- SQL section 'Down' is executed when this migration is rolled back
29```
30
31#### Directives
32
33Comments that start with `+micrate` are interpreted by micrate when running your migrations. In this case, the `Up` and `Down` directives are used to indicate which SQL statements must be run when applying or reverting a migration. You can now go along and write your migration like this:
34
35```sql
36-- +micrate Up
37CREATE TABLE posts(
38 post_id INT PRIMARY KEY,
39 title VARCHAR NOT NULL,
40 content TEXT NOT NULL,
41 publiched_at TIMESTAMP NOT NULL,
42);
43
44-- +micrate Down
45DROP TABLE posts;
46```
47
48### Running Migrations
49
50Now run it using `amber db migrate`. This command will execute all pending migrations:
51
52```
53$ amber db migrate
54Migrating db, current version: 0, target: 20160524162947
55OK 20171204192501874_create_posts.sql
56```
57
58### Rollback Migrations
59
60If you ever need to roll back the last migration, you can do so by executing `amber db rollback`.
61
62### Redoing Migrations
63
64There's also `amber db redo` which rolls back the last migration and applies it again.
65
66### Migration Status
67
68Last but not least: use `amber db status` to find out the state of each migration:
69
70```
71Applied At Migration
72=======================================
732016-05-24 16:31:07 UTC -- 20160524162446_add_users_table.sql
74Pending -- 20160524163425_add_address_to_users.sql
75```
76
77### Migration Version
78
79At any time you can find out the current version of the database with `amber db version`.
80
81```
8220160524162446
83```
84
85
86### Complex Statements
87
88If using complex statements that might contain semicolons, you must give a hint on how to split the script into separate statements. You can do this with StatementBegin and StatementEnd directives
89
90```
91-- +micrate Up
92-- +micrate StatementBegin
93CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
94returns void AS $$
95DECLARE
96 create_query text;
97BEGIN
98 FOR create_query IN SELECT
99 'CREATE TABLE IF NOT EXISTS histories_'
100 || TO_CHAR( d, 'YYYY_MM' )
101 || ' ( CHECK( created_at >= timestamp '''
102 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
103 || ''' AND created_at < timestamp '''
104 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
105 || ''' ) ) inherits ( histories );'
106 FROM generate_series( $1, $2, '1 month' ) AS d
107 LOOP
108 EXECUTE create_query;
109 END LOOP; -- LOOP END
110END; -- FUNCTION END
111$$
112language plpgsql;
113-- +micrate StatementEnd
114```