node的数据库管理方案knex使用手册

丘友樵

2023-12-01

定义

knex: 是一个查询构建器，可以用于构建和执行数据库查询，支持多种数据库（包括MySQL），提供了灵活的查询构建API，能够方便地进行复杂的查询操作。

knex的使用

首先需要通过npm安装knex库，可以使用以下命令：

npm install knex mysql2 --save

安装完成后，在代码中引入knex库：

const knex = require('knex');

接下来需要使用knex来连接数据库并执行查询操作。下面是一个简单的示例，演示如何连接到MySQL数据库并查询users表中的所有记录：

const knex = require('knex')({
  client: 'mysql2', // 当然还可以配置其他数据库驱动，pg、sqlite3 等，对于需要安装相关驱动
  connection: {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
  }
});

knex('users').select('*').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

在这个示例中，我们首先使用knex()函数创建一个MySQL连接，其中传入了一个包含数据库连接信息的对象。然后使用knex(‘users’)来指定要查询的表名，使用select(‘*’)来查询所有列的数据。最后通过.then()来获取查询结果，.finally()来释放连接。

在实际使用中，我们还可以使用knex来执行更复杂的查询操作，例如JOIN操作、分组和聚合操作等，具体的操作方法可以参考knex的文档和示例代码。

创建表

integer()：整数列。
float()：浮点数列。
decimal()：十进制数列。
boolean()：布尔值列。
date()：日期列。
dateTime()：日期时间列。
time()：时间列。
timestamp()：时间戳列。
string()：字符串列。
text()：文本列。
json()：JSON 列。
binary()：二进制数据列。
uuid()：UUID 列。

下列的type就是就是上述类型：

knex.schema.createTable('table_name', function(table) {
  table.[type]('column_name');
});

查询相关

查询表中所有数据

knex('table_name').select('*').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询表中特定列的数据

knex('table_name').select('column1', 'column2').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询表中符合条件的数据

knex('table_name').where('column_name', '=', 'value').select('*').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询表中前N条数据

knex('table_name').limit(N).select('*').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询表中特定列的平均值

knex('table_name').avg('column_name').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询表中不同列的数据并按照指定列排序

knex('table_name').select('column1', 'column2').orderBy('column1', 'asc').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

查询两个表中的数据并进行JOIN操作

knex('table1').join('table2', 'table1.column1', '=', 'table2.column2').select('*').then(rows => {
  console.log(rows);
}).finally(() => {
  knex.destroy();
});

删除相关

删除表中所有数据

knex('table_name').del().then(() => {
  console.log('All rows deleted');
}).finally(() => {
  knex.destroy();
});

删除表中符合条件的数据

knex('table_name').where('column_name', '=', 'value').del().then(() => {
  console.log('Rows deleted');
}).finally(() => {
  knex.destroy();
});

删除表中前N条数据

knex('table_name').orderBy('column_name', 'asc').limit(N).del().then(() => {
  console.log('Rows deleted');
}).finally(() => {
  knex.destroy();
});

联表删除

knex('table1')
  .join('table2', 'table1.column_name', '=', 'table2.column_name')
  .where('table1.column_name', '=', 'value')
  .del()
  .then(() => {
    console.log('Rows deleted');
  })
  .finally(() => {
    knex.destroy();
  });

在这个示例中，table1和table2是要连接的两个表，column_name是要连接的列名。在where方法中，指定了要删除的行所需要满足的条件。

需要注意的是，进行连表删除操作时，需要确保在删除表中的行时不会违反数据库中的完整性约束。

新增相关

插入单个数据行

knex('table_name')
  .insert({
    column1: 'value1',
    column2: 'value2',
    column3: 'value3',
  })
  .then(() => {
    console.log('Row inserted');
  })
  .finally(() => {
    knex.destroy();
  });

插入多个数据行：

const rows = [
  { column1: 'value1', column2: 'value2', column3: 'value3' },
  { column1: 'value4', column2: 'value5', column3: 'value6' },
  { column1: 'value7', column2: 'value8', column3: 'value9' },
];

knex('table_name')
  .insert(rows)
  .then(() => {
    console.log('Rows inserted');
  })
  .finally(() => {
    knex.destroy();
  });

插入数据行并返回自增ID：

knex('table_name')
  .insert({
    column1: 'value1',
    column2: 'value2',
    column3: 'value3',
  }, 'id') // 指定返回的自增ID列名
  .then((ids) => {
    console.log(`Inserted row with ID: ${ids[0]}`);
  })
  .finally(() => {
    knex.destroy();
  });

使用Knex的子查询插入数据：

knex('table1')
  .insert({
    column1: 'value1',
    column2: knex('table2').select('column_name').where('condition', 'value'),
  })
  .then(() => {
    console.log('Row inserted');
  })
  .finally(() => {
    knex.destroy();
  });

更新相关

更新单个数据行

knex('table_name')
  .where('id', '=', 1) // 指定更新条件
  .update({
    column1: 'new_value1',
    column2: 'new_value2',
    column3: 'new_value3',
  })
  .then(() => {
    console.log('Row updated');
  })
  .finally(() => {
    knex.destroy();
  });

