postgresql使用
When working with databases you can choose to use the primitives offered by the database, or use a library that builds on top and abstract the tiny details for you.
使用数据库时,您可以选择使用数据库提供的原语,也可以使用建立在顶部并为您抽象一些小细节的库。
Sequelize is one of those libraries, and it’s a very popular Node.js wrapper for PostgreSQL, MySQL and other databases.
Sequelize是其中的一个库,它是PostgreSQL , MySQL和其他数据库的非常流行的Node.js包装器。
In this post I’m going to explore how to use Sequelize to work with a PostgreSQL database.
在本文中,我将探讨如何使用Sequelize与PostgreSQL数据库一起使用。
安装和配置Sequelize (Install and configure Sequelize)
Under the hood, Sequelize uses the pg
library to connect to PostgreSQL, so when we install the sequelize
npm package, we also need to install pg
:
在后台,Sequelize使用pg
库连接到PostgreSQL,因此,当我们安装sequelize
npm软件包时,我们还需要安装pg
:
npm install pg sequelize
Tip: don’t forget to first run
npm init -y
if the project is brand new and you don’t have apackage.json
file already.提示:如果项目是全新的,并且您还没有
package.json
文件,请不要忘记先运行npm init -y
。
In your Node.js file, you first define the database access variables:
在您的Node.js文件中,首先定义数据库访问变量:
const user = '<postgres user>'
const host = 'localhost'
const database = '<postgres db name>'
const password = '<postgres password>'
const port = '<postgres port>'
Then import 3 objects from sequelize
:
然后从sequelize
导入3个对象:
import { Sequelize, Model, DataTypes } from 'sequelize'
Then you can initialize a new Sequelize
object instance using this syntax:
然后,您可以使用以下语法初始化新的Sequelize
对象实例:
const sequelize = new Sequelize(database, user, password, {
host,
port,
dialect: 'postgres',
logging: false
})
We tell Sequelize which kind of database this is in the dialect
property (as mentioned, it can handle more than just Postgres).
我们在dialect
属性中告诉Sequelize这是哪种数据库(如前所述,它不仅可以处理Postgres,还可以处理更多数据库)。
We also disable logging, because it can be very verbose as it logs all the SQL queries, which we don’t really need to look at (unless you’re debugging a problem).
我们还禁用日志记录,因为它记录所有SQL查询时可能非常冗长,而我们实际上并不需要查看它们(除非您正在调试问题)。
如何创建Sequelize模型 (How to create a Sequelize model)
For every table you want to manipulate using Sequelize, you create a model.
对于要使用Sequelize进行处理的每个表,您都可以创建一个模型 。
Here’s an example, suppose we have a dogs
table with two columns: name
and age
.
这是一个示例,假设我们有一个包含两列的dogs
表: name
和age
。
We create a Dog
class extending the Model
base class:
我们创建一个Dog
类来扩展Model
基类:
import { Sequelize, Model, DataTypes } from 'sequelize'
const class Dog extends Model {}
Then call the init()
static method on the class describing the data it contains and the rules we want to apply. In this case, we disable null
:
然后在该类上调用init()
静态方法,以描述其包含的数据以及我们要应用的规则。 在这种情况下,我们禁用null
:
Dog.init({
name: {
type: DataTypes.STRING,
allowNull: false
},
age: {
type: DataTypes.INTEGER,
allowNull: false
}
}, {
sequelize,
modelName: 'dog',
timestamps: false
})
We used DataTypes.STRING
and DataTypes.INTEGER
. The DataTypes
object contains reference to all the types we can use, and they map to each specific database type. See the official docs for more types you can use.
我们使用了DataTypes.STRING
和DataTypes.INTEGER
。 DataTypes
对象包含对我们可以使用的所有类型的引用,它们映射到每种特定的数据库类型。 有关更多可用类型, 请参见官方文档 。
如何从数据库获取数据 (How to get data from the database)
Now that we have a model, how do we get data out of a table?
现在我们有了一个模型,我们如何从表中获取数据?
We can use the findAll()
method:
我们可以使用findAll()
方法:
Dog.findAll()
Calling this method will return a list of all the rows, and we’ll assign it to a variable:
调用此方法将返回所有行的列表,我们将其分配给变量:
const results = await Dog.findAll()
We use
await
becausefindAll()
returns a promise我们使用
await
因为findAll()
返回了一个promise
To limit the columns we retrieve, pass an object with the attributes
array:
要限制我们检索的列,请传递一个带有attributes
数组的对象:
Dog.findAll({
attributes: ['age']
})
Add a WHERE
clause to the query using the where
property. For example, get all dogs with age 8:
使用where
属性将WHERE
子句添加到查询where
。 例如,获取所有8岁的狗:
Dog.findAll({
where: {
age: 8,
}
})
Or get all dogs with age higher than 5:
或让所有年龄大于5岁的狗:
Dog.findAll({
where: {
age: {
[Op.gte]: 5,
}
}
})
More properties allow you to do other operations like limit
and order
:
更多的属性允许您执行其他操作,例如limit
和order
:
Dog.findAll({
limit: 10,
order: [
['name', 'DESC']
]
})
如何将数据插入数据库 (How to insert data into the database)
We can call Dog.create()
passing an object to create a new row in the database:
我们可以调用Dog.create()
传递一个对象来在数据库中创建新行:
const name = 'Roger'
const age = 8
const result = await Dog.create({ name, age })
如何更新数据 (How to update data)
Use the update()
method to update values in the table.
使用update()
方法更新表中的值。
In this example I set the age of ‘Roger’ to 9:
在此示例中,我将“ Roger”的年龄设置为9:
Post.update({
age: 9
}, {
where: {
name: 'Roger'
}
})
Removing the where
property will update all rows:
删除where
属性将更新所有行:
Post.update({
age: 10
})
postgresql使用