sequelize/sequelize

Feature-rich ORM for modern Node.js and TypeScript, it supports PostgreSQL (with JSON and JSONB support), MySQL, MariaDB, SQLite, MS SQL Server, Snowflake, Oracle DB (v6), DB2 and DB2 for IBM i.

Sequelize

Sequelize is an easy-to-use and promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, DB2, Microsoft SQL Server, Snowflake, Oracle DB and Db2 for IBM i. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Would you like to contribute? Read our contribution guidelines to know more. There are many ways to help! 😃

💻 Getting Started

Ready to start using Sequelize? Head to sequelize.org to begin!

• Our Getting Started guide for Sequelize 6 (stable)
• Our Getting Started guide for Sequelize 7 (alpha)

💸 Supporting the project

Do you like Sequelize and would like to give back to the engineering team behind it?

We have recently created an OpenCollective based money pool which is shared amongst all core maintainers based on their contributions. Every support is wholeheartedly welcome. ❤️

📝 Major version changelog

Please find upgrade information to major versions here:

• Upgrade from v5 to v6
• Upgrade from v6 to v7

📖 Resources

• Documentation
• Databases Compatibility Table
• Changelog
• Discussions
• Slack
• Stack Overflow

🔧 Tools

• CLI
• For GraphQL
• For CockroachDB
• Awesome Sequelize
• For YugabyteDB

💬 Translations

• English (Official)
• 中文文档 (Unofficial)

⚠️ Responsible disclosure

If you have security issues to report, please refer to our Responsible Disclosure Policy for more details.

sequelize's Issues

The library has no SQL injection protection

Hi, I have been trying this library and found it fun to play with. For fun I tried to make an SQL injection into my database with "...,null); DROP TABLE Cards;--" and the injection worked. There does not seem to be any protection against SQL injection at all.

How hard would it be to create an equalent of prepared statements or PHP's mysql_real_escape_string (just using something like .replace(/(['"])/g, '$1')) does not feel safe enough), .

Bad Handshake...

I get this error every once and a while and I am not sure what it means:

8 Apr 15:35:24 - 'Executing the query: SELECT * FROM cd_traffic_source WHERE campaign_id = 235'
8 Apr 15:35:24 - { field_count: 255,
errno: 1043,
message: 'Bad handshake' }

Any ideas?

Complex Associations

Right now, nice stuff can be done with self associations:

User.hasMany(User, { as: "Friends" });
User.hasOne(User, { as: "Father" });
User.hasMany(User, { as: "Teachers" });

But attempting to do something similar involving other models doesn't work:

Project.belongsTo(User, { as: "Founder" });
Project.hasMany(User, { as: "Owners" });
Project.hasMany(User, { as: "Workers" });

This is how I thought this functionality could work like:

// Associations
Project.belongsTo(User, { as: "Founder" });
Project.hasMany(User, { as: "Owner" }); // singular
Project.hasMany(User, { as: "Worker" });

// creates tables ProjectOwners & ProjectWorkers, and adds "FounderId" to Project table.

// methods
someproject.getFounder();
someproject.getOwners();
someproject.getWorkers();

// reverse-methods
stephen.getProjectsAsFounder();
stephen.getProjectsAsOwner();
stephen.getProjectsAsWorker();

Self associations don't have these reverse methods atm, so with this, you could now retrieve a Father's children by:

Person.hasOne(Person, { as: "Father" });

stephen.setFather(david);
stephen.getFather(); // retrieves david
david.getPersonsAsFather(); // retrieves stephen

You could prettify getPersonsAsFather() to getChildren() by creating a model method.

I haven't dug into the source code too deeply, so I don't know how difficult it would be to implement. Before delving in and trying to write this, I thought it would be a good idea to ask if anyone already has something like this planned, to get some feedback on possible API and general thoughts.

choose a license

Create foreign key constraints when creating tables using .sync()

Foreign key constraints are not created when creating tables using .sync(). I can look in to doing this if it is something there is interest in.

Add possibility to pass instance methods and attributes

Smth like

var Picture = sequelize.define('Picture', {
title: Sequelize.STRING
}, {
resize: function(width, height) {
//resize logic
}
})

underscore: true doesn't work

I'm adding underscore: true option to my model like,

context.define(
'project',
{
title: Sequelize.STRING,
description: Sequelize.TEXT
},
{ underscore: true }
);

but timestamp fields still syncs with db as createdAt instead of created_at.

