postgresql+Sequelize基础
什么是Sequelize
Sequelize是一个Node.js的ORM（对象关系映射）库，它提供了一种简洁而强大的方式来与关系型数据库进行交互。ORM库的作用是将数据库表格与对象之间进行映射，使开发者可以使用面向对象的方式来操作数据库，而不需要直接编写SQL查询语句。通过使用Sequelize，您可以更高效地管理和操作关系型数据库，提高开发效率，并且减少编写和维护原生SQL语句的工作量。
初始化项目
mkdir sequelize_learning
cd sequelize_learning
npm init
#安装Sequelize
npm i sequelize
# 安装pg数据库驱动程序
npm i pg pg-hstore
根目录下新建server.js文件。
连接到数据库
要连接到数据库,必须创建一个 Sequelize 实例。
//创建一个Sequelize实例
const Sequelize = require("sequelize");
const sequelize = new Sequelize('database_name', 'username', 'password', {
host: 'localhost',
dialect: 'postgres',
//可选项，连接池参数
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
}
});
测试连接
你可以使用.authenticate()函数测试连接是否正常：
//测试连接
async function testConnect(){
try {
await sequelize.authenticate();
console.log('Connection has been established successfully.');
} catch (error) {
console.error('Unable to connect to the database:', error);
}
}
testConnect();
模型
模型定义
使用 sequelize.define:
const User = sequelize.define('User', {
// 在这里定义模型属性
firstName: {
type: Sequelize.STRING,
allowNull: false
},
lastName: {
type: Sequelize.STRING
// allowNull 默认为 true
}
}, {
// 这是其他模型参数
});
以下是一些常用的其他模型参数：
timestamps：默认为true。如果设置为false，将不会自动添加createdAt和updatedAt字段到模型中。
tableName：指定模型对应的数据库表格的名称。如果未指定，则默认情况下将使用模型名称的复数形式作为表格名称。
具体参数可以参考官方文档
模型同步
通过调用一个异步函数(返回一个Promise)model.sync(options)。通过此调用,Sequelize 将自动对数据库执行 SQL 查询。
User.sync()- 如果表不存在,则创建该表(如果已经存在,则不执行任何操作)User.sync({ force: true })- 将创建表,如果表已经存在,则将其首先删除User.sync({ alter: true })- 这将检查数据库中表的当前状态(它具有哪些列,它们的数据类型等),然后在表中进行必要的更改以使其与模型匹配.
示例：
//模型同步
async function uploadModel() {
await User.sync({ force: true });
console.log("用户模型表刚刚(重新)创建！");
}
uploadModel();
一次同步所有模型
你可以使用 sequelize.sync() 自动同步所有模型. 示例：
//一次同步所有模型
async function uploadAllModel() {
await sequelize.sync({ force: true });
console.log("所有模型均已成功同步.");
}
uploadAllModel();
server.js代码：
const Sequelize = require("sequelize");
//创建一个Sequelize实例
const sequelize = new Sequelize('testdb', 'postgres', '****', {
host: 'localhost',
dialect: 'postgres',
//可选项，连接池参数
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
}
});
//测试连接
async function testConnect(){
try {
await sequelize.authenticate();
console.log('Connection has been established successfully.');
} catch (error) {
console.error('Unable to connect to the database:', error);
}
}
testConnect();
//定义模型
const User = sequelize.define('User', {
// 在这里定义模型属性
firstName: {
type: Sequelize.STRING,
allowNull: false
},
lastName: {
type: Sequelize.STRING
// allowNull 默认为 true
}
}, {
// 这是其他模型参数
});
//模型同步
async function uploadModel() {
await User.sync({ force: true });
console.log("用户模型表刚刚(重新)创建！");
}
uploadModel();
创建模型实例
Sequelize提供了 create 方法，用于创建模型实例并保存到数据库中。
const Sequelize = require("sequelize");
//创建一个Sequelize实例
const sequelize = new Sequelize('testdb', 'postgres', 'wfgdpg212', {
host: 'localhost',
dialect: 'postgres',
//可选项，连接池参数
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
}
});
//定义模型
const User = sequelize.define("user", {
name: Sequelize.TEXT,
favoriteColor: {
type: Sequelize.TEXT,
defaultValue: 'green'
},
age: Sequelize.INTEGER,
cash: Sequelize.INTEGER
});
//模型同步
// (async ()=>{
// await sequelize.sync({ force: true });
// console.log("所有模型均已成功同步.");
// })();
//创建模型实例
(async () => {
const jane3 = await User.create({ name: "Jane3" });
console.log("jane3", jane3.toJSON());
})();
查看数据库中users表格，可见新添加了一条name值为jane3的数据：
模型查询
简单SELECT查询
你可以使用 findAll 方法从数据库中读取整个表：
(async ()=>{
const users = await User.findAll();
console.log("All users:", JSON.stringify(users));
})();
输出结果：
Executing (default): SELECT "id", "name", "favoriteColor", "age", "cash", "createdAt", "updatedAt" FROM "users" AS "user";
All users: [{"id":1,"name":"Jane","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T02:46:17.748Z","updatedAt":"2023-07-11T02:46:17.748Z"},{"id":2,"name":"Jane","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T02:53:38.110Z","updatedAt":"2023-07-11T02:53:38.110Z"},{"id":3,"name":"Jane2","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T02:53:56.419Z","updatedAt":"2023-07-11T02:53:56.419Z"},{"id":4,"name":"Jane2","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T02:54:52.274Z","updatedAt":"2023-07-11T02:54:52.274Z"},{"id":5,"name":"Jane2","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T02:55:38.601Z","updatedAt":"2023-07-11T02:55:38.601Z"},{"id":6,"name":"Jane3","favoriteColor":"green","age":null,"cash":null,"createdAt":"2023-07-11T07:38:43.207Z","updatedAt":"2023-07-11T07:38:43.207Z"}]
应用 WHERE 子句
where 参数用于过滤查询.where 子句有很多运算符,可以从 Op 中以 Symbols 的形式使用.
const Op = require("sequelize");
User.findAll({
where: {
name: {
[Op.eq]: "jane"
}
}
});
// SELECT * FROM user WHERE name = jane;
实例：pg中有创建好的表格，如何使用Sequelize做查询
数据库中创建tutorials表，并向其中插入几条数据：
使用Sequelize连接数据库，并作查询
const Sequelize = require("sequelize");
const Op = Sequelize.Op;
//创建一个Sequelize实例
const sequelize = new Sequelize('testdb', 'postgres', '****', {
host: 'localhost',
dialect: 'postgres',
//可选项，连接池参数
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
}
});
//定义一个模型，模型名称要和要操作的表格名一致，这里是“tutorials”;
//模型属性必须根据数据库中表格的字段来设置，与表格保持一致;
//其他模型参数中，设置不添加时间戳，否则会自动添加“createdAt”和“updatedAt”字段，破坏数据库原有表格结构
const tutorials = sequelize.define("tutorials", {
title: {
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
published: {
type: Sequelize.BOOLEAN
}
}, {
timestamps: false
});
//模型同步.sync()函数中不要添加参数，表示如果表不存在,则创建该表(如果已经存在,则不执行任何操作)
// (async ()=>{
// await tutorials.sync();
// console.log("模型成功同步.");
// })();
let condition = {
title: {
[Op.iLike]: "%title"
}
}
tutorials.findAll({ where: condition })
.then(data => {
console.log("data", data);
})
.catch(err => {
console.log(err.message);
})
输出结果：
Executing (default): SELECT "id", "title", "description", "published" FROM "tutorials" AS "tutorials" WHERE "tutorials"."title" ILIKE '%title';
data [
tutorials {
dataValues: {
id: 1,
title: 'firsttitle',
description: '关于生活',
published: true
},
_previousDataValues: {
id: 1,
title: 'firsttitle',
description: '关于生活',
published: true
},
uniqno: 1,
_changed: Set(0) {},
_options: {
isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array]
},
isNewRecord: false
},
tutorials {
dataValues: {
id: 2,
title: 'secondtitle',
description: '关于工作',
published: true
},
_previousDataValues: {
id: 2,
title: 'secondtitle',
description: '关于工作',
published: true
},
uniqno: 1,
_changed: Set(0) {},
_options: {
isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array]
},
isNewRecord: false
},
tutorials {
dataValues: {
id: 3,
title: 'thirdtitle',
description: '关于编程',
published: true
},
_previousDataValues: {
id: 3,
title: 'thirdtitle',
description: '关于编程',
published: true
},
uniqno: 1,
_changed: Set(0) {},
_options: {
isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array]
},
isNewRecord: false
}
]
sequelize.query原始查询
使用 sequelize.query 方法来执原始SQL查询。
默认情况下,函数将返回两个参数 - 一个结果数组,以及一个包含元数据(例如受影响的行数等)的对象。
const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
// 结果将是一个空数组,元数据将包含受影响的行数.
在不需要访问元数据的情况下,你可以传递一个查询类型来告诉后续如何格式化结果. 例如,对于一个简单的选择查询你可以做:
const { QueryTypes } = require('sequelize');
const users = await sequelize.query("SELECT * FROM `users`", { type: QueryTypes.SELECT });
// 我们不需要在这里分解结果 - 结果会直接返回
sql语句中插入变量
替换
查询中的替换可以通过两种不同的方式完成:使用命名参数(以:开头),或者由？表示的未命名参数. 替换在options对象中传递。
如果传递一个数组, ? 将按照它们在数组中出现的顺序被替换
如果传递一个对象, :key 将替换为该对象的键. 如果对象包含在查询中找不到的键,则会抛出异常,反之亦然。
const { QueryTypes } = require('sequelize');
await sequelize.query(
'SELECT * FROM projects WHERE status = ?',
{
replacements: ['active'],
type: QueryTypes.SELECT
}
);
await sequelize.query(
'SELECT * FROM projects WHERE status = :status',
{
replacements: { status: 'active' },
type: QueryTypes.SELECT
}
);
要使用通配符运算符 ％,请将其附加到你的替换中. 以下查询与名称以 'ben' 开头的用户相匹配.
const { QueryTypes } = require('sequelize');
await sequelize.query(
'SELECT * FROM users WHERE name LIKE :search_name',
{
replacements: { search_name: 'ben%' },
type: QueryTypes.SELECT
}
);
绑定
绑定参数由 $1, $2, ... (numeric) 或 $key引用。
- 如果传递一个数组,
$1被绑定到数组中的第一个元素 (bind[0])。 - 如果传递一个对象,
$key绑定到 object['key']. 每个键必须以非数字字符开始. $1 不是一个有效的键,即使 object['1'] 存在。
$$可以用来转义一个$字符符号
const { QueryTypes } = require('sequelize');
await sequelize.query(
'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
{
bind: ['active'],
type: QueryTypes.SELECT
}
);
await sequelize.query(
'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $status',
{
bind: { status: 'active' },
type: QueryTypes.SELECT
}
);
