CRUD

What is CRUD?

CRUD stands for:

C-reate: Involves the creation of addition of data to a database. In essence, it “create”s entries in databases.

R-ead: Involves the retrieval, querying, or “read”ing of existing data from a database.

U-pdate: Involves the modification or “update”ing of existing data in a database. Operation of changing the values of specific fields in a system.

D-elete: Involves the removal or “delete”ion of existing data from a database. It’s the operation of eliminating information from a database.

In the past, each function has had its own link to fetch from, but with CRUD all four functions will be under one link, differentiated by the request type most suited for it. Create: POST request, as create operation needs to sent data back to the backend, best suited by post request Read: GET request, as read operation is simple and requires no data to be sent back Update: PUT request, as update requires the user ID of the user being updated, and this can be modified in the URL which PUT requests allow Delete: DELETE request, self explanatory This is simpler and makes fetching less confusing, as everything is done under one link.

Authentication

  • How users will authenticate to be able to access function that require a token
  • POST request is sent to backend
    • payload contains user ID and password
  • Backend compares user ID and password, and returns with token if successful
  • Note that authentication and authorization are two different things
    • Authentication is identifying who you are, client saying hi to backend
    • Authorization is getting permission to view something, website letting client know that they can view something

Frontend

function login_user(){
    // Set Authenticate endpoint
    const url = 'http://127.0.0.1:8086/api/users/authenticate';

    // Set the body of the request to include login data from the DOM
    const body = {
        uid: document.getElementById("uid").value,
        password: document.getElementById("password").value,
    };

    // Change options according to Authentication requirements
    const authOptions = {
        mode: 'cors', // no-cors, *cors, same-origin
        credentials: 'include', // include, same-origin, omit
        headers: {
            'Content-Type': 'application/json',
        },
        method: 'POST', // Override the method property
        cache: 'no-cache', // Set the cache property
        body: JSON.stringify(body)
    };

    // Fetch JWT
    fetch(url, authOptions)
    .then(response => {
        // handle error response from Web API
        if (!response.ok) {
            const errorMsg = 'Login error: ' + response.status;
            console.log(errorMsg);
            alert("Failed Authentication: Credentials Incorrect")
            return;
        }
        // Success!!!
        // Redirect to the database page
        window.location.href = "/csp-blog//log/2024/01/30/Users.html";
    })
    // catch fetch errors (ie ACCESS to server blocked)
    .catch(err => {
        console.error(err);
    });
}

// Attach login_user to the window object, allowing access to form action
window.login_user = login_user;

Backend

from werkzeug.security import generate_password_hash, check_password_hash
# update password, this is conventional setter
def set_password(self, password):
    """Create a hashed password."""
    self._password = generate_password_hash(password, "pbkdf2:sha256", salt_length=10)

# check password parameter versus stored/encrypted password
def is_password(self, password):
    """Check against hashed password."""
    result = check_password_hash(self._password, password)
    return result
def post(self):
            try:
                body = request.get_json()
                if not body:
                    return {
                        "message": "Please provide user details",
                        "data": None,
                        "error": "Bad request"
                    }, 400
                ''' Get Data '''
                uid = body.get('uid')
                if uid is None:
                    return {'message': f'User ID is missing'}, 400
                password = body.get('password')
                
                ''' Find user '''
                user = User.query.filter_by(_uid=uid).first()
                if user is None or not user.is_password(password):
                    return {'message': f"Invalid user id or password"}, 400
                if user:
                    try:
                        token = jwt.encode(
                            {"_uid": user._uid},
                            current_app.config["SECRET_KEY"],
                            algorithm="HS256"
                        )
                        resp = Response("Authentication for %s successful" % (user._uid))
                        resp.set_cookie("jwt", token,
                                max_age=3600,
                                secure=True,
                                httponly=False,
                                path='/',
                                samesite='None'  # This is the key part for cross-site requests

                                # domain="frontend.com"
                                )
                        return resp
                    except Exception as e:
                        return {
                            "error": "Something went wrong",
                            "message": str(e)
                        }, 500
                return {
                    "message": "Error fetching auth token!",
                    "data": None,
                    "error": "Unauthorized"
                }, 404
            except Exception as e:
                return {
                        "message": "Something went wrong!",
                        "error": str(e),
                        "data": None
                }, 500

Create

Frontend

The code below uses user entered data from a form, and sends a POST request to the backend with the user entered data.

