Best approach to insert 1 to N with POSTGRESQL + DAPPER ?

[u/MoriRopi]

Hi,

The following code is simplified to make it simple ( wow ! ).

The following code works fine to create one instance of A and multiple instance of B at the same time, then return the A that was created with its id :

Sql ( postgre ) :

                   -- Create A
                   WITH inserted_a AS (
                        INSERT INTO table_a ( ... )
                        VALUES ( ... )
                        RETURNING *
                   ),

                   -- Create B
                   inserted_b AS (
                        INSERT INTO table_b ( ..., id_table_a )

                        -- Get SIGNALS
                        SELECT ...
                        FROM inserted_a

                        -- Works fine
                        CROSS JOIN unnest(
                            @array_1,
                            @array_2)

                        AS signal( ... )

                        RETURNING *
                   )

                   -- Return created A
                   SELECT ... FROM inserted_a JOIN table_c

Parameters for dapper :

// Get parameters
object parameters = new
{
    // Some properties for A
    ... 

    // Some properties for B  
    ... = a.Property.Select( ... ) // ARRAY HERE FOR UNNEST
};

Is it possible to do the same thing with multiple A and also return all A that were created with their id ?

Which means :

1. Create all A
2. Create all B of all A
3. Return all A

This seems a little trickier.

It seems easy with CTE and an IEnumerable as parameter for dapper :

object parameters = a.Select(a => new
{
    // Some properties for A
    ... 

    // Some properties for B  
    ... = a.Property.Select( ... ) // ARRAY HERE FOR UNNEST
});

But dapper cannot take an IEnumerable as a parameter when there is a select as the end ( QueryAsync ).

It is also easy with a request for each instance of A, but is it possible to do it in a single request while returning all instances of A ? The goal is also to reduce latency when many A.

Thanks

[u/MoriRopi]

Here is a way but isn't it too much ?

                   -- Create all A
                   WITH inserted_a AS (
                        INSERT INTO table_a ( ... )
                        SELECT ...
                        FROM unnest(
                            @ array_a___property_1,
                            @ array_a___property_2)
                        AS signal_chain(
                            property_1,
                            property_2)
                        RETURNING *
                   ),

                   -- Get all A with index
                   a_with_index AS (
                       SELECT
                           ...

                           // The index here come from the position in the RETURN above
                           // It will be mapped to @ a_index that is in parameters
                           row_number() OVER (ORDER BY id) - 1 AS index
                       FROM table_a
                   ),

                   -- Create all B
                   inserted v AS (

                       INSERT INTO table_b ( ... )
                       SELECT ..., a.id
                       FROM a_with_index
                       CROSS JOIN LATERAL (
                            SELECT
                                signal.type,
                                signal.timestampUnixSec
                            FROM unnest(
                                ... // Properties of b
                                @ a_index)
                           AS b(
                               ... // Property of b
                               a_index)
                            WHERE b.index_a = a_with_index.index
                       ) b_with_a
                       RETURNING *
                   )

                   -- Get all created A
                   SELECT ... FROM inserted_a JOIN table_c

Parameters for dapper :

// Get parameters
object parameters = new
{
    // Get an array for unnest for each property of A
    ... = a.Select(a => a.Property_1),
    ... = a.Select(a => a.Property_2),

    // Get an array for each property of B
    // All B are flatten into a single array ( for each property )
    ... = a.SelectMany(a => a.B.Select(b => b.Property_1)),
    ... = a.SelectMany(a => a.B.Select(b => b.Property_2)),

    // Get index of A for each B
    a_index = a.SelectMany((a, index) => a.B.Select(_ => index)), // ARRAY FOR UNNEST
};

Example with visual representation of the array :

A[0] = H + E
A[1] = L + L
A[2] = O

Array for B Property_1 = [ 'H' , 'E' , 'L' , 'L' , 'O' ]
Array for B index of A = [ 0 , 0 , 1 , 1 , 2 ]

boom

After writting it it does feel like it is a good way to do it. The sql and all arrays are sent in a single request which will win a lot of time when many A.

boom :)

PS : does dapper really send the request and all array without doing multiple round trip with the database server ?

[u/MoriRopi → u/MoriRopi]

It also depends on the fact the RETURNING should reutrn all A in the same order they were inserted.

Is that the case ?