Sequelize Basics w/ PostgreSQL

A crash course on working with Sequelize and Connecting to the PostgreSQL database

Contents

- What is Sequelize?

- What is PostgreSQL?

- Connecting the Database

- Installation

- Starter Code

- Testing the Connection

- Creating the Database in PostgreSQL

- Sequelize Core Concepts

- Models

- Creating Models

- Model Synchronization

- Model Data Types

- Model Instances

- Model Querying

- Model Associations

- Creating Sample Data

- Learning Resources

---

What is Sequelize?

Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases:

• MySQL

• PostgreSQL

• MariaDB.

• SQLite.

• MSSQL

Learn More Here: Sequelize v6 | Sequelize

---

What is PostgreSQL?

PostgreSQL is considered to be the most advanced relational database system. It offers amazing features such as multi-version concurrency control, reliability, availability and strong data types backed by the 15 years of research and development.

Download Link: PostgreSQL: Downloads

---

Connecting to the Database

Install Dependencies

Once you have your node project initialized, install the Sequelize and PostgreSQL dependencies using these commands in the terminal.

Sequelize:

npm i --save sequelize 

PostgreSQL:

npm i --save pg pg-hstore

---

Starter Code

To connect to the database, you must create a Sequelize instance. This can be done by either passing the connection parameters separately to the Sequelize constructor or by passing a single connection URI:

Create an index.js file with the following code:

const { Sequelize } = require('sequelize');

// Option 1: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') // Example for postgres

// Option 2: Passing parameters separately (sqlite)
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});

// Option 3: Passing parameters separately (other dialects)
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'postgres' | 'sqlite' | 'mariadb' | 'mssql' | 'db2' | 'snowflake' | 'oracle' */
});

---

Testing the connection

You can use the .authenticate() function to test if the connection is OK:

try {
  await sequelize.authenticate();
  console.log('Connection has been established successfully.');
} catch (error) {
  console.error('Unable to connect to the database:', error);
}

"Sequelize will keep the connection open by default, and use the same connection for all queries. If you need to close the connection, call sequelize.close()"

However, since we haven’t created the database in PostgreSQL, it will return the following error ( You will receive more fields ).

{
  name: 'SequelizeConnectionError',
  message: 'database "dbname" does not exist'
}

---

Creating the Database in PostgreSQL:

To create the database, open your PostgreSQL Admin.

Give your database a proper name and choose the user.

---

Sequelize Core Concepts

Models

A model is an abstraction that represents a table in your database. In Sequelize, it is a class that extends Model.

The model tells Sequelize several things about the entity it represents, such as the name of the table in the database and which columns it has (and their data types).

A model in Sequelize has a name. This name does not have to be the same name of the table it represents in the database. Usually, models have singular names (such as User) while tables have pluralized names (such as Users), although this is fully configurable.

---

Creating Models

Models can be defined in two equivalent ways in Sequelize:

• Calling sequelize.define(modelName, attributes, options)

• Extending Model and calling init(attributes, options)

After a model is defined, it is available within sequelize.models by its model name.

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

//Method 1: Sequelize.define() 

const User = sequelize.define('User', {
  // Model attributes are defined here
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING
    // allowNull defaults to true
  }
}, {
  // Other model options go here
});


//Method 2: Extending Model
class User extends Model {}

User.init({
  // Model attributes are defined here
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING
    // allowNull defaults to true
  }
}, {
  // Other model options go here
  sequelize, // We need to pass the connection instance
  modelName: 'User' // We need to choose the model name
});


// `sequelize.define` also returns the model
console.log(User === sequelize.models.User); // true

"Internally, sequelize.define calls Model.init, so both approaches are essentially equivalent."

---

Model Synchronization

​When you define a model, you're telling Sequelize a few things about its table in the database. However, what if the table actually doesn't even exist in the database? What if it exists, but it has different columns, less columns, or any other difference?

This is where model synchronization comes in. A model can be synchronized with the database by calling model.sync(options), an asynchronous function (that returns a Promise). With this call, Sequelize will automatically perform an SQL query to the database. Note that this changes only the table in the database, not the model in the JavaScript side.

• User.sync() - This creates the table if it doesn't exist (and does nothing if it already exists)

• User.sync({ force: true }) - This creates the table, dropping it first if it already existed

• User.sync({ alter: true }) - This checks what is the current state of the table in the database (which columns it has, what are their data types, etc), and then performs the necessary changes in the table to make it match the model.

Example:

await User.sync({ force: true });
console.log("The table for the User model was just (re)created!");

You can use sequelize.sync() to automatically synchronize all models. Example:

await sequelize.sync({ force: true });
console.log("All models were synchronized successfully.");

---

Model Data Types

​Every column you define in your model must have a data type. Sequelize provides a lot of built-in data types. To access a built-in data type, you must import DataTypes:

const { DataTypes } = require("sequelize"); // Import the built-in data types

Timestamps:

By default, Sequelize automatically adds the fields createdAt and updatedAt to every model, using the data type DataTypes.DATE.

UUIDs:

For UUIDs, use DataTypes.UUID. It becomes the UUID data type for PostgreSQL and SQLite, and CHAR(36) for MySQL. Sequelize can generate UUIDs automatically for these fields, simply use DataTypes.UUIDV1 or DataTypes.UUIDV4 as the default value:

{
  type: DataTypes.UUID,
  defaultValue: DataTypes.UUIDV4 // Or DataTypes.UUIDV1
}

Default Values:

By default, Sequelize assumes that the default value of a column is NULL. This behavior can be changed by passing a specific defaultValue to the column definition:

