mysqlgodocker-composego-gorm

dial tcp 192.168.48.2:3306: connect: connection refused

My connection to the DB keeps getting refused i tried to make sure all the ports were correct and parameters were matched up but i still keep getting this error

this is my docker compose file

services:
  db:
    image: mysql:8.0
    platform: linux/amd64
    container_name: mysql_container
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: dbname
      MYSQL_USER: user
      MYSQL_PASSWORD: user_password
    expose:
      - "3306"
    volumes:
      - mysql-data:/var/lib/mysql

  playgobackend:
    build: 
      context: .
      dockerfile: ./cmd/main/Dockerfile
    container_name: playgobackend
    ports:
      - "8080:8080"
    depends_on:
      - db
    environment:
      DB_HOST: db
      DB_PORT: 3306
      DB_USER: user
      DB_PASSWORD: user_password
      DB_NAME: dbname

volumes:
  mysql-data:
networks:
  default:

this is the code I have to connect the go api to the database

var (
    db *gorm.DB
)

func Connect() {
    dsn := "user:user_password@tcp(db:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
    d, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    db = d
}

I tried changing the porst and tried using to root user instead but still giving me the same error.

Solution

  • All code is available at https://github.com/mwmahlberg/go-gorm-78643564

    Most likely, you have a race condition: Your application tries to connect to the database before it is ready. Using your code (mostly), I was able to reproduce the problem.

    There are three main things you can do:

    1. Use docker compose facilities to ensure the database is up and running before your application container is started.
    2. Add a retry logic to your application
    3. Use a rather nifty tool called dockerize to ensure the database is available before your application is started.

    Using docker compose

    Advantages:

    Disadvantages:

    What you do here is twofold: You add a health check to the dependency and modify your depends_on statement to explicitly expect the dependency to be healthy (abbreviated for readability):

    service:
  db:
    healtcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      start_interval: 10s
      start_period: 40s
      timeout: 20s
      retries: 10
  playgobackend:
    depends_on:
      db:
        condition: service_healthy

    Now, docker-compose will only start the playgobackend container after the first successful startup check, which will be executed every 10s in the first 40s after the container was started.

    Problem is: If you want to migrate to Kubernetes or even just running your application on a cli, you kind of have the same problem again. From my point of view, it is s stop-gap measure to get things running for a proof of concept, for example. For production use, one of the other solutions should be used.

    Adding a retry logic to your application.

    Advantages:

    Disadvantages:

    The naive method would be something like this

    for {
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err == nil {
      break
    }
}

    The obvious problem here is that the application will always appear to run while may or may not be stuck in a loop. A better way would be to try to establish the connection a couple of times and ultimately fail if none of the connection attempts was successful. While "a little copying is better than a little dependency" I like to use avast/retry-go for this purpose: It is simple to use, mature and has all the bells and whistles needed if you need them without making it complicated to use them (blocks and functions reordered for readability):

    package main

import (
    "context"
    "flag"
    "fmt"
    "log"
    "os/signal"
    "syscall"
    "time"

    "github.com/avast/retry-go/v4"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

func main() {
    flag.Parse()

    log.Println("starting up")

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?%s", dbuser, dbpass, dbhost, dbport, dbname, dbparams)

    db, dbConnError := retry.DoWithData(
        // This function will be called until it returns nil or the number of attempts is reached
        // Note that it returns a *gorm.DB and an error, so we basically can use it easily
        // as a drop in replacement for gorm.Open.
        func() (*gorm.DB, error) {
            return gorm.Open(mysql.Open(dsn), &gorm.Config{})
        },
        // Try 5 times with a 5 second delay between each attempt and only return the last error.
        // See https://pkg.go.dev/github.com/avast/retry-go/v4#Options for more options.
        retry.Attempts(5),
        retry.LastErrorOnly(true),
        retry.Delay(5*time.Second),
        retry.OnRetry(func(n uint, err error) {
            log.Printf("connection attempt %d failed: %s", n, err)
        }))

    if dbConnError != nil {
        panic(fmt.Errorf("failed to connect to database: %w", dbConnError))
    }

    log.Println("Migrating user table")
    if err := db.AutoMigrate(&User{}); err != nil {
        panic(fmt.Errorf("failed to migrate: %w", err))
    }

    log.Println("waiting for signal")
    ctx, cancel := signal.NotifyContext(context.Background(), syscall.SIGINT, syscall.SIGTERM)
    defer cancel()
    <-ctx.Done()
    log.Println("shutting down")
}

// User is a dummy model for testing
type User struct {
    gorm.Model
    Name string
}

var (
    dbuser   string
    dbpass   string
    dbhost   string
    dbport   string
    dbname   string
    dbparams string
)

func init() {
    flag.StringVar(&dbuser, "dbuser", "user", "database user")
    flag.StringVar(&dbpass, "dbpass", "user_password", "database password")
    flag.StringVar(&dbhost, "dbhost", "db", "database host")
    flag.StringVar(&dbport, "dbport", "3306", "database port")
    flag.StringVar(&dbname, "dbname", "dbname", "database name")
    flag.StringVar(&dbparams, "dbparams", "charset=utf8mb4&parseTime=True&loc=Local", "additional database params")
}

    Use dockerize

    Advantages:

    Disadvantages:

    Our use case is rather simple to implement: you put dockerize into your container, and have it call your application as soon as the mysql server is available:

    CMD /usr/local/bin/dockerize -timeout 20s -wait tcp://${DB_HOST}:${DB_PORT} /path/to/your/app

    A more complete example:

    FROM golang:1.22.4-alpine3.19 AS builder
COPY . /app
WORKDIR /app
RUN go build -o main .

FROM alpine:3.19
ENV DOCKERIZE_VERSION v0.7.0

RUN apk update --no-cache \
  && apk add --no-cache wget openssl \
  && wget -O - https://github.com/jwilder/dockerize/releases/download/$DOCKERIZE_VERSION/dockerize-linux-amd64-$DOCKERIZE_VERSION.tar.gz | tar xzf - -C /usr/local/bin
COPY --from=builder /app/main /usr/local/bin/migrate-users
CMD /usr/local/bin/dockerize -timeout 20s -wait tcp://${DB_HOST}:${DB_PORT} /usr/local/bin/migrate-users

    Conclusion

    Now, the question arises "Which solution should I use?" and the answer as almost always is: "It depends."

    If you can live with a little additional dependency and a few lines of additional code, I'd use retry-go. It is the most robust solution. However, if you are sure that your application will always run in a containerized environment and dockerize solves some additional problems for you, that might be the way to go. If you are just throwing together a PoC, though, both of those solutions might be a bit too much, and using docker compose to orchestrate the startup may well be sufficient.

    hth.