my Problem is fairly simple: I got 3 Methods:
export function F_SetupDatabase(_logger: any): voidexport function Q_RunQuery(query: string, db: "session" | "global"): anyexport function Q_RunQueryWithParams(query: string, params: string[], db: "session" | "global"): any
To Access a Database File (SQLite3).
( Call order: F_SetupDatabase(..) -> Q_RunQuery(...) )
F_SetupDatabase(...) is used to open a Database File global-database.db and create another in-Memory Database session. My main Problem is, that in my App's Main Function, the logs from Q_RunQuery show up before F_SetupDatabase. And Query's also won't work and always return undefined. So i guess it has something to do with async/sync calls or something like that.
I tried to avoid async calls/methods completley 'cause i lack the experience and always hang up myself with these.
SQL Querys are correct.
Full Source Code of database.ts
/**
* File: database/database.ts
* Description: Database Manager
* Author: Oliver Karger <kmaster@oliver-karger.de>
*/
import * as sqlite from 'sqlite3';
import chalk from 'chalk';
// can be used in other methods outside this file
export var sessionDatabase: sqlite.Database;
export var database: sqlite.Database;
// Signale Logger
var logger: any;
/**
* @description Create/Connect Database and Setup in-Memory Session Database`
* @author Oliver Karger <kmaster@oliver-karger.de>
* @param {any} _logger Signale Logger
*/
export function F_SetupDatabase(_logger: any): void {
logger = _logger.scope('Database');
// Open in-Memory Database
sessionDatabase = new sqlite.Database(
':memory:',
function (err: any): void {
if (err) {
logger.fatal(new Error(err));
}
logger.success('Opened in-Memory Session Database!');
}
);
// Setup in-Memory Database
// Note: auto_increment not needed, a column: int primary key will auto increment
// https://stackoverflow.com/questions/24600375/syntax-error-near-autoincrement-with-sqlite-database-creation
const setupSessionDatabaseQuery: string = `
create table sessions (
sessionID int unique,
clientID varchar(25) not null,
primary key (sessionID)
);`
sessionDatabase.run(setupSessionDatabaseQuery, [], function(err: any){
if(err) logger.fatal(new Error(err));
logger.success("Setup of in-Memory Session Database complete!")
});
// Open Database File
database = new sqlite.Database(
'./database/global-database.db', sqlite.OPEN_READWRITE,
function (err: any): void {
if (err) {
logger.fatal(new Error(err));
}
logger.success('Opened Database File!');
}
);
}
/**
* @description Run Query without Params
* @author Oliver Karger <kmaster@oliver-karger.de>
* @param {string} query SQL Query
* @param {"session" | "database"} db On which Database to execute the Query
*/
export function Q_RunQuery(query: string, db: "session" | "global"): any {
logger.info("SQL Query: <" + chalk.bold(query) + "> for: " + chalk.bold(db))
if(db === "global") {
database.serialize(function() {
database.all(query, [], function(err: any, rows: any) {
if(err){
logger.fatal(new Error(err));
}
return rows;
});
});
} else if(db === "session"){
sessionDatabase.serialize(function() {
sessionDatabase.all(query, [], function(err: any, rows: any) {
if(err){
logger.fatal(new Error(err));
}
return rows;
});
});
}
}
/**
* @description Run Query with Params
* @author Oliver Karger <kmaster@oliver-karger.de>
* @param {string} query SQL Query
* @param {string[]} params SQL Query Params
* @param {"session" | "database"} db On which Database to execute the Query
*/
export function Q_RunQueryWithParams(query: string, params: string[], db: "session" | "global"): any {
logger.info("SQL Query: <" + chalk.bold(query) + "> for: " + chalk.bold(db))
if(db === "global") {
const data = database.serialize(function() {
database.all(query, params, function(err: any, rows: any) {
if(err){
logger.fatal(new Error(err));
}
return rows;
});
});
} else if(db === "session"){
return sessionDatabase.serialize(function() {
sessionDatabase.all(query, params, function(err: any, rows: any) {
if(err){
logger.fatal(new Error(err));
}
return rows;
});
});
}
}
main.ts
export function F_Start(enableLogger?: boolean): http.Server{
if (enableLogger === true) {
logger.enable();
} else if (enableLogger === false) {
logger.disable();
} else {
logger.enable();
}
console.clear();
F_SkynetLogo();
F_EnableMiddleware(skynetApi, logger);
F_SetupRoutes(skynetApi, logger);
F_SetupDatabase(logger);
skynetServer = F_ServerListener(skynetApi, logger);
Q_RunQuery(`INSERT INTO sessions (clientID) VALUES ("client-1-0-0")`, "session");
// Get Session (clientID)
const session = Q_RunQuery(`SELECT * FROM sessions`, "session");
console.log(session);
return skynetServer;
}
Log from Console
[Database] › ℹ info SQL Query: <INSERT INTO sessions (clientID) VALUES ("client-1-0-0")> for: session
[Database] › ℹ info SQL Query: <SELECT * FROM sessions> for: session
undefined
[Server] › ▶ start Instance available on: 127.0.0.1:3000
[Database] › ✔ success Opened in-Memory Session Database!
[Database] › ✔ success Opened Database File!
[Database] › ✔ success Setup of in-Memory Session Database complete!
Full Source Code available here: https://github.com/OliverKarger/skynet/tree/development
