======================== Correct Answer Start ========================

According to the Postgres image documentation you can extend the original image by running a script or an SQL file.

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files and source any *.sh scripts found in that directory to do further initialization before starting the service.

For your Dockerfile, you could do something like this.

FROM postgres:9.6.5

ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxx
ENV POSTGRES_DB postgres

ADD create-role.sh /docker-entrypoint-initdb.d
ADD localDump.sql /docker-entrypoint-initdb.d

You will also need the create-role.sh script to execute the plsql command

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE ROLE read_only;
EOSQL

Note that I'm not a SQL DBA, the plsql command is just an example.

======================== Correct Answer End ========================

Original answer left for posterity :

Can you use psql ? Something like that.

FROM postgres:9.6.5

ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxx
ENV POSTGRES_DB postgres

RUN psql -U postgres -d database_name -c "SQL_QUERY"

ADD localDump.sql /docker-entrypoint-initdb.d

You should find how to run SQL Query for Postgres from bash, and then add a RUN instruction in your Dockerfile.

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'

RUN gets used to build the layers in an image. CMD is the command that is run when you launch an instance (a "container") of the built image.

Also, if your script depends on those environment variables, if it's an older version of Docker, it might fail because those variables are not defined the way you want them defined!

In older versions of docker the Dockerfile ENV command uses spaces instead of "="

Your Dockerfile should probably be:

FROM microsoft/mssql-server-windows-express
COPY ./create-db.sql .
ENV ACCEPT_EULA Y
ENV SA_PASSWORD ##$wo0RD!
RUN sqlcmd -i create-db.sql 

This will create an image containing the database with your password inside it.

(If the SQL file somehow uses the environment variables, this wouldn't make sense as you might as well update the SQL file before you copy it over.) If you want to be able to override the password between the docker build and docker run steps, by using docker run --env sa_password=##$wo0RD! ..., you will need to change the last line to:

CMD sqlcmd -i create-db.sql && .\start -sa_password $env:SA_PASSWORD \
-ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

Which is a modified version of the CMD line that is inherited from the upstream image.

By providing sh -c "/opt/mssql-tools/bin/sqlcmd ... to the run command you are replacing the command that starts the SQL server in the container with the sqlcmd command.

The server is not started inside the container. You need to start it first and run your command after it has finished the startup.

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

You can load the sql file during the build phase of the image. To do this you create a Dockerfile for the db service that will look something like this:

CopyFROM mysql:5.6
COPY setup.sh /mysql/setup.sh
COPY setup.sql /mysql/setup.sql
RUN /mysql/setup.sh

where setup.sh looks something like this:

Copy#!/bin/bash
set -e
service mysql start
mysql < /mysql/setup.sql
service mysql stop

And in your docker-compose.yml you'd change image to build: ./db or the path where you put your files.

Now this works if you have all your sql in a raw .sql file, but this wont be the case if you're using rails or a similar framework where the sql is actually stored in code. This leaves you with two options.

1. Instead of using FROM mysql:5.6 you can use FROM your_app_image_that_has_the_code_in_it and apt-get install mysql .... This leaves you with a larger image that contains both mysql and your app, allowing you to run the ruby commands above. You'd replace the mysql < /mysql/setup/sql with the rails-app bundle exec rake db:create lines. You'd also have to provide an app config that hits a database on localhost:3306 instead of db:3306

2. My preferred option is to create a script which exports the sql into a .sql file, which you can then use to build your database container. This is a bit more work, but is a lot nicer. It means that instead of running rails-app bundle exec rake db:create you'd just run the script to load a db.

Such a script would look something like this:

Copy#!/bin/bash
set -e
docker-compose build rails-app
docker run -d --name mysql_empty mysql:5.6
docker run --link mysql_empty:db -v $PWD:/output project_rails-app export.sh

where export.sh looks something like this:

Copy#!/bin/bash
set -e
RAILS_ENV=development
rails-app bundle exec rake db:create
mysqldump > /output/setup.sql

You could also replace the docker run script with a second compose file if you wanted to.

Change the password of the sql server to be complex enough.

docker run -d -p 1433:1433 -e "sa_password=ComplexPW2019!" -e "ACCEPT_EULA=Y" <sqlserverimageid>

I ended up using a slightly modified version of VDR's solution which waits for the sqlservr to start by checking the logs instead of sleeping 10 seconds:

RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd' -i /opt/mssql-scripts/000_create_db.sql \
    && pkill sqlservr