avatar
@bangbang93

Sequelize With SQLite

10/25/2014, 12:40:37 AM

BMCD由于不想再附加一个MongoDB的依赖,于是选用了SQLite作为数据库。不过手写SQL语句实在是太烦了,于是我又选用了Sequelilze作为SQLite的ORM模型处理。

折腾了一天之后稍微总结一下它的用法

var Sequelize = require('sequelize');
var sequelize = new Sequelize('bmcd', null, null, {
    dialect: 'sqlite',
    storage: 'bmcd.db'
});

用法没啥特别的,require一下然后new一个出来,第一个参数是数据库名称,二和三是用户名密码,不需要认证的SQLite数据库保持null即可,第三个参数是一个Object,dialect指定了数据库类型,Sequelize支持MySQL,SQLite和PostgreSQL,storage是SQLite独有的,用于指定存储的文件

sequelize.authenticate();
sequelize.sync({
    logging: console.log
//    force: true
});

第六行是验证登录,相当于连接数据库,第七行创建数据库和表。
值得注意的是force参数,默认为false,生成的SQL语句是

CREATE TABLE IF NOT EXISTS

如果设置为true,会成为

DROP TABLE IF EXISTS...
CREATE TABLE

然后就可以开始定义模型了

var User = sequelize.define('user', {
    username: Sequelize.STRING,
    password: Sequelize.STRING,
    isAdmin: Sequelize.BOOLEAN
});

定义的时候和mongoose不同的是类型不能直接用String,而要用Sequelize.STRING

Sequelize.STRING // VARCHAR(255) Sequelize.STRING(1234) // VARCHAR(1234) Sequelize.STRING.BINARY // VARCHAR BINARY Sequelize.TEXT // TEXT

Sequelize.INTEGER // INTEGER
Sequelize.BIGINT // BIGINT
Sequelize.BIGINT(11) // BIGINT(11)
Sequelize.FLOAT // FLOAT
Sequelize.FLOAT(11) // FLOAT(11)
Sequelize.FLOAT(11, 12) // FLOAT(11,12)

Sequelize.DECIMAL // DECIMAL
Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)

Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.BOOLEAN // TINYINT(1)

Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.

Sequelize.BLOB // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

对应关系可以看这里http://sequelizejs.com/docs/1.7.8/models#block-2-line-0

现在正在研究他的关联怎么用……