Here is a practical demonstration:
import { sequelize } from '../../db';
import { Model, DataTypes, Op } from 'sequelize';
class User extends Model {}
User.init(
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false,
},
name: DataTypes.STRING,
type1: DataTypes.STRING,
type2: DataTypes.STRING,
},
{ sequelize, tableName: 'users', modelName: 'user' },
);
(async function() {
try {
await sequelize.sync({ force: true });
await User.bulkCreate([
{ id: 1, name: 'mike', type1: 'a', type2: 'a' },
{ id: 2, name: 'tobias', type1: 'c', type2: 'd' },
]);
const inputType = 'a';
const inputName = 'mike';
const query = { where: { id: 1, name: inputName } };
if (inputType) {
query['where'][Op.and] = [{ type1: inputType }, { type2: inputType }];
}
const result = await User.findAll(query);
console.log(result);
} catch (error) {
console.log(error);
} finally {
await sequelize.close();
}
})();
The outcome of the execution:
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" SERIAL , "name" VARCHAR(255), "type1" VARCHAR(255), "type2" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "users" ("id","name","type1","type2") VALUES (1,'mike','a','a'),(2,'tobias','c','d') RETURNING *;
Executing (default): SELECT "id", "name", "type1", "type2" FROM "users" AS "user" WHERE ("user"."type1" = 'a' AND "user"."type2" = 'a') AND "user"."id" = 1 AND "user"."name" = 'mike';
[ user {
dataValues: { id: 1, name: 'mike', type1: 'a', type2: 'a' },
_previousDataValues: { id: 1, name: 'mike', type1: 'a', type2: 'a' },
_changed: {},
_modelOptions:
{ timestamps: false,
validate: {},
freezeTableName: true,
underscored: false,
paranoid: false,
rejectOnEmpty: false,
whereCollection: [Object],
schema: null,
schemaDelimiter: '',
defaultScope: {},
scopes: {},
indexes: [],
name: [Object],
omitNull: false,
sequelize: [Sequelize],
tableName: 'users',
hooks: {} },
_options:
{ isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array] },
isNewRecord: false } ]
Data entries in the database:
node-sequelize-examples=# select * from users;
id | name | type1 | type2
----+--------+-------+-------
1 | mike | a | a
2 | tobias | c | d
(2 rows)