# How to use Sequelize to interact with PostgreSQL

> Learn how to use Sequelize with PostgreSQL in Node.js: install pg, configure connection, define a model, and query data with findAll, create, and update.

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2020-08-01 | Topics: [Node.js](https://flaviocopes.com/tags/node/) | Canonical: https://flaviocopes.com/sequelize/

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](https://sequelize.org/) is one of those libraries, and it's a very popular [Node.js](https://flaviocopes.com/nodejs/) wrapper for [PostgreSQL](https://flaviocopes.com/postgres-introduction/), [MySQL](https://flaviocopes.com/mysql-how-to-install/) and other databases.

In this post I'm going to explore how to use Sequelize to work with a PostgreSQL database.

## Install and configure Sequelize

Under the hood, Sequelize uses the `pg` library to connect to PostgreSQL, so when we install the `sequelize` [npm](https://flaviocopes.com/npm/) package, we also need to install `pg`:

```sh
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 a `package.json` file already.

In your Node.js file, you first define the database access variables:

```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`:

```js
import { Sequelize, Model, DataTypes } from 'sequelize'
```

Then you can initialize a new `Sequelize` object instance using this syntax:

```js
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).

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).

## How to create a Sequelize model

For every table you want to manipulate using Sequelize, you create a **model**.

Here's an example, suppose we have a `dogs` table with two columns: `name` and `age`.

We create a `Dog` class extending the `Model` base class:

```js
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`:

```js
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](https://sequelize.org/v5/manual/data-types.html) for more types you can use.

## 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:

```js
Dog.findAll()
```

Calling this method will return a list of all the rows, and we'll assign it to a variable:

```js
const results = await Dog.findAll()
```
 
> We use `await` because `findAll()` returns a promise

To limit the columns we retrieve, pass an object with the `attributes` array:

```js
Dog.findAll({
  attributes: ['age']
})
```

Add a `WHERE` clause to the query using the `where` property. For example, get all dogs with age 8:

```js
Dog.findAll({
  where: {
    age: 8,
  }
})
```

Or get all dogs with age higher than 5:

```js
Dog.findAll({
  where: {
    age: {
      [Op.gte]: 5,
    }
  }
})
```

More properties allow you to do other operations like `limit` and `order`:

```js
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:

```js
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.

In this example I set the age of 'Roger' to 9:

```js
Post.update({
  age: 9
}, {
  where: {
    name: 'Roger'
  }
})
```

Removing the `where` property will update all rows:

```js
Post.update({
  age: 10
})
```