by the way I suggest underscore or freezetablename like options would better be given from top level like,

new Sequelize("db", "root", "pass", { underscore: true });

which makes more sense to me.

Custom IDs

Yesterday I ran some test to see if sequelize could be useful and I ran into weird issue, I was able to retrieve some record after writing some schema but unable to save it because sequelize wasn't able to find the record with the specified ID.

The thing is my database was already in place so I was just trying to do benchmarks and all so I didn't create any new table. Also most of the tables in this database are using non integer IDs, in fact most of the case those IDs are randomly generated in PHP right now and are basically sha1 hashes so that's why it wasn't working.

So, I was wondering if it would be possible to have custom IDs field and make them primary. Also, it would be a great idea if we could declare a field as "unique" but that's another story...

A few issues with findAll, associated data and a weird order bug

Hi,

I haven't had time to try this with other find methods, but I've been having to work around findAll. Here is the final example I currently have working:

https://gist.github.com/5d71389619a71e897530

As you can see, around the rendering function I'm having to do a lot of additional work to get tags associated with each question. With this I have other models (such as the User who created it, and comments attached to it) to also do and I can see this code getting rather long.

I have two proposals to improve this:

1. Include an option to request all associated data with a model instance. See SUGGESTION 1 in the gist.

I did try adding a getAssociated to the instance method and getting this additional data, but again it was difficult to track when this was done to ensure it would be passed to the frontend and a lot of time my page was being rendered before this request was finished.

2. Allow .chainQueries to be passed an instance of a findAll array, and provide a return object within the callback, so maybe something like SUGGESTION 2 in the gist

One other thing I have noticed is I am not getting consistent returns with my data. In my gist, I can either get [Question: Foo, Question: Bar] OR [Question: Bar, Question: Foo]. This is noted as the weird output in the gist.

Take NPM package off TODO! :)

Record are not Saved in DB

