CODEWITHSUNDEEP
sql-serverdockerdockerfile
noblerare
noblerare

Reputation: 11943

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

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.

Upvotes: 0

Views: 460

Answers (2)

andre.obueno
andre.obueno

Reputation: 43

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 :)

Upvotes: 1

Marc Bernier
Marc Bernier

Reputation: 2996

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)

Upvotes: 0

Related Questions