How to run Database migrations

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 4.5.3
  • Operating System: macOS 13.2.1
  • Database: postgreSQL
  • Node Version: 16.17.0
  • NPM Version: 8.15.0

Hello,
Can someone help me to run database migrations?
I’m trying to create and populate table “state”.
when i start the application she is reading the migration, appearing at strapi_migrations and for some reason it doesn’t create the table and consequently doesn’t populate it.

Do i have to run some CLI to run the migration before start the application?

my migration file

//./database/migrations/2023.05.25T14.49.35.create-and-populate-state-table.js

"use strict";

async function up(knex) {
  await knex.schema.createTable("state", (table) => {
    table.increments();
    table.string("region");
    table.string("uf");
    table.string("name");
    table.timestamps();
  });

  const states = [
    { region: "NORTE", uf: "AC", name: "Acre" },
    { region: "NORDESTE", uf: "AL", name: "Alagoas" },
    { region: "NORTE", uf: "AP", name: "Amapá" },
    { region: "NORTE", uf: "AM", name: "Amazonas" },
    { region: "NORDESTE", uf: "BA", name: "Bahia" },
    { region: "NORDESTE", uf: "CE", name: "Ceará" },
    { region: "CENTRO-OESTE", uf: "DF", name: "Distrito Federal" },
    { region: "SUDESTE", uf: "ES", name: "Espírito Santo" },
    { region: "CENTRO-OESTE", uf: "GO", name: "Goiás" },
    { region: "NORDESTE", uf: "MA", name: "Maranhão" },
    { region: "CENTRO-OESTE", uf: "MT", name: "Mato Grosso" },
    { region: "CENTRO-OESTE", uf: "MS", name: "Mato Grosso do Sul" },
    { region: "SUDESTE", uf: "MG", name: "Minas Gerais" },
    { region: "NORTE", uf: "PA", name: "Pará" },
    { region: "NORDESTE", uf: "PB", name: "Paraíba" },
    { region: "SUL", uf: "PR", name: "Paraná" },
    { region: "NORDESTE", uf: "PE", name: "Pernambuco" },
    { region: "NORDESTE", uf: "PI", name: "Piauí" },
    { region: "SUDESTE", uf: "RJ", name: "Rio de Janeiro" },
    { region: "NORDESTE", uf: "RN", name: "Rio Grande do Norte" },
    { region: "SUL", uf: "RS", name: "Rio Grande do Sul" },
    { region: "NORTE", uf: "RO", name: "Rondônia" },
    { region: "NORTE", uf: "RR", name: "Roraima" },
    { region: "SUL", uf: "SC", name: "Santa Catarina" },
    { region: "SUDESTE", uf: "SP", name: "São Paulo" },
    { region: "NORDESTE", uf: "SE", name: "Sergipe" },
    { region: "NORTE", uf: "TO", name: "Tocantins" },
  ];

  for (const state of states) {
    await knex
      .insert([{ region: state.region, uf: state.uf, name: state.name }])
      .into("state");
  }
}

module.exports = { up };
2

Don’t even think you need to use knex for this.
You could just create it in bootstrap in index.js
From here you can use entity service to just add the data.

Might also want to add a check if the data is already there, but that’s what I would suggest.

2 Likes
3

Thank you so much!

I hadn’t thought on this possibility.
This worked for me. :smiley:

1 Like
4

I had the same problem. Something I noticed that will be useful for others with the same problem:

Every time you run a migration, the name of your javascript/typescript migration file (e.g. 2023.05.25T14.49.35.create-and-populate-state-table.js) is stored in the database so that strapi knows not the run that file again.

TLDR: Make sure you rename your migration file or create a new one for new changes as strapi doesn’t rerun them.

For the strapi team: would it be worth storing in the strapi_migrations table the migration file’s checksum (e.g. sha, md5, etc) as well? #foodForThought