I trying to insert a record in the database using the following script
and i notice that although the script doesn't result in error it neither save the record in database. plus I have to set the id attributes manually using "user.id = 1" which is quite weird
cause I presume it is just what active_record is for rails. and we never do the above in rails
(Tell me I'm wrong)

Here my script
var Sequelize = require("sequelize").Sequelize
var sequelize = new Sequelize('users', 'root', 'root123')

var User = sequelize.define('users', {
id: Sequelize.INTEGER,
name: Sequelize.STRING,
created_at: Sequelize.DATE,
updated_at: Sequelize.DATE
})

var user = new User({
id: "1",
name: 'Viren from Node.js',
created_at: new Date,
updated_at : new Date
})

Bundled client typo

http://github.com/sdepold/sequelize/blob/master/lib/nodejs-mysql-native/client.js#L18

I assume this value should read "localhost"

fix for self associations

Self-reference FK's never created

When creating a model that has a parent/child relationshop, the FK is never created.

An example is my Role model:

exports.getRoleModel = function(Sequelize, sequelize) {
sequelize.define('Role', {
name : Sequelize.STRING,
priority : Sequelize.INTEGER
});

Role.hasMany('children', Role);
Role.belongsTo('parent', Role);

A FK for the parentId is never created.

Bundled client connection parameters

http://github.com/sdepold/sequelize/blob/master/lib/nodejs-mysql-native/client.js#L20

These should rather use the variables above...

Name many-to-many tables after relation name rather than Model

see: http://github.com/sdepold/sequelize/issues#issue/12

I have a User model and want users to be related in a many-to-many relationship;

var User = sequelize.define('User', {
  'username': Sequelize.STRING,
  'email': Sequelize.STRING,
  'password': Sequelize.STRING,
  'deleted': Sequelize.BOOLEAN,
  'spammer': Sequelize.BOOLEAN
});

User.hasMany('friends', User);

But instead of UsersFriends the table is called UsersUsers which is confusing...It's not a huge issue, and I'm not sure how much work it is to change, but it would be nice if both these (similar) issues could be implemented. :)

Complete CRUD tracking by adding deletedAt field.

If createdAt, updatedAt fields are added automatically, then a deletedAt field should be added to complete CRUD capability. When using find, simply only return rows that have NULL in deletedAt field. This could help with simple compliance and data retention requirements.

Additionally, a CRUD option should be added to the define options to only place these fields in the table if it is asked. It would also be nice to be able to specify the field names that contain these values. This would help in integration with existing models and models that are utilized by other libraries other than this one.

updateattributes to set assocs

let update attributes set associations

Sequelize.BOOLEAN currently forces user to have to input a value on insert/update

After requiring I simply have to add...

Sequelize.BOOLEAN = "TINYINT(1) NULL";

...or I will get an error if I leave out any boolean field values on insert/update (save).

Name foreign keys after relation name rather than model

When creating belongsTo relationships, the key name should be named after the relationship rather than the model.

For example:

Asset.belongsTo('owner', Role);
Role.hasMany('owner_assets', Asset);

Asset.belongsTo('delegate', Role);
Role.hasMany('delegate_assets', Asset);

When the Asset table is created, rather than create ownerId and delegateId, it only creates a field called roleId

Error when fetching associations?

Hello, I have some problems with fetching associations.

pascal-opitzs-macbook-pro:kanban pascalopitz$ node fetch.js
10 Nov 09:42:48 - 'Executing the query: SELECT * FROM Categories'
10 Nov 09:42:48 - 'Executing the query: SELECT * FROM Categories WHERE id = 1 ORDER BY id DESC LIMIT 1'

/Users/pascalopitz/local/lib/node/.npm/sequelize/0.4.3/package/lib/sequelize/Factory.js:60
les[Sequelize.Helper.SQL.manyToManyTableName(assocName, backAssocName)].klass,
^
TypeError: Cannot read property 'klass' of undefined

I put the code into a gist:

https://gist.github.com/670620

A little problem when finding entries by ID

Hello, before getting into matter, let me thank you for creating Sequelize, it is a great ORM and hope it fares well.

Now to stuff; when you've defined a model/class with its attributes, for example:

var User = Sequelize.define(
    'USERS',
    {
        ID:        { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
        Login:     { type: Sequelize.STRING, allowNull: true },
        UserLevel: { type: Sequelize.INTEGER, allowNull: false }
    },
    {
        timestamps:      false,
        paranoid:        false,
        underscore:      false,
        freezeTableName: true
    }
);

As you can see, I've defined ID as my primary key, and other attributes. When I tried to fetch an entry from the database (I've ommited the onSuccess event part):

User.find(1);

But no information was fetched because the query was generated like this: SELECT * FROM "USERS" WHERE "id"=1 LIMIT 1.

So I redid the query a bit:

User.find({ where: { ID: 1 } })
    .on('success', function(user){
        console.log(user);
    });

and the generated query was this one: SELECT * FROM "USERS" WHERE "ID"=1 LIMIT 1.

I find a bit odd you just select everything (*) and return it. I don't know if it's because I come from Doctrine, but I am used to have an object that will receive only the information the model requires, i.e. it will only select the attributes passed by the model definition. Am I wrong?

The other thing I wanted to discuss is if it would be possible (and if it would be correct) to support having model attributes mapped to names specified in the definition. Now it requires that the names of the fields must be the same as the ones from the database, but it can happen that table's fields have names defined in other (sometimes odd) ways.

I thought about having something like this:

{
        userLevel: { type: Sequelize.INTEGER, tableName: 'UserLevel' allowNull: false }
}

Thank you for your attention.

hasAndBelongsToMany...

The foreign keys are working like a charm, but I can't seem to join a table to itself in a many-to-many relationship...It just adds parentId/childId columns to the users table.

Change tracking capability for compliance or management requirements.

Some systems require historical change tracking. When defining, adding a "keepHistory" attribute in the options could automatically create a history table. Additionally, a configurable history length can be used to truncate the history keeping it from going out of control.

When saving an object and it is an update, simply insert into history table (select from current table where id= my object id) then update the existing table with the changed row. This can be done within a transaction to guarantee both actions have been completed.

test performance of other mysql libs

...

importDefinition refactoring

would be better if the imported file had the format:

exports = function(){}

Data migration for schema sync

Would be awesome to be able to keep as much data as possible while syncing the database schema...

defaultValue utils.js:91

Hi. I have some columns, where default value have to be 0.
In utils.js on line 91 i see this:

if(dataType.defaultValue) {

I think it will be better checking defaultValue != undefined

Thnx

use commonjs exports

$ node
Type '.help' for options.
node> require('./sequelize')
{}
node> Sequelize
ReferenceError: Sequelize is not defined

Need a method to remove a single association.

If I have one associated item that I want to remove from the list of associated items, I would have to get all the existing items, find this one in the list and remove it. Then call setItems([]) to remove that one item.

It would stand to reason that if I know one item it would be easier to simply remove it with a single call (given that I have the primary key(s)). Passing as the callback parameter either the item removed (like setItems([]) ) or an error that the item was not found. Sending a list of items would be awesome... but would add more complexity. Maybe in stages....

add chainQueries example to website

smth like http://gist.github.com/583112

Modifications to initialization for atypical host/port

http://github.com/sdepold/sequelize/blob/master/lib/sequelize/Sequelize.js#L8

this.options = {'host': 'localhost', 'post': 3306}
if (options) for (var key in options) this.options[key] = options[key]

http://github.com/sdepold/sequelize/blob/master/lib/sequelize/Sequelize.js#L8

connection = require(__dirname + "/../nodejs-mysql-native/client").createTCPClient(this.options.host, this.options.port);

These changes allow you to specify a different host/port :)

sequelize = new Sequelize('db', 'usr', 'pwd', {'port': 8889});

callback for error response from database

There is not a callback when the database respond with an error, that might be a constraint violation.

automatically define the foreignkey for belongsto and has one for selfreferences

ffg

timestamp fields, finding last object

I'm trying to share a common table between a nodejs application and a rails application. By convention, rails uses timestamps as "created_at" and "updated_at". Is there a way to tell sequelize to use timestamp fields the way I use them with rails?

Also I was wondering if it's possible for sequelize to find a single item of an association, something like this:

User.getPosts({last: true, order: "created_at DESC"}, callback);

Thanks a lot!

Redifine the default 'id' field?

Hi,

Love your library, am definitely going to start using it, and as I figure out how it works hopefully contribute something back ;)

