connecting to snowflake

[shacharon]

Issue

connecting to snowflaoke using sequelize-typescript with exsisting tables .

Versions

• sequelize: "6.19.2",
• sequelize-typescript: "2.1.3"
• typescript: "3.7.3"

Issue type

• [ x] bug report
• [ ] feature request

Actual behavior

is is possible to connect to exsisting snowflake DB that alredy has data using sequelize-typescript ? i just manage to create new tables not to connect to the old one if so can you refrence me the the repo or documention ?

Expected behavior

Steps to reproduce

the table test is already exsist is the DB

when i run the code i get original: Error [OperationFailedError]: SQL compilation error: error line 1 at position 7 invalid identifier '"id"'

Related code

export const sequelizeConn = new Sequelize('dev', '', '', {
    dialect: 'snowflake',
    define: {
        freezeTableName: true
      },
    dialectOptions: {
      // put your snowflake account here,
      account: 'xx',  // my-app.us-east-1
      define: {
        freezeTableName: true
      },
      // below option should be optional
      role: 'xx',
            warehouse: 'xx',
      schema: 'PUBLIC'
    },
     username: 'xxx',
    password: 'xx',
    database: 'DEV',
   
  })

 * file app.ts 
  sequelizeConn.addModels([test]);
  * file test
export class test extends Model<test> {
    myid: number;
name:number
   }

test.findAll().then((res: any) => console.log(JSON.stringify(res)));

[chiblackhawks]

I have been using sequelize-typescript with Snowflake for a few months now. I remember this causing me issues at the beginning. I am almost positive the reason why you are running into this error is because the default config for quoteIdentifiers is true. When quoting fields in snowflake - it makes it case sensitive. To get passed this, I flip quoteIdentifiers to equal false:

const sequelize = new Sequelize(SNOWFLAKE_DATABASE, SNOWFLAKE_USERNAME, SNOWFLAKE_PASSWORD, { dialect: 'snowflake', dialectOptions: {...dbConfig}, username: SNOWFLAKE_USERNAME, password: SNOWFLAKE_PASSWORD, database: SNOWFLAKE_DATABASE, quoteIdentifiers: false })

[chiblackhawks]

Also, a field 'id' would be added to your model by default via Model - so if you created this table outside of this project and it does not have a column 'Id' - that could also be the issue.

[WikiRik]

I am almost positive the reason why you are running into this error is because the default config for quoteIdentifiers is true. When quoting fields in snowflake - it makes it case sensitive. To get passed this, I flip quoteIdentifiers to equal false:

const sequelize = new Sequelize(SNOWFLAKE_DATABASE, SNOWFLAKE_USERNAME, SNOWFLAKE_PASSWORD, { dialect: 'snowflake', dialectOptions: {...dbConfig}, username: SNOWFLAKE_USERNAME, password: SNOWFLAKE_PASSWORD, database: SNOWFLAKE_DATABASE, quoteIdentifiers: false })

@chiblackhawks this sounds like it could be an issue with the implementation of snowflake in sequelize itself. Unfortunately we do not have access to a database to run our tests on so we are dependent on others to research these issues. Would you be willing to look into this for us?

[chiblackhawks]

I can give it a shot but for what its worth - I have been using the Snowflake dialect without issue for a couple months now. The only major hiccup is that it doesn't return an auto increment identity column when creating a new record - but this is expected because Snowflake was not designed for this type of use case.

I was surprised to see https://github.com/Snowflake-Labs fork the branch, publish it, and then essentially abandon it. I am trying to push my account team to encourage Snowflake to take more ownership on this and can pass along the issue of not having a database to test against onto them. @Snowflake-Labs