import { uri, options } from '/students/assets/js/api/config.js';
//
//    const url = uri + '/api/users/authenticate';
//    const body = {
//            // name: document.getElementById("name").value,
//            uid: "toby",
//            password: "123toby"
//            // dob: document.getElementById("dob").value
//        };
//    const authOptions = {
//            ...options, // This will copy all properties from options
//            method: 'POST', // Override the method property
//            cache: 'no-cache', // Set the cache property
//            body: JSON.stringify(body)//       };
//    fetch(url, authOptions)
    function login_user(){
        // Set Authenticate endpoint
        const url = uri + '/api/users/';

        // Set the body of the request to include login data from the DOM
        const body = {
            name: document.getElementById("name").value,
            uid: document.getElementById("uid").value,
            password: document.getElementById("password").value,
            dob: document.getElementById("dob").value
        };

        // Change options according to Authentication requirements
        const authOptions = {
            ...options, // This will copy all properties from options
            method: 'POST', // Override the method property
            cache: 'no-cache', // Set the cache property
            body: JSON.stringify(body)
        };

        // Fetch JWT
        fetch(url, authOptions)
        .then(response => {
            // handle error response from Web API
            if (!response.ok) {
                const errorMsg = 'Login error: ' + response.status;
                console.log(errorMsg);
                return;
            }
            // Success!!!
            // Redirect to the database page
            window.location.href = "/students/data/database";
        })
        // catch fetch errors (ie ACCESS to server blocked)
        .catch(err => {
            console.error(err);
        });
    }

    // Attach login_user to the window object, allowing access to form action
    window.login_user = login_user;

Backend

The backend then takes the user data, and adds a new user to the database, given that the code is input properly. Note the lack of token required, as this is the create method and shouldn’t need authentication.

def post(self, current_user): # Create method
            ''' Read data for json body '''
            body = request.get_json()
            
            ''' Avoid garbage in, error checking '''
            # validate name
            name = body.get('name')
            if name is None or len(name) < 2:
                return {'message': f'Name is missing, or is less than 2 characters'}, 400
            # validate uid
            uid = body.get('uid')
            if uid is None or len(uid) < 2:
                return {'message': f'User ID is missing, or is less than 2 characters'}, 400
            # look for password
            password = body.get('password')

            active_classes = body.get('active_classes')
            if active_classes is None or len(active_classes) == 0:
                return {'message': 'Active class is missing'}, 400

            archived_classes = body.get('archived_classes')

            ''' #1: Key code block, setup USER OBJECT '''
            uo = User(name=name, 
                      uid=uid,
                      active_classes=active_classes,
                      archived_classes=archived_classes)
            
            ''' Additional garbage error checking '''
            # set password if provided
            if password is not None:
                uo.set_password(password)
            # set server request status    
            
            ''' #2: Key Code block to add user to database '''
            # create user in database
            user = uo.create()
            # success returns json of user
            if user:
                return jsonify(user.read())
            # failure returns error
            return {'message': f'Processed {name}, either a format error or User ID {uid} is duplicate'}, 400
    def create(self):
        try:
            # creates a person object from User(db.Model) class, passes initializers
            db.session.add(self)  # add prepares to persist person object to Users table
            db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
            return self
        except IntegrityError:
            db.session.remove()
            return None

Read

Frontend

This example read code sends a GET request to the backend. Note that read doesn’t necessarily mean returning the entire database, but simply reading already existing information from the database. Note that the javaScript below creates a table with the retrieved information but other things can be done with it.

// uri variable and options object are obtained from config.js
  import { uri, options } from '/teacher_portfolio/assets/js/api/config.js';

  // Set Users endpoint (list of users)
  const url = uri + '/api/users/';

  // prepare HTML result container for new output
  const resultContainer = document.getElementById("result");

  // fetch the API
  fetch(url, options)
    // response is a RESTful "promise" on any successful fetch
    .then(response => {
      // check for response errors and display
      if (response.status !== 200) {
          const errorMsg = 'Database response error: ' + response.status;
          console.log(errorMsg);
          const tr = document.createElement("tr");
          const td = document.createElement("td");
          td.innerHTML = errorMsg;
          tr.appendChild(td);
          resultContainer.appendChild(tr);
          return;
      }
      // valid response will contain JSON data
      response.json().then(data => {
          console.log(data);
          for (const row of data) {
            // tr and td build out for each row
            const tr = document.createElement("tr");
            const name = document.createElement("td");
            const id = document.createElement("td");
            const age = document.createElement("td");
            // data is specific to the API
            name.innerHTML = row.name; 
            id.innerHTML = row.uid; 
            age.innerHTML = row.age; 
            // this builds td's into tr
            tr.appendChild(name);
            tr.appendChild(id);
            tr.appendChild(age);
            // append the row to table
            resultContainer.appendChild(tr);
          }
      })
  })
  // catch fetch errors (ie ACCESS to server blocked)
  .catch(err => {
    console.error(err);
    const tr = document.createElement("tr");
    const td = document.createElement("td");
    td.innerHTML = err + ": " + url;
    tr.appendChild(td);
    resultContainer.appendChild(tr);
  });

