Sequelize 추가공부
터미널
npm init -y
npm i express
npm i sequelize-cli
npm i mysql2
npm i sequelize
//------------------------------------------------------------------
//db생성
npx sequelize init
npx sequelize db:create
//-------------------------------------------------------------------
//모델생성
npx sequelize model:generate --name User --attributes nickname:string,password:string
npx sequelize model:generate --name Post --attributes title:string,content:text
npx sequelize model:generate --name Comment --attributes content:text
//------------------------------------------------------------------
//마이그레이션/ 외래키설정
npx sequelize migration:generate --name fk-setting
npx sequelize db:migrate
//-------------------------------------------------------------------sequelize 데이터타입
Sequelize.STRING // VARCHAR(255)
Sequelize.STRING(1234) // VARCHAR(1234)
Sequelize.STRING.BINARY // VARCHAR BINARY
Sequelize.TEXT // TEXT
Sequelize.TEXT('tiny') // TINYTEXT
Sequelize.CITEXT // CITEXT PostgreSQL and SQLite only.
Sequelize.INTEGER // INTEGER
Sequelize.BIGINT // BIGINT
Sequelize.BIGINT(11) // BIGINT(11)
Sequelize.FLOAT // FLOAT
Sequelize.FLOAT(11) // FLOAT(11)
Sequelize.FLOAT(11, 10) // FLOAT(11,10)
Sequelize.REAL // REAL PostgreSQL only.
Sequelize.REAL(11) // REAL(11) PostgreSQL only.
Sequelize.REAL(11, 12) // REAL(11,12) PostgreSQL only.
Sequelize.DOUBLE // DOUBLE
Sequelize.DOUBLE(11) // DOUBLE(11)
Sequelize.DOUBLE(11, 10) // DOUBLE(11,10)
Sequelize.DECIMAL // DECIMAL
Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)
Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6) // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY // DATE without time.
Sequelize.BOOLEAN // TINYINT(1)
Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.
Sequelize.ARRAY(Sequelize.ENUM) // Defines an array of ENUM. PostgreSQL only.
Sequelize.JSON // JSON column. PostgreSQL, SQLite and MySQL only.
Sequelize.JSONB // JSONB column. PostgreSQL only.
Sequelize.BLOB // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)
Sequelize.CIDR // CIDR datatype for PostgreSQL
Sequelize.INET // INET datatype for PostgreSQL
Sequelize.MACADDR // MACADDR datatype for PostgreSQL
Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only.
Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.
Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT') // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geometry type and SRID. PostgreSQL (with PostGIS) or MySQL only.migrations/user
await queryInterface.createTable("Users", {
userId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
nickname: {
allowNull: false,
unique: true,
type: Sequelize.STRING,
},
password: {
allowNull: false,
type: Sequelize.STRING,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
});migrations/post
await queryInterface.createTable("Posts", {
postId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
title: {
allowNull: false,
type: Sequelize.STRING,
},
content: {
allowNull: false,
type: Sequelize.TEXT,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
});migrations/comment
await queryInterface.createTable("Comments", {
commentId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
content: {
allowNull: false,
type: Sequelize.TEXT,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
});models/user.js
module.exports = (sequelize, DataTypes) => {
class User extends Model {
static associate(models) {
//유저번호가 글 작성자와 댓글 작성자에 참조로 쓰이고 있음.(1:N)//model은 단수라서
models.User.hasMany(models.Post, { foreignKey: "userId" });
models.User.hasMany(models.Comment, { foreignKey: "userId" });
}
}
User.init(
{
userId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
nickname: {
allowNull: false,
unique: true,
type: DataTypes.STRING,
},
password: {
allowNull: false,
type: DataTypes.STRING,
},
},
{
sequelize,
modelName: "User",
}
);
return User;
};models/post.js
module.exports = (sequelize, DataTypes) => {
class Post extends Model {
static associate(models) {
// 게시글 작성자는 무조건 한명
models.Post.belongsTo(models.User, { foreignKey: "userId" });
models.Post.hasMany(models.Comment, { foreignKey: "postId" });
}
}
Post.init(
{
postId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
title: {
allowNull: false,
type: DataTypes.STRING,
},
content: {
allowNull: false,
type: DataTypes.TEXT,
},
},
{
sequelize,
modelName: "Post",
}
);
return Post;
};
models/comment.js
module.exports = (sequelize, DataTypes) => {
class Comment extends Model {
static associate(models) {
models.Comment.belongsTo(models.User, { foreignKey: "userId" });
models.Comment.belongsTo(models.Post, { foreignKey: "postId" });
}
}
Comment.init(
{
commentId: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
content: {
allowNull: false,
type: DataTypes.TEXT,
},
},
{
sequelize,
modelName: "Comment",
}
);
return Comment;
};fk-setting
//up
//게시글 작성자 외래키
await queryInterface.addColumn("Posts", "userId", {
type: Sequelize.INTEGER,
});
await queryInterface.addConstraint("Posts", {
fields: ["userId"],
type: "foreign key",
name: "Users_Posts_id_fk",
references: {
table: "Users",
field: "userId",
},
onDelete: "cascade",
onUpdate: "cascade",
});
// 댓글 작성자 외래키
await queryInterface.addColumn("Comments", "userId", {
type: Sequelize.INTEGER,
});
await queryInterface.addConstraint("Comments", {
fields: ["userId"],
type: "foreign key",
name: "Users_Comments_id_fk",
references: {
table: "Users",
field: "userId",
},
onDelete: "cascade",
onUpdate: "cascade",
});
// 댓글 어느 게시글에 달렸는지 외래키
await queryInterface.addColumn("Comments", "postId", {
type: Sequelize.INTEGER,
});
await queryInterface.addConstraint("Comments", {
fields: ["postId"],
type: "foreign key",
name: "Posts_Comments_id_fk",
references: {
table: "Posts",
field: "postId",
},
onDelete: "cascade",
onUpdate: "cascade",
});//down
await queryInterface.removeColumn("Posts", "userId");
await queryInterface.removeColumn("Comments", "userId");
await queryInterface.removeColumn("Comments", "postId");
개발자 꿈나무