SQLTypeProvider SaveContextSchema not saving the fields (columns)

[mcspud]

Description

When calling SaveContextSchema() and specifying the ContextSchemaPath in the SqlDataProvider, the table names are serialised but not the table definitions themselves. This means that the program can't compile as the types cant be generated and reflected upon.

Repro steps

Using Npgsql 4.1.1:

type Sql =
    SqlDataProvider<
        ContextSchemaPath=ContextSchemaPath,
        ConnectionString=ConnectionString,
        DatabaseVendor=DatabaseVendor,
        CaseSensitivityChange=CaseSensitivityChange,
        ResolutionPath=ResolutionPath>

let getContext(): DbContext =
    Sql.GetDataContext()

let saveContext(): unit =
    getContext()
    |> fun ctx -> ctx.SaveContextSchema()


// some database access code

saveContext() |> ignore

Expected behavior

The saved context schema file has all reflected table properties on it.

Actual behavior

A file is generated with function and view definitions, but only a top level "name" definition for tables.

Sample output file here

Known workarounds

None

Related information

• Postgresql 11.3
• OSX 10.13.6 (High Sierra)
• Netcore 3.0.100
• Npgsql

[Thorium]

The method is lazy. So it only saves the fields needed, not all fields, because this is intended to work with huge databases. If your code uses the fields, they will be saved. So this is more of a workaround for offline builds (CI, "I'm in an airplane", etc), not for offline development. For off-line development you should have a database installed locally.

[mcspud]

Thanks for the quick reply.

Does the provider "walk" the code from the entry point somehow, or do I need to do something specific at design time to get it to do this? For example, I have this function:

let getUserByUsername: string -> DbContext -> User option = fun username ctx ->
    query {
        for user in ctx.Public.User do
            where (user.Username = username)
            select user
    }
    |> Seq.tryHead

This function is used in the [<EntryPoint>] annotated function itself, so its definitely called. Alternatively, is using the query computational expression an antipattern for this?

[Thorium]

No, it's not an anti-pattern.

You should write the ctx.SaveContextSchema() after the getUserByUsername function.

Edit: F# is read from top-to-bottom so the order counts.

[mcspud]

As a minimum example see the following

type Sql =
    SqlDataProvider<
        ContextSchemaPath=ContextSchemaPath,
        ConnectionString=ConnectionString,
        DatabaseVendor=DatabaseVendor,
        CaseSensitivityChange=CaseSensitivityChange,
        ResolutionPath=ResolutionPath>

type DbContext = Sql.dataContext
type User = DbContext.``public.userEntity``

let getContext(): DbContext =
    Sql.GetDataContext()

let getUserByUsername: string -> DbContext -> User option = fun username ctx ->
    query {
        for user in ctx.Public.User do
            where (user.Username = username)
            select user
    }
    |> Seq.tryHead

getContext().SaveContextSchema()

I deleted the schema file, and had it create a new one here.

At the top of that file I'm seeing:

    "Columns@": [],
    "Individuals@": [],
    "IsOffline@": true,
    "Packages@": [],
    "PrimaryKeys@": [],
    "Relationships@": [],
    "SprocsParams@": [],

and at the bottom I'm seeing

"Tables@": [
        ...
        {
            "Key": "public.user",
            "Value": {
                "Name@": "user",
                "Schema@": "public",
                "Type@": "base table"
            }
    ]

If I grep the file for any of the field names for any of the tables (id, identifier, date_created...) it all turns up empty. I'm not sure if there is a way to enable verbose logging for the design-time type builder (sorry I'm new to the MS ecosystem, I'm not sure what the right terminology is) or something else I can have a look at.

Thanks again for the fast reply!

[Thorium]

Is there any relevant tool-tips in the method or does the call return an object with any interesting methods?

Clearly it has not managed to populate the data, it should look something like

{"Columns@":[
    {"Key":"Public.User","Value":{
        "serializedData":[
            {"key":"Username",
             "value":{
                "HasDefault@":false,
                "IsAutonumber@":false,
                "IsNullable@":false,
                "IsPrimaryKey@":false,
                "Name@":"Username",
                "TypeInfo@":{"value":"decimal"},
                "TypeMapping@":{
                    "ClrType@":"System.Decimal",
                    "DbType@":7,
                    "ProviderTypeName@":{"value":"decimal"},
                    "ProviderType@":{"value":5}
                }
             }
            }, ...

Does everything work when you don't have the schemafile in use, does it find the columns then? If the columns are found in GetColumns they are added to the schemacache here. On the other hand, if it would be a .NET Core serialization problem, I would expect the whole file not being generated while it is saved

[mcspud]

If I don't specify a schema file, the database entities are correctly reflected in VS Code (Version: 1.39.1) in either the tooltip or the ionide info panel. See below:

If I don't specify a schema file I can successfully run the project and query the database successfully.

If you can give me some advice on how to check the logs of the background type generation process I'd love to be able to help with this.

Cheers!

[joshuapassos]

@Thorium Is part of project release support to offline development without database running locally ? maybe creating schema snapshot in file. Sorry to revive issue

[Thorium]

SaveContextSchema saves the schema (for any database) as JSON file to enable offline builds. That will save only used fields, not the full database. Should be working already.

Besides that we have now the dacpac support for SQL-server, which is another possibility to solve the same thing.