使用nodejs保存在SQL Server中

Question

我正在尝试使用nodejs，Express，SQL Server作为数据服务器进行登录/寄存器。 登录/寄存器有效，但我不知道如何保存由ExpressSession创建的会话。 创建了cookie，但（我认为）它并没有保存。

index.js-服务器 -

const express = require('express');
var bodyParser = require('body-parser')
const sql = require('mssql');
const config = require('./src/config');
var crypto = require('crypto');

const path = require('path');
const queries = require('./src/queries')
var cookieParser = require('cookie-parser');



var app = express();
const server = require('http').createServer(app);

app.use(bodyParser.urlencoded({ extended: false }))
app.use('/js', express.static(__dirname + '/../public/js'));
app.use("/css", express.static(__dirname + '/../public/css'));

app.use(cookieParser());
app.use(bodyParser.json());
const MSSQLStore = require('connect-mssql-v2');
const session = require('express-session');

const options = {
    table: 'Sessions',
    autoRemove: true,
    useUTC: true
}
const store = new MSSQLStore(queries.sqlConfig, options);

app.use(session({
    store: store,
    secret: 'secret',
    resave: false,
    saveUninitialized: false,
    cookie: {
        maxAge: 1000 * 60 * 60 * 2,
        path: '/',

    }
}))

let res = '';
const io = require("socket.io")(server, {
    cors: {
        origin: "*",
        methods: ["GET", "POST"]
    }
});
var c;

io.on('connection', socket => {
    socket.on('signUp', () => {
        socket.emit('serverValidation', res);
        res = '';
    })
})

function deleteUser(username) {
    q = `DELETE FROM Users WHERE username='${username}'`;
    c.query(q, function(error, result, fields) {
        if (error != null && error != undefined) {
            console.log(error);
        } else {


        }
    })
}

app.post('/signUp', async(request, response) => {
    c = await queries.testConnection(sql);
    const username = request.body.username;
    const email = request.body.email;
    const password = request.body.password;
    const cPassword = request.body.cPassword;

    const fName = request.body.firstName;
    const lName = request.body.lastName;
    const age = request.body.age;


    const city = request.body.city;
    const workIn = request.body.workIn;
    const street = request.body.Street;
    const number = request.body.number;
    console.log(request.body);
    var hashPassword = crypto.createHash('sha256').update(password).digest('base64');

    var q = `SELECT * FROM Users WHERE username='${username}' OR email='${email}'`;
    console.log(q);
    var flag = true;
    try {
        await c.query(q, async(error, result, field) => {
            if (error != null && error != undefined) {
                console.log('chupq se na purvo q');
                flag = false;
            } else {
                console.log('ne se chupq na purvo q');
            }
        })

        q = `SELECT * FROM Companies WHERE CompanyName=N'${workIn}';`;
        console.log(q);
        await c.query(q, async(err, res, f) => {
            if (err != null && err != undefined) {
                flag = false;
                console.log(err);
                console.log('tuk sum');
            } else {

            }
        })

        if (flag) {
            q = `INSERT INTO Users (username, email, password) VALUES ('${username}','${email}','${hashPassword}');`
            await c.query(q, async(error, result, fields) => {
                if (error != null && error != undefined) {
                    console.log(error);
                } else {


                }
            })

            q = `INSERT INTO User_Data (username, firstName, lastName,city,[Street Address],age,workIn) VALUES ('${
                username}',N'${fName}',N'${lName}',N'${city}',N'${number+" " +street}', '${age}',N'${workIn}');`;
            await c.query(q, async(err, res, f) => {
                if (err != null && err != undefined) {
                    console.log(err);
                    console.log('ne e dobaven')
                    res = 'username'
                    deleteUser(username);
                    response.sendFile(path.join(__dirname + '/../public/html/signUp.html'));
                    response.end();
                } else {
                    res = '0';
                    console.log("Successfully added a user");

                }
            })



        }


        console.log("Mai e dobaven");
        res = 'bachka'
        response.sendFile(path.join(__dirname + '/../public/html/login.html'));
    } catch (err) {
        console.log(err);


    }

})

