I have a Database that was created in a Node App with Sequilize.
I am moving the whole app from node to php 8.3 and Symfony 6.4 + doctrine.
When trying to adapt the entities to Doctrine and run the migrations Doctrine is recreating some fields and also the FK. I want to avoid this to happen but I am not sure what else I should be doing…
The Entities in the NodeApp looks like this:
module.exports = (sequelize, DataTypes) => {
class CognitoUser extends Model {
static associate (models) {
CognitoUser.belongsTo(models.UserProfile, {
foreignKey: 'userProfileUuid',
onUpdate: 'CASCADE',
onDelete: 'RESTRICT'
});
}
...
CognitoUser.init({
UserPoolId: {
type: DataTypes.STRING,
primaryKey: true,
allowNull: false
},
Username: {
type: DataTypes.STRING,
primaryKey: true,
allowNull: false
},
UserEmail: DataTypes.STRING,
UserCreateDate: DataTypes.DATE,
UserLastModifiedDate: DataTypes.DATE,
UserStatus: DataTypes.STRING,
Enabled: DataTypes.BOOLEAN,
// MixedCase for Cognito native, camelCase for everything else
customSwitchToEmailFlow: DataTypes.BOOLEAN,
cognitoSyncedAt: DataTypes.DATE,
userProfileUuid: {
type: DataTypes.UUID,
allowNull: false
}
}, { sequelize, modelName: 'CognitoUser', tableName: 'CognitoUser', timestamps: false });
return CognitoUser;
the UserProfile entity looks like this:
module.exports = (sequelize, DataTypes) => {
class UserProfile extends Model {
static associate (models) {
UserProfile.hasOne(models.CognitoUser, { foreignKey: 'userProfileUuid' }); // -> CognitoUser kriegt ne Spalte
UserProfile.belongsTo(models.CoreCustomer, {
foreignKey: 'coreCustomerUuid'
});
UserProfile.hasMany(models.UserProductRole, { foreignKey: 'userProfileUuid' });
}
}
UserProfile.init({
uuid: {
type: DataTypes.UUID,
primaryKey: true,
allowNull: false,
defaultValue: DataTypes.UUIDV4
}
}, { sequelize, modelName: 'UserProfile', tableName: 'UserProfile' });
return UserProfile;
};
The tables that are being generated by the Sqlize migration look like this:
CREATE TABLE `CognitoUser` (
`UserPoolId` varchar(255) NOT NULL,
`Username` varchar(255) NOT NULL,
`UserEmail` varchar(255) DEFAULT NULL,
`UserCreateDate` datetime DEFAULT NULL,
`UserLastModifiedDate` datetime DEFAULT NULL,
`UserStatus` varchar(255) DEFAULT NULL,
`Enabled` tinyint(1) DEFAULT NULL,
`cognitoSyncedAt` datetime DEFAULT NULL,
`userProfileUuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`customSwitchToEmailFlow` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`UserPoolId`,`Username`),
KEY `userProfileUuid` (`userProfileUuid`),
CONSTRAINT `CognitoUser_ibfk_1` FOREIGN KEY (`userProfileUuid`) REFERENCES `UserProfile` (`uuid`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
and the UserProfile Table:
CREATE TABLE `UserProfile` (
`uuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
`coreCustomerUuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`uuid`),
KEY `coreCustomerUuid` (`coreCustomerUuid`),
CONSTRAINT `UserProfile_ibfk_1` FOREIGN KEY (`coreCustomerUuid`) REFERENCES `CoreCustomer` (`uuid`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
The entities in Doctrine are defined like this:
#[ORMEntity]
#[ORMTable(name:"CognitoUser")]
#[ORMIndex(name: 'userProfileUuid', columns: ['userProfileUuid'])]
class CognitoUser
{
#[ORMId]
#[ORMColumn(type: "string", length: 255)]
private string $UserPoolId;
#[ORMId]
#[ORMColumn(type:"string", length: 255)]
private string $Username;
#[ORMColumn(type:"string", length: 255, nullable: true)]
private ?string $UserEmail = null;
#[ORMColumn(length: 255, nullable: true)]
private ?DateTime $UserCreateDate = null;
#[ORMColumn(length: 255, nullable: true)]
private ?DateTime $UserLastModifiedDate = null;
#[ORMColumn(length: 255, nullable: true)]
private ?string $UserStatus = null;
#[ORMColumn(nullable: true)]
private ?bool $Enabled = null;
#[ORMColumn(type: "boolean", nullable: true)]
private ?bool $customSwitchToEmailFlow = null;
#[ORMColumn(nullable: true)]
private ?DateTime $cognitoSyncedAt = null;
#[ORMManyToOne(targetEntity: UserProfile::class, inversedBy: 'cognitoUsers')]
#[ORMJoinColumn(
name:"userProfileUuid",
referencedColumnName: "uuid",
nullable: false,
columnDefinition: "CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL"
)]
private ?UserProfile $userProfileUuid = null;
and the UserProfile Entity looks like this:
#[ORMEntity]
class UserProfile
{
use DefaultEntityPropertiesTrait;
#[ORMId]
#[ORMColumn(
type: "string",
length: 36,
options: ["fixed"=>true],
columnDefinition: "CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL"
)]
private ?string $uuid = null;
/**
* @var Collection<int, CognitoUser>
*/
#[ORMOneToMany(targetEntity: CognitoUser::class, mappedBy: 'userProfileUuid')]
private Collection $cognitoUsers;
/**
* @var Collection<int, UserProductRole>
*/
#[ORMOneToMany(targetEntity: UserProductRole::class, mappedBy: 'userProfileUuid')]
private Collection $userProductRoles;
#[ORMManyToOne(inversedBy: 'userProfiles')]
#[ORMJoinColumn(name: "coreCustomerUuid", referencedColumnName: "uuid", nullable: true)]
private ?CoreCustomer $coreCustomerUuid = null;
public function __construct()
{
$this->cognitoUsers = new ArrayCollection();
$this->userProductRoles = new ArrayCollection();
}
I have also made this changes in doctrine.yaml file:
doctrine:
dbal:
...
default_table_options:
charset: utf8mb4
collate: utf8mb4_bin
When running the command make:migration I am getting the following which I am trying to avoid:
$this->addSql('ALTER TABLE CognitoUser DROP FOREIGN KEY CognitoUser_ibfk_1');
$this->addSql('ALTER TABLE CognitoUser CHANGE userProfileUuid userProfileUuid CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL');
$this->addSql('ALTER TABLE CognitoUser ADD CONSTRAINT FK_60B8CF0DD034F61E FOREIGN KEY (userProfileUuid) REFERENCES UserProfile (uuid)');
