Introduction

As discussed in Part 2, I designed the overall layout of the backend server by creating all the essential endpoints, connecting it to the frontend, and tested it using a mock database.

Now, it's time to transition to a real database. Why is a persistent database essential?

• Manual Data Updates: With a mock database, data updates are manual and not persistent across sessions.
• Scalability Limitations: In-memory arrays become inefficient and unmanageable with growing datasets.
• Inefficient Authentication: Each login attempt requires iterating through the entire dataset, which is highly inefficient for real applications.

What We'll Cover: The Database Layer

In this part, we'll cover how I set up a relational database using PostgreSQL, and connected it to the backend server using Knex.js. We'll also explore why I chose synchronous bcryptjs for password hashing, and the crucial step of securely transferring the Clarifai API call to the backend (a task we deferred in Part 2).

Key Technologies for This Section

• Database: PostgreSQL
• Query Builder: Knex.js
• Database Management: Combination of DBeaver and psql
• Password Hashing: bcryptjs

Setting Up Your Database: PostgreSQL

Why PostgreSQL?

I specifically chose a relational database, opting for PostgreSQL due to its widespread popularity, robustness, and the invaluable learning opportunity it offers over non-relational alternatives. PostgreSQL is a mature, reliable, and widely supported database system.

Database Installation and Setup

