I have several years of experience in programming, but I'm still rather new to nodejs and its connections to databases. This said, I am working on a project which has nodejs and sequelize implemented to connect to mysql databases, and I have been doing development on those in light of other code already implemented and apparently functioning properly.
But I came across an issue which I have been unable to solve, despite my best efforts.
I will try to put it as best I can, and I apologize in advance for such a long post:
The project is a management platform and each client has its own database (all structurally similar), so the main configured connection is for the logged client database. However, for some procedures, info is registed on the company's own database, which has distinct access credentials.
All credentials are stored encrypted in an .env file, which is read by the backend, decrypted and used to initialize both connections upon nodejs startup. When a request comes from the frontend, it goes through Controller -> Service -> Repository which initializes a connection instance to the database and executes the target query.
The thing is all connections to the clients own database seem to work properly, starting and ending upon query execution. An after query to the company's database, which only UPDATEs an existing record's date column, stays pending in sleep mode, which cannot happen.
I have been reading the sequelize documentation but was unable to find anything helpful.
From what I could gather from the documentation, the sequelize connections, instances and such are all correctly setup.
Specific client database loader file
(loader file for company is similar with different credentials)
export class DbClientLoader {
private static _instance: DbClientLoader;
public database;
private constructor() {
this.initDb();
}
/**
* Initialize the connection pool to the db
*/
private initDb() {
const { Sequelize } = require('sequelize');
this.database = new Sequelize(
process.env.BD_CLIENT_SCHEMA,
process.env.BD_CLIENT_USER,
process.env.BD_CLIENT_PASSWORD,
{
host: process.env.BD_CLIENT_HOST,
dialect: 'mysql',
pool: {
max: +process.env.BD_MAX_CONNECTIONS, // connection pool size
min: 0,
acquire: 30000,
idle: 10000,
},
timezone: 'SYSTEM',
logging: false,
dialectOptions: {
timezone: 'local',
decimalNumbers: true,
dateStrings: true,
typeCast: function (field, next) {
// for reading from database
if (field.type === 'DATETIME') {
return field.string();
}
return next();
},
},
}
);
}
/**
* Singleton loader
*/
public static get Instance() {
return this._instance || (this._instance = new this());
}
}
export const DbClientLoaderInst = DbClientLoader.Instance;
Database loader (called on nodejs server startup)
(all tries are apparently run successfuly)
export class DbLoader {
public static async loadAll() {
// Sets connection with the client database
const DbClientLoader = require('./DbClientLoader');
const dbClient = DbClientLoader.DbClientLoaderInst.database;
try {
await dbClient.authenticate();
console.log('Connection established with the client database.');
} catch (error) {
console.error('(!) ERROR: Connection with the client database failed:');
console.error('> ', error);
}
// Sets connection with the company database
const DbCompanyLoader = require('./DbCompanyLoader');
const dbCompany = DbCompanyLoader.DbCompanyLoaderInst.database;
try {
await dbCompany.authenticate();
console.log('Connection established with the company database.');
} catch (error) {
console.error('(!) ERROR: Connection with the company database failed:');
console.error('> ', error);
}
}
}
Database ids file
(just ids, not actual schema names, those are included in the credentials)
export enum DB_ID {
CLIENT = 'client',
COMPANY = 'company',
}
Database utilities file
import ...
import { DbClientLoader } from '../loaders/DbClientLoader';
import { DbCompanyLoader } from '../loaders/DbCompanyLoader';
export class dbUtils {
/**
* Gets the connection pool for the client database
*/
static getClientDB(): Sequelize {
return DbClientLoader.Instance.database;
}
/**
* Gets the connection pool for the company database
*/
static getCompanyDB(): Sequelize {
return DbCompanyLoader.Instance.database;
}
}
Generic repository file
import cls from 'continuation-local-storage';
import { Transaction } from 'sequelize';
import { dbUtils } from '../dbUtils';
import { DB_TYPE } from './DB_ID.enum';
import { Base_Repository_Interface } from './Base_Repository.interface';
/**
* Generic implementation of a repository
*/
export class Base_Repository implements Base_Repository_Interface {
protected DBID: DB_ID;
constructor(dbid: DB_ID) {
this.DBID = dbid;
}
async getTransaction(): Promise<Transaction> {
const session = cls.getNamespace('managersoft');
let transation;
switch (this.DBID) {
case DB_ID.CLIENT:
transation = session.get('transaction_client');
if (!transation) {
transation = await this.getConnection().transaction();
session.set('transaction_client', transation);
}
break;
case DB_ID.COMPANY:
transation = session.get('transaction_company');
if (!transation) {
transation = await this.getConnection().transaction();
session.set('transaction_company', transation);
}
break;
}
return transation as Transaction;
}
public getConnection() {
switch (this.DBID) {
case DB_ID.CLIENT:
return dbUtils.getClientDB();
break;
case DB_ID.COMPANY:
return dbUtils.getCompanyDB();
break;
}
}
}
Simplified example running scripts:
User_Service
(accesses User_Repository, calls Company_Service)
import ...
// custom services
import { DIUser_Service, User_Service_Interface } from './User_Service.interface';
import { DICompany_Service, Company_Service_Interface } from './Company_Service.interface';
// custom repositories
import { DIUser_Repository, User_Repository_Interface } from '../repositories/User_Repository.interface';
@provide(DIUser_Service)
export class User_Service implements User_Service_Interface {
constructor(
// custom services
@inject(DICompany_Service) private companyService: Company_Service_Interface,
// custom repositories
@inject(DIUser_Repository) private userRepository: User_Repository_Interface,
) {}
public async getUserData(params) {
...
}
public async setUserData(params) {
const upsert = await this.userRepository.set({
id: params.userid,
name: params.name,
nickname: params.nickname,
birthdate: params.birthdate,
});
if (upsert.inserted > 0 || upsert.updated > 0) {
this.companyService.update({
what: 'userstats',
id: params.userid,
});
}
return upsert;
}
}
User_Repository (accessed by User_Service)
import ...
import { DB_ID } from './DB_ID.enum';
import { Base_Repository } from './Base_Repository';
import { User_Repository_Interface, DIUser_Repository } from './User_Repository.interface';
@provide(DIUser_Repository)
export class User_Repository extends Base_Repository implements User_Repository_Interface {
constructor() {
super(DB_ID.CLIENT);
}
async get(params) {
...
}
async set(params) {
/*
QUERIES
*/
const sql = {
users: {
INSERT: `INSERT INTO users (__FIELDS__)
VALUES (__PLACEHOLDERS__)`,
UPDATE: `UPDATE users
SET __PLACEHOLDERS__
WHERE id = :id`,
},
...
params: {
type: params.id > 0 ? 'UPDATE' : 'INSERT',
transaction: await this.getTransaction(),
replacements: {},
},
ready: '',
}
/*
SETUP READY QUERY AND PARAMS
*/
switch (sql.params.type) {
case 'INSERT':
...
break;
case 'UPDATE':
// prepare placeholders and params
let __PLACEHOLDERS__ = '';
...
sql.ready = sql.users.UPDATE.replace('__PLACEHOLDERS__', __PLACEHOLDERS__);
break;
}
/*
EXECUTE QUERY
*/
return await super.getConnection()
.query(sql.ready, sql.params)
.then((data) => {
// data<array>
// [0] => last id (on INSERT)
// [1] => affected rows
return { id: data[0], upserted: data[1] }
})
.catch((err) => {
console.error(err);
});
}
}
Company_Service (accesses Company_Repository, called by User_Service)
import ...
// custom services
import { DICompany_Service, Company_Service_Interface } from './Company_Service.interface';
// custom repositories
import { Company_Repository_Interface, DICompany_Repository } from '../repositories/Company_Repository.interface';
@provide(DICompany_Service)
export class Company_Service implements Company_Service_Interface {
constructor(
@inject(DICompany_Repository) private companyRepository: Company_Repository_Interface,
) {}
public async update(params) {
const upsert = await this.companyRepository.set(params);
return upsert;
}
}
Company_Repository (accessed by Company_Service)
import ...
import { DB_ID } from './DB_ID.enum';
import { Base_Repository } from './Base_Repository';
import { Company_Repository_Interface, DICompany_Repository } from './Company_Repository.interface';
@provide(DICompany_Repository)
export class Company_Repository extends Base_Repository implements Company_Repository_Interface {
constructor() {
super(DB_ID.COMPANY);
}
async get(params) {
...
}
async set(params) {
/*
QUERIES
*/
const sql = {
userstats: {
INSERT: `INSERT INTO userstats (id, created, lastupdate)
VALUES (id, NOW(), NULL)`,
UPDATE: `UPDATE userstats
SET lastupdate = NOW()
WHERE id = :id`,
},
...
params: {
type: params.id > 0 ? 'UPDATE' : 'INSERT',
transaction: await this.getTransaction(),
replacements: {},
},
ready: '',
}
/*
SETUP READY QUERY AND PARAMS
*/
sql.ready = sql.users[sql.params.type];
sql.params.replacements.id = params.id;
/*
EXECUTE QUERY
*/
return await super.getConnection()
.query(sql.ready, sql.params)
.then((data) => {
// data<array>
// [0] => last id (on INSERT)
// [1] => affected rows
return { id: data[0], upserted: data[1] }
})
.catch((err) => {
console.error(err);
});
}
}
Tried the solution above and later reworked the scripts in order to further separate the scripts, removing the call to the Company_Service from within User_Service, setting it independently and calling it from the frontend after successful user update response.
Unfortunately the outcome was the same: database table get LOCKed in the connection's sleep status.
I only want the UPDATE query to execute with a successful finished transation, without the table getting LOCKed nor the connection staying idle in sleep mode.
To anyone who got this far, thank you in advance for you time.
Any help will be much appreciated.