更新多个数据行：

const rows = [
  { id: 1, column1: 'new_value1', column2: 'new_value2', column3: 'new_value3' },
  { id: 2, column1: 'new_value4', column2: 'new_value5', column3: 'new_value6' },
  { id: 3, column1: 'new_value7', column2: 'new_value8', column3: 'new_value9' },
];

knex('table_name')
  .update(rows)
  .then(() => {
    console.log('Rows updated');
  })
  .finally(() => {
    knex.destroy();
  });

更新数据行并返回更新行数

 knex('table_name')
  .where('id', '=', 1)
  .update({
    column1: 'new_value1',
    column2: 'new_value2',
    column3: 'new_value3',
  })
  .then((rowCount) => {
    console.log(`${rowCount} rows updated`);
  })
  .finally(() => {
    knex.destroy();
  });

koa配合knex的使用

const Koa = require('koa');
const Router = require('koa-router');
const Knex = require('knex');

const knex = Knex({
  client: 'mysql2',
  connection: {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'database_name',
  },  
});

const app = new Koa();
const router = new Router();

// 测试有么连接到数据库
knex.raw('SELECT 1')
  .then(() => console.log('Connection successful'))
  .catch((err) => console.error('Connection failed', err));


router.get('/users', async (ctx) => {
  const users = await knex('users').select('*');
  ctx.body = users;
});

router.post('/users', async (ctx) => {
  const { name, email } = ctx.request.body;
  await knex('users').insert({ name, email });
  ctx.body = { message: 'User created' };
});

router.put('/users/:id', async (ctx) => {
  const { id } = ctx.params;
  const { name, email } = ctx.request.body;
  await knex('users').where('id', '=', id).update({ name, email });
  ctx.body = { message: `User ${id} updated` };
});

router.delete('/users/:id', async (ctx) => {
  const { id } = ctx.params;
  await knex('users').where('id', '=', id).del();
  ctx.body = { message: `User ${id} deleted` };
});

app.use(router.routes());
app.use(router.allowedMethods());

app.listen(3000, () => {
  console.log('Server started on port 3000');
});

封装一个简单实用的方法

async function createOrUpdateTable(tableName, fields) {
  const tableExists = await knex.schema.hasTable(tableName);
  if (!tableExists) {
    await createTable(tableName, fields);
  } else {
    await updateTable(tableName, fields);
  }
}

async function createTable(tableName, fields) {
  await knex.schema.createTable(tableName, (table) => {
    table.increments();
    Object.entries(fields).forEach(([fieldName, type]) => {
      table[type](fieldName).notNullable();
    });
    table.timestamps(false, true);
  }).then(() => {
    console.log(`${tableName} table created`);
  }).catch((err) => {
    console.log(err);
    throw err;
  });
}

async function updateTable(tableName, newFields) {
  const currentFields = await knex(tableName).columnInfo();
  const fieldsToAdd = {};
  const fieldsToDelete = [];
  
  // 找到需要添加的新字段
  Object.entries(newFields).forEach(([fieldName, type]) => {
    if (!currentFields[fieldName]) {
      fieldsToAdd[fieldName] = type;
    }
  });
  
  // 找到需要删除的旧字段
  Object.keys(currentFields).forEach((fieldName) => {
    if (!newFields[fieldName]) {
      fieldsToDelete.push(fieldName);
    }
  });

  // 向表中添加新字段
  if (Object.keys(fieldsToAdd).length > 0) {
    await knex.schema.alterTable(tableName, (table) => {
      Object.entries(fieldsToAdd).forEach(([fieldName, type]) => {
        table[type](fieldName).defaultTo(null);
      });
    }).then(() => {
      console.log(`New fields added to ${tableName} table`);
    }).catch((err) => {
      console.log(err);
      throw err;
    });
  }
  
  // 从表中删除旧字段
  if (fieldsToDelete.length > 0) {
    await knex.schema.alterTable(tableName, (table) => {
      fieldsToDelete.forEach((fieldName) => {
        table.dropColumn(fieldName);
      });
    }).then(() => {
      console.log(`Fields deleted from ${tableName} table`);
    }).catch((err) => {
      console.log(err);
      throw err;
    });
  }
}

// 调用createOrUpdateTable函数创建或更新users表格
createOrUpdateTable('users', {
  name: 'string',
  email: 'string',
});

// 调用createOrUpdateTable函数创建或更新users表格
createOrUpdateTable('users', {
  name: 'string',
  email: 'string',
  address: 'string',
  age: 'integer',
});

连接数据库可能遇见的bug

没有加权限

Connection failed Error: Access denied for user 'root'@'xx.xx.xx.xx' (using password: YES)

解决方案:

GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'xx.xx' IDENTIFIED BY 'password';

之后一定要刷新权限！！！

FLUSH PRIVILEGES;

欢迎在评论区留言，如果有相关数据库bug会帮忙解决完善文章内容~

node的数据库管理方案knex使用手册

定义

knex的使用

创建表

查询相关

删除相关

新增相关

更新相关

koa配合knex的使用

封装一个简单实用的方法

连接数据库可能遇见的bug

相关阅读

相关文章

相关问答

相关文档