Quick q. - would it be possible to enable over-ride of the identifier? e.g. I want to retrofit this against an existing database, I can't do that if that existing database has a different naming convention for the identifier field.

Thanks!
Clifton

finish documentation

Make empty password available

Database credentials should work without a password

findall returns offset time

Hi Sasha,
I've recently updated from 3.0 to 4.2, I've recreated all tables, however now I'm getting this issue:

findAll returns the UpdatedAt fields that are offset 5 hours from their actual values.

E.g.
I have UpdatedAt value as 21.45 in my DB, but the resultset from findAll returns 16.45.
The same SELECT query returns correct values when run from MySQL.
Perhaps I'm missing something?
I really suspect this has something to do with my system time being UTC+5, however I wonder why this had not happened with version 3.0
could you point me to the right direction? Thanks.

Limitations on Sequelize

I'm trying to store images in a database as base64 encoded strings. It works well with some small (<30kb) images, but on larger images, the string stored is being truncated.
I've already investigated a lot on the backend side, nodejs, front-end javascript and the only place where the content actually changes is between sequelize and the database. So I'm wondering if there are any limitations on Sequelize's side regarding string sizes to be stored.

throw new Error("Uncaught, unspecified 'error' event.");

I get the error below when i try and run the example app. (Node version v0.1.103)

events:14
throw new Error("Uncaught, unspecified 'error' event.");
^
Error: Uncaught, unspecified 'error' event.
at EventEmitter.emit (events:14:15)
at cmd.process_packet (/Users/leondewey/Projects/express-play/lib/sequelize/lib/nodejs-mysql-native/commands.js:140:16)
at [object Object].dispatch_packet (/Users/leondewey/Projects/express-play/lib/sequelize/lib/nodejs-mysql-native/client.js:115:37)
at Stream. (/Users/leondewey/Projects/express-play/lib/sequelize/lib/nodejs-mysql-native/client.js:161:28)
at Stream.emit (events:26:26)
at IOWatcher.callback (net:507:33)
at node.js:267:9

Clean instance

The instance objects currently have circular references which means they cannot be parsed to JSON.

Sometimes you want to send a fetched object to a client, or store it in a key-value store for a session. With all of the extra instance methods this cannot be done.

I propose a "clean" method for instances that returns an object with only stuff you want. Something like this:

Model.prototype.clean = function () {
    var cleanObj = {};
    for (var prop in this) {
        if (this.attributes.indexOf(prop) >= 0) {
            cleanObj[prop] = this[prop];
        }
    }
    return cleanObj;
}

So you can then do:

