How do I run SQL scripts within a Dockerfile to populate Microsoft SQL Docker image?

Question

I've downloaded the Microsoft SQL Server Express image off of Docker Hub. I'm trying to extend it by adding empty databases with empty tables in it and have that output as an image that I can then use throughout my project.

How do I run the following SQL Script?

initdb.sql

USE MASTER
GO

CREATE DATABASE MyDB1
GO

CREATE TABLE [dbo].[MyTable1]
(
    [Id] INT NOT NULL,
    [Name] NVARCHAR(256) NOT NULL,
)

CREATE TABLE [dbo].[MyTable2]
(
    [Id] INT NOT NULL,
    [Name] NVARCHAR(256) NOT NULL,
)
GO

-- Create database and tables for Clinical
CREATE DATABASE MyDB2
GO

CREATE DATABASE MyDB3
GO

Here's the Dockerfile I have so far:

FROM microsoft/mssql-server-windows-express AS base
WORKDIR /app

ENV ACCEPT_EULA Y
ENV sa_password supersecretpassword

FROM base AS final
WORKDIR /app

I don't know whether or not to use the CMD command or somehow call SQLCMD but I don't the right way to do that.

Any help would be greatly appreciated.

andre.obueno · Accepted Answer · 2019-12-27 12:42:54Z

1

When creating your DockerFile, you can do something like this:

FROM mysql:latest
ENV MYSQL_ROOT_PASSWORD 1234
ENV MYSQL_DATABASE dbName
ENV MYSQL_USER user
ENV MYSQL_PASSWORD 123
ADD myScript.sql /docker-entrypoint-initdb.d
EXPOSE 3306

By doing that, it will already run the myScript.sql when creating this docker image.

Ah, it is important to mention that the myScript.sql file should be in the same directory as the DockerFile file.

I hope this can help you :)

answered Dec 27, 2019 at 12:42

andre.obueno

431 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Bernier · Accepted Answer · 2018-08-30 20:06:38Z

0

Hopefully someone will have a better solution than this, but I ended up writing a little C# function to do it. As I'm using the SQL docker to do some C# based automated testing, it was not a big deal to add, this in:

private bool SqlServerScript(string connection, string filename) {
  try {
    using (SqlConnection sqlConnection = new SqlConnection(connection)) {
      sqlConnection.Open();
      SqlCommand sqlCommand = new SqlCommand(File.ReadAllText(filename), sqlConnection);
      sqlCommand.ExecuteNonQuery();
      return true;
    }
  } catch {
  }
  return false;
}

(error handling left as an exercise for the reader)

answered Aug 30, 2018 at 20:06

Marc Bernier

3,00628 silver badges45 bronze badges

Collectives™ on Stack Overflow

How do I run SQL scripts within a Dockerfile to populate Microsoft SQL Docker image?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related