@patrick Similar to php code you have shared here Custom module (php) - invoking connection, can you please share for NodeJS also?
Here the php module rewritten for nodejs.
const db = require('../../lib/core/db');
module.exports = {
// get user from database
getuser: async function(options) {
// connection option is required
const connection = this.parseRequired(options.connection, 'string', 'connection is required.');
// userid option is required
const userId = this.parseRequired(options.userId, 'number', 'userId is required.');
// get the database connection
const db = this.getDbConnection(connection);
// throw error if connection not found
if (!db) throw new Error(`Connection "${connection}" doesn't exist.`);
// get results from database
let results = await db.raw('SELECT * FROM Users WHERE UserId = ?', [userId]);
// raw method returns raw response from driver, needs some conversion (http://knexjs.org/#Raw-Queries)
if (db.client.config.client == 'mysql' || db.client.config.client == 'mysql2') {
results = results[0];
} else if (db.client.config.client == 'postgres' || db.client.config.client == 'redshift') {
results = results.rows;
}
return results;
}
// get user from database (using knex as query builder)
getuser2: async function(options) {
// connection option is required
const connection = this.parseRequired(options.connection, 'string', 'connection is required.');
// userid option is required
const userId = this.parseRequired(options.userId, 'number', 'userId is required.');
// get the database connection
const db = this.getDbConnection(connection);
// throw error if connection not found
if (!db) throw new Error(`Connection "${connection}" doesn't exist.`);
// get results from database
return db.select('*').from('Users').where('UserId', userId);
}
}
2 Likes
Thanks a ton. Will try to build a custom module around this. 
Hi @patrick, this works great for normal SQL queries. However, it doesn’t work when I try to do transactions or declare variables within a custom module.
Could someone please provide the code, or give me links for which language to research, in order to achieve this? Not sure what language to start with - is it nodejs or a related package/library like knex or mysql/mysql2? I am not familiar with nodejs at all, but I could learn with some guidance!
The variable db is a Knex object, you can use any method that exists in Knex:
Here are raw queries for you to compare with the code above:
https://knexjs.org/guide/raw.html#raw-queries
Here are documents for transactions:
https://knexjs.org/guide/transactions.html
1 Like
Thanks @Apple!
I actually had a google of this earlier and fell down a knex rabbit hole lol. So far I still haven’t found a way to execute a transaction as a block via knex. Is there no way to do that? Now I’m thinking of just executing the queries as separate statements and chaining them via the knex .then functions to make it execute as a transaction. Any better suggestions?
The .insert() method can also accept an array of objects, would that work for you?
Thanks for the quick reply again! Hmm not really, since I was planning on building up the sql transaction using “set values” in the api json, rather than passing all the variables to the js and constructing the query there. Let me know if that’s not the right way to be thinking about it though
You’d have to be careful to avoid SQL injection if you’re building the SQL dynamically (you can search a topic about it here on the forums if you wish to learn more about that)
You’re welcome
I haven’t played too much with Knex, so I only know the basics. I essentially passed arrays to it
1 Like
Good point, thank you! I guess I will work on passing arrays to it as well. Can I ask - how did you pass your arrays and write a Knex query off the back of that?
I’m guessing writing a new custom module and related script for every new transaction I want to create isn’t the right way to go about this… but I can’t think of how else to do it. I can’t find a way to run a JS directly from the json API.
EDIT: do you do it by writing a generic custom module and passing the array by stringifying to json? I can’t figure out how to do this for my use case either lol… my data is structured like arrays containing multiple variables.
Knex’s .insert() method accepts a string as well as array input
I did a custom module that has a field where you can insert either a string or an array
I’ve been meaning to publish it publicly but I’m not ready to do it yet, need to find some time to make sure it works properly and stuff (it has more options that just those up there).
.hjson for the input array field:
{ name: 'inputArray', optionName:'inputArray', title: 'Input array of rows (optional)', type: 'text', required: false, defaultValue :[], serverDataBindings: true, help: 'The array of rows, also accepts single object'},
Parsing field:
let knex = this.getDbConnection(this.parse(options.dbName) || "db")
let inputObj = this.parse(options.inputArray)
Insert:
let result = await knex(tableName).insert(inputObj)
2 Likes
Wow thanks @Apple! I will definitely try this out! Let me know when you do publish that custom module - keen to see what else you’ve come up with too!
One more thing if you could help - how do you debug custom module/knex js? I haven’t figured out a way, and I like to be able to see what I’m doing as I code in a new language I’m unfamiliar with. The “return” and writing to console/log didn’t seem to help with debugging either.
Hi,
No problem!
Console.log should work, are you sure it’s not working? You can see those in Wappler in the Web server logs tab.
Good luck
1 Like
Awesome thanks so much! Will give this a good crack
Hmm I actually don’t think it’s sending to console. Would you mind having a look and seeing where I’m going wrong?
hjson file
js file
Nothing shows up on Chrome console:
Edit: I know the action is being triggered because I can see the json api being triggered in the Chrome dev tools. It’s just not writing to console.
You’re writing a server-side module, by doing console.log() on your module you’re printing to the server’s console, not your browser’s console
Web Server Logs is the right console
1 Like
Ahh thanks for the explanation! Facepalm as you can tell I am very much a noob at full stack development, but I am trying to learn!
1 Like