Recently I wanted use the Fluent migrator for seeding my postgres database, which was running as a docker container. I basically had a table User, which had a primary key of type UUID that needs to be auto-generated.
My migration code looked as follows.
Create.Table(User.TABLE_NAME) .WithColumn("id").AsGuid().NotNullable().WithDefaultValue(SystemMethods.NewGuid).PrimaryKey() .WithColumn("username").AsString().NotNullable() .WithColumn("password").AsString().NotNullable();
The above code would essentially turn out as the following postgres sql.
CREATE TABLE "public"."users" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "username" text NOT NULL, "password" text NOT NULL, CONSTRAINT "PK_users" PRIMARY KEY ("id"));
Notice the default function
uuid_generate_v4() used in the query. This would generate an error if you use the default Postgres docker image for building your container.
The error was 42883: function uuid_generate_v4() does not exist
This is because the function
uuid_generate_v4() is defined in an extension
uuid-ossp which has to be installed first in the container before the command could be executed. Since we are using
docker compose for handling our containers, we will use the same to install the extension.
We will define our required scripts to install the extension in a separate file and include it in our docker compose.
\c ntuserauth; CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
In the above code, we have used the
ntuserauth database (which is the database in which we need to install the extension) and then instructed to install the extension
uuid-ossp if it doesn’t already exists. We will the scripts in a file
install-extensions.sql and place in our scripts directory.
We will then use the scripts defined above to be copied over to
docker-entrypoint-initdb.d and used to initialize the database.
From the official Docker image page for Postgres
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, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service
Let us go ahead change our
docker compose to include the scripts.
nt.authservice.db: image: postgres:14.1-alpine container_name: nt.authservice.db hostname: "nt.authservice.db" restart: always environment: - POSTGRES_USER=postgres - POSTGRES_PASSWORD=mypassword - POSTGRES_DB=ntuserauth ports: - '5432:5432' volumes: - nt.authservice.db.volume:/var/lib/postgresql/data - ./services/db/scripts:/docker-entrypoint-initdb.d
You can verify that you have the extension installed correctly by running the
\dx command against the database in the container.
List of installed extensions Name | Version | Schema | Description -----------+---------+------------+------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language uuid-ossp | 1.1 | public | generate universally unique identifiers (UUIDs) (2 rows)
That’s all we need to ensure our required extension
uuid-ossp is installed in the postgres container and we are able to initialize the database using the migrations from fluent migrations.