Could not update tables: Error 1054: Unknown column '$1' in 'field list'

[scott-wilson]

Hello,

I'm trying to use the goose.Up function in my project, but I'm running into the error Could not update tables: Error 1054: Unknown column '$1' in 'field list'. I'm probably doing something wrong, but some help with this would be greatly appreciated.

Failing code

func UpdateTables() error {
	log.Println("Updating the tables.")
	config, err := config.New()

	if err != nil {
		log.Println("Could not access the config struct")
		return err
	}

	conn, err := connections.New()

	if err != nil {
		log.Println("Could not access the database")
		return err
	}

	// conn is a sqlx.DB, so get the sql.DB pointer from that
	// config.MigrationsPath points to the db directory with the migrations file.
	if err := goose.Up(conn.DB, config.MigrationsPath); err != nil {
		log.Println("Could not update database")
		return err
	}

	return nil
}

func CreateDefaults() error {
	log.Println("Creating default entries.")
	if err := CreateDefaultStatuses(); err != nil {
		return err
	}
	return nil
}

Migrations file

-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied

-- Enums
CREATE TABLE StatusEnum(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name)
);
CREATE TABLE ClassEnum(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name)
);
CREATE TABLE GenderEnum(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  other VARCHAR(150) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name)
);

-- Roles and permissions
CREATE TABLE Role(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  status_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  isAdmin BOOLEAN NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name),
  CONSTRAINT statusRole_fkConstraint
    FOREIGN KEY (status_id) REFERENCES StatusEnum (id)
);
CREATE TABLE Permission(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name)
);
CREATE TABLE RolePermission(
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  CONSTRAINT roleRolePermission_fkConstraint
    FOREIGN KEY (role_id) REFERENCES Role (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT permissionRolePermission_fkConstraint
    FOREIGN KEY (permission_id) REFERENCES Permission (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- Hero/Party classes
CREATE TABLE Hero(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  firstName VARCHAR(50) NOT NULL,
  lastName VARCHAR(50) NOT NULL,
  email VARCHAR(200) NOT NULL,
  gender_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  status_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  UNIQUE(email),
  PRIMARY KEY(id),
  CONSTRAINT statusHero_fkConstraint
    FOREIGN KEY (status_id) REFERENCES StatusEnum (id),
  CONSTRAINT genderHero_fkConstraint
    FOREIGN KEY (gender_id) REFERENCES GenderEnum (id),
  CONSTRAINT roleHero_fkConstraint
    FOREIGN KEY (role_id) REFERENCES Role (id)
);
CREATE TABLE Party(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  partyLeader_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT heroParty_fkConstraint
    FOREIGN KEY (id) REFERENCES Hero (id)
);
CREATE TABLE HeroParty(
  hero_id INT UNSIGNED NOT NULL,
  party_id INT UNSIGNED NOT NULL,
  CONSTRAINT heroHeroParty_fkConstraint
    FOREIGN KEY (hero_id) REFERENCES Hero (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT partyHeroParty_fkConstraint
    FOREIGN KEY (party_id) REFERENCES Party (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- Authentication
CREATE TABLE Auth(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  seed CHAR(128) NOT NULL,
  iterations INT UNSIGNED NOT NULL,
  hashedPassword CHAR(128) NOT NULL,
  hero_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT heroAuth_fkConstraint
    FOREIGN KEY (hero_id) REFERENCES Hero (id)
);

-- Invitation
CREATE TABLE Invitation(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug CHAR(128) NOT NULL,
  status_id INT UNSIGNED NOT NULL,
  party_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(slug),
  CONSTRAINT statusInvitation_fkConstraint
    FOREIGN KEY (status_id) REFERENCES StatusEnum (id),
  CONSTRAINT partyInvitation_fkConstraint
    FOREIGN KEY (party_id) REFERENCES Party (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- Blog
CREATE TABLE BlogPost(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(200) NOT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  submitDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  displayDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  hero_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(slug),
  CONSTRAINT heroBlogPost_fkConstraint
    FOREIGN KEY (hero_id) REFERENCES Hero (id)
);

-- Survey
CREATE TABLE Survey(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  description TEXT NOT NULL,
  startDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  endDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);
CREATE TABLE Question(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  survey_id INT UNSIGNED NOT NULL,
  question JSON,
  PRIMARY KEY(id),
  CONSTRAINT surveyQuestion_fkConstraint
    FOREIGN KEY (survey_id) REFERENCES Survey (id)
);
CREATE TABLE Response(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  question_id INT UNSIGNED NOT NULL,
  hero_id INT UNSIGNED NOT NULL,
  response JSON,
  PRIMARY KEY(id),
  CONSTRAINT questionResponse_fkConstraint
    FOREIGN KEY (question_id) REFERENCES Question (id),
  CONSTRAINT heroResponse_fkConstraint
    FOREIGN KEY (hero_id) REFERENCES Hero (id)
);


-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE Response;
DROP TABLE Question;
DROP TABLE Survey;
DROP TABLE Invitation;
DROP TABLE BlogPost;
DROP TABLE Auth;
DROP TABLE HeroParty;
DROP TABLE Party;
DROP TABLE Hero;
DROP TABLE RolePermission;
DROP TABLE Permission;
DROP TABLE Role;
DROP TABLE StatusEnum;
DROP TABLE GenderEnum;
DROP TABLE ClassEnum;

[scott-wilson]

Turns out I needed to do goose.SetDialect before upgrading the table.

[Lilihx]

Thanks a lot