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?
2
Answers
I resolve this problem by deleting migration table and correcting sql file (delete ',' in every string of last columns)
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:
This command spins up an instance of Postgres with these two things:
5432
on your machine is mapped to the port5432
of your Docker container).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:
Thanks to this command, I was able to create the folder path
db/migrations
and two files within it (one for theup
migration and one for thedown
one).Fill the files with the code
The next step was to fill the above-created files with the
CREATE
andDROP
statements. Let’s start with theup
one.000001_create_users_table.up.sql
fileTo 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
fileThe 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:
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:
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!