How to Add User Registration Using PostgreSQL and Javascript - Part 1: Set up the Database 708

Introduction

In this tutorial, we will build a registration system that allows users of your Postgres- and Node.js-based web application to create a username and password, add the new user to our database, send that new user a confirmation email, receive a response from their click on the link we sent them in that email, and set a flag in the PostgreSQL database that the user is confirmed. This article is part one of a five-part series. In this series, we will address the following: – Part 1: Add a users table to a Postgres database. – Part 2: Build the registration form using HTML, CSS, Javascript, and Postgres. Our interaction with the database will be minimal in this part 2. Note: In the interest of keeping this tutorial as simple as possible, we’ll save creating a responsive layout for a future article. – Part 3: Validate user input, create a hash of the user’s password, and insert this data into PostgreSQL. We’ll use Node.js for this part of the tutorial. – Part 4: Give the user feedback on the screen and send them a confirmation email. Again, we’ll use Node.js and Postgres for this part of the tutorial. We’ll also be using HTML and CSS. – Part 5: Receive email confirmation from the user and set a flag in the PostgreSQL database using Node.js.

Overview of this article

In this part 1 of our series, we will add a table called users to our relational database, along with all the fields (columns) needed. The SQL script for doing this is provided below for your ease. Our overall goals include: – Modularity for easy future changes and expansion. – Clarity, consistency, and readability of fields (see the Naming conventions section below). – Ease for you to both understand and quickly copy/paste the code below into your database management tool’s ‘Run SQL’ feature.

Assumptions and prerequisites

We’ll assume the following for the purposes of you being able to follow this part 1 of the 5-part tutorial: – You have a PostgreSQL database set up. – You have access to a database management tool like PGadmin, DBeaver, or any of the numerous others that allow you to create and manage tables in your Postgres database. – You know how to run SQL commands with your database management tool in order to create the users table below.

Note: This tutorial is specifically created for Postgres. However, If you have little or no experience with PostgreSQL OR if your database is actually MySQL, Microsoft SQL Server, or some other database platform, the instructions below – while not guaranteed – should work fine or require very little adjustment.

Create the users table

Paste the code below into your db management tool’s Query tool. If you are using PGadmin, do the following: (a) Select your database. In the image below we called ours “practice”. (b) Click the “Tools” dropdown menu. (c) Choose “Query Tool”. (d) In the right pane labeled “Query Editor,” paste in the code we’ve given you below that begins with “CREATE TABLE public.users”.


When the above code is executed, we get the following table:


Autoincrement

Did you notice that we chose the “serial” type for the id field? With PostgreSQL, when you choose “serial” as the data type for a field (column), the database tool will place the following into the “default [value]” property for that field in your users table:

The clue here is the system’s use of the terms “nextval” and “seq”. Used in the overall syntax we see here, the result is that any time we create a new row in the users table, the id field is autoincremented by 1. Here is a picture illustrating the results of this process:


Naming conventions

You may have noticed the “t”, “i”, “d”, and “b” prefixing our field (column) names. For purposes of consistency, organizatione, code re-use, elegance, and overall efficiency, we recommend use of prefixes like these (or of your own design). If you want to know more about our take on naming conventions, we invite you to look at this article: [https://kb.objectrocket.com/postgresql/postgresql-naming-conventions-637] (https://kb.objectrocket.com/postgresql/postgresql-naming-conventions-637)

Extra fields?

For the purpose of this tutorial, you will not need the following fields: – t_name_first – t_name_last – t_phone – i_security_level

That said, there is no impact on performance or storage by leaving these fields in for now. Why leave them in? When you expand the scope of your application, it is likely those fields – and more – will later be needed.

Conclusion

You now have a users table in your PostgreSQL database. In the subsequent parts of this five-part tutorial, we’ll use that table to save and get data. Look for part 2, with a title like “Build a registration form using HTML, CSS, Javascript, and Postgres.”