Can't use migration to one database from two different services on Golang from Docker

Question

I have a problem migrating PostgreSQL between two applications written on Golang inside Docker. Only one table is created, which was migrated first through the Docker container.

I use the Migration utility. The first application is “Items”, the second is “Order”. In each directory of these applications there are "migrations" folders. Inside "Items/migrations" there are 001_create_items_up.sql and "down" respectively, and inside "order/migrations" there is 001_create_order_up.sql. If start the "items" container first, the table for "order" will not be created and vice versa.

001_create_items_up.sql

CREATE TABLE IF NOT EXISTS items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL NOT NULL
);

001_create_order_up.sql

CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    item_id INT NOT NULL,
    quantity INT DEFAULT 1
);

Dockerfile for both apps

FROM golang:1.22 as builder
WORKDIR /app
COPY go.mod go.sum ./
RUN go mod download
COPY . .
RUN CGO_ENABLED=0 GOOS=linux go build -o service .
FROM alpine:latest  
RUN apk --no-cache add ca-certificates
WORKDIR /root/
COPY --from=builder /app/service .
COPY --from=builder /app/migrations /root/migrations
CMD ["./service"]

Docker-compose

version: '3.8'
services:
  postgres:
    image: postgres:13
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: dbname
    ports:
      - "5432:5432"
    volumes:
      - /database:/var/lib/postgresql_data
  items-service:
    build: 
      context: /items
      dockerfile: Dockerfile
    depends_on:
      - postgres
  orders-service:
    build: 
      context: /order
      dockerfile: Dockerfile
    depends_on:
      - postgres

Code for both apps "items" and "order"

package main

import (
    "log"

    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    m, err := migrate.New(
        "file://migrations",
        "postgres://user:password@postgres:5432/dbname?sslmode=disable",
    )
    if err != nil {
        log.Fatalf("migration failed to initialize: %v", err)
    }
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        log.Fatalf("migration failed: %v", err)
    }
}

Answer 1

The tool you use, go-migrate, assumed that you have a single directory per database. It stores the numeric identifier (either incrementing number, or datetime) in a table to determine at what version the database is, so it knows what to do.

If you use the incrementing number (as you do in your question), when your second service starts, migrate will think that all migrations have already been executed.

If you use the datetime (as you tried based on the comment by @DavidMaze), you'll find that migrate may see that the database it at a "higher version" than your highest up, and so, try to downgrade, only to find that there is no down script.

A work around for this problem would be to use the x-migrations-table option to instruct migrate to use different tables to track the migrations from your two different applications.

So your main would become something like this:

func main() {
    m, err := migrate.New(
        "file://migrations",
        "postgres://user:password@postgres:5432/dbname?sslmode=disable&x-migrations-table=orders-service-schema",
    )

    // your error handling and what not
}