knex基础使用

npm install knex --save

npm install pg
# npm install pg-native
# npm install sqlite3
# npm install better-sqlite3
# npm install mysql
# npm install mysql2
# npm install oracledb
# npm install tedious

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test',
  },
	 useNullAsDefault: true, //允许为null
});

const knex = require('knex')({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: './mydb.sqlite',
  },
	 useNullAsDefault: true,
});

db.schema.hasTable("users").then(function (exists) {
  if (!exists) {
    return db.schema
      .createTable("users", function (t) {
        t.increments("id").primary();
        t.string("username", 100);
        t.string("password", 100);
        t.string("account", 100);
        t.datetime("created_at").defaultTo(getDateTime());
      })
      .then(() => {
        console.log("users table created");
        db("users")
          .insert({
            username: "管理员",
            password:
              "8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92",
            account: "admin",
          })
          .then(() => {
            console.log("admin created");
          });
      });
  }
});

// 新增单行数据
knex('test').insert({name:'Tom',age:18});
//新增多行数据
knex('test').insert([{name:'Jerry',age:18},{name:'Spike',age:18}]);

//修改某一条数据
knex('test').where({ id: 12 }).update({title: "新标题",});

//修改满足条件的所有数据
knex('books').where('age', '<', 18).update({
  isAdult: 0,
});

//查询name为Tom的数据
knex('test').where({ name: 'Tom' });
//查询name为Tom的数据
knex('test').where('name','Tom');

//删除name为Jerry的数据
knex('test').where('name', 'Jerry').del();