[@loopback/sequelize] syncSequelizeModel error for models having property type array & item-type object

Describe the bug

Everything was working fine in old versions 0.5.2 but after upgrading 0.6.0 facing issues,
This started after this code :

in this PR :

https://github.com/loopbackio/loopback-next/pull/10284/files#diff-eed487b3bfde7c98474b321fa2c69aeea23caff62901d6b710ef7c44517ff328

My model has this property :

@property({
type: 'array',
itemType: 'object',
name: 'patientPresenterOrgs',
description:
'Organizations associated with the user as patient presenters.',
})
patientPresenterOrgs?: PatientPresenterOrg[];

Error :

"SQLITE_ERROR: near "[]": syntax error"

"Error: \n at Database. (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/dialects/sqlite/query.js:236:27)\n at /Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/dialects/sqlite/query.js:234:50\n at new Promise ()\n at Query.run (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/dialects/sqlite/query.js:234:12)\n at /Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/sequelize.js:650:28\n at SQLiteQueryInterface.createTable (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/dialects/abstract/query-interface.js:229:12)\n at Function.sync (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/sequelize/src/model.js:1353:7)\n at UserViewRepository.syncSequelizeModel (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/node_modules/@loopback/sequelize/src/sequelize/sequelize.repository.base.ts:861:5)\n at async Promise.all (index 1)\n at Context. (/Users/vaibhav.kumar/Documents/projects/telescope/telescope-health-backend-api/services/snap-md-service/src/tests/acceptance/encounter-helper.controller.acceptance.ts:34:5)"

Error is in this line :

beforeEach('run before every test case', async () => {
await Promise.all(
allRepos
.map(rep => rep.syncSequelizeModel({force: true}))
.filter(Boolean),
);
});

This is complete error reason :

{ name: "SequelizeDatabaseError", parent: { errno: 1, code: "SQLITE_ERROR", sql: "CREATE TABLE IF NOT EXISTS v_users (deletedTINYINT(1) DEFAULT 0,deleted_onDATETIME,deleted_byVARCHAR(255),created_onDATETIME,modified_onDATETIME,created_byVARCHAR(255),modified_byVARCHAR(255),suffixVARCHAR(255),first_nameVARCHAR(255),last_nameVARCHAR(255),full_nameVARCHAR(255),usernameVARCHAR(255),emailVARCHAR(255),phoneVARCHAR(255),last_loginDATETIME,auth_client_idsVARCHAR(255),genderVARCHAR(255),dobDATETIME,designationVARCHAR(255),timezoneVARCHAR(255),statusVARCHAR(255),localeVARCHAR(255),user_tenant_idVARCHAR(255),role_idVARCHAR(255),tenant_idVARCHAR(255),role_typeINTEGER,nameVARCHAR(255),user_lockedTINYINT(1),npiVARCHAR(255),photo_urlVARCHAR(255),practicing_sinceDATETIME,departmentVARCHAR(255),specialityVARCHAR(255),schedulingTINYINT(1),adhocTINYINT(1),presenceINTEGER,patientPresenterOrgsINTEGER[],is_master_tenantTINYINT(1),tenant_nameVARCHAR(255),statesVARCHAR(255)[],license_idVARCHAR(255),user_configsJSON);", }, original: { errno: 1, code: "SQLITE_ERROR", sql: "CREATE TABLE IF NOT EXISTSv_users (deletedTINYINT(1) DEFAULT 0,deleted_onDATETIME,deleted_byVARCHAR(255),created_onDATETIME,modified_onDATETIME,created_byVARCHAR(255),modified_byVARCHAR(255),suffixVARCHAR(255),first_nameVARCHAR(255),last_nameVARCHAR(255),full_nameVARCHAR(255),usernameVARCHAR(255),emailVARCHAR(255),phoneVARCHAR(255),last_loginDATETIME,auth_client_idsVARCHAR(255),genderVARCHAR(255),dobDATETIME,designationVARCHAR(255),timezoneVARCHAR(255),statusVARCHAR(255),localeVARCHAR(255),user_tenant_idVARCHAR(255),role_idVARCHAR(255),tenant_idVARCHAR(255),role_typeINTEGER,nameVARCHAR(255),user_lockedTINYINT(1),npiVARCHAR(255),photo_urlVARCHAR(255),practicing_sinceDATETIME,departmentVARCHAR(255),specialityVARCHAR(255),schedulingTINYINT(1),adhocTINYINT(1),presenceINTEGER,patientPresenterOrgsINTEGER[],is_master_tenantTINYINT(1),tenant_nameVARCHAR(255),statesVARCHAR(255)[],license_idVARCHAR(255),user_configsJSON);", }, sql: "CREATE TABLE IF NOT EXISTSv_users (deletedTINYINT(1) DEFAULT 0,deleted_onDATETIME,deleted_byVARCHAR(255),created_onDATETIME,modified_onDATETIME,created_byVARCHAR(255),modified_byVARCHAR(255),suffixVARCHAR(255),first_nameVARCHAR(255),last_nameVARCHAR(255),full_nameVARCHAR(255),usernameVARCHAR(255),emailVARCHAR(255),phoneVARCHAR(255),last_loginDATETIME,auth_client_idsVARCHAR(255),genderVARCHAR(255),dobDATETIME,designationVARCHAR(255),timezoneVARCHAR(255),statusVARCHAR(255),localeVARCHAR(255),user_tenant_idVARCHAR(255),role_idVARCHAR(255),tenant_idVARCHAR(255),role_typeINTEGER,nameVARCHAR(255),user_lockedTINYINT(1),npiVARCHAR(255),photo_urlVARCHAR(255),practicing_sinceDATETIME,departmentVARCHAR(255),specialityVARCHAR(255),schedulingTINYINT(1),adhocTINYINT(1),presenceINTEGER,patientPresenterOrgsINTEGER[],is_master_tenantTINYINT(1),tenant_nameVARCHAR(255),statesVARCHAR(255)[],license_idVARCHAR(255),user_configs JSON);", parameters: { }, }

