Forem: Daragh Walsh

Database Normalisation (1st-4th N.F.) using an example

Daragh Walsh — Fri, 09 Oct 2020 13:22:36 +0000

Introduction

So we have just been hired as a database administrator for a car dealership. Currently they operate with only one table, which is below. I have also included some entries that exist in this table.

Initial Database Schema

CustomerName is the name of the customer (e.g. Alan Smith).

Purchase is what the customer bought (e.g. Corolla).

Address is the address of the customer (e.g. 2 college road).

Newsfeed is what kind of marketing information the customer wishes to receive (e.g. a booklet on Toyota car offers).

Supplier is the brand which supplies the car the customer purchases (e.g. Toyota)

SupplierEmail is the contact email address for the Supplier (e.g. toyota@toyota.inc)

Price is the selling price of the car

Example Entries

1st Normalisation Form

For the database to be in the 1st Normalisation Form we must satisfy the following conditions:

Each cell to be single valued
Entries in the column(s) is/are to be of the same type
Rows are to be uniquely identified

The current iteration of the table has many flaws. For example when you view the above sample data we cannot determine whether “Alan Smith” is the two different people or one person who bought two cars. We also have comma-separated values and this is very messy to deal with.

The first thing we will need to do is add or assign a column as the primary key and no field in the current instance of the database will satisfy the conditions of a primary key. So we will add a CustomerID field which will solve the person identification issue. Once we have made that change our table schema will look as follows:

And now when we look below at the data we now see that it was in fact two /different/ people named “Alan Smith” who purchased a car from us. As a result of this change we have also removed the issue of comma-separated values and each cell is now contains a single value.

2nd Normalisation Form

For the database to be in the 2nd Normalisation Form we must satisfy the following conditions:

All attributes (columns that are non-key) must be dependent on the key

We must look at each column in the table and ask ourselves, is this dependent on the CustomerID? When we take a closer look we can see that the CustomerName, Address and Newsfeed are dependant. Just ask yourself, “Do I need the primary key to associate this attribute with the correct entry?”. If the answer is yes, then keep it in the table.

Now when your answer is no then you must separate this attribute out into another table. When we separate out our values we now have the following two tables:

Note: for clarity sake, from now on I will highlight the primary key in orange

The issue now is that we have lost our transactions! Well, for that we will want to create a junction (or associative) table. Now for anyone who doesn’t know what a junction table is don’t worry. It is just the name given to a table that maps two or more tables together by referencing the primary keys of each table. The entries are known as compound keys. See what it looks like below for more clarity.

3rd Normalisation Form

For the database to be in the 3rd Normalisation Form we must satisfy the following conditions:

All fields (columns) can be determined only by the key in the table (and not any other column).

Now if we look at the table on the right (the purchases table) you will notice that the Supplier and the SupplierEmail will always be the same (i.e. the Toyota contact email will never be matched to the Renault supplier). As you add more sales of different cars you will notice that you would get duplicate data for these fields.

Now let’s say we got a new rep for Renault and she contacted us to say that we are to update the email address for Renault to their address. We would have to go through all the entries in the Purchases database and update the SupplierEmail field. This is not acceptable to us! What we will do is pull out the Supplier and SupplierEmail and place it in a different table. We will then reference this table via the use of a foreign key. A foreign key is simply an entry that references the primary key of another table.

Note: the foreign key is in blue, which now references the new supplier table

We have removed the redundancy and can update the contact information in one place.

4th Normalisation Form

For the database to be in the 4th Normalisation Form we must satisfy the following conditions:

No multi-valued dependencies

So a multi-valued dependency exists in a table when two attributes in the table are independent entries, but are both dependent on a third attribute.

This can be seen above, CustomerName, Address and Newsfeed all depend on the CustomerID. Now what happens when a customer wants to unsubscribe from receiving the monthly or quarterly brochures from their preferred car brand(s)? Do we place a /null/ in the Newsfeed column? No, this would be inefficient and just a mess to maintain. Instead we will move the Newsfeed attribute to a new table.

Final Database Schema

So in the end we arrive at this structure. These tables all adhere to the various conditions and will result in better data management and maintenance for you (the database administrator).

Thanks for taking the time to read this, let me know what thought of it! Or inform me of any changes I could make to the post.

Handling password changes with Next.js, GraphQL and Mikro-ORM

Daragh Walsh — Tue, 29 Sep 2020 19:03:51 +0000

