Im trying to use Sequelize for MySQL queries, in my code I defined my Models and links them with his respective associations.
Trying to simulate a music playlist that have:
Artists, they can have multiples songs.
Playlist, has multiples songs too.
Songs, they are part of 1 or multiple playlists and have 1 or multiple artists.
That models are defines like this:
In each model I defined a interface with his respective fields for safe type and comfort
ARTIST
export interface IArtist {
ID_ARTIST: number,
NAME: string
}
export const Artist = seq.define<Model<IArtist>>("Artist", {
ID_ARTIST: {
primaryKey: true,
type: DataTypes.INTEGER,
allowNull: false,
autoIncrement: true
},
NAME: DataTypes.STRING(30)
}, {
timestamps: true,
createdAt: true,
updatedAt: false,
tableName: "ARTISTS",
})
PLAYLIST
export interface IPlaylist {
ID_PLAYLIST: number,
NAME: string
}
export const PlayList = seq.define<Model<IPlaylist>>("Playlist", {
ID_PLAYLIST: {
primaryKey: true,
type: DataTypes.INTEGER,
allowNull: false,
autoIncrement: true,
},
NAME: DataTypes.STRING(20)
}, {
timestamps: true,
createdAt: true,
updatedAt: false,
tableName: "PLAYLISTS"
})
SONG
export interface ISong {
ID_SONG: number,
ID_ARTIST: number,
ID_PLAYLIST: number,
DURATION: number,
NAME: string
}
export const Song = seq.define<Model<ISong>>("Song", {
ID_SONG: {
primaryKey: true,
type: DataTypes.INTEGER,
allowNull: false,
autoIncrement: true
},
ID_ARTIST: {
type: DataTypes.INTEGER,
references: {
model: Artist,
key: "ID_ARTIST"
}
},
ID_PLAYLIST: {
type: DataTypes.INTEGER,
references: {
model: PlayList,
key: "ID_PLAYLIST"
}
},
DURATION: DataTypes.INTEGER,
NAME: DataTypes.STRING(40)
}, {
timestamps: true,
updatedAt: false,
tableName: "SONGS"
})
And the relations of each table:
Artist.hasMany(Song)
PlayList.hasMany(Song)
Song.belongsTo(Artist, { foreignKey: "ID_ARTIST" })
Song.belongsTo(PlayList, { foreignKey: "ID_PLAYLIST" })
Then when I try to do a ‘findAll’ operation like this:
const query = await Song.findAll({
include: {
model: Artist,
required: true
},
where: {
ID_ARTIST: idArtist
}
})
idArtist is a function parameter -> idArtist: string
I get the next error:
sqlMessage: “Unknown column ‘Song.ArtistIDARTIST’ in ‘field list'”
And the query result from sequelize:
sql: “SELECT Song.ID_SONG, Song.ID_ARTIST, Song.ID_PLAYLIST, Song.DURATION, Song.NAME, Song.createdAt, Song.ArtistIDARTIST, Song.PlaylistIDPLAYLIST, Artist.ID_ARTIST AS Artist.ID_ARTIST, Artist.NAME AS Artist.NAME, Artist.createdAt AS Artist.createdAt FROM SONGS AS Song INNER JOIN ARTISTS AS Artist ON Song.ID_ARTIST = Artist.ID_ARTIST WHERE Song.ID_ARTIST = ‘1’;”
Why sequelize tries to get an nonexistent column like Song.PlaylistIDPLAYLIST or Song.ArtistIDARTIST when not use ‘attributes’ field in findAll or similar methods
Thanks in advance, if other info is needed tell me 🙂