Loading…

BitBadger.Npgsql.Documents
Basic Usage

The standard document manipulation functions handle counting documents, checking for existence of documents, and create/read/update/delete (CRUD) operations on documents. For C#, these methods are part of the Document static class; for F#, the functions and sub-modules will already be in scope.

General Structure

Every function or method takes the table name as its first parameter. The second parameter is the criteria (more on the different types below). The third parameter, if present, is a document or partial document. Functions and methods are organized into groups, which show as static classes in C# and modules in F#, and the functions and methods themselves describe how the action is to take place. This makes the general form of a command:

What.How(from-table, which-ones, document) (C#)
What.how from-table which-ones document (F#)

Throughout the remainder of this page, references to methods will use Pascal case (C# style) capitalization; for F#, modules use Pascal case and functions use camel case.

IDs, Containment, and JSON Path queries

These are the “how” of the commands, and operations fall under one of the following:

JSON Containment queries can use anonymous types (C# new { Country = "USA", Rating = 4 }, F# {| Country = "USA"; Rating = 4 |}); both examples would issue the query above. JSON Path query parameters are always strings.

Saving Documents

The library provides three different ways to save data. The first equates to a SQL INSERT statement, and adds a single document to the repository.

// C#
    var room = new Room(/* ... */);
    // Parameters are table name and document
    await Document.Insert("room", room);
// F#
    let room = { Room.empty with (* ... *) }
    do! insert "room" room

The second is Save, and inserts the data it if does not exist, and replaces the document if it does exist (what some call an “upsert”). It utilizes PostgreSQL's ON CONFLICT syntax to ensure an atomic statement. Its parameters are the same as those for Insert.

The third equates to a SQL UPDATE statement. Updates can operate on a full or partial document, and apply updates based on ID, JSON containment, or JSON Path. For a few examples, let's begin with a query that may back the “edit hotel” page. This page lets the user update nearly all the details for the hotel, so updating the entire document would be appropriate.

// C#
    var hotel = await Document.Find.ById<Hotel>("hotel", hotelId);
    if (!(hotel is null))
    {
        // update hotel properties from the posted form
        await Document.Update.Full("hotel", hotel.Id, hotel);
    }
// F#
    match! Find.byId<Hotel> "hotel" hotelId with
    | Some hotel ->
        do! Update.full "hotel" hotel.Id updated
                { hotel with (* properties from posted form *) }
    | None -> ()

For the next example, suppose we are upgrading our hotel, and need to take rooms 221-240 out of service*. We can utilize a partial document update via JSON Path to accomplish this.

// C#
    await Document.Update.PartialByJsonPath("room",
        "$ ? (@.HotelId == \"abc\" && (@.RoomNumber >= 221 && @.RoomNumber <= 240)",
        new { InService = false });
// F#
    do! Update.partialByJsonPath "room"
            "$ ? (@.HotelId == \"abc\" && (@.RoomNumber >= 221 && @.RoomNumber <= 240)"
            {| InService = false |};

* - we are ignoring the current reservations, end date, etc. This is very naïve example!

There is an Update.FullFunc variant that takes an ID extraction function run against the document instead of its ID. This is detailed in the Advanced Usage section.

Finding Documents

Functions to find documents start with Find.. There are variants to find all documents in a table; by ID (returning null (C#) or None (F#) if no matching document is found); by JSON containment; or by JSON Path. The hotel update example above utilizes an ID lookup; the descriptions of JSON containment and JSON Path show examples of the criteria used to retrieve using those techniques. Of special note here is that Find methods and functions are generic; specifying the expected type is crucial.

Deleting Documents

Functions to delete documents start with Delete.. Document deletion is supported by ID, JSON containment, or JSON Path. The pattern is the same as for finding or partially updating. (There is no library method provided to delete all documents, though deleting by JSON Path $ ? (1 == 1) would accomplish this.)

Counting Documents

Functions to count documents start with Count.. Documents may be counted by JSON containment, JSON Path, or by table. Counting by ID is an existence check!

Document Existence

Functions to check for existence start with Exists.. Documents may be checked for existence by ID, JSON containment, or JSON Path.

What / How Cross-Reference

The table below shows which commands are available for each access method.

Operation All ById ByContains ByJsonPath FirstByContains FirstByJsonPath
Count X X X
Exists X X X
Find X X X X X X
Update.Partial X X X
Delete X X X

Insert, Save, and Update.Full operate on single documents.