knex examples to replace sequelize.
first create knex user:
DROP USER IF EXISTS ‘knexUser’@‘localhost’ ;
CREATE USER ‘knexUser’@‘localhost’ IDENTIFIED BY ‘knexPassword’;
GRANT ALL ON `comptonTransAnlys`.* TO ‘knexUser’@‘localhost’
IDENTIFIED BY ‘knexPassword’ WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON `comptonTransAnlys`.* TO ‘knexUser’@‘localhost’ ;
- mariadb —host=localhost —user=knexUser —password=knexPassword comptonTransAnlys ;
// https://zetcode.com/javascript/knex/
// mariadb —host=localhost —user=knexUser —password=knexPassword knexDb ;
const knexConnectOptions = {
client: ‘mysql’,
debug: true,
connection: {
host : ‘localhost’,
port : 3306,
user : ‘knexUser’,
password : ‘knexPassword’,
database : ‘knexDb’
}
};
const knex = require(‘knex’)(knexConnectOptions);
knex.raw(“SELECT VERSION”) .then ( (version) => console.log((version00)) ) .catch((err) => { console.log( err); throw err }) .finally(() => { knex.destroy(); });
knex.schema .createTable(‘cars’, (table) => { table.increments(‘id’) ; table.string(‘name’) ; table.integer(‘price’) ; }) .then(() => console.log(“table created”)) .catch((err) => { console.log(err); throw err }) .finally(() => { knex.destroy(); process.exit(); });
const cars = [
{ name: ‘Audi’, price: 52642 },
{ name: ‘Mercedes’, price: 57127 },
{ name: ‘Skoda’, price: 9000 },
{ name: ‘Volvo’, price: 29000 },
{ name: ‘Bentley’, price: 350000 },
{ name: ‘Citroen’, price: 21000 },
{ name: ‘Hummer’, price: 41400 },
{ name: ‘Volkswagen’, price: 21600 },
]
knex.from(‘cars’) // or just knex(‘cars’) .insert(cars) .then((id) => console.log(“data inserted: “ + id ) /* note display of returned ID! */ .catch((err) => { console.log(err); throw err }) .finally(() => { knex.destroy(); process.exit(); });
knex.from(‘cars’) // or just knex(‘cars’) .select(“*”) .where (true) // optional! .then((rows) => { for (row of rows) { console.log(`${row[‘id’]} ${row[‘name’]} ${row[‘price’]}`); } }).catch((err) => { console.log( err); throw err }) .finally(() => { knex.destroy(); process.exit(); });
knex.from(‘cars’) // or just knex(‘cars’) .select(“name”, “price”).where(‘price’, ‘>’, ‘50000’) .where (true) .then((rows) => { for (row of rows) { console.log(`${row[‘name’]} ${row[‘price’]}`); } }) .catch((err) => { console.log( err); throw err }) .finally(() => { knex.destroy(); process.exit(); });
// mariadb —host=localhost —user=knexUser —password=knexPassword knexDb ;
const knexConnectOptions = {
client: ‘mysql’,
debug: true,
connection: ‘mysql://knexUser:knexPassword@localhost:3306/knexDb’
};
const knex = require(‘knex’)(knexConnectOptions);
knex(‘cars’)
.where ({‘id’: 6 }) // OR .where (‘id’ , ‘=’, 6)
.update({ name : ‘Toyota’}) /* notice {} */
.catch((err) => { console.log( err); throw err })
.finally(() => {
knex.destroy();
process.exit();
});
// delete!
knex(‘cars’)
.where (‘id’ , ‘=’, 6)
.del()
.catch((err) => { console.log( err); throw err })
.finally(() => {
knex.destroy();
process.exit();
});
// short connection string:
const knexConnectOptions = {
client: ‘mysql’,
connection: ‘mysql://knexUser:knexPassword@localhost:3306/knexDb’
};
const knex = require(‘knex’)(knexConnectOptions);
knex.from(‘cars’) .select(“name”, “price”) .where(‘id’, ‘=’, ‘6’) .then((rows) => { for (row of rows) { console.log(`${row[‘name’]} ${row[‘price’]}`); } }) .catch((err) => { console.log( err); throw err }) .finally(() => { knex.destroy(); process.exit(); });
