LHL-Knex.js and PstgreSQL

You need to spend time to practice sql after bootcamp!!!
Node-postgres

PostgreSQL DESCRIBE TABLE using psql: \d table_name
create table example

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

const pg = require('pg') //set up pg module
const client = new pg.Client(config) //create new client module
client.connect()
performQueries(client);

Calling client.connect with a callback

const { Client } = require('pg')
const client = new Client()
client.connect((err) => {
  if (err) {
    console.error('connection error', err.stack)
  } else {
    console.log('connected')
  }
})

标准 SQL 中，字符串使用的是单引号
sql防注入攻击：例如 lastName = '): DROP TABLE ;--; 将抹去整个数据库
sanitize the input:

//括号里是'' + ' + ", "+'+'+"
lastName = lastName.replace("'","''")
//另一种方法
const inserQuery = `INSERT INTO people (first_name, last_name) 
								VALUES($1::text, $2::text);`;
db.query(insertQuery, [firstName, lastName]
还例如
const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'

Knex.jsKnex is an SQL query builder for Node.js
https://devhints.io/knex
this is Knex cheatsheet

knex init
npm install pg
knex migrate:make create_pastries //create a new database 'migration'
//migration will be including;
exports.up = function(knex, Promise) {
knex.schema.createTable('pastries, (table) => {
table.increments();
  table.string('name');
  table.integer('flakiness');
  table.string('filling');
  table.timestamps();
};
exports.down = function(knex, Promise) {
return knex.schema.dropTable('pastries');
};
knex migrate:latest
const env = process.env.NODE_ENV; //development envire
const knexConfig = require('./knexfile');
const knex = require('knex')(knexConfig[env]); // start the connection
const newPastry = {
name: "strudel",
flakiness: 6,
filling: "Apple"
}
knex('pastries').insert(newPastry, '*'). asCallback((err,res) => { 
console.log(res);
});
//以下是callback的写法
knex('pastries').asCallback((err, rows) => {
rows.forEach(row => {
console.log ("Name: ", row.name);
console.log("Filling: ", row.filling);
console.log("Flakiness: ", "*".repeat(row.flakiness));
});
knex.destroy() // stop the connection
//以下是then 的写法，需要.then ().catch(err)
knex('pastries').then(rows) => {
rows.forEach(row => 
console.log ("Name: ", row.name);
console.log("Filling: ", row.filling);
console.log("Flakiness: ", "*".repeat(row.flakiness));
})
.catch(err => {
console.error("WTF;", err)
})

});

echo NODE_ENV //check development enviroment