Sequelize.js: Create a table with auto incrementing id (JavaScript & TypeScript examples)

Introduction

Sequelize.js is a popular Node.js ORM (Object-Relational Mapping) library that allows developers to interact with relational databases using JavaScript or TypeScript. It provides a high-level abstraction for database operations, making it easier to write secure and maintainable code. A common requirement when working with databases is the creation of a primary key that auto-increments with every new record, ensuring a unique identifier for each entry. In this tutorial, we’ll go through the steps to create a table with an auto-incrementing id using Sequelize, with code examples in both JavaScript and TypeScript.

Setting Up Your Project

Before you start, ensure you have Node.js installed. Then, create a new Node.js project and install Sequelize and a database driver, such as pg for PostgreSQL:

$ mkdir sequelize-demo
$ cd sequelize-demo
$ npm init -y
$ npm install sequelize pg pg-hstore

Next, initialize a new Sequelize instance:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  host: 'localhost'
});

In TypeScript, you’d do the following:

import { Sequelize } from 'sequelize';
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  host: 'localhost'
});

Defining the Model

To create a table with Sequelize, you define a model. A model represents a table in the database. Define a model with an auto-incrementing primary key like this:

const User = sequelize.define('user', {
  // The id column is automatically created and configured as primary and auto-increment
  name: {
    type: Sequelize.STRING
  },
  email: {
    type: Sequelize.STRING
  }
});

In TypeScript, the process is the same, but you can optionally define an interface that corresponds with your model for stronger typing:

import { Model, DataTypes } from 'sequelize';

interface UserAttributes {
  id: number;
  name: string;
  email: string;
}

class User extends Model implements UserAttributes {
  public id!: number; // Note: the exclamation mark is used here for non-null assertions in TypeScript
  public name!: string;
  public email!: string;
}

User.init({
  // id column will be automatically created by Sequelize
  name: DataTypes.STRING,
  email: DataTypes.STRING
}, {
  sequelize,
  modelName: 'user'
});

Syncing the Model with the Database

With the model defined, you can now sync it with your database:

User.sync().then(() => {
  console.log('User table created successfully.');
}).catch(error => {
  console.error('Error creating table:', error);
});

In TypeScript, the process is identical. The above code will create a table named ‘users’ with an auto-incrementing ‘id’ column, along with ‘name’ and ’email’ columns.

Advanced Features

Sequelize also offers the ability to further customize your model definitions and syncing options. For example, you could specify starting values for your auto-incrementing id or define custom sequences in databases that support this feature.

// Customize auto-increment starting value
User.init({
  // Other model attributes
}, {
  sequelize,
  modelName: 'user',
  autoIncrement: true,
  autoIncrementStartingValue: 1000 // Start auto-increment from 1000
});

// Sync model with options
User.sync({ force: true }) // Use 'force' only for development! It drops the table if it already exists.
  .then(() => {
    console.log('User table (re)created successfully.');
  })
  .catch(error => {
    console.error('Error (re)creating table:', error);
  });

Conclusion

In conclusion, Sequelize.js simplifies the process of creating and managing tables with auto-incrementing ids in a Node.js application. By following the above steps, you can set up a Sequelize model with an auto-incrementing primary key in both JavaScript and TypeScript, providing a robust foundation for your data models. Remember to consult the Sequelize documentation for more advanced features and best practices.