62
loading...
CRUD operation with knex & mysql in node.js
Knex.js is a SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full-featured query and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects.
Create a folder then enter to it and then type the command bellow:
npm init -y
npm i express knex mysqlCreate a file call
app.js in your project root and write these codes bellow:const express = require('express')
const app = express()
app.get('/', (req, res) => {
res.send('app running')
})
app.listen(5000, () => {
console.log('Server running on http://localhost:50000');
})Now, create a file for database called
db.js and add these codes bellow. You need to replace the user(if any, by default it's root), password (default: empty string), database name.const knex = require("knex");
const db = knex({
client: "mysql",
connection: {
host: "localhost",
user: "root",
password: "",
database: "databasename",
},
});
module.exports = db;Now in your database called(databasename, you might replace it) create a table called users. You can create the table by running these command:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
isAdmin TINYINT DEFAULT 0,
PRIMARY KEY(id)
);Ok, everything is done 😀. Now it's time to give some shots 🦵. First of all import db from our
So, let's create a user from our app. For this make a route called '/users/create' bellow our index route. When you will visit the link http://localhost:5000/users/create it will create insert a user to our users table in the database.
db.js file. So, let's create a user from our app. For this make a route called '/users/create' bellow our index route. When you will visit the link http://localhost:5000/users/create it will create insert a user to our users table in the database.
const db = require('./db.js');
app.get('/users/create', async (req, res) => {
const userId = await db('users').insert({
name: "John Doe"
})
res.json({
message: "User created",
userId
})
})app.get('/users', async (req, res) => {
let users = await db('users').select()
users = users.map(user => ({...user});
res.json({
users
})
})app.get('/users/:id', async (req, res) => {
const { id } = req.params;
await db('users')
.where('id', id)
.update({
name: "Saikat"
});
res.send('User updated');
})app.get('/users/delete/:id', async (req, res) => {
const { id } = req.params;
await db('users').where('id', id').del();
res.send('User deleted');
})const express = require('express')
const app = express()
app.get('/', (req, res) => {
res.send('app running')
})
// Create a user
app.get('/users/create', async (req, res) => {
const userId = await db('users').insert({
name: "John Doe"
})
res.json({
message: "User created",
userId
})
})
// Query all users
app.get('/users', async (req, res) => {
let users = await db('users').select()
users = users.map(user => ({...user}); // it's needed bcz it return something called RawDataPacket
res.json({
users
})
})
// Update user
app.get('/users/:id', async (req, res) => {
const { id } = req.params;
await db('users')
.where('id', id)
.update({
name: "Saikat"
});
res.send('User updated');
}
// Delete a user
app.get('/users/delete/:id', async (req, res) => {
const { id } = req.params;
await db('users').where('id', id').del();
res.send('User deleted');
})
app.listen(5000, () => {
console.log('Server running on http://localhost:50000');
})62