Posted to [my blog](https://dwalsh.surge.sh/changing-password-with-next-js/) first! Please feel free to read it there.

Introduction

I am going to assume you have your application up and running, if you have not set it up yet I suggest just following along with their docs and then coming back to here. A great thing about Next.js is that you can get started very quickly.

For the login portion of this I will assume you have that handled, however I will probably write another post about handling that with this stack soon and link it here in case anyone struggles with it. It is also important to note that I will include examples and explanations of my backend code and although I understand that you may not be using this exact stack, it will be useful for explaining the logic behind my decisions.

Some of the main packages I am using are mikro-orm, type-graphql, urql, graphQL, Apollo server express, nodemailer, redis and uuid. I will again point this out in my code snippets as we go along.

Below are the steps we will take when a user wants to change their password.

Steps

User selects forgot password on the website
We check if the email is valid and in use
We generate a token for this user
We email the user a link to change the password with this token in the url
The user submits the change password form
We handle this password change on our backend and delete the token

Now let’s get started!

Backend Logic

When developing out certain features I like to have the backend logic roughly completed first and I then implement the frontend for it and making any necessary adjustments. As my backend uses graphQL the first step is to create my function which handles the user requesting an email to change their password.

My Context

I just want to place here my context, which is accessible in all my resolvers. The request and response objects are pretty standard and I got their types simply from hovering over them in VSCode. What important to note here is the em and redis objects. The em object is the ORM which is configured to connect to my database and the redis object is used to access my redis instance, which is where user sessions are stored.

  // my context
  context: ({ req, res }: MyContext) => ({
      em: orm.em,
      req,
      res,
      redis,
    }),
  //...

  // types.ts
  export type MyContext = {
  em: EntityManager<any> & EntityManager<IDatabaseDriver<Connection>>;
  req: Request & { session: Express.Session };
  res: Response;
  redis: Redis;
};

Forgot Password Mutation

This mutation takes an email parameter and will return a boolean depending on whether the email address was valid and if the link could be sent. Below you will see the definition of this function and a first look at the use of type-graphql.

@Mutation(() => Boolean)
  async forgotPassword(@Arg("email") email: string, @Ctx() { em, redis }: MyContext) {
  // ...
  // ...
  }

The reason for using type-graphql is because it allows you to define schemas using only their decorators. It then allows us to inject dependencies into our resolvers and put auth guards in place, all while cutting down on code redundancy.

So the function takes an email parameter and accesses the em and redis objects (see here for clarification). The first thing we will do is check if the email address is in the database and return false if it is not present.

  // ...
const person = await em.findOne(User, { email });
    if (!person) {
      return false;
    }
  // ...
  }

If the user is present we will generate a token using uuid's v4 function. This token is stored with the forgot-password: prefix and the key is the user's id field. The token will expire 3 days after the user makes the request.

// ...
const token = v4()
redis.set(
  `${FORGET_PASSWORD_PREFIX}${token}`,
  person.id,
  "ex",
  1000 * 60 * 60 * 24 * 3
) // 3 days
// ...

Once the token is set and stored we will send the user the email with the link. This link will include the token and we use this to identify the user.

//..
await sendEmail(
      email,
      `<a href="http:localhost:3000/change-password/${token}">reset password</a>`
    );
    return true;
}

The contents of the sendEmail function are taken directly from the example given in the Nodemailer docs. For clarity I will include it below.

let testAccount = await nodemailer.createTestAccount()
console.log("test account: ", testAccount)
let transporter = nodemailer.createTransport({
  host: "smtp.ethereal.email",
  port: 587,
  secure: false, // true for 465, false for other ports
  auth: {
    user: testAccount.user, // generated ethereal user
    pass: testAccount.pass, // generated ethereal password
  },
})

let info = await transporter.sendMail({
  from: '"Sample Person" <foo@example.com>', // sender address
  to: to, // list of receivers
  subject: "Change Password", // Subject line
  html,
})

console.log("Message sent: %s", info.messageId)

console.log("Preview URL: %s", nodemailer.getTestMessageUrl(info))

Forget Password Page

Now in our Next.js application, in the ./src/pages/ folder, we will create a change-password folder. In this folder we create a [token].tsx file.

(So the full path will be ./src/pages/change-password/[token].tsx)

Dynamic Routing

In Next.js the [param] file syntax is used for dynamic routes. This parameter will be sent as a query parameter to this page.

The next step is you must then decide when you will need to access this on the page via the props. This can be accomplished a handful of functions given to us by Next.js, however the use case will decide what function.

The three options too us are:

getServerSideProps
getStaticProps

I choose getServerSideProps as the data must be fetched at request time. We do not have a list of possible token's at build time.

The docs for getStaticProps states that we should only be using this function if:

The data required to render the page is available at build time ahead of a user’s request.

So in our [token].tsx file we start with the following scaffolding:

