本文共 4722 字,大约阅读时间需要 15 分钟。
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.
使用数据库时,您可以选择使用数据库提供的原语,也可以使用建立在顶部并为您抽象一些小细节的库。
is one of those libraries, and it’s a very popular Node.js wrapper for , and other databases.
是其中的一个库,它是 , 和其他数据库的非常流行的Node.js包装器。
In this post I’m going to explore how to use Sequelize to work with a PostgreSQL database.
在本文中,我将探讨如何使用Sequelize与PostgreSQL数据库一起使用。
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 = ''const host = 'localhost'const database = ' 'const password = ' 'const 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查询时可能非常冗长,而我们实际上并不需要查看它们(除非您正在调试问题)。
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. for more types you can use.
我们使用了DataTypes.STRING
和DataTypes.INTEGER
。 DataTypes
对象包含对我们可以使用的所有类型的引用,它们映射到每种特定的数据库类型。 更多可用类型, 。
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'] ]})
We can call Dog.create()
passing an object to create a new row in the database:
我们可以调用Dog.create()
传递一个对象来在数据库中创建新行:
const name = 'Roger'const age = 8const result = await Dog.create({ name, age })
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使用
转载地址:http://wamgb.baihongyu.com/