This is my complete model file :

import {model, property} from '@loopback/repository';
import {UserModifiableEntity, UserStatus} from '@sourceloop/core';
import {
AuditLogGroups,
CommonErrors,
Gender,
PatientPresenterOrg,
PATTERNS,
Presence,
RoleType,
STATES,
Title,
} from '..';
import {Locales} from '../enums';
import {Config} from '../interfaces/user-config.interface';

@model({
name: 'v_users',
description: 'The model definition for user.',
settings: {
logGroup: AuditLogGroups.UserLogs,
allowExtendedOperators: true,
},
})
export class UserView extends UserModifiableEntity {
@property({
type: 'string',
id: true,
generated: true,
description: 'The unique identifier for the user.',
})
id?: string;

@property({
type: 'string',
jsonSchema: {
enum: [Title.Dr, Title.Mr, Title.Mrs, Title.Ms],
},
required: false,
description: 'The title or prefix for the user (e.g., Mr, Mrs, Dr).',
})
suffix?: Title;

@property({
type: 'string',
jsonSchema: {
pattern: PATTERNS.alphabetsOnlyPattern,
errorMessage: CommonErrors.ALLOWED_MIDDLENAME,
},
required: true,
name: 'first_name',
description: 'The first name of the user.',
})
firstName: string;

@property({
type: 'string',
jsonSchema: {
pattern: PATTERNS.alphabetsOnlyPattern,
errorMessage: CommonErrors.ALLOWED_LASTNAME,
},
name: 'last_name',
description: 'The last name of the user.',
})
lastName: string;

@property({
type: 'string',
name: 'full_name',
description:
'The full name of the user (combination of first and last name).',
})
fullName: string;

@property({
type: 'string',
required: false,
description: 'The username of the user.',
})
username: string;

@property({
type: 'string',
jsonSchema: {
pattern: PATTERNS.emailPatter,
errorMessage: CommonErrors.ALLOWED_EMAIL,
},
required: true,
description: 'The email address of the user.',
})
email: string;

@property({
type: 'string',
jsonSchema: {
pattern: PATTERNS.phonePattern,
errorMessage: CommonErrors.ALLOWED_PHONE,
},
name: 'phone',
description: 'The phone number of the user.',
})
'phone'?: string;

@property({
type: 'date',
name: 'last_login',
postgresql: {
column: 'last_login',
},
description: "The timestamp of the user's last login.",
})
lastLogin?: Date;

@property({
type: 'string',
name: 'auth_client_ids',
description: "The client IDs associated with the user's authentication.",
})
authClientIds: string;

@property({
type: 'string',
description:
'The gender of the user (M for male, F for female, O for other).',
})
gender?: Gender;

@property({
type: 'date',
description: 'The date of birth of the user.',
})
dob: Date;

@property({
type: 'string',
name: 'designation',
description: 'The designation or job title of the user.',
})
designation?: string;

@property({
type: 'string',
name: 'timezone',
description: 'The timezone of the user.',
})
timezone?: string;

@property({
type: 'string',
name: 'status',
description: 'The status of the user (e.g., Active, Inactive).',
})
status: UserStatus;

@property({
type: 'string',
name: 'locale',
description: 'The locale or language preference of the user.',
})
locale: Locales;

@property({
type: 'string',
name: 'user_tenant_id',
description: "The ID of the user's tenant.",
})
userTenantId: string;

@property({
type: 'string',
name: 'role_id',
description: "The ID of the user's role.",
})
roleId: string;

@property({
type: 'string',
name: 'tenant_id',
description: 'The ID of the tenant associated with the user.',
})
tenantId: string;

@property({
type: 'number',
name: 'role_type',
description: "The type of the user's role.",
})
roleType: RoleType;

@property({
type: 'string',
name: 'name',
description: "The name associated with the user's role.",
})
roleName: string;

@property({
type: 'boolean',
name: 'user_locked',
description: 'Indicates whether the user is locked or not.',
})
userLocked: boolean;

@property({
type: 'string',
description: 'The NPI (National Provider Identifier) of the user.',
})
npi?: string;

@property({
name: 'photo_url',
type: 'string',
description: "The URL of the user's profile photo.",
})
imageKey?: string;

@property({
name: 'photo_url',
type: 'string',
description: "The URL of the user's profile photo.",
})
photoUrl?: string;

@property({
type: 'date',
name: 'practicing_since',
description: 'The date since which the user has been practicing.',
})
praticingSince?: string;

@property({
type: 'string',
description: 'The department or specialty of the user.',
})
department?: string;

@property({
type: 'string',
description: 'The specialty of the user.',
})
speciality?: string;

@property({
type: 'boolean',
description: 'Indicates if the user has scheduling privileges.',
})
scheduling?: boolean;

@property({
type: 'boolean',
description: 'Indicates if the user has adhoc privileges.',
})
adhoc?: boolean;

@property({
type: 'number',
description: 'The presence status of the user.',
})
presence: Presence;

@property({
type: 'array',
itemType: 'object',
name: 'patientPresenterOrgs',
description:
'Organizations associated with the user as patient presenters.',
})
patientPresenterOrgs?: PatientPresenterOrg[];

@property({
type: 'boolean',
name: 'is_master_tenant',
description: 'Indicates if the user is a master tenant.',
})
isMasterTenant?: boolean;

@property({
type: 'string',
name: 'tenant_name',
description: 'The name of the tenant associated with the user.',
})
tenantName?: string;

@property({
type: 'array',
name: 'states',
itemType: 'string',
jsonSchema: {
enum: STATES.STATE,
errorMessage: CommonErrors.ALLOWED_STATE,
},
description: 'The states associated with the user.',
})
states?: string[];

@property({
type: 'string',
name: 'license_id',
description: 'The license ID of the user.',
})
licenceId: string;

@property({
type: 'object',
name: 'user_configs',
description: 'config for individual user',
})
userConfigs?: Config;

constructor(data?: Partial) {
super(data);
}
}

export type UserViewWithRelations = UserView;

Logs

No response

Additional information

No response

Reproduction

Given all required details in description

Start at syncSequelizeModel in @loopback/sequelize, especially sequelize.repository.base.ts around line 861, and compare the behavior introduced by pull request 10284. Reproduce the issue with the UserView patientPresenterOrgs property and SQLite; done means synchronization no longer generates invalid INTEGER[] or VARCHAR(255)[] column definitions.