PostgreSQL and Database Provisioning

PostgreSQL and database provisioning

The supplied KitchenPC.DB adapter uses NHibernate. PostgreSQL is the maintained and demonstrated database path.

The easiest setup is the Database Initializer sample, which loads a static snapshot, recreates the KitchenPC schema, and imports the snapshot.

Start PostgreSQL

docker run --name kitchenpc-postgres \
  --detach \
  --env POSTGRES_PASSWORD=postgres \
  --publish 5432:5432 \
  postgres:17

docker exec -it kitchenpc-postgres \
  psql --username postgres --command 'CREATE DATABASE "KPCSample";'

The initializer creates tables inside an existing database; it does not issue CREATE DATABASE.

Run the initializer

git clone https://github.com/KitchenPC/Samples.git KitchenPC-Samples
cd KitchenPC-Samples
export KITCHENPC_CONNECTION_STRING='Host=localhost;Port=5432;Database=KPCSample;Username=postgres;Password=postgres'
dotnet run --project DatabaseInitializer/DatabaseInitializer.csproj

Type PROVISION at the destructive-operation prompt. Use -- --yes only in an intentionally disposable environment.

Verify data:

docker exec -it kitchenpc-postgres \
  psql --username postgres --dbname KPCSample \
  --command 'SELECT COUNT(*) FROM shoppingingredients; SELECT COUNT(*) FROM recipes;'

How provisioning works

var source = StaticContext.Configure
   .DataDirectory(dataDirectory)
   .Identity(() => AuthIdentity.Anonymous)
   .Create();

var destination = DBContext.Configure
   .Adapter(DatabaseAdapter.Configure.DatabaseConfiguration(
      PostgreSQLConfiguration.PostgreSQL82.ConnectionString(connectionString)))
   .Identity(() => AuthIdentity.Anonymous)
   .Create();

source.Initialize();
destination.InitializeStore();
destination.Import(source);

Contexts implement provisioning interfaces:

• IProvisionSource.Export() returns a DataStore snapshot;
• IProvisionTarget.InitializeStore() creates its storage structures;
• IProvisionTarget.Import(source) copies snapshot records into the target.

The import includes ingredients, forms, metadata, NLP lookup data, recipes and ingredients, menus, favorites, queues, ratings, shopping lists, and list items when those collections exist in the source.

Destructive warning

DBContext.InitializeStore() recreates the KitchenPC schema and deletes existing KitchenPC data. It is not a migration system. Never call it automatically during normal production startup. Resolve the database name and environment, require explicit confirmation, take backups where appropriate, and use it only for a new or intentionally replaceable database.

Schema notes

The ingredient catalog table is shoppingingredients, a legacy name retained for compatibility with the KitchenPC website. Public types remain Ingredient/Ingredients. The similarly named shoppingingredientsfornlp table contains default weight, volume, and unit form pairings used by NLP.

The adapter can accept NHibernate conventions through DatabaseAdapter.Configure.AddConvention(...). Custom database enum types must exist before schema creation if a convention maps CLR enums to them.

Production checklist

• Create a dedicated role with only required privileges.
• Require TLS when the database is remote.
• Store the connection string in a secret provider.
• Back up before schema or bulk import operations.
• Apply your own migration discipline after initial provisioning.
• Test the chosen capability profile with production data.
• Replace NHSearch if search scale or language behavior needs a specialized index.