Simple CRUD Example Using FeathersJS And Sequelize, Part II.
This is the second article of a three-part series. This series of articles will focus on building a complete CRUD interface. The reader is encouraged to optionally install Postman and simulate the same steps using a REST interface.
All steps in this three-part article series could be accomplished by referencing Github FastFeathers project sql-tiny. However, the reader is encouraged to follow the steps outlined as a learning experience.
Article One Of The Three-part Series
Article Three Of The Three-part Series
INTRODUCTION
Hopefully, you have successfully inserted a record into a database with absolutely minimal coding. This article will introduce how to fetch rows, alter rows, and of course remove them.
Actually, all of our server work is already done for us! All that is needed now is to introduce the remaining steps to complete our CRUD interface. All the steps in this article will be done within the Chrome console.
INDIVIDUAL QUERY
Now that a row has been inserted, let’s examine it. We are going to require two queries, one for an individual row, and the other for more than one row. When querying for an individual row, we will need to know the row ID number. For now, let’s cheat a bit and either get the ID number by executing a SELECT * FROM testTable; in the MariadDB console window, or simply looking at the feedback result when the row was first inserted. Note that if you are using MariaDB and this is your first insert, the ID number will probably be a one.
Enter the following FeathersJS get code into your console browser:
async function getTiny(id) { /* get(GET) (just one by index) */
let getResult = await app.service('tiny-connect').get(id);
console.log('getTiny function: ' + JSON.stringify(getResult) );
return getResult;
};
Yes that’s it! This is all the code required to query the database using an individual ID number. Now let’s call this function and see what returns:
let getTinyResult = null; let key = 1; // assuming your row ID is one
getTiny(key).then( value => { /* get(GET) */
getTinyResult = value;
console.log('getTiny(' + key + ') : ' + JSON.stringify(getTinyResult) );
});
You should see a result like this, or something very similar:
Returning the result to a global variable is of course optional as I am doing with the getTinyResult variable. Remember, our get call returns one and only one row, since it is using the primary key index with a singular value. Or should I say it will return one value assuming the primary key matches our input value.
QUERY MULTIPLE ROWS
To query several rows, or possibly all rows, we will use the FeathersJS find command:
async function findTiny(query) { /* find(GET) (get all or by query) */
console.log('Query function using: ' + JSON.stringify(query) );
let findResult = await app.service('tiny-connect').find(query);
console.log('findTiny function: ' + JSON.stringify(findResult) );
return findResult;
};
FeathersJS find command allows us to pass in a query. For our first example, let’s just grab everything. We will be passing in a null query:
let findTinyResultAll;
findTiny(null).then((value) => { /* find(GET) (find all) */
findTinyResultAll = value;
console.log('findTiny without query: ' + JSON.stringify(findTinyResultAll) );
});
The results are probably identical to your get query, unless you added more rows, as hopefully, you have done already out of curiosity.
Let’s pass our find an actual query, rather than merely passing a null query:
let findTinyResultOne; let findDataObject = {where:{ id: 0}}; // again, assuming your id is zero!
findTiny(findDataObject).then((value) => { /* find(GET) (with data ) */
findTinyResultOne = value;
console.log('findTiny with query: ' + JSON.stringify(findTinyResultOne) );
});
The query we are passing, {query:{ id: 0}} is certainly nothing dramatic, but it illustrates filtering your results. And query statements can become very complicated very quickly! The reader is encouraged to refer to Sequelize Queries for a complete list.
Bonus: try using the query {query:{ name: ‘mark edwards’}} instead.
I have found it useful to memorize the phrase: “get one, find all” while learning FeathersJS, and how it differs slightly from regular REST calls.
ROW UPDATES
It’s time to update a table. We have a very interesting choice to make here, update or patch. This author prefers using patch because update actually will remove columns if they are not specified in the call! So for our purposes, we will stick with just using the safer patch command.
This code is, of course, very similar to the table insert command.
async function patchTiny(id, data, params) { /* patch(PATCH) */
let patchResult = await app.service('tiny-connect').patch(id, data, params);
console.log('patchTiny function: ' + JSON.stringify(patchResult) );
return patchResult;
};
In this tutorial, I will just be passing in the ID. We could pass in a null for the second parameter and then include a third parameter in the form of a query, but let’s keep this example simple:
let patchResult = null; let patchKey = 0; let patchData = {address: '5678 There Street!' };
patchTiny(patchKey, patchData ).then( value => { /* patch(PATCH) */
patchResult = value;
console.log('patchResult: ' + JSON.stringify(patchResult));
});
And that is all there is to modifying a column!
ROW DELETE
This code probably is starting to look very familiar:
async function removeTiny(id, params) { /* remove(DELETE) */
let removeResult = await app.service('tiny-connect').remove(id, params);
console.log('removeTiny function: ' + JSON.stringify(removeResult) );
return removeResult;
};
And now let’s call this remove function:
let removeResult = null; let removeKey = 0;
removeTiny(removeKey).then( value => { /* remove(DELETE) */
removeResult = value;
console.log('removeResult: ' + JSON.stringify(removeResult));
});
Again, we could have passed in a null key for the second parameter, and a query value for the third parameter.
OPTIONAL CALLBACKS
There may be times when your application is sharing a table with other applications, or even the same application being used by multiple users. In this case, we want to be informed of any data changes, giving our application the opportunity to respond to those changes. For this purpose, we are going to use the FeathersJS callback functions:
/* optionally, establish a listener for create(POST), patched(PATCH), update(PUT), and remove(DELETE) */
app.setup(); /* optional, but good practice to include anyways */
app.service('tiny-connect').on('created', createMessage => { console.log('create(POST) : ', JSON.stringify(createMessage))});
app.service('tiny-connect').on('updated', updateMessage => { console.log('update(PUT) : ', JSON.stringify(updateMessage))});
app.service('tiny-connect').on('patched', patchMessage => { console.log('patch(PATCH) : ', JSON.stringify(patchMessage ))});
app.service('tiny-connect').on('removed', removeMessage => { console.log('removed(DELETE): ', JSON.stringify(removeMessage))});
However, recall in the first lesson, we skipped over security. For our purposes here, we must bypass FeathersJS’s excellent security by making a change to the ./src/channels.js file. Edit this file and change all occurrences of both:
app.channel('anonymous').leave(connection);
app.channel('authenticated').leave(connection);
to:
app.channel('everybody').leave(connection); /* changed in four places */
Now whenever one of the steps above is performed, any and all applications using this FeathersJS service will be notified.
NEXT
In the final article, https:// (ssl) security issues will be addressed.
