Today we'll just dump a huge load of knex recipes in order to help you to make good use of the best parts of such marvelous query builder and also to overcome the not so great parts. Ready, set, go!
01- installing it on your project
02- creating a simple migration file to define a table
03- creating a simple migration file to insert data
04- creating a simple knex hello world
05- creating a simple seed file (and understand when and why to use seed files)
06- adding a column to a table
07- removing a column from a table
08- creating tables with foreign keys
09- performing various operations with just one migration file
10- using knex.raw() to create views
11- dropping vews
12- modifying tables which are member of views
13- making sure you'll never face data modelling issues
I'll assume that you already know how to write simple nodejs applications and are comfortable with npm.
01 - Installing it on your project
If you have none you always can open console and create a folder and:cd myproject npm init
After that, you can do the following:
npm intall knex --save npm install -g knex knex init
Your package.json will look like this:
{
"name": "myproject",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"knex": "^0.12.2"
}
}
Once those steps are fullfiled, your project will count on a new file called knexfile.js. He's useful to set up our database access on various environments.
On this tutorial we'll assume that your database is a reliable postgresql so the knexfile.js may look like this:
// Update with your config settings.
module.exports = {
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3'
}
},
staging: {
client: 'postgresql',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
production: {
client: 'postgresql',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
}
};
As suggested, change this file as needed to connect with your database.
On your nodejs app entry point (usually the index.js as described in package.json) you can do the following:
var knexfile = require("./knexfile")
var knexcfg = process.env.NODE_ENV == "production" ? "production" : "development"
var knex = require("knex")(knexfile[knexcfg])
Remember that the knex instance holds the connection pool therefore shall not be instantiated again but shared across the app.
02- creating a simple migration file to define a table
On the command line, do the folloowing:knex migrate:make initial_table
The expected output is something (but for sure not equal) like this:
Created Migration: /home/sombriks/Downloads/myproject/migrations/20161008000211_initial_table.js
Knex will add this timestamp to the migration file. Open it and let's add code on it:
exports.up = function(knex, Promise) {
return knex.schema.createTable("goodies", function(table) {
table.increments("idgoodies");
table.string("descriptiongoodies").notNullable();
table.decimal("pricegoodies").defaultTo(1.99);
});
};
exports.down = function(knex, Promise) {
return knex.schema.dropTable("goodies");
};
The sole most important thing about migrations file is: what up does, down must undoes.
Once you created this migration file, you can use the following command:
knex migrate:latest
This command will run the up part and create the table on your database.
The expected output is something like this:
Using environment: development Batch 1 run: 1 migrations /home/sombriks/Downloads/myproject/migrations/20161008000211_initial_table.js
All documentation about knex migrations can be found here.
03- creating a simple migration file to insert data
On the command line, do the folloowing:knex migrate:make initial_data
The output is pretty much what we seen on step 02, open this new file and do the inserts:
exports.up = function(knex, Promise) {
return knex("goodies").insert([
{descriptiongoodies: "banana"},
{descriptiongoodies: "canned soup"},
{descriptiongoodies: "bullets"}
]);
};
exports.down = function(knex, Promise) {
return knex("goodies").del();
};
Don't forget to run the knex:migrate:latest to add this change to the database.04- creating a simple knex hello world
On the previous seen sample entry point, you'll be able to do the following:var knexfile = require("./knexfile")
var knexcfg = process.env.NODE_ENV == "production" ? "production" : "development"
var knex = require("knex")(knexfile[knexcfg])
knex("goodies").select().where("idgoodies",1).then(function(ret){
console.log(ret[0])
})
By running it either with node index.js the expected output is somethig like this:
{ idgoodies: 1,
descriptiongoodies: 'banana',
pricegoodies: '1.99' }
05- creating a simple seed file (and understand when and why to use seed files)
Create a seed is pretty much like create a migration file.The key difference is the seed is meant to perform an idempotent operation o the database.
Do NOT use seed files to alter the database structure permanently. Use migrations to do that.
Do the following command to create a seed file:
knex seed:make 010-my_seed_fileThe expected output follows:
Using environment: development Created seed file: /home/sombriks/Downloads/myproject/seeds/010-my_seed_file.jsNow let's add some code to this seed:
exports.seed = function(knex, Promise) {
// alter the prices to 2.99
return knex('goodies').update({
pricegoodies:2.99
});
};
Run the seeds is very similar to run migrations:
knex seed:run
The expected outpuit follows:
Using environment: development Ran 1 seed files /home/sombriks/Downloads/myproject/seeds/010-my_seed_file.js
As discussed before there are specificy situations that may need a seed file.
One can use it, for example, to save on a status table the timestamp of the latest app startup.
06- adding a column to a table
Make a migrate file as seen in the previous steps and a code like this:exports.up = function(knex, Promise) {
return knex.schema.table("goodies", function(table){
table.integer("instockgoodies");
});
};
exports.down = function(knex, Promise) {
return knex.schema.table("goodies", function(table){
table.dropColumn("instockgoodies");
})
};
Do not misuse knex.schema.createTable with knex.schema.table.07- removing a column from a table
Do the same as seen on step 06 but the dropColumn part will be in the up function.08- creating tables with foreign keys
Create the migrate file as seen previously and do something like this:exports.up = function(knex, Promise) {
return knex.schema.createTable("goodiesreview", function (table){
table.increments("idgoodiesreview");
table.text("descriptiongoodiesreview");
table.integer("idgoodies").notNullabel().references("goodies.idgoodies");
// longer form:
// table.integer("idgoodies").notNullable().references("idgoodies").inTable("goodies");
});
};
exports.down = function(knex, Promise) {
return knex.schema.dropTable("goodiesreview");
};
Now the idgoodies column at goodiesreview will be a forign key pointing to idgoodies at goodies.Ando no, they are not forced to share the same column name. It's just this way to allow me to use natural joins, but not everyone have a fond on it.
09- performing various operations with just one migration file
Do as you did in the previous steps and:exports.up = function(knex, Promise) {
return knex.schema.createTable("order", function(table){
table.increments("idorder");
table.timestamp("dtcreationorder").defaultTo(knex.fn.now());
table.string("detailsorder");
}).createTable("goodies_order", function (table){
table.integer("idgoodies").notNullable().references("goodies.idgoodies");
table.integer("idorder").notNullable().references("order.idorder");
table.integer("quantitygoodies").defaultTo(1);
table.primary(["idgoodies","idorder"]);
});
};
exports.down = function(knex, Promise) {
return knex.schema
.dropTable("goodies_order")
.dropTable("order");
};
Do not forget about the knex migrate:latest thing.
10- using knex.raw() to create views
On the present day, thare is no code sugar on knex query and schema builder for views.It does not means, however, that we are unable to manage our views using knex migrations.
All you need is to use the powers of knex.raw() facility.
Create another migration and:
exports.up = function(knex, Promise) {
return knex.raw("create view vw_order as select * from \"order\" " +
"natural join goodies_order natural join goodies");
};
exports.down = function(knex, Promise) {
return knex.raw("drop view vw_order");
};
In this particular case we had to escape the table name.
11- dropping vews
See the down function at step 10.
12- modifying tables which are member of views
On popstgresql you will not be allowed to drop columns if they are figuring on any view.In order to be able to add/remove columns on tables which are figuyring on views:
- create a migrate file to drop the view
- create the migrate which actually changes the table
- create another migrate to bring the view back
13- making sure you'll never face data modelling issues
Hey... There is no such thing. There's no silver bullet.Once you start developing/running your app, it will face an organic grow. That's ok, but you have to face, for example, the consequences of manage data structure alterations in productions environments.
When developing, we always can do a knex migrate:rollback and fix whats wrong.
This is not that simple in production mode.
Instead of do rollbacks on production, you will need to create new migrations to fix what's wrong.
And not every migrate will be small and kind. Some might need heavy use of knex.raw or any other sort of trick.
As long as you can use your migrations to replicate any point in time of your data schema, you'll have a good chance to triumph.
Good luck.
Nenhum comentário :
Postar um comentário