Quick Jump Start - Sequelize

By xngo on September 14, 2022

Prerequisite

  • Install SQLite and set environment PATH so it can be run anywhere.
  • npm install sqlite3
  • npm install sequelize

Sequelize

// Usage: rm database-ex.sqlite; node .\sequelize-quick-jump-start_kn.js; 'Select * from Users' | sqlite3 -column -header database-ex.sqlite
 
// Prerequisite
//  - Install SQLite and set environment PATH so it can be run anywhere.
//  - npm install sqlite3
//  - npm install sequelize
 
// Reference: 
//  - https://sequelize.org/api/v6/class/src/sequelize.js~sequelize
//  - https://sequelize.org/docs/v6/core-concepts/model-querying-finders/#findbypk
 
const { Sequelize, DataTypes } = require('sequelize');
 
// Connect to database.
const sequelize = new Sequelize({
                            dialect: 'sqlite',
                            storage: 'database-ex.sqlite'
                        });
 
(async() => {
 
    // Test connection.
        try {
            await sequelize.authenticate();
            console.log('Connection has been established successfully.');
        } catch (error) {
            console.error('Unable to connect to the database:', error);
        }
 
    // Define User table model.
        const User = sequelize.define('User', {
                                        username: DataTypes.STRING,
                                        birthday: DataTypes.DATE,
                                    });
 
    // Create all tables defined by models.
        await sequelize.sync();
 
    // Insert users in table.
        const anne = await User.create({
                                username: 'anne', birthday: new Date(1982, 9, 22)
                            });
 
        const joe = await User.create({
                                username: 'joe', birthday: new Date(2008, 11, 29)
                            });
 
    // Update user data.
        anne.update(  {username: 'anne-2'}, 
                    {where: {_id: anne.id}} );
 
    // Find User by ID(primary key).
        const foundAnne = await User.findByPk(anne.id);
        console.log(`Found user by ID=${anne.id} is ${foundAnne.username}.`);
 
    // Find User by column value.
        const foundJoe = await User.findOne( {where: {username: 'joe'}} );
        console.log(`Found user by username='joe' is ${foundJoe.username}.`);
 
})();

Output

Executing (default): SELECT 1+1 AS result
Connection has been established successfully.
Executing (default): SELECT name FROM sqlite_master WHERE type='table' AND name='Users';
Executing (default): CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(255), `birthday` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`Users`)
Executing (default): INSERT INTO `Users` (`id`,`username`,`birthday`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4);
Executing (default): INSERT INTO `Users` (`id`,`username`,`birthday`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4);
Executing (default): SELECT `id`, `username`, `birthday`, `createdAt`, `updatedAt` FROM `Users` AS `User` WHERE `User`.`id` = 1;
Executing (default): UPDATE `Users` SET `username`=$1,`updatedAt`=$2 WHERE `id` = $3
Found user by ID=1 is anne-2.
Executing (default): SELECT `id`, `username`, `birthday`, `createdAt`, `updatedAt` FROM `Users` AS `User` WHERE `User`.`username` = 'joe' LIMIT 1;
Found user by username='joe' is joe.
id  username  birthday                        createdAt                       updatedAt
--  --------  ------------------------------  ------------------------------  ------------------------------
1   anne-2    1982-10-22 04:00:00.000 +00:00  2022-09-14 17:31:29.301 +00:00  2022-09-14 17:31:29.326 +00:00
2   joe       2008-12-29 05:00:00.000 +00:00  2022-09-14 17:31:29.315 +00:00  2022-09-14 17:31:29.315 +00:00

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.