Using ORM | Ucode

Appearance

On this page

Using ORM ​

Directly using the sqlite3 interface requires providing SQL statements for database operations, which can be cumbersome. To simplify this, we can use Object-Relational Mapping (ORM), which maps database tables to JavaScript objects.

For instance, a users table can be represented as:

idemailnamepassword
1admin@example.comBob123456
2lucy@example.comLucyabcdef
3alice@example.comAlicehello123

Each row can be modeled as a JavaScript object. To facilitate this mapping, we can use an ORM framework like Sequelize.

Setting Up Sequelize ​

First, we need to install Sequelize and the SQLite driver:

bash
npm install sequelize sqlite3

Then, define the ORM mapping in orm.mjs:

javascript
// orm.mjs:
import { Sequelize, DataTypes } from 'sequelize';

export const sequelize = new Sequelize('sqlite:test.db');

export const User = sequelize.define('User', {
    id: {
        primaryKey: true,
        autoIncrement: true,
        type: DataTypes.INTEGER,
        allowNull: false
    },
    email: {
        unique: true,
        type: DataTypes.STRING,
        allowNull: false
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false
    }
}, {
    tableName: 'users'
});

Integrating with the Application ​

Next, modify app.mjs to use Sequelize for database operations:

javascript
import { sequelize, User } from './orm.mjs';

async function initDb() {
    await sequelize.sync();
    const email = 'admin@example.com';
    let user = await User.findOne({ where: { email } });

    if (user === null) {
        await User.create({
            email,
            name: 'Bob',
            password: '123456'
        });
    }
}

await initDb();

Updating Sign-In Logic ​

Update signin.mjs to utilize Sequelize for user authentication:

javascript
import { User } from '../orm.mjs';

async function signin(ctx, next) {
    let email = ctx.request.body.email || '';
    let password = ctx.request.body.password || '';
    
    let user = await User.findOne({ where: { email } });
    
    if (user && user.password === password) {
        console.log('signin ok!');
        ctx.render('signin-ok.html', { title: 'Sign In OK', name: user.name });
    } else {
        console.log('signin failed!');
        ctx.render('signin-failed.html', { title: 'Sign In Failed' });
    }
}

Running the Application ​

When you run node app.mjs, you’ll see the SQL statements generated by Sequelize in the console. Note that Sequelize automatically adds createdAt and updatedAt fields to your model, so the users table will look like this:

sql
CREATE TABLE IF NOT EXISTS `users` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `name` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `createdAt` DATETIME NOT NULL,
    `updatedAt` DATETIME NOT NULL
);

Common Operations ​

Sequelize provides various methods for database operations, such as:

  • findAll(): Returns multiple records.
  • findOne(): Returns a single record.
  • create(): Saves a new record.
  • save(): Updates an existing record.
  • destroy(): Deletes a record.

For more advanced features like associations, refer to the official Sequelize documentation.