To install PostgreSQL, I utilized Homebrew (as I'm using a Mac). Here are the steps I took to successfully install and start PostgreSQL:

1. First, I ran brew update to ensure Homebrew was up-to-date.
2. Next, I executed brew doctor for system health checks.
3. Then, I simply ran brew install postgresql.
4. Finally, brew services start postgresql to start the PostgreSQL service.

After starting PostgreSQL, I created a new database using the command: createdb 'your_database_name'

To connect to the database via the command line, I used: psql 'your_database_name'

Designing the Database Schema

Next, I designed a simple, secure database schema comprising two interconnected tables: users (for general user information) and login (for storing hashed passwords and login credentials, ensuring user privacy).

To simplify visualization and management, I connected the PostgreSQL database to DBeaver, a graphical tool, which greatly helped visualize the database for a first-timer. (Consider adding a screenshot of DBeaver here showing your tables!)

The users Table

The users table stores general user information. For this table, I created columns for id, name, email, entries, and joined. The id column serves as the primary key.

To build the users table, I ran this query in DBeaver:

CREATE TABLE users (
    id serial PRIMARY KEY,
    name VARCHAR (100),
    email text UNIQUE NOT NULL,
    entries BIGINT DEFAULT 0,
    joined TIMESTAMP NOT NULL
);

The login Table

This login table includes valuable login information. It consists of id, hash (for the hashed password), and email. The id also serves as the primary key, connecting it to the users table (though implicitly in this setup, often via foreign keys in more complex designs).

To build the login table, I ran this query:

CREATE TABLE login (
    id serial PRIMARY KEY,
    hash VARCHAR(100) NOT NULL,
    email text UNIQUE NOT NULL
);

Connecting Backend to Database with Knex.js

Introducing Knex.js: Your SQL Query Builder

Knex.js is a powerful SQL query builder for JavaScript, simplifying database interactions and query construction in Node.js applications without the full abstraction of an ORM. It provides a simple and powerful way to build and execute SQL queries, as well as a helpful set of utilities for working with databases.

I chose Knex.js over raw SQL or a full ORM due to its clear documentation and a more intuitive query building syntax, which I found easier for this project's scope. Ultimately, the choice depends on project needs and personal preference.

Installation and Configuration

Now, it’s time to install Knex.js to establish the connection with our database:

1. To install Knex.js, I simply ran the command: npm install knex
2. Since I was using PostgreSQL, I also installed its client library: npm install pg
3. Then, I imported Knex into my server file: import knex from 'knex'

4. Next, I configured the database connection:JavaScript
   

   const db = knex({
       client: 'pg',
       connection: {
         host : '127.0.0.1', // localhost
         user : 'postgres', // Add your database username here
         port: 5432, // Standard PostgreSQL port. Ensure this matches your setup.
         password : '', // Add your correct password here
         database : 'smartweb' // Add your database name here
       }
   });
   

   (Note:* Ensure your port is correctly set to 5432 unless you've configured a custom port.)*

To test that the connection was established, I used a SELECT query builder from Knex's documentation:

db.select('*').from('users')
  .then(data => console.log(data))
  .catch(err => console.error("Database connection test failed:", err));

I didn’t get any errors, which meant everything was working fine. Now, it was time for Refactoring API Endpoints.

Refactoring API Endpoints for Database Interaction

Implementing Password Hashing with bcryptjs

Password hashing is critical: storing plain text passwords is a severe security vulnerability. I chose bcryptjs for its proven reliability and adherence to modern security standards, ensuring user password privacy. Passwords should only be known to the user, not even the company.

To integrate bcryptjs, I installed it first:

npm install bcryptjs

Then, I imported it into my server.js file: import bcrypt from "bcryptjs";

For this initial implementation, I opted for bcryptjs's synchronous hashing to simplify the learning curve. Future iterations will adopt asynchronous methods for improved performance and non-blocking operations in a production environment.

Now, let's refactor the endpoints. I largely referred to the Knex.js documentation for guidance during this process.

Refactoring the /register Endpoint

To make it easier to understand, here's the code, followed by a line-by-line explanation:

app.post('/register', (req, res) => {
    const {name, email, password} = req.body;
    if (!name || !email || !password) {
        return res.status(400).json('Incorrect form submission');
    }
    const salt = bcrypt.genSaltSync(10);
    const hash = bcrypt.hashSync(password, salt);
    db.transaction(trx => {
        trx.insert({
            hash: hash,
            email: email
        })
        .into('login')
        .returning('email')
        .then(loginEmail => {
            return trx('users')
            .returning('*')
            .insert({
                name: name,
                email: loginEmail[0].email,
                joined: new Date()
            })
            .then(user => {
                res.json(user[0]);
            });
        })
        .then(trx.commit)
        .catch(trx.rollback)
        .catch(err => res.status(400).json('Unable to register'));
    });               
});

• const {name, email, password} = req.body;: This is a simple destructuring method, used for cleaner code by avoiding req.body.name repeatedly.
• if (!name || !email || !password) { return res.status(400).json('Incorrect form submission'); }: This validation ensures all required fields are submitted, preventing incomplete user registrations and improving data integrity.
• const salt = bcrypt.genSaltSync(10); const hash = bcrypt.hashSync(password, salt);: This step is for hashing the password using bcryptjs. As mentioned, I used the synchronous method for simplicity in this initial project.
• db.transaction(trx => { ... });: I utilized Knex.js transactions to ensure atomic operations when inserting user data into both the login and users tables. This guarantees that either both inserts succeed, or neither does, maintaining data consistency.
  • trx.insert({ hash: hash, email: email }).into('login').returning('email'): Inserts the hashed password and email into the login table, returning the email to be used in the next step.
  • .then(loginEmail => { return trx('users').returning('*').insert({ ... }) ... });: Takes the returned email from the login table and inserts it into the users table along with other user information, returning the newly created user record.
• .then(user => { res.json(user[0]); }): Sends the newly created user object back to the client.
• .then(trx.commit).catch(trx.rollback): .then(trx.commit) ensures the transaction is committed if all operations succeed, while .catch(trx.rollback) rolls back all changes if any error occurs within the transaction, maintaining data consistency.
• .catch(err => res.status(400).json('Unable to register'));: Catches any errors during the transaction and returns a generic 'Unable to register' message to the client for security reasons, avoiding revealing exact error details.

Refactoring the /signin Endpoint

Now, it’s time for refactoring the sign-in endpoint:

app.post('/signin', (req, res) => {
    const {password, email} = req.body;
    if (!email || !password) {
        return res.status(400).json('Please enter email and password');
    }
    db.select('email', 'hash').from('login')
    .where('email', '=', email)
    .then(data => {
        if (!data.length) { // Check if user exists before attempting to access data[0]
            return res.status(400).json('Wrong credentials');
        }
        const isValid = bcrypt.compareSync(password, data[0].hash);
        if (isValid) {
            return db.select('*').from('users')
            .where('email', '=', email)
            .then(user => {
                res.json(user[0]);
            })
            .catch(err => res.status(400).json('Unable to get user'));
        } else {
            res.status(400).json('Wrong credentials');
        }
    })
    .catch(err => res.status(400).json('Unknown user')); 
});

• db.select('email', 'hash').from('login').where('email', '=', email): This step fetches the email and hashed password (hash) from the login table for the provided email.
• if (!data.length) { return res.status(400).json('Wrong credentials'); }: Crucially, this check ensures that a user with the provided email exists before attempting to compare passwords, preventing errors if data is empty.
• const isValid = bcrypt.compareSync(password, data[0].hash);: The retrieved hash is then compared with the provided password using bcryptjs.compareSync().
• If isValid is true (password matches), the code fetches the full user profile from the users table and responds with user[0]. Otherwise, it responds with 'Wrong credentials' or 'Unknown user' in case of other errors, again avoiding specific error details.

Refactoring the /profile/:id Endpoint

This endpoint is simpler, as it only needs to retrieve a user's profile based on their ID from req.params.

app.get('/profile/:id', (req, res) => { // Corrected endpoint path
    const {id} = req.params;
    db.select('*').from('users')
    .where('id', '=', id)
    .then(user => {
        if (user.length) {
            res.json(user[0]);
        } else {
            res.status(400).json('Not Found');
        }  
    })
    .catch(err => res.status(400).json('Error getting user'));
});

Refactoring the /image Endpoint

Similar to /profile/:id, this endpoint updates the image entries count for a user.

app.put('/image', (req, res) => {
    const { id } = req.body;
    db('users').where('id', '=', id)
        .increment('entries', 1)
        .returning('entries')
        .then(entries => {
            res.json(entries[0].entries);
        })
        .catch(err => res.status(400).json('Unable to update entries'));
});

After implementing these changes, I thoroughly tested all endpoints, and everything was working correctly without errors. This was a significant step forward!

Handling Database Errors and Security Considerations

Robust Error Management

For error management, I ensured that no exact error details are exposed to the user. All endpoints return generic error messages (e.g., 'Unable to register', 'Wrong credentials', 'Not Found'), enhancing security and user experience.

Basic Frontend Response Handling

While the backend handles primary security like hashing and API key management, the frontend also plays a role in how it processes responses. After fetching data from the backend:

.then(res => res.json())
.then(user => {
    if (user.id) {
        this.props.loadUser(user);
        this.props.onRouteChange('home');
    } else {
        alert("Invalid credentials");
    }
})

This snippet ensures that if a valid user ID is returned from the backend, the user's data is loaded, and the application navigates to the 'home' route. If no user ID is present, an alert indicates invalid credentials. The loadUser function (defined in App.jsx) updates the application's state with the user's details:

loadUser = (data) => {
    this.setState({user : {
        id: data.id,
        name: data.name,
        email: data.email,
        entries: data.entries,
        joined: data.joined
    }});
};

This state management ensures the frontend accurately reflects the logged-in user's information.

(Important Security Note:* For production applications, storing sensitive user data or tokens directly in localStorage or sessionStorage on the frontend should be done with extreme caution due to XSS vulnerabilities. More secure methods, like httpOnly cookies, are typically preferred for handling authentication tokens.)*

Transferring Clarifai API to Backend

To prevent exposing my Personal Access Token (PAT) key from the frontend console, I transferred the Clarifai API call to the backend. This is a critical security measure.

Here’s the backend code snippet for handling the Clarifai API call:

const handleAPICall = (req, res) => {
    const { input } = req.body;
    // IMPORTANT: These should be loaded from environment variables (.env file)
    // and NOT hardcoded or committed to version control in a real application.
    const PAT = process.env.CLARIFAI_PAT; // Your Personal Access Token
    const USER_ID = process.env.CLARIFAI_USER_ID; // Your Clarifai User ID
    const APP_ID = process.env.CLARIFAI_APP_ID; // Your Clarifai App ID

    const MODEL_ID = 'face-detection';   
    const IMAGE_URL = input;

    // Clarifai API request setup (largely from Clarifai Docs)
    const raw = JSON.stringify({
        "user_app_id": {
            "user_id": USER_ID,
            "app_id": APP_ID
        },
        "inputs": [
            {
                "data": {
                    "image": {
                        "url": IMAGE_URL
                    }
                }
            }
        ]
    });

    const requestOptions = {
        method: 'POST',
        headers: {
            'Accept': 'application/json',
            'Authorization': 'Key ' + PAT
        },
        body: raw
    };

    fetch("https://api.clarifai.com/v2/models/" + MODEL_ID + "/outputs", requestOptions)
        .then(response => response.json())
        .then(data => {
            res.json(data);
        })
        .catch(err => {
            res.status(400).json('Unable to work with API');
        });
};

// You'll also need an endpoint to handle this API call, e.g.:
// app.post('/imageurl', (req, res) => { handleAPICall(req, res); });

The core API call structure largely follows Clarifai's documentation, adapted for a Node.js environment. I've created a dedicated endpoint (/imageurl) on the backend to manage this API call securely. The key modification is handling the API response and passing it back to the frontend.

Here's the frontend code snippet after adjusting the Clarifai API call to the backend:

JavaScript

onButtonSubmit = () => {
    this.setState({imageUrl: this.state.input}, () => {
        fetch('http://localhost:3000/imageurl', { // IMPORTANT: Change this to your DEPLOYED backend URL for production!
          method: 'post',
          headers: {'Content-Type': 'application/json'},
          body: JSON.stringify({
            input: this.state.input
          })
        })
        .then(response => response.json())
        .then(result => {
          if (result) {
            fetch('http://localhost:3000/image', { // IMPORTANT: Change this to your DEPLOYED backend URL for production!
              method: 'put',
              headers: {'Content-Type': 'application/json'},
              body: JSON.stringify({
                id: this.state.user.id
              })
            })
            .then(res => res.json())
            .then(count => {
              this.setState(Object.assign(this.state.user, {entries: count}));
            });
          }
          this.displayFaceBox(this.calculateFaceLocation(result));
        })
        .catch(error => console.log('error', error));
    });
};

The frontend now sends the image URL to its own backend endpoint (/imageurl), which then securely handles the Clarifai API interaction and returns the results. If a successful result is received, a subsequent call updates the user's image entry count.

Conclusion: The Full-Stack Picture Nears Completion

It was incredibly satisfying to see everything working seamlessly after creating the real database and connecting it to the backend server using Knex.js. Knex made building queries and interacting with the backend surprisingly simple. My application now feels like a complete full-stack app, with user data stored perfectly and securely in the database.

Key Learning Points:

• Database design principles (including table separation for security).
• Working with PostgreSQL for persistent data storage.
• Mastering Knex.js for efficient and readable database operations.
• Implementing secure password hashing with bcryptjs.
• Refactoring API endpoints for robust database interaction.
• Understanding the importance of moving sensitive API calls to the backend for security.

What's Next: Deployment

Deployment proved to be a significant challenge. While my prior learning focused on Heroku, I opted for Render.com due to its free tier – a platform with distinct deployment processes. Despite taking a full day, successfully deploying the application was a highly rewarding experience.

I'm thinking of sharing my complete experience with Render.com, a step-by-step guide on how I deployed my app, as it might help someone else facing similar issues.

Okay, lastly, don’t forget to share your thoughts and leave some comments below. And as I also say at the end of my every blog, if you find something advisable, please tell me—I’m always open to advice!