Executing SQL Scripts on Docker Container

Executing SQL scripts on docker container

If you are going to do pipe redirections in your command, pass it as a string to /bin/sh:

docker exec <container_id> /bin/sh -c 'mysql -u root -ppassword </dummy.sql'

How to run .sql script against docker container after a different (dependent) container starts?

So here's my solution.

I modified my flyway code to dynamically include extra scripts if they exists as follows.

In my database java config in configApi I read an env variable that specifies any dir with extra/app external scripts:

// on class level
@Value("${FLYWAY_FOLDER}")
private String externalFlywayFolder;

//when creating DataSource bean
List<String> flywayFolders = new ArrayList<>();
flywayFolders.add("classpath:db/migrations");
if (externalFlywayFolder != null && !externalFlywayFolder.isEmpty()) {
    flywayFolders.add("filesystem:"+externalFlywayFolder);
}
String[] flywayFoldersArray = new String[flywayFolders.size()];
flywayFolders.toArray(flywayFoldersArray);

Flyway flyway = Flyway
        .configure()
        .dataSource(dataSource)
        .baselineOnMigrate(true)
        .schemas("flyway")
        .mixed(true)
        .locations(flywayFoldersArray)
        .load();
flyway.migrate();

Then I modified the docker compose to attach extra files to the container and set the FLYWAY_FOLDER env variable:

  configApi:
    image: org/config-api:latest
    volumes:
      - ./scripts/flyway/configApi:/flyway #attach scripts to container
    ports:
      - 8234:8234
    environment:
      - DB_PORT=5432
      - DB_HOST=postgres
      - FLYWAY_FOLDER=flyway #specify script dir for api
    depends_on:
      - postgres

Then just a case of adding the files, but the trick is to make them repeatable migrations so they don't interfere with any versioned migrations that may be done for the configApi itself.

Repeatable migrations are applied after versioned migrations, also they get reapplied if their checksum changes.

How do I run a sql file of inserts through docker run?

to execute commands against a running container use docker exec.

to copy a file (ex: dump.sql) into a container, use docker cp

So your approach might look something like this:

docker cp ./dump.sql pg_test:/docker-entrypoint-initdb.d/dump.sql
docker exec -u postgres pg_test psql postgres postgres -f docker-entrypoint-initdb.d/dump.sql

here it is in generic form:

docker cp ./localfile.sql containername:/container/path/file.sql
docker exec -u postgresuser containername psql dbname postgresuser -f /container/path/file.sql

And note that if you need to seed your database every time it is run, the folder /docker-entrypoint-initdb.d/ does have special significance, if you're using the offical postgres image

executing mysql script docker

Since your script is outside the container, there's no need to catch the shell input redirection. You should be able to run the following:

docker exec -i 3a21e5e3669d mysql -u root -ppwd <./test.sql

---

Related Topics