Extensions

The Extension class ensures that a PostgreSQL extension is installed in the database. Extensions are common for adding capabilities like UUID generation, full-text search languages, or spatial types.

Registering an Extension

var uuidExt = new Extension("uuid-ossp");
var plv8Ext = new Extension("plv8");
var postgisExt = new Extension("postgis");

^{snippet source | anchor}

Extension names are automatically normalized to lowercase and trimmed.

Using with a Database

Include extensions in your PostgresqlDatabase subclass so they are created before tables and functions that depend on them.

public class AppDatabaseWithExtensions : PostgresqlDatabase
{
    public AppDatabaseWithExtensions(NpgsqlDataSource dataSource)
        : base(new DefaultMigrationLogger(), AutoCreate.CreateOrUpdate,
               new PostgresqlMigrator(), "app", dataSource)
    {
    }

    public override IFeatureSchema[] BuildFeatureSchemas()
    {
        return [new AppFeatureSchema(this)];
    }

    private class AppFeatureSchema : FeatureSchemaBase
    {
        public AppFeatureSchema(AppDatabaseWithExtensions database)
            : base("App", database.Migrator)
        {
        }

        public override Type StorageType => typeof(AppDatabaseWithExtensions);

        protected override IEnumerable<ISchemaObject> schemaObjects()
        {
            // Extensions should be listed first
            yield return new Extension("uuid-ossp");
            yield return new Extension("postgis");

            // Then tables, functions, etc.
            var table = new Weasel.Postgresql.Tables.Table("users");
            table.AddColumn<int>("id").AsPrimaryKey();
            yield return table;
        }
    }
}

^{snippet source | anchor}

Delta Detection

Weasel queries pg_extension to check whether the extension is already installed. If not, it returns a Create delta.

var ext = new Extension("hstore");

// Used internally during migration, but you can invoke manually:
var migrator = new PostgresqlMigrator();
var writer = new StringWriter();
ext.WriteCreateStatement(migrator, writer);
// CREATE EXTENSION IF NOT EXISTS hstore;

^{snippet source | anchor}

Generating DDL

var ext = new Extension("uuid-ossp");
var migrator = new PostgresqlMigrator();
var writer = new StringWriter();

ext.WriteCreateStatement(migrator, writer);
// CREATE EXTENSION IF NOT EXISTS uuid-ossp;

ext.WriteDropStatement(migrator, writer);
// DROP EXTENSION IF EXISTS uuid-ossp CASCADE;

^{snippet source | anchor}

WARNING

Creating extensions requires the CREATE privilege on the database. In managed hosting environments (e.g., AWS RDS, Azure), some extensions may need to be enabled through the hosting provider's console.