How Sable Works ​

Overview ​

To set the stage for talking about how Sable works, let's first talk about how things works without Sable. Without Sable, you'd just use the Marten Command Line Tooling support to run commands like marten-patch and marten-apply on your project. Sable does not add any additional functionality to that toolset. In fact, it is built on top of it, and will literally just run those same commands that you'd run manually.

When manually using the command line tooling support, you need to connect to an actual database, which introduces the problems outlined in Why Sable. Sable solves those problems by using a temporary shadow database instead of an actual one. For instance, when running the Sable command to create a new migration, Sable will:

• Dynamically create a Postgres docker container to be used as the shadow database.
• Create a script from the existing migrations.
• Apply the script to build the shadow database.
• Run the marten-patch command on your project. That command is executed in a context where an environment variable is set by Sable. That environment variable is then used to override the connection string for Marten in the project so that it points to the Docker container instead of an actual database. The script generated by Marten is then saved in a sable migrations directory. If no changes were detected, the file fill be empty.
• Delete the Docker container.

Custom Shadow Database ​

By default, Sable uses a Docker container built from a version of the official Postgres image from the DockerHub registry. However, in a corporate environment, maybe you have to use an image from an internal private registry. Or maybe you just want to use a different image from DockerHub. That's possible. Any Sable command that needs to use a shadow database has a -c|--container-options option. That option can be used to point to a JSON file that contains the configuration for how build a custom container for the shadow database. An example looks like this:

{
  "Image": "postgres:15.1",
  "PortBindings": [
    {
      "HostPort": 5470,
      "ContainerPort": 5432
    }
  ],
  "EnvironmentVariables": {
    "PGPORT": "5432",
    "POSTGRES_DB": "postgres",
    "POSTGRES_USER": "postgres",
    "POSTGRES_PASSWORD": "postgres"
    
  },
  "ConnectionString": "Host=localhost;Port=5470;Username=postgres;Password=postgres;Database=postgres"
}

{
  "Image": "postgres:15.1",
  "PortBindings": [
    {
      "HostPort": 5470,
      "ContainerPort": 5432
    }
  ],
  "EnvironmentVariables": {
    "PGPORT": "5432",
    "POSTGRES_DB": "postgres",
    "POSTGRES_USER": "postgres",
    "POSTGRES_PASSWORD": "postgres"
    
  },
  "ConnectionString": "Host=localhost;Port=5470;Username=postgres;Password=postgres;Database=postgres"
}

ConnectionString is the connection string that should be used to connect to the container once it is running.

Migration Tracking and Idempotency ​

To ensure applying a migration is executed as an idempotent operation, Sable maintains a table in the database called <database-schema-name>.__sable_migrations to keep track of already applied migrations. A migration script generated by Sable will look something like this:

---Generated by Sable on 10/14/2023 11:32:48 PM


BEGIN;

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM  orders.__sable_migrations WHERE migration_id = '20231013224735_AddIndexOnCustomerId') THEN

        RAISE NOTICE 'Running migration with Id = 20231013224735_AddIndexOnCustomerId';

        CREATE INDEX mt_doc_order_idx_customer_id ON orders.mt_doc_order USING btree ((CAST(data ->> 'CustomerId' as uuid)));

        
        INSERT INTO orders.__sable_migrations (migration_id, backfilled)
        VALUES ('20231013224735_AddIndexOnCustomerId', '0');
    END IF;
END $$;

COMMIT;


BEGIN;

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM  orders.__sable_migrations WHERE migration_id = '20231014233240_AddIndexOnDatePurchased') THEN

        RAISE NOTICE 'Running migration with Id = 20231014233240_AddIndexOnDatePurchased';

        CREATE INDEX mt_doc_order_idx_date_purchased_utc ON orders.mt_doc_order USING btree ((orders.mt_immutable_timestamp(data ->> 'DatePurchasedUtc')));

        
        INSERT INTO orders.__sable_migrations (migration_id, backfilled)
        VALUES ('20231014233240_AddIndexOnDatePurchased', '0');
    END IF;
END $$;

COMMIT;

---Generated by Sable on 10/14/2023 11:32:48 PM


BEGIN;

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM  orders.__sable_migrations WHERE migration_id = '20231013224735_AddIndexOnCustomerId') THEN

        RAISE NOTICE 'Running migration with Id = 20231013224735_AddIndexOnCustomerId';

        CREATE INDEX mt_doc_order_idx_customer_id ON orders.mt_doc_order USING btree ((CAST(data ->> 'CustomerId' as uuid)));

        
        INSERT INTO orders.__sable_migrations (migration_id, backfilled)
        VALUES ('20231013224735_AddIndexOnCustomerId', '0');
    END IF;
END $$;

COMMIT;


BEGIN;

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM  orders.__sable_migrations WHERE migration_id = '20231014233240_AddIndexOnDatePurchased') THEN

        RAISE NOTICE 'Running migration with Id = 20231014233240_AddIndexOnDatePurchased';

        CREATE INDEX mt_doc_order_idx_date_purchased_utc ON orders.mt_doc_order USING btree ((orders.mt_immutable_timestamp(data ->> 'DatePurchasedUtc')));

        
        INSERT INTO orders.__sable_migrations (migration_id, backfilled)
        VALUES ('20231014233240_AddIndexOnDatePurchased', '0');
    END IF;
END $$;

COMMIT;

If a record already exists in the table for a migration, It won't be applied. Otherwise, applying the migration as well as recording that it has been applied in the migration table will execute in the same database transaction.