sequelize.define('User', {
  name: {
    type: DataTypes.STRING,
    defaultValue: "John Doe"
  }
});

Read More about the Sequelize Data Types Here: Model Basics | Sequelize

---

Model Instances

Build( ):

Although a model is a class, you should not create instances by using the new operator directly. Instead, the build method should be used:

const jane = User.build({ name: "Jane" });
console.log(jane instanceof User); // true
console.log(jane.name); // "Jane"

Save( ):

The code above does not communicate with the database at all (note that it is not even asynchronous)! This is because the build method only creates an object that represents data that can be mapped to a database. In order to really save (i.e. persist) this instance in the database, the save method should be used:

await jane.save();
console.log('Jane was saved to the database!');

"Note, from the usage of await in the snippet above, that save is an asynchronous method. In fact, almost every Sequelize method is asynchronous; build is one of the very few exceptions."

The save method can also be used to update an instance in the database.

Create( ):

Sequelize provides the create method, which combines the build and save methods shown above into a single method:

const jane = await User.create({ name: "Jane" });
// Jane exists in the database now!
console.log(jane instanceof User); // true
console.log(jane.name); // "Jane"

Set( ):

You can update several fields at once with set.

const jane = await User.create({ name: "Jane", favoriteColor: "green" });

jane.set({
  name: "Ada",
  favoriteColor: "blue"
});
// As above, the database still has "Jane" and "green"
await jane.save();
// The database now has "Ada" and "blue" for name and favorite color

Update( ):

If you want to update a specific set of fields, you can use update:

const jane = await User.create({ name: "Jane", favoriteColor: "green" });
jane.favoriteColor = "blue"
await jane.update({ name: "Ada" })
// The database now has "Ada" for name, but still has the default "green" for favorite color
await jane.save()
// Now the database has "Ada" for name and "blue" for favorite color	

Destroy():

You can delete an instance by calling destroy:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
await jane.destroy();
// Now this entry was removed from the database	 

Read More on Instance Methods Here: Model Instances | Sequelize

---

Model Querying

findAll( ):

You can read the whole table from the database with the findAll method:

// Find all users
const users = await User.findAll();

The where option is used to filter the query.

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2;

findByPk( ):

The findByPk method obtains only a single entry from the table, using the provided primary key.

const project = await Project.findByPk(123);
if (project === null) {
  console.log('Not found!');
} else {
  console.log(project instanceof Project); // true
  // Its primary key is 123
}

findOne( ):

The findOne method obtains the first entry it finds (that fulfills the optional query options, if provided).

const project = await Project.findOne({ where: { title: 'My Title' } });
if (project === null) {
  console.log('Not found!');
} else {
  console.log(project instanceof Project); // true
  console.log(project.title); // 'My Title'
}

findOrCreate( ):

The method findOrCreate will create an entry in the table unless it can find one fulfilling the query options. In both cases, it will return an instance (either the found instance or the created instance) and a boolean indicating whether that instance was created or already existed.

The where option is considered for finding the entry, and the defaults option is used to define what must be created in case nothing was found. If the defaults do not contain values for every column, Sequelize will take the values given to where (if present).

const [user, created] = await User.findOrCreate({
  where: { username: 'sdepold' },
  defaults: {
    job: 'Technical Lead JavaScript'
  }
});
console.log(user.username); // 'sdepold'
console.log(user.job); // This may or may not be 'Technical Lead JavaScript'
console.log(created); // The boolean indicating whether this instance was just created
if (created) {
  console.log(user.job); // This will certainly be 'Technical Lead JavaScript'
}

Read More Here: Model Querying - Basics | Sequelize

---

Model Associations

Sequelize supports the standard associations: One-To-One, One-To-Many and Many-To-Many.

To do this, Sequelize provides four types of associations that should be combined to create them:

• The HasOne association

• The BelongsTo association

• The HasMany association

• The BelongsToMany association

const A = sequelize.define('A', /* ... */);
const B = sequelize.define('B', /* ... */);

A.hasOne(B); // A HasOne B
A.belongsTo(B); // A BelongsTo B
A.hasMany(B); // A HasMany B
A.belongsToMany(B, { through: 'C' }); // A BelongsToMany B through the junction table C

Read More Here: Associations | Sequelize

---

Creating Sample Data

We can create sample data through the process of seeding.

To do this, we place all our models and model instances within a seed function and call runSeed when the file is required.

async function seed() {
  await db.sync({ force: true });


  const users = await Promise.all(
    User.create({
      firstName: "John",
      lastName: "Doe",
      ...
    }),
  );
  const products = await Promise.all([
    Product.create({
      name: "Apple",
      price: 15.0,
    }),
    
    Product.create({
      name: "Banana",
      price: 15.0,
    }),
  ]);

}

async function runSeed() {
  console.log("seeding...");
  try {
    await seed();
  } catch (err) {
    console.error(err);
    process.exitCode = 1;
  } finally {
    console.log("closing db connection");
    await db.close();
    console.log("db connection closed");
  }
}

// Execute the `seed` function, IF we ran this module directly (`node seed`).
// `Async` functions always return a promise, so we can use `catch` to handle
// any errors that might occur inside of `seed`.
if (module === require.main) {
  runSeed();
}

// we export the seed function for testing purposes (see `./seed.spec.js`)

We can run the seed script by creating the script in the package.json file:

"seed": "node script/seed.js"

Then we run the script in the terminal:

npm run seed

---

Learning Resources:

1. Sequelize v6 | Sequelize

2. Getting Started with Sequelize and PostgreSQL | CodeForGeek