いつも使用しているNode.jsのORM 「Sequelize」でよく使うパターンと型定義
※自分自身が使う機能に絞ってます。
※癖の強い使い方だと自覚は・・・・・
型定義ファイル
sequelize-model.ts
import { Transaction, Sequelize } from 'sequelize';
export interface Models {
sequelize: Sequelize;
Sequelize: any;
close: () => Promise<void>;
modelNames: ModelNames;
// ここからテーブルのModel
Table1: Models.Table1;
}
export interface ModelNames {
Table1: 'Table1';
}
export namespace Models {
// Sequelize modelのメソッド用
export interface Base<T> {
save: (transaction?: TransactionOption) => Promise<T>;
findAll: (option?: any) => Promise<T[] | null>;
findOne: (option: any, transaction?: TransactionOption) => Promise<T | null>;
create: (data: any, transaction?: TransactionOption) => Promise<T>;
findByPk: (pk: number, transaction?: TransactionOption) => Promise<T | null>;
bulkCreate: (data: any, transaction?: TransactionOption) => Promise<T | null>;
destroy: (transaction?: TransactionOption) => Promise<T>;
fieldRawAttributesMap: { [key: string]: { field: string; fieldName: string } };
}
export interface TransactionOption {
transaction?: Transaction;
}
// Table1のModel定義
export interface Table1 extends Base<Table1>, Table1Row {}
export interface Table1Row {
id: number;
text: string;
}
}
各テーブルのModel定義ファイル
sequelize-model-table1.ts
import { Sequelize, DataTypes } from 'sequelize';
export const Table1 = (sequelize: Sequelize) => {
return sequelize.define(
'Table1',
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
field: 'id',
},
text: { type: DataTypes.STRING, field: 'text' },
},
{ tableName: 'table1', timestamps: false }
);
};
DB接続してModelオブジェクトの生成部分
sequelize-main.ts
// [ sequelize ]
import { Sequelize, Options, DataTypes } from 'sequelize';
// [ 型定義 ]
import { Models as _models } from './sequelize-model';
// [ Model定義 ]
import { Table1 } from './sequelize-model-table1';
//-------------------------------------------------------------------
export namespace DB {
export type Models = _models;
//dbConfig ホントは環境変数から参照する
const dbConfig = {
host: 'localhost',
port: 3306,
dbName: 'test',
user: 'root',
pass: 'root',
};
const DbModelList = {
Table1,
} as const;
/**
* 各モデルのField（プロパティ）名の配列取得用
* データ更新時にフロントエンドから送信されたプロパティ名を検証したり・・色々。
*/
export const GetFieldName = (models: Models, modelName: ModelName) => {
return Object.values(models[modelName].fieldRawAttributesMap).map((row) => row.fieldName);
};
/**
* Model名の文字列型
* ※特定のModelだけの接続オブジェクトを生成する用のパラメータの型定義厳格化用
* モデルが大量になった場合に速度アップに効果あるかも？・・・未検証
*/
export type ModelName = keyof typeof DbModelList;
/**
* DB接続用のModelを取得する
*/
export const GetModels = (arg?: { targetModelList?: ModelName[]; dbName?: string } | undefined): Models => {
const config: { targetModelList: ModelName[]; dbName: string } = {
targetModelList: [],
dbName: dbConfig.dbName,
...arg,
};
const sequelizeConf: Options = {
host: dbConfig.host,
port: dbConfig.port,
dialect: 'mysql',
timezone: '+09:00',
// logging: true,
logging: false,
dialectOptions: {
timezone: '+09:00',
// useUTC: false,
dateStrings: true,
// 日付型を文字列で返却する
typeCast: (field: any, next: any) => {
if (field.type === 'DATETIME') {
return field.string();
}
return next();
},
},
};
const sequelize: Sequelize = new Sequelize(config.dbName, dbConfig.user, dbConfig.pass, sequelizeConf);
const models: Models = {} as any;
//[ models ]
const modelNameList: any = {};
(Object.keys(DbModelList) as ModelName[]).forEach((key) => {
if (config.targetModelList.length === 0 || config.targetModelList.includes(key)) {
models[key] = (DbModelList as any)[key](sequelize);
modelNameList[key] = key;
}
});
//Model定義をスプレッドシートから生成したくて極力定義をシンプルにしているので使ってない。
//[ associate ]
// (Object.keys(DbModelList) as ModelName[]).forEach((key) => {
// if (config.targetModelList.length === 0 || config.targetModelList.includes(key)) {
// if (models[key].associate) {
// models[key].associate(models);
// }
// }
// });
models.modelNames = modelNameList;
models.sequelize = sequelize;
models.Sequelize = Sequelize;
//DBとの接続解除用、コネクションつかんだまましないため。
models.close = async () => {
if((models.sequelize as any)!==null){
await sequelize.close();
(models.sequelize as any) = null;
}
};
return models;
};
}
よく使用するパターン（例
レコード追加
const models = DB.GetModels();
const transaction = await models.sequelize.transaction({ autocommit: false });
try {
const insertData = {};
//即席関数内で型指定を厳格に管理して値を投入
((obj: DB.Models['Table1']) => {
obj.text = 'created >> ' + dayjs().format('YYYYMMDD HHmmssSSS');
})(insertData as any);
const createResult = await models.Table1.create(insertData, {
transaction,
});
await transaction.commit();
console.log('createResult', JSON.stringify(createResult));
return createResult.id;
} catch (error) {
await transaction.rollback();
console.log('error', error);
return null;
} finally {
models.sequelize.close();
}
レコード更新
const models = DB.GetModels();
const transaction = await models.sequelize.transaction({ autocommit: false });
try {
const table1 = await models.Table1.findByPk(id, {
transaction,
});
if (table1 === null) return;
console.log('Table1', JSON.stringify(table1));
table1.text = 'update >> ' + dayjs().format('YYYYMMDD HHmmssSSS');
await table1.save({ transaction });
await transaction.commit();
} catch (error) {
await transaction.rollback();
console.log('error', error);
} finally {
models.sequelize.close();
}
sqlを投げたい時（リスト生成とかテーブルをJoinしたり集計したりしたい時）
sequelizeのモデルでもでき・・・ごにょごにょ・・・・・sql投げれるとだいたい幸せになれますw
type Row = {
id: number;
text: number;
};
const models = DB.GetModels();
// const transaction = await models.sequelize.transaction({ autocommit: false });
try {
const sql = `select * from table1 where id=:id`;
const queryResult = await models.sequelize.query<Row>(sql, {
raw: false,
type: QueryTypes.SELECT,
replacements: { id: 1 },
// transaction: transaction,
});
console.log('queryResult', JSON.stringify(queryResult));
} catch (error) {
console.log('error', error);
} finally {
models.sequelize.close();
}
サンプルコード