I just created a node application using knex.js and I need help on how to create a migration based on this sql dump. Would be really happy if someone could help me that has experience with Knex.
I don't ask for the whole thing, I just need some help on how to get started. I'm still really bad at javascript and I feel stuck :(
--
-- Table structure for table `role`
--
DROP TABLE IF EXISTS `role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`authority` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `authority` (`authority`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `role`
--
LOCK TABLES `role` WRITE;
/*!40000 ALTER TABLE `role` DISABLE KEYS */;
INSERT INTO `role` VALUES (1,'ROLE_ADMIN'),(2,'ROLE_USER');
/*!40000 ALTER TABLE `role` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'$2a$10$BCYu4wAXWMDXpjnqb9PdSeNi2lUtqRCHvUYv6oWxaOKjEgiJN4Sz2','Admin'),(2,'$2a$10$Pv8Y8BDxeiSbg6yb/CMdrOD0z2Z3FZb3R/DfwW2zGXIEFvAbyQp7y','Rob7');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user_role`
--
DROP TABLE IF EXISTS `user_role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_role` (
`role_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`role_id`,`user_id`),
KEY `role_id` (`role_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user_role`
--
LOCK TABLES `user_role` WRITE;
/*!40000 ALTER TABLE `user_role` DISABLE KEYS */;
INSERT INTO `user_role` VALUES (1,1),(2,2);
/*!40000 ALTER TABLE `user_role` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-03-08 12:00:23
Here is how I started:
exports.up = function(knex, Promise) {
return Promise.all([
knex.schema.createTable('role', function(table){
table.string('username');
table.string('password');
table.timestamps();
}),
knex.schema.createTable('user', function(table){
table.string('username');
table.string('password');
table.timestamps();
}),
knex.schema.createTable('user_role', function(table){
table.string('username');
table.string('password');
table.timestamps();
})
])
};
Alright, so first let's take a look at the role table. First thing we have is a primary key 'id' which auto-increments. So, the corresponding knex line would be this:
t.increments('id').primary().unsigned();
Then, we have authority. This is a unique varchar, which corresponds to a knex string. The equivalent line would be this:
t.string('authority', 255).unique();
Ok, now on to the users table. We have a primary, unique, auto-incrementing ID as before, so we can copy and paste the ID line from above. Then, we have username and password fields, which are NOTNULL varchars max 255, and username being unique. We can represent this using the following knex lines:
t.string('username', 255).notNull().unique();
t.string('password', 255).notNull();
Now, we just need to do our lines for the user_role table. First, we need to add an ID column as before. Then, we can add our foreign key columns like this:
table.integer('user_id').unsigned().references('user.id');
table.integer('role_id').unsigned().references('role.id');
When we put it all together, we'll get this:
exports.up = function(knex, Promise) {
return knex.schema.createTable('role', function(table){
table.increments('id').primary().unsigned();
table.string('authority', 255).unique();
table.timestamps();
}).createTable('user', function(table){
table.increments('id').primary().unsigned();
table.string('username', 255).notNull().unique();
table.string('password', 255).notNull();
table.timestamps();
}).createTable('user_role', function(table){
table.increments('id').unsigned().primary();
table.integer('user_id').unsigned().references('user.id');
table.integer('role_id').unsigned().references('role.id');
table.timestamps();
})
])
};
The above code will create all the tables, columns, etc, however it will not insert values. We will need to create a seedfile for that. We can create a seedfile to insert values, which will look like this:
exports.seed = function(knex, Promise) {
// Deletes ALL existing entries
return knex('users').del()
.then(function () {
// Inserts seed entries
return knex('users').insert([
{
id: 1,
username: "Admin",
password: "$2a$10$BCYu4wAXWMDXpjnqb9PdSeNi2lUtqRCHvUYv6oWxaOKjEgiJN4Sz2"
},
{
id: 2,
username: "Rob7",
password: "$2a$10$Pv8Y8BDxeiSbg6yb/CMdrOD0z2Z3FZb3R/DfwW2zGXIEFvAbyQp7y"
}
]);
});
};
You'll want to create a new seedfile for each table, following the format above. I believe this answer should cover everything, but if you have any questions let me know!
To roll back, we need to add an exports.down method to our migration. It should look like this:
exports.down = function(knex, Promise) {
return Promise.all([
knex.schema.dropTable("role");
knex.schema.dropTable("user");
knex.schema.dropTable("user_role");
]);
};