npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@hosportal/sequelize-temporalize

v5.0.1

Published

Temporal tables for Sequelize

Downloads

7

Readme

Temporalize tables for Sequelize

(aka "Historical records")

This package is a derivative of sequelize-temporal, with some bug fixes and major changes merged in. The project is now a substantial divergence from sequelize-temporal, so it is no longer interchangeable.

What is it?

Temporalize tables maintain historical versions of data. Modifying operations (UPDATE, DELETE) on these tables don't cause permanent changes to entries, but create new versions of them. Hence this might be used to:

  • log changes (security/auditing)
  • undo functionalities
  • track interactions (customer support)

Under the hood a history table with the same structure, but without constraints is created.

The normal singular/plural naming scheme in Sequelize is used:

  • model name: modelName + 'History'
  • table name: modelName + 'Histories'
  • index name: indexName + '_history'

Installation

npm install sequelize-temporalize

How to use

1) Import sequelize-temporalize

var Sequelize = require('sequelize');
var Temporalize = require('sequelize-temporalize');

or using imports

import { Sequelize, Model, UUID, UUIDV4 } from 'sequelize';
import { Temporalize } from 'sequelize-temporalize';

Create a sequelize instance and your models, e.g.

var sequelize = new Sequelize('', '', '', {
  dialect: 'sqlite',
  storage: __dirname + '/.test.sqlite'
});

2) Add the temporalize feature to your models

var User = sequelize.define('User', {paranoid: true});
// paranoid: true necessary to keep track of deleted entries in history table
var UserHistory = Temporalize({
    model: User,
    sequelize: sequelize,
    temporalizeOptions: {/* some options can be put here */});

or using es6 classes (useful in Typescript)

export class User extends Model {
  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;
  public readonly deletedAt!: Date; // necessary if using paranoid:true
  public id!: string;
  public username!: string;
  public email!: string;
}
export class UserHistory extends User {
  public readonly archivedAt!: Date;
  public readonly transactionId!: string; // necessary if logTransactionId: true
  public readonly eventId!: string;
  public readonly deletion!: boolean;
}
// initialize the User model
User.init({
  id: {
    type: UUID,
    defaultValue: UUIDV4,
    primaryKey: true,
    unique: true,
  },
  username: {
    type: new DataTypes.STRING(),
    unique: true,
  },
  email: {
    type: new DataTypes.STRING(),
  },
},
{
  sequelize,
  tableName: 'User',
  paranoid: true,
});
// initialize the UserHistory model
Temporalize({
  model: User,
  modelHistory: UserHistory,
  sequelize,
  temporalizeOptions: {/* some options can be put here */},
});

The output of Temporalize is its history model. If you pass it a modelHistory class, it will return that same modelHistory class.

IMPORTANT NOTE

If you would like to keep track of deletes in the history table, you MUST use the paranoid: true option when creating the original table.

Options

The default syntax for Temporalize is:

Temporalize({model, modelHistory, sequelize, temporalizeOptions})

whereas the temporalizeOptions are listed here (with default value).

temporalizeOptions.blocking = true

Runs the insert within the sequelize hook promise chain, disable for increased performance without warranties.

temporalizeOptions.modelSuffix = 'History'

By default sequelize-temporalize will add 'History' to the history model name and 'Histories' to the history table. By updating the modelSuffix value, you can decide what the naming will be. The value will be appended to the history model name and its plural will be appended to the history tablename.

examples for table User: modelSuffix: '_Hist' --> History Model Name: User_Hist --> History Table Name: User_Hists modelSuffix: 'Memory' --> History Model Name: UserMemory --> History Table Name: UserMemories modelSuffix: 'Pass' --> History Model Name: UserPass --> History Table Name: UserPasses

temporalizeOptions.indexSuffix = '_history'

All indexes that are preserved during the creation of the history table will have a '_history' suffix added to make it distinct to the original table's index.

temporalizeOptions.allowTransactions = true

By default, transactions are allowed but can be disabled with that flag for the historical tables (transactions on original tables should stay the same). It is useful in case you are using a separate DB than the one use by the original DB.

NOTE: IF YOU USE A SEPARATE DB FOR HISTORICAL TABLE, SET THE VALUE TO FALSE OR YOU WILL GET AN ERROR.

temporalizeOptions.logTransactionId = true

Logging the transactionId allows you to identify records that have been updated in a single transaction across tables.

temporalizeOptions.logEventId = true

Logging eventIds allows you to keep track of operations that occur in a single event, and if you simultaneously keep a history of requests in anther table, who performed them. For example, we may be updating multiple tables in a single request. We want a way of identifying the single operation across multiple tables in the table histories. To do this, we store the eventId. It is important that immediately after the creation of the eventId that the eventId, current time, route being requested and user ID of the individual making the request (and any other information you think is important) is stored in a request table.

import { v4 as uuidv4 } from 'uuid';
import { RequestLog } from '../models/request-log'; // the RequestLog table
import { Post } from '../models/post'; // the Posts table

export function doPostCreate(req, res) {
  const userId = req.userId;
  const eventId = uuidv4(); // for example, can use other uuid functions to generate unique ids for the request event
  // Log the request
  await RequestLog.create(
    {
      userId,
      date: new Date()
    },
    { eventId } // use `{ eventId } as any` here if using typescript, as eventId is not defined on sequelize options types
  );

  // Do other things with the same eventId
  await Post.create({
    title: req.body.title,
    text: req.body.text
  },
  {eventId});
  // The eventId will be logged in the associated history table for Post AND in RequestLog
});

temporalizeOptions.eventIdColumnName

Because it is quite likely that there will be a conflict between the column name eventId in the history table, and a column in the min table, an option is provided to rename the eventId column. Note that the eventId parameter attached to the options object in Model.create(obj, options) etc remains unchanged.

History table

History table stores historical versions of rows, which are inserted by triggers on every modifying operation executed on current table. It has the same structure and indexes as current table, but it doesn’t have any constraints. History tables are insert only and creator should prevent other users from executing updates or deletes by correct user rights settings. Otherwise the history can be violated.

Hooks

Triggers for storing old versions of rows to history table are inspired by referential integrity triggers. They are fired for each row after UPDATE and DELETE (within the same transaction)

IMPORTANT

If you use destroy or restore methods it is very important that you use paranoid: true on all models you plan to use destroy/restore on, to ensure that copies of the data exist in the DB to be copied into the history table.

License

The MIT License (MIT)

Copyright (c) 2015 James Jansson, Hosportal, BonaVal

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.