SNOWFLAKE_COLUMNS_CASE_SENSITIVE may not be compatible with GraphQL

[c-tanner]

When using Lighthouse, field name cases do not seem to be uppercased as the config value suggests it should be.

Snowflake schema has table ACCOUNT with ID,NAME columns.

Laravel model:

class Account extends Model
{

    protected $connection = 'snowflake_pdo';
    protected $table = 'account';

}

GraphQL Schema:

type Query {
  Accounts: [Account]! @all
}

type Account {
  id: Int!
  name: String
}

GraphQL query:

query {
    Accounts {
        id
        name
    }
}

Error:

Cannot return null for non-nullable field "Account.id"

When fields in GraphQL schema are uppercased, the query executes successfully. Also note that $table is defined in lowercase within the model, and successfully finds the uppercased Snowflake table.

Additionally, adding $maps and getter methods to the model resolve the issue. However, it is rather cumbersome to map every single model attribute just for case-insensitivity:

...
    protected $maps = [
        'ID' => 'id',
    ];

    public function getIdAttribute()
    {
        return $this->attributes['ID'];
    }
...

System specs:

laravel/framework                                  11.27.2
nuwave/lighthouse                                 6.45.0 
yoramdelangen/laravel-pdo-odbc        1.2.5

[c-tanner]

After more discovery, this seems to be due to the way that Lighthouse structures queries. Inspecting the wrapColumn function shows that even when specifying columns in the GraphQL query, the query actually calls *, bypassing the uppercasing.

Would still be great to try and find a clean solution for this.

[yoramdelangen]

There is a connection setting in Snowflake ODBC that allows you to control how Snowflake should handle the double quoted wrapped column names and its casing sensitivity.

By default we have set this setting to false so when double quoted we control the casing. You can disable it, by enabling SNOWFLAKE_DISABLE_FORCE_QUOTED_IDENTIFIER=true. You can find it here.

Maybe this resolves the problem for you?

[c-tanner]

Thanks for the reply, unfortunately this doesn't solve the issue in this situation.

While the Laravel model attributes can now be lowercased (with caveats, below), the GraphQL schema definitions must still be uppercase in order for the query to execute successfully.

What's interesting - model relationships do not return expected results when used through Lighthouse. When attributes are lowercased ,a query that calls relationships returns an empty array for the relationship. Single model queries work as expected.

To recap:

env:

SNOWFLAKE_COLUMNS_CASE_SENSITIVE=false
SNOWFLAKE_DISABLE_FORCE_QUOTED_IDENTIFIER=true

"Working"
type Account {
  ID: Int!
  NAME: String
}

"Not working"
type Account {
  id: Int!
  name: String
}

# Working for single model queries
class Account extends Model
{
    protected $connection = 'snowflake_pdo';
    protected $primaryKey = 'id';
    protected $table = 'account';
}

# Not working for queries calling relationships
class Account extends Model
{
    protected $connection = 'snowflake_pdo';
    protected $primaryKey = 'id';
    protected $table = 'account';

    public function children(): HasMany
    {
        return $this->hasMany(Children::class, 'parent_id');
    }
}

From the above example, this query would return an empty children array, with Account data being returned as expected:

query {
    Accounts {
        ID
        NAME
        children {
            ID
            NAME
        }
    }
}

Works as expected when model attributes are all caps.

[yoramdelangen]

@c-tanner I though I had wrote a response alright.. but somehow it got lost :D

I know there is a server-sided option for Snowflake as well to set the casing:
https://docs.snowflake.com/en/sql-reference/parameters#quoted-identifiers-ignore-case

This option can also be set on session level, making the database connection string option obsolete?

Another note: How does the transformation of the GraphQL query look like in SQL statement? Note that None quoted field names always be set to uppercased names. Check their "Identifier requirements" documentation.

So in short:

query {
    Accounts {
        id
        name
    }
}

Should transform into something like:

SELECT "id", "name" FROM Accounts
-- note that Account will be UPPERCASED as well

But instead it is currently transformed into:

SELECT id, name FROM Accounts
-- interpret by snowflake as:
SELECT ID, NAME FROM ACCOUNTS