store.on('connect', () => {
    console.log('connection established');
    //console.log(err);
})
store.on('error', (error) => {
    console.log(error);
    console.log('connection error');

})
store.on('sesionError', (error, classMathod) => {
    console.log('eternal error');
    console.log(classMathod);
    console.log('\n\n\nerror\n\n\n')
    console.log(error);
})

app.post("/login", async(request, response) => {
    const username = request.body.username;
    const password = request.body.password;
    console.log("Parola pri login : " + password);
    console.log(username + ' ' + password);
    let hashPassword = crypto.createHash('sha256').update(password).digest('base64');

    let time;

    if (username && password) {

        var q = `SELECT * FROM Users WHERE (username='${username}' OR email='${username}') AND password='${hashPassword}'`;
        c = await queries.testConnection(sql);
        var hui = await c.query(q, function(error, results, field) {
            if (error) {
                console.log(error);
                console.log('chupq se');
                console.log(hashPassword);
            }
            if (results.rowsAffected > 0) {
                request.session.loggedin = true;
                // request.session.username = username;
                // Redirect to home page

                response.cookie()
                response.redirect('/');

            } else {
                console.log(results.rowsAffected);
                console.log(q);
                //response.send('Incorrect Username and/or Password!');
                return response.sendFile(path.join(__dirname + '/../public/html/signUp.html'));
            }
            response.end();
        });
        console.log('trying to save cookie')
        request.session.cookie.username = username;

        q = `INSERT INTO Sessions(sid,session,expires,username) VALUES('${request.sessionID}','${
             request.session}','${request.session.cookie._expires}','${request.session.cookie.username}')`;
        console.log(q);
    } else {
        response.send('please enter username and password');

        response.end();
    }
})

app.get('/', function(request, response) {
    response.sendFile(path.join(__dirname + '/../public/html/index.html'));
})
app.get('/index', function(request, response) {
    response.sendFile(path.join(__dirname + '/../public/html/index.html'));
})


app.get('/login', function(request, response) {
    response.sendFile(path.join(__dirname + '/../public/html/login.html'));



    //console.log(request)

})


app.get('/signUp', function(request, response) {
    response.sendFile(path.join(__dirname + '/../public/html/signUp.html'));
})


app.get('/data', function(request, response) {
    if (request.session.loggedin) {
        console.log("here")
            // Output username
        return response.sendFile(path.join(__dirname + '/../public/html/data.html'));
    } else {
        console.log("logni se pedal")
            // Not logged in
        return response.sendFile(path.join(__dirname + '/../public/html/login.html'));

    }


})
app.get('/profile', function(request, response) {
    console.log('in profile');
    // If the user is loggedin
    if (request.session.loggedin) {
        console.log("here")
            // Output username
        return response.sendFile(path.join(__dirname + '/../public/html/profile.html'));
    } else {
        console.log("logni se pedal")
            // Not logged in
        return response.sendFile(path.join(__dirname + '/../public/html/login.html'));

    }

})
app.get('/cameras', function(request, response) {
    if (request.session.loggedin) {
        console.log("here")
            // Output username
        response.sendFile(path.join(__dirname + '/../public/html/cameras.html'));
    } else {
        console.log("logni se pedal")
            // Not logged in
        return response.sendFile(path.join(__dirname + '/../public/html/login.html'));

    }

})

server.listen(8080, () => {
    console.log(config.url);
})

给我的错误，

RequestError: Cannot insert the value NULL into column 'username', table 'OfficeManagement.dbo.Sessions'; column does not allow nulls. INSERT fails.
    at handleError (D:\projects\office\OfficeManagement\private\node_modules\mssql\lib\tedious\request.js:384:15)
    at Connection.emit (events.js:314:20)
    at Connection.emit (D:\projects\office\OfficeManagement\private\node_modules\tedious\lib\connection.js:1077:18)
    at RequestTokenHandler.onErrorMessage (D:\projects\office\OfficeManagement\private\node_modules\tedious\lib\token\handler.js:357:21)
    at Readable.<anonymous> (D:\projects\office\OfficeManagement\private\node_modules\tedious\lib\token\token-stream-parser.js:26:33)
    at Readable.emit (events.js:314:20)
    at addChunk (_stream_readable.js:298:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at Readable.push (_stream_readable.js:214:10)
    at next (internal/streams/from.js:88:29)

我已经从 connect-mssql-v2 和旧版本 但是我不能简单地理解它们。