I'm starting learn Go and SQL. I tryed to make initial migration using golang-migrate in my go project. Database is postgresql
This is migration file:
CREATE TABLE users
(
id serial not null unique,
name varchar(255) not null,
username varchar(255) not null unique,
password_hash varchar(255) not null,
)
CREATE TABLE todo_lists
(
id serial not null unique,
title varchar(255) not null,
description varchar(255),
);
CREATE TABLE users_lists
(
id serial not null unique,
user_id int references users (id) on delete cascade not null,
list_id int references todo_lists (id) on delete cascade not null,
);
CREATE TABLE todo_items
(
id serial not null unique,
title varchar(255) not null,
description varchar(255),
done boolean not null default false,
);
CREATE TABLE lists_items
(
id serial not null unique,
item_id int references todo_items (id) on delete cascade not null,
list_id int references todo_lists (id) on delete cascade not null,
);
The command I use:
migrate -path ./schema -database 'postgres://postgres:root@localhost:5432/to_do?sslmode=disable' up
And bash returns:
no change
(without any error)
Where can be problem?
CodePudding user response:
I put together a small guide to help you solve your issue. Please be sure to follow along and you would be good to go!
Run Postgres
To run the Postgres instance I used to test my solution I used the following command:
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres
This command spins up an instance of Postgres with these two things:
- It sets up the port mapping (the port
5432
on your machine is mapped to the port5432
of your Docker container). - It sets the password for the
postgres
user topostgres
(just for the sake of the demo).
Create the migration scripts
To create the first migration script, I used the following command:
migrate create -ext sql -dir db/migrations -seq create_users_table
Thanks to this command, I was able to create the folder path db/migrations
and two files within it (one for the up
migration and one for the down
one).
Fill the files with the code
The next step was to fill the above-created files with the CREATE
and DROP
statements. Let's start with the up
one.
000001_create_users_table.up.sql
file
CREATE TABLE IF NOT EXISTS users
(
id serial not null unique,
name varchar(255) not null,
username varchar(255) not null,
password_hash varchar(255) not null
);
CREATE TABLE IF NOT EXISTS todo_lists
(
id serial not null unique,
title varchar(255) not null,
description varchar(255)
);
CREATE TABLE IF NOT EXISTS users_lists
(
id serial not null unique,
user_id int references users (id) on delete cascade not null,
list_id int references todo_lists (id) on delete cascade not null
);
CREATE TABLE IF NOT EXISTS todo_items
(
id serial not null unique,
title varchar(255) not null,
description varchar(255),
done boolean not null default false
);
CREATE TABLE IF NOT EXISTS lists_items
(
id serial not null unique,
item_id int references todo_items (id) on delete cascade not null,
list_id int references todo_lists (id) on delete cascade not null
);
To make the migration idempotent I added the IF NOT EXISTS
check. Take this as a best practice when you're about to write migrations.
000001_create_users_table.down.sql
file
DROP TABLE IF EXISTS users_lists;
DROP TABLE IF EXISTS lists_items;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS todo_lists;
DROP TABLE IF EXISTS todo_items;
The same applies here with the IF EXISTS
check. Pay attention to the order in which you're deleting stuff as you can easily get into error due to objects' dependencies.
Run migrations
To run this migration, be sure that the to_do DB is created. To apply the migration run:
migrate -database 'postgres://postgres:postgres@localhost:5432/to_do?sslmode=disable' -path ./db/migrations up
With this, you'll get this output: 1/u create_users_table (44.392422ms)
.
If you run this twice, the second output will be: no change
.
When you want to undo the migration, you've to run the following statement:
migrate -database 'postgres://postgres:postgres@localhost:5432/to_do?sslmode=disable' -path ./db/migrations down
This will undo all of the migrations applied so far. For a deeper understanding, please refer to the official doc: https://github.com/golang-migrate/migrate#cli-usage.
Let me know if this solves your issue or if you need anything else!
CodePudding user response:
I resolve this problem by deleting migration table and correcting sql file (delete ',' in every string of last columns)