Golang-migrate returns "no change"

Question

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?

Answer 1

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:

1. It sets up the port mapping (the port 5432 on your machine is mapped to the port 5432 of your Docker container).
2. It sets the password for the postgres user to postgres (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!

Answer 2

I resolve this problem by deleting migration table and correcting sql file (delete ',' in every string of last columns)