This app help learners to find a tutor that you fits their pocket.
It's simple for tutors to get going. Select a subject to tutor, your hourly rate and your name and your ready to go.
This app will store it's data in PostgreSQL. And it will be an ExpressJS web app.
Create new folder called tutor-finder. Change into it. Open the folder in VS Code.
To install PostgreSQL follow these steps:
Create a database called my_products and username - coder with a password of pg123. Enter the password when prompted after executing the createuser command.
sudo -u postgres createdb tutor-finder;
-- sudo -u postgres createuser coder -P;
Now run psql as the postgres user:
sudo -u postgres psql;
Grant the coder user access to the tutor-finder database by running this command:
grant all privileges on database tutor-finder to coder;
Type in \q to exit psql as the postgres user.
Connect to your database using: psql -d tutor-finder
To create the database in your local PostgreSQL do this.
First create the database: createdb tutor-finder
Create a folder called sql and create a file in there called create-tutor-table.sql.
In that file put the database script code to create the tutor table.
create table tutor (
id serial not null primary key,
full_name text not null,
email text not null,
subject text not null,
hourly_rate decimal(10,2)
);Connect to the tutor-finder database using psql the PostgreSQL command line client. Do this from your projects route folder. Not from the sql folder.
psql tutor-finder
Once in psql run the \dt command you will get a message stating Did not find any relations. - meaning that there are no databases in the tutor-finder database.
To create the tutor table in the databae run the create-tutor-table.sql script that we created earlier using this command.
\i sql/create-tutor-table.sql
You should see a message of CREATE TABLE.
Running the \dt command. You should see a list of the tables in the database. There should be one table the tutor reason.
Let's explore SQL now. Using SQL you can add, update, remove and find data in a SQL database.
To create a new tutor use an insert query like this:
insert into tutor (full_name, email, subject, hourly_rate) values ('Lindani Pani', '[email protected]', 'Mathematics', 45);You can copy and paste this into the psql window and press enter. Note the semi-colon ; which executes the sql command.
insert into tutor (full_name, email, subject, hourly_rate) values ('Nkosi Ndlovu', '[email protected]', 'Mathematics', 40);
insert into tutor (full_name, email, subject, hourly_rate) values ('Thando Ntini', '[email protected]', 'Mathematics', 35);
You can also use a sql script file with insert statements. To do that create a file your sql folder called insert-tutors.sql and add insert statements on seperate lines.
Execute the script in psql using: \i sql/insert-tutors.sql
Add some 3 different tutors for Mathematics, Life Sciences and Economics. You can add for other subjects as well.
To see all the tutors in the database you can use a select query in psql:
select * from tutor;To find all the tutors for a given subject you can adda where clause to your SQL:
select * from tutor where subject = 'Mathematics'; You can even check for math tutors how are cheaper than a given rate:
select * from tutor where subject = 'Mathematics' and hourly_rate < 50; You can also use a greater than or greater/less and equal signs - >, <, <= and >=
To update a tutors rate you can use an update query:
update tutor set hourly_rate = 33.50 where id = 3;You can even do increase all the mathematics tutor rates with 10%:
update tutor set hourly_rate = hourly_rate * 1.1 where subject = 'Mathematics';Play around and see what other updates you can do.
To remove a tutor from the database you can use a delete query.
delete from tutor where email = '[email protected]';
You can have a where clause on any column in the database. If a delete statement has no where clause it will delete all the entries in the database so be carefull.
So far all the SQL we wrote ran from psql the PostgreSQL command line database client.
Let's change that and run some SQL queries from NodeJS using mocha.
To do that we will need to initiate a NodeJS project using npm and install mocha and a NodeJS database driver for PostgreSQL.
Do this using these commands inside your tutor-finder folder.
npm init --y
npm install --save-dev mocha
npm install --save pg
Create a test folder in the tutor-finder folder and create a file called tutor-sql-test.js in there.
In your package.json file change the test command from:
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
to
"scripts": {
"test": "mocha"
},
You can now run your tests using npm test.
Put this code in the tutor-sql-test.js:
const assert = require("assert");
describe("Using the tutor SQL table from NodeJS", function(){
it("should be able to run a select query", function(){
assert.equal(1,2);
});
it("should be able to run an update query", function(){
assert.equal(1,2);
});
it("should be able to run an insert query", function(){
assert.equal(1,2);
});
it("should be able to run a delete query", function(){
assert.equal(1,2);
});
});If you run this using npm test you should have 4 failing tests.
To run a select query on NodeJS from PostgreSQL you will need to import the PostgreSQL NodeJS driver like this.
const pg = require('pg');To start using it you will need to do this:
Callback select block:
const pg = require("pg");
const Pool = pg.Pool;
const connectionString = process.env.DATABASE_URL || 'postgresql://coder:pg123@localhost:5432/tutor-finder';
const pool = new Pool({
connectionString
});
pool.query("select * from tutor", function(err, results){
if (err) {
console.log(err)
} else {
console.log("This will print later");
console.log(results.rows);
}
});
console.log("This will print first");This code is using a callback function. Calls to databases in NodeJS are asyncronyous. It's non blocking. Look at the console.log statements to see the flow of the code.
The code above can be changed to use Promises like this:
pool
.query("select * from tutor")
.then(function(results){
console.log("This will print later");
console.log(results.rows);
})
.catch(function(err){
console.log(err)
});
console.log("This will print first");But we will be using the async/await notation to run our code - it makes it easier to use asynchronous code in NodeJS.
Put the "Callback select block" piece of code into mocha in the should be able to run a select query test.
Remember to import the Postgres driver in your test:
const pg = require("pg");
const Pool = pg.Pool;Connect to your database in your tests describe function:
const connectionString = process.env.DATABASE_URL || 'postgresql://coder:pg123@localhost:5432/tutor-finder';
const pool = new Pool({
connectionString
});Use the pool variables query function to connect to run your sql query.
You should see all the tutor entries in the database now.
But we would like to use async/await instead.
Change your code in the should be able to run a select query test too:
it("should be able to run a select query", function () {
const results = await pool.query("select * from tutor");
console.log(results.rows);
// assert.equal(1,2);
});That's much simpler... but if you run the code it will fail with an error like this:
const results = await pool.query("select * from tutor");
^^^^
SyntaxError: Unexpected identifier
As you haven't added an async modifier to your it function
Change it too:
it("should be able to run a select query", async function () {
// your code is still here
});Run it now! You should see all your tutors displayed on the screen now. Fix the test to have an assert that check how many rows are returned from the database.
Like this for example:
assert.equal(3, results.rows.length)You can insert data into your database from NodeJS using an insert query.
Try this to add a new tutor:
it("should be able to run an insert query", async function () {
const tutorData = ["Andy Phiri", "[email protected]", "Economics", 45];
// ensure the tutor is not in the database
await pool.query("delete from tutor where email = $1", [tutorData[1]]);
// insert a new tutor
await pool.query("insert into tutor (full_name, email, subject, hourly_rate) values ($1, $2, $3, $4)", tutorData);
const results = await pool.query("select * from tutor where email = $1", [tutorData[1]]);
assert.equal(1, results.rows.length);
});Run a select query from psql to see if the new tutor is getting added.
You can extend your test to:
- delete the tutor then
- add it
- and then check if it was added succussfully like this.
Now lets write some code that can update a tutors Subject
it("should be able to run an update query", async function () {
// set tutor with id=3 subject to History
const tutorData = ["History", 3];
await pool.query("update tutor set subject = $1 where id = $2", tutorData);
// test that it worked
const results = await pool.query("select subject from tutor where id = $1", [3]);
assert.equal("History", results.rows[0].subject);
});To delete a tutor you will use a SQL delete query and you can add where clause the same as before.
To add tutors