CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Dmitry Vikhorev
Dmitry Vikhorev

Reputation: 13

Defining Sequelize model for existing MySQL database

Please advise on the following issue. I'd like to use Sequelize to work with existing database.

Stack used: 

- Node.js v6.10.3
- MySQL 2nd Gen 5.7 running on Google Cloud
- Sequelize 4.7.5

MySQL contains 1 table - 'test_table' with columns 'id', 'content', 'status' and holds some data.

I want to define testTable model which would "explain" my MySQL 'test_table' for sequelize, so I run:

const connection = new Sequelize("dbName", "user", "pass", {
                                     host: xxx.xxx.xxx.xxx,
                                     dialect: mysql
                                 });
const testTable = connection.define('test_table', {
                                      id: {type: Sequelize.INTEGER, 
                                           allowNull: false, unique: true, 
                                           primaryKey: true, autoIncrement: 
                                           true},
                                      content: {type: Sequelize.STRING},
                                      status: {type: Sequelize.STRING},
                                   });

First surprise I get is that from this moment onwards Node completely ignores existence of 'test_table' and creates its 'analogue' (???).

C:\Users\dmitri\Desktop\app>node query.js

Executing (default): CREATE TABLE IF NOT EXISTS `test_tables` (`id` INTEGER NOT NULL auto_increment UNIQUE , `content` VARCHAR(255), `status` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DAT
ETIME NOT NULL, UNIQUE `test_tables_id_unique` (`id`), PRIMARY KEY (`id`)) ENGINE=InnoDB;


Executing (default): SELECT `id`, `content`, `status`, `createdAt`, `updatedAt` FROM `test_tables` AS `test_table`;
Executing (default): SHOW INDEX FROM `test_tables`

Now MySQL contains two tables: 

 - 'test_table' (singular) 
 - 'test_tables' (plural) identical to "test_table" but empty.

I am trying to SELECTid,contentFROMtest_table` by executing the following Sequelize code: 

testTable
    .findAll({attributes: ['id', 'content']})
    .then(result=>{
           console.log(result)
         })

Instead of returning contents of 'test_table', node keeps ignoring it:

C:\Users\dmitri\Desktop\app>node query.js Executing (default): SELECT id, content FROM test_tables AS test_table;

and returns [].

What may be the reason it voluntarily jumps to query 'test_tables' instead of 'test_table'? Am I completely missing something here?

Many thanks!

Upvotes: 1

Views: 5768

Answers (1)

yBrodsky
yBrodsky

Reputation: 5041

You should have specified the table name in the options. Delete this new table, add the table name option and re-lift the app.

connection.define('test_table', {
  //.... your fields
}, {
  tableName: 'test_table'
});

On a side note, I like to have my models named with camelcase. TestTable. Same goes for connection.define('TestTable'...

EDIT: As your comments I clarify a couple of things:

  • You can/have to specify the table name when defining the model. Look at my example. Specifying the tableName property guarantees the pluralization doesnt screw you over.
  • You shouldnt do sequelize.sync() (unless you know what it does and you want it). Otherwise you might get problems (like sequelize creating another table because it cant find the one it looks for).
  • Defining the models is something you have to do if you want to use sequelize. Otherwise it doesnt make sense and you should use just mysql2. You have to define the models to match the tables in your database.

Upvotes: 2

Related Questions