Person.find(33).on("success", function () {
    res.send(person.clean());
}

Default-Values

Queries not wrapped correctly

If I want to use a word like "group" as one of my fields, this conflicts with MySQL keyword in the current version of Sequelize.

All fields should be wrapped in backquotes to be valid. Example provided here: https://gist.github.com/bea5d75754cf5f05a57b

Need ability to assign indexes and manually assign primary key column

I have not seen anywhere where I can define a column to have an index associated with it. This could really increase the speed of searches based on other columns than ID or foreign keys.
Forcing of a primary key "id" when I want a different column to be that key.

What I have is an existing data model that I want to utilize with this library. One other thing that I feel is bad is that when defining a model, "user", the library adds an "s" to it automatically making it "users". This change, I believe, is not something that should happen as it changes the developer's expectation of the data model on the back-end. And it makes integration with other systems that would use the back-end data difficult if they are the ones that originally defined the model.

remove array usage from chainqueries

==>Sequelize.chainQueries({...}, {...}, function(){})

Adding additional options while creating tables

Hi,
I am using the rewrite branch of sequelize, and so far its pretty good.
I wanted to control the engine(MyISAM, InnoDb etc..) of the table being created, but didnt really find support for that.
I have made a few changes, to give you an idea and they are here:
https://github.com/pinetechlabs/sequelize/blob/rewrite/lib/sequelize/model-definition.js
https://github.com/pinetechlabs/sequelize/blob/rewrite/lib/sequelize/query-generator.js

Thanks,
P.V.S.

too many fk columns...

The latest version creates a FK column for each association, including a repeat of the pk...

(function(){

    /*
     * This file defines the database schema.
     */

    var Sequelize = require('./sequelize/lib/sequelize/Sequelize').Sequelize,
        sequelize = new Sequelize('marketspace', 'root', 'root', {'disableLogging': true, 'port': 8889});

    // generate schema

    var _bool = {'type': Sequelize.BOOLEAN, 'default': 0, 'allowNull': false},

        User = sequelize.define('user', {
            'username': Sequelize.STRING,
            'email': Sequelize.STRING,
            'password': Sequelize.STRING,
            'isDeleted': _bool,
            'isSpammer': _bool
        }),

        Status = sequelize.define('status', {
            'text': Sequelize.STRING,
            'isDeleted': _bool
        }),

        Notification = sequelize.define('notification', {
            'text': Sequelize.STRING,
            'isRead': Sequelize.BOOLEAN,
            'isDeleted': _bool
        }),

        Post = sequelize.define('post', {
            'title': Sequelize.STRING,
            'slug': Sequelize.STRING,
            'description': Sequelize.TEXT,
            'price': Sequelize.FLOAT,
            'isDeleted': _bool,
            'isSpam': _bool
        }),

        Feature = sequelize.define('feature', {
            'position': Sequelize.STRING,
            'isDeleted': _bool
        }),

        Location = sequelize.define('location', {
            'title': Sequelize.STRING,
            'slug': Sequelize.STRING,
            'isDeleted': _bool
        }),

        Category = sequelize.define('category', {
            'title': Sequelize.STRING,
            'slug': Sequelize.STRING,
            'isDeleted': _bool
        });

    // set associations

    User.hasMany('statuses', Status);
    Status.belongsTo('user', User);

    User.hasMany('notifications', Notification);
    Notification.belongsTo('user', User);

    User.hasMany('posts', Post);
    Post.belongsTo('user', User);

    Location.hasMany('posts', Post);
    Post.belongsTo('location', Location);

    Location.belongsTo('parent', Location);

    Category.hasMany('posts', Post);
    Post.belongsTo('category', Category);

    Category.belongsTo('parent', Category);

    // set exports

    exports.User = User;
    exports.Status = Status;
    exports.Notification = Notification;
    exports.Post = Post;
    exports.Feature = Feature;
    exports.Location = Location;

    exports.restore = function()
    {
        sequelize.drop(function(){
            sequelize.sync(function(){
                console.log('Database schema restored.');
            });
        });
    };

})();

Selecting values larger than x (WHERE x >=)

Pardon me if I'm asking something obvious.

Is there a way to use "find" with something else than "equals" ?
For example, I need to select some entries with Datestamp larger than some date.

Currently "find" uses "=", like in "updatedAt=" below
SELECT * FROM Projects WHERE updatedAt='2010-09-04 17:45:21' ORDER BY id DESC LIMIT 1'

but I need to select all entries from 2010-09-04 and later.
Can I do this using current Sequelize syntax?

npm install is broken

details here: http://gist.github.com/542626

I believe this just involves updating your package.json to provide a "main" lib. i.e.

"main": "sequelize.js"