import { GetServerSideProps, NextPage } from "next";

const ChangePassword: NextPage<{token: string}> = ({token}) => {
  return (
    //..
    // form goes here
    //..
  )
};

export const getServerSideProps: GetServerSideProps = async ({ params }) => {
  return {
    props: {
      token: params.token,
    },
  };
};

export default ChangePassword;

As we use dynamic routing, params contains this dynamic data. The reason we use params.token is because we named our file [token].tsx. If we were to name it [user-id] then the props passed would be token: params.user-id.

I then use Formik and urql to handle form state and sending the data to the server. Once the form is submitted without any errors back from the server the user is logged back in with the new password and redirected to the home page. This will now take us back to the backend for handling this data submission.

Handling the password change

Once we are back in our resolvers we create the changePassword resolver and it is important to take the time to define the type for the response to this. We can then make use of this type then when we generate our types in the frontend with the graphql-codegen package.

The UserResponse object will return an array of errors (each with a field and message field) and the user, with both having the option of being null. I choose an array of objects because I have a helper function for the frontend which will map the errors to the appropriate formik field and display them accordingly (I got this function from a Ben Awad video and I will include this below).

// toErrorMap.tsx
import { FieldError } from "../generated/graphql";

// map errors accordingly
// taken from Ben Awad video
export const toErrorMap = (errors: FieldError[]) => {
  const errorMap: Record<string, string> = {};
  errors.forEach(({ field, message }) => {
    errorMap[field] = message;
  });
  return errorMap;
};

// form.tsx
// usage example in a formik form
const form = () => {

  const handleSubmit = (values, {setErrors}) => {
    // send data via graphql
    const response = sendDataViaGraphl(values);
    if (response.data?.errors) {
      // if there’s errors
      setErrors(toErrorMap(response.data.errors))
    }
  }
  return (
  // form down here
  )
}

Below is the schema typings I described above for the data returned from the mutation.

@ObjectType()
class FieldError {
  @Field()
  field: string
  @Field()
  message: string
}

@ObjectType()
class UserResponse {
  @Field(() => [FieldError], { nullable: true })
  errors?: FieldError[]

  @Field(() => User, { nullable: true })
  user?: User
}

Now onto the changePassword function itself! It takes 2 arguments, token and newPassword. From our context again we take the redis, em and req objects. We also state our response type as the previously defined UserResponse type.

@Mutation(() => UserResponse)
  async changePassword(
    @Arg("token") token: string,
    @Arg("newPassword") newPassword: string,
    @Ctx() { redis, em, req }: MyContext
  ): Promise<UserResponse> {
  // ...
  // ...
  };

The first thing we will check is the password length, it is just a basic security measure. Again make sure to note that this return matches the errors type we defined above.

// ...
{
  if (newPassword.length <= 5) {
    return {
      errors: [
        {
          field: "newPassword",
          message: "password is not long enough",
        },
      ],
    }
  }
}
// ..

Next we move onto checking the redis database for the users id. Remember, we are accessing the redis object via context.

// ..
const key = FORGET_PASSWORD_PREFIX + token
const userId = await redis.get(key)
// ..

Now we apply some more checks to see if the user exists both the redis and user database and if either fails we return the appropriate errors (and their corresponding messages).

// ..
if (!userId) {
  return {
    errors: [{ field: "token", message: "token expired" }],
  }
}
const user = await em.findOne(User, { id: parseInt(userId) })
if (!user) {
  return {
    errors: [{ field: "token", message: "token expired" }],
  }
}
// ..

If there is no issues with finding the user, we then hash the password taken as a function argument and update the database.

As a security measure we delete the key from redis so the user (or someone else) cannot go back and use the same token again.

Finally we login the user using the req object via the use of a session and return the user.

// ..
user.password = await argon2.hash(newPassword);
    em.persistAndFlush(user);
    await redis.del(key);
    req.session.userId = user.id;

    return { user };
};

And that's it! The user will be logged in on the frontend when they end up back on the home page.

Final Notes

Thanks for taking the time to read this. Should you have any feedback or questions please feel free to reach out and let me know!

Managing user roles in React using CASL!

Daragh Walsh — Sat, 16 Mar 2019 14:57:32 +0000

Also posted on my blog!

So the best place to start when deciding authentication systems is use case. For myself it was being implemented in a team software project as part of my degree.

We had to implement a grants proposal system which required various user interfaces for the different types of users.

The user roles we had in our system were:

Researcher
Reviewer
Admin

Libraries

CASL

From some research online I found CASL (which has a nice ReactJS package). CASL (pronounced castle) is described by the author as:

An isomorphic authorization JavaScript library which restricts what resources a given user is allowed to access.

From reading up on this package it seemed perfect for my use case.

Redux

Needs no introduction really, everyone who uses React knows about Redux. This was what I was most comfortable with for the storage of user information and the various responses to API calls within the application.

Implementation

I am going to continue on the premise that you have a functional redux store.

Install Packages

To begin, you must first install the CASL packages necessary. To do so run:

npm i @casl/react @casl/ability

Scoping Can

For this section I will be operating with 2 files, ability.js and Can.js. Both these files I have placed in a config folder. For help with file structure, please see this helpful post by Dan Abramov.

Why should we scope Can? Well, if you don't scope it you must pass the ability we are checking against with every Can call (e.g. <Can I="create" a="Post" ability={ability}>, where ability is the abilities we defined in the ability.js file, or wherever you placed the abilities).

Scoping was implemented for cases where you define several abilities in your app or you want to restrict a particular Can component to check abilities using another instance.

I took the implementation for our Can.js file from the docs:

// Can.js
import { createCanBoundTo } from "@casl/react"
import ability from "./ability"

export default createCanBoundTo(ability)

We import our ability (defined in the next section) and scope this particular Can component to handle the those abilities.

Defining Abilities For User Roles

// Can.js
import { createCanBoundTo } from "@casl/react"
import ability from "./ability"

export default createCanBoundTo(ability)

As you saw above, we import ability, which is where all user permissions are defined. So lets go to that file now. I am going to break it down into sections and then at the end show you the entire file.

//ability.js
import { Ability, AbilityBuilder } from "@casl/ability"
import store from "../store/index"

// Defines how to detect object's type
function subjectName(item) {
  if (!item || typeof item === "string") {
    return item
  }
  return item.__type
}

const ability = new Ability([], { subjectName })

Okay, so what's going on here? The subjectName function takes in the object and will return the property __type of that object if it exists. Otherwise if the item passed is a string it will simply return that string, etc (I.E. if you pass subjectName('Admin') it will return 'Admin').

//ability.js
import { Ability, AbilityBuilder } from "@casl/ability"
import store from "../store/index"

// Defines how to detect object's type
function subjectName(item) {
  if (!item || typeof item === "string") {
    return item
  }
  return item.__type
}

const ability = new Ability([], { subjectName })

Now, what is this? Well, this is one of two ways to define an Ability instance. What we are doing here is defining an empty Ability instance, which will use the provided subjectName to help decide what rules to attach to a particular user.

Next, we will bring in the redux store to get the current logged in user, if there is any:

//ability.js
...
const ability = new Ability([], { subjectName });

let currentAuth;
store.subscribe(() => {
  const prevAuth = currentAuth;
  currentAuth = store.getState().currentUserReducer;
  if (prevAuth !== currentAuth) {
    ability.update(defineRulesFor(currentAuth));
  }
});

Here we are subscribing to changes in the store and will call the ability.update(defineRulesFor(currentAuth)) method with the current user in the store when the store updates the currentUserReducer object. For reference, here's my currentUserReducer object:

//CurrentUserReducer
const initialState = {
  isLoggedIn: null,
  user: null,
  role: "",
  errorMsg: "",
}

But wait, what's the defineRulesFor function? Well, we implement this ourselves. Here we will return the rules for the current user based on their role. Here is our function:

//ability.js
// this is just below store.subscribe()

function defineRulesFor(auth) {
  const { can, rules } = AbilityBuilder.extract()
  if (auth.role === "researcher") {
    can("view", "Proposal")
    can("view", "Draft")
    can("apply", "Proposal")
    can("view", "Profile")
    can("view", "Teams")
  }
  if (auth.role === "admin") {
    can("add", "Proposal")
    can("view", "Proposal")
    can("accept", "Application")
    can("reject", "Application")
    can("view", "PendingReviews")
  }
  if (auth.role === "reviewer") {
    can("review", "Proposal")
  }
  return rules
}

We are using CASL's AbilityBuilder to define the abilities for the user. We are calling the extract() method simply to make things more legible (avoid nesting). Otherwise it would look something like this:

function defineRulesFor(auth) {
  return AbilityBuilder.define((can, cannot) => {
    if (user.role === "researcher") {
      can("view", "Proposal")
      can("view", "Draft")
      can("apply", "Proposal")
      can("view", "Profile")
      can("view", "Teams")
    }
  })
  //etc.
}

So this is just for my personal preference, both are perfectly fine I just find the first option easier to read. All you have to make sure to do (if you are going with option 1) is to return rules at the end of this function.

Now, let's take the researcher role for an example to explain what's going on. We are saying that if the user is a researcher we want them to be able to:

View a Proposal
View a Draft
Apply for a Proposal
View a Profile
View Teams

The can function will add these abilities to the rules for this user, once we have the rules defined for the user we then return them at the end of the function.

Once that's done we now have to make sure to export the ability we previously defined (and updated the rules accordingly).

//abilty.js
function defineRulesFor(auth) {
  ...
  if (auth.role === "reviewer") {
    can("review", "Proposal")
  }
  return rules
}
export default ability;

Now, we have covered how I specified the role based rules for each role. Let's get to implementing them in the UI!

Checking rules in the UI

I will give two examples here where I have done this, one is which menu items appear in the sidebar for users to click, which takes them to a particular route, and the other is in rendering the routes only if you have the correct role.

Sidebar

We now use the Can component we previously defined (see the Can.js file above) to conditionally render components. Here is the SidebarRoutes component which renders ListItemLink's where you pass the route and text displayed on the menu item:

//SidebarRoutes.jsx
//Other imports here
import Can from '../../config/Can';

...

const SidebarRoutes = ({ classes }) => (
  <List className={classes.root}>
    <ListItemLink text="Home" />
    <Can I="view" a="Profile">
      {() => <ListItemLink route="profile" text="Profile" />}
    </Can>
    <NestedProposals />
  </List>
);

We import the Can component and check if I can view a Profile. If this is true then it will render the ListItemLink, otherwise it simply won't render it.

I do the same thing for the various rules in the NestedProposals component, which a snippet of can be seen below:

//NestedProposals.jsx
...
<Can I="add" a="Proposal">
    {() => (
        <ListItemLink
        route="admin/proposals/add"
        text="Add Proposals"
        className={classes.nested}
        />
    )}
</Can>
<Can I="review" a="Proposal">
    {() => (
        <ListItemLink
        route="proposals/respond"
        text="Respond To Applications"
        className={classes.nested}
        />
    )}
</Can>
...

Essentially the same thing. I check if user roles permit them to do certain things, and if they are allowed I will render the link.

Routes

So again I will give a snippet of my routes.jsx file. Here it is:

//routes.jsx
...
const Routes = () => (
  <Switch>
    <Route exact path="/" component={GridCards} />

    <Route
      path="/profile"
      render={props => (
        <Can I="view" a="Profile">
          {() => <Profile {...props} />}
        </Can>
      )}
    />
</Switch>
...

So we make use of React Router's render prop to let us check the rules of the current user and do the appropriate rendering. As you can see it's pretty much the same across the board for implementation once you have the rules properly defined.

End

Thank you for reading! I would appreciate any input (positive/negative) on my writing to improve it going forward. Any thoughts/queries please feel free to shoot me a DM on Twitter.

Entire `ability.js` file

/* eslint-disable no-underscore-dangle */
import { Ability, AbilityBuilder } from "@casl/ability"
import store from "../store/index"

// Defines how to detect object's type
function subjectName(item) {
  if (!item || typeof item === "string") {
    return item
  }
  return item.__type
}

const ability = new Ability([], { subjectName })

let currentAuth
store.subscribe(() => {
  const prevAuth = currentAuth
  currentAuth = store.getState().currentUserReducer
  if (prevAuth !== currentAuth) {
    ability.update(defineRulesFor(currentAuth))
  }
})

function defineRulesFor(auth) {
  const { can, rules } = AbilityBuilder.extract()
  if (auth.role === "researcher") {
    can("view", "Proposal")
    can("view", "Draft")
    can("apply", "Proposal")
    can("view", "Profile")
    can("view", "Teams")
  }
  if (auth.role === "admin") {
    can("add", "Proposal")
    can("view", "Proposal")
    can("accept", "Application")
    can("reject", "Application")
    can("view", "PendingReviews")
  }
  if (auth.role === "reviewer") {
    can("review", "Proposal")
  }
  return rules
}

export default ability

Forem: Daragh Walsh

Database Normalisation (1st-4th N.F.) using an example

Introduction

Initial Database Schema

Example Entries

1st Normalisation Form

2nd Normalisation Form

3rd Normalisation Form

4th Normalisation Form

Final Database Schema

Handling password changes with Next.js, GraphQL and Mikro-ORM

Introduction

Steps

Backend Logic

My Context

Forgot Password Mutation

Forget Password Page

Dynamic Routing

Handling the password change

Final Notes

Managing user roles in React using CASL!

Libraries

CASL

Redux

Implementation

Install Packages

Scoping Can

Defining Abilities For User Roles

Checking rules in the UI

Sidebar

Routes

End

Entire ability.js file

Entire `ability.js` file