引入依赖
const Sequelize = require('sequelize')
建立数据库连接
// 建立连接
const sequelize = new Sequelize('数据库名称', '用户名', '密码', {
host: 'localhost',
/* 数据库类型 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
dialect: 'mysql'
})
const connection = await sequelize.authenticate()
console.log('连接成功')
连接成功
定义模型
// 定义模型
const Fruit = sequelize.define('Fruit', {
// 定义主键为UUID,(如果没有定义,默认是id自增)
id: {
// 设置id为UUID类型
type: Sequelize.DataTypes.UUID,
defaultValue: Sequelize.DataTypes.UUIDV1,
primaryKey: true
},
name: {
type: Sequelize.STRING(20),
allowNull: false
},
price: {
type: Sequelize.FLOAT,
allowNull: false
},
stock: {
type: Sequelize.INTEGER,
defaultValue: 0
}
}, {
// 是否创建'createdAt'和'updatedAt'字段(默认为true)
timestamps: false,
// 默认false修改表名为复数,true不修改表名,与数据库表名同步
freezeTableName: true,
// 数据库表名
tableName: 'TBL_FRUIT'
})
同步数据库
// 同步数据库
let ret = await Fruit.sync()
// 强制同步:创建表之前先删除已存在的表
// ret = await Fruit.sync({ force: true })
console.log(ret)
Fruit
插入数据
// 插入数据
ret = await Fruit.create({
name: 'strawberry',
price: 88,
stock: 22
})
console.log('create: ', JSON.stringify(ret, '', '\t'))
create: {
"stock": 0,
"id": 4,
"name": "strawberry",
"price": 88,
"updatedAt": "2019-12-27T09:09:13.544Z",
"createdAt": "2019-12-27T09:09:13.544Z"
}
更新数据
// 更新数据
ret = await Fruit.update({
price: 5.2
}, {
where: {
name: 'banana'
}
})
console.log('update: ', ret)
update: [ 1 ]
查询数据
findAll参数可以为空
// 条件查询
const Op = Sequelize.Op
ret = await Fruit.findAll({
where: {
// 价格小于100, 大于30
price: { [Op.lt]: 100, [Op.gt]: 30 }
}
})
console.log('find: ', JSON.stringify(ret, '', '\t'))
find: [
{
"id": 2,
"name": "apple",
"price": 50.1,
"stock": 0,
"createdAt": "2019-12-27T08:49:39.000Z",
"updatedAt": "2019-12-27T08:49:39.000Z"
}
]
完整代码
(async () => {
const Sequelize = require('sequelize')
// 建立连接
const sequelize = new Sequelize('数据库名称', '用户名', '密码', {
host: 'localhost',
/* 数据库类型 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
dialect: 'mysql'
})
const connection = await sequelize.authenticate()
console.log('连接成功')
// 定义模型 fruits
const Fruit = sequelize.define('Fruit', {
// 定义主键为UUID,(如果没有定义,默认是id自增)
id: {
// 设置id为UUID类型
type: Sequelize.DataTypes.UUID,
defaultValue: Sequelize.DataTypes.UUIDV1,
primaryKey: true
},
name: {
type: Sequelize.STRING(20),
allowNull: false
},
price: {
type: Sequelize.FLOAT,
allowNull: false
},
stock: {
type: Sequelize.INTEGER,
defaultValue: 0
}
}, {
// 是否创建'createdAt'和'updatedAt'字段(默认为true)
timestamps: false,
// 默认false修改表名为复数,true不修改表名,与数据库表名同步
freezeTableName: true,
// 数据库表名
tableName: 'TBL_FRUIT'
})
// 同步数据库
ret = await Fruit.sync()
// 强制同步:创建表之前先删除已存在的表
// ret = await Fruit.sync({ force: true })
console.log(ret)
// 插入数据
ret = await Fruit.create({
name: 'strawberry',
price: 88,
stock: 22
})
console.log('create: ', JSON.stringify(ret, '', '\t'))
// 更新数据
ret = await Fruit.update({
price: 5.2
}, {
where: {
name: 'banana'
}
})
console.log('update: ', ret)
// 查询数据
const Op = Sequelize.Op
ret = await Fruit.findAll({
where: {
price: { [Op.lt]: 100, [Op.gt]: 30 }
}
})
console.log('find: ', JSON.stringify(ret, '', '\t'))
})()
sequelize文档 https://sequelize.org/master/manual/getting-started.html