Backend

The below code returns all users from the database in as JSON when fetched.

@token_required
def get(self, current_user): # Read Method
    users = User.query.all()    # read/extract all users from database
    json_ready = [user.read() for user in users]  # prepare output in json
    return jsonify(json_ready)  # jsonify creates Flask response object, more specific to APIs than json.dumps

Put

Frontend

The following code sends a put request to backend to update a user based on the user’s UID.

function update_user(){
      if (document.getElementById("password").value != document.getElementById("confirmpassword").value) {
        alert("Error: Passwords do not match.");
        return;
      }
      const body = {
        uid: document.getElementById("uid").value,
        password: document.getElementById("password").value,
        name: document.getElementById("name").value,
      };
      const AuthOptions = {
                  mode: 'cors', // no-cors, *cors, same-origin
                  credentials: 'include', // include, same-origin, omit
                  headers: {
                      'Content-Type': 'application/json',
                  },
                  method: 'PUT', // Override the method property
                  cache: 'no-cache', // Set the cache property
                  body: JSON.stringify(body)
              };
        // fetch the API
        fetch(url, AuthOptions)
          // response is a RESTful "promise" on any successful fetch
          .then(response => {
            // check for response errors and display
            if (response.status !== 200) {
                window.location.href = "/csp-blog/403.html";
            }
            // valid response will contain JSON data
            response.json().then(data => {
              // insert whatever code you want here
            })
        })
        // catch fetch errors (ie ACCESS to server blocked)
        .catch(err => {
          console.log(err)
        });
    }
    // Attach login_user to the window object, allowing access to form action
    window.update_user = update_user;

Backend

@token_required
def put(self, current_user):
    body = request.get_json() # get the body of the request
    uid = body.get('uid') # get the UID (Know what to reference)
    name = body.get('name') # get name (to change)
    password = body.get('password') # get password (to change)
    users = User.query.all() # get users
    for user in users:
        if user.uid == uid: # find user with matching uid
            user.update(name,'',password) # update info
    return f"{user.read()} Updated"

Delete

Frontend

Similarly to put, this code removes the appropriate user from the database

function delete_user(){
    const body = {
      uid: document.getElementById("duid").value,
    };
    const AuthOptions = {
                mode: 'cors', // no-cors, *cors, same-origin
                credentials: 'include', // include, same-origin, omit
                headers: {
                    'Content-Type': 'application/json',
                },
                method: 'DELETE', // Override the method property
                cache: 'no-cache', // Set the cache property
                body: JSON.stringify(body) // delete if using backend code that fetches directly from the cookie
            };
      // fetch the API
      fetch(url, AuthOptions)
        // response is a RESTful "promise" on any successful fetch
        .then(response => {
          // check for response errors and display
          if (response.status !== 200) {
              window.location.href = "/csp-blog/403.html";
          }
          // valid response will contain JSON data
          response.json().then(data => {
            window.location.href = "/csp-blog//log/2024/01/30/Users.html";
          })
      })
      // catch fetch errors (ie ACCESS to server blocked)
      .catch(err => {
        console.log(err)
      });
  }
  window.delete_user = delete_user;

Backend

Mr.Mortensen Version

def delete(self, id):  # Delete method
#Find user by ID 
user = User.query.get(id)
if user is None:
    return {'message': f'User with ID {id} not found'}, 404

''' Delete user '''
user.delete()
return {'message': f'User with ID {id} has been deleted'}

Ian’s Version

@token_required
def delete(self, current_user):
    body = request.get_json()
    uid = body.get('uid')
    users = User.query.all()
    for user in users:
        if user.uid == uid:
            user.delete()
    return jsonify(user.read())

Tarun’s Version

@token_required
def delete(self, current_user):
    token = request.cookies.get("jwt")
    cur_user= data=jwt.decode(token, current_app.config["SECRET_KEY"], algorithms=["HS256"])['_uid']
    users = User.query.all()
    for user in users:
        if user.uid == uid and user.id==cur_user: # modified with the and user.id==cur_user so random users can't delete other ppl
            user.delete()
        elif(user.uid == uid):
            print(cur_user," tried to delete someone who they are not")
            return 
    return jsonify(user.read())