BitBadger.Npgsql.Documents
Setting Up

Configuring a Data Source

ADO.NET in .NET 7 introduced the DbDataSource type, designed to be a singleton which can execute commands, hand out connections, manage a connection pool, etc. Npgsql introduced NpgsqlDataSource as its implementation of this type, and the libraries use this as the basis for its connections. Npgsql also provides NpgsqlDataSourceBuilder type; its static Create method creates a data source from a connection string, and its other methods allow for configuring several other options before creating the data source.

NOTE: Although NpgsqlDataSource implements IDisposable, it should not be created using using (C#) or use (F#), as this will cause it to be disposed when the initialization logic goes out of scope.

Once the data source has been created, telling the library about it is a one-liner:

// C#
using BitBadger.Npgsql.Documents;
using Npgsql;

// ...
    var dataSource = NpgsqlDataSourceBuilder.Create("[conn-str]").Build();
    Configuration.UseDataSource(dataSource);
// ...

// F#
open BitBadger.Npgsql.FSharp.Documents
open Npgsql

// ...
    let dataSource = NpgsqlDataSourceBuilder.Create("[conn-str]").Build ()
    Configuration.useDataSource dataSource
// ...

You may choose to register the data source as a singleton in your dependency injection container of choice, but you can also access this instance via Configuration.DataSource() (C#) or Configuration.dataSource () (F#).

Namespaces

• C# using BitBadger.Npgsql.Documents; will make the various static classes and enumerations visible.
• F# open BitBadger.Npgsql.FSharp.Documents will make the modules and DUs visible.

The examples on this and subsequent pages assume these statements are used. Additionally, F# examples that use let!, do!, etc. are assumed to be within the context of a task or backgroundTask CE.

Ensuring Tables and Indexes Exist

As a standard relational database, PostgreSQL stores data in tables, and utilizes indexes to retrieve that data. Each application will need to determine the tables and indexes it expects.

As a full relational database, we can also utilize schemas to organize/isolate our data. Everywhere this library expects a table name, a qualified table name (schema.table) may be provided. Creating these schemas is outside the scope of functionality provided by this library; CREATE SCHEMA IF NOT EXISTS is your friend here.

An example will probably help the concepts make sense. For the purposes of our example, let's consider a hotel chain; they have several hotels, and each hotel has several rooms. While each hotel could have its rooms as part of a Hotel document, there would likely be a lot of contention when concurrent updates for rooms, so we will put rooms in their own table. The hotel will store attributes like name, address, etc.; while each room will have the hotel's ID, along with things like room number, floor, and a list of date ranges where the room is not available. (This could be for customer reservation, maintenance, etc.)

PostgreSQL provides two options for indexing JSONB fields; one indexes all fields in a way where all JSON operators work well against it, while the other excludes a few operators in exchange for faster performance. This library refers to the regular GIN index as Full, while the jsonb_path_ops variant is Optimized. For our examples, we have decided that the hotels need a full index, while the rooms need an optimized index.

This library's table and index creation both use IF NOT EXISTS, so they are safe to use in application startup. If the table/index exists, nothing happens; however, if they do not, they are created. To implement our example requirements, we would use the following code:

// C#
    await Definition.EnsureTable("hotel");
    await Definition.EnsureIndex("hotel", DocumentIndex.Full);
    await Definition.EnsureTable("room");
    await Definition.EnsureIndex("room", DocumentIndex.Optimized);

// F#
    do! Definition.ensureTable "hotel"
    do! Definition.ensureIndex "hotel" Full
    do! Definition.ensureTable "room"
    do! Definition.ensureIndex "room" Optimized

Now that we have tables, let's use them!

« BitBadger.Npgsql.Documents Home View GitHub Repo