Tables in SQL

A single database can have multiple tables inside. Think of them as collections in a MongoDB database.

Until now, we have a database that we can interact with. The next step in case of postgres is to define the schema of your tables.

SQL stands for Structured query language. It is a language in which you can describe what/how you want to put data in the database.

To create a table, the command to run is -

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

There are a few parts of this SQL statement, let’s decode them one by one

1. CREATE TABLE users

CREATE TABLE users: This command initiates the creation of a new table in the database named users.

2. id SERIAL PRIMARY KEY

• id: The name of the first column in the users table, typically used as a unique identifier for each row (user). Similar to _id in mongodb
• SERIAL: A PostgreSQL-specific data type for creating an auto-incrementing integer. Every time a new row is inserted, this value automatically increments, ensuring each user has a unique id.
• PRIMARY KEY: This constraint specifies that the id column is the primary key for the table, meaning it uniquely identifies each row. Values in this column must be unique and not null.

3. email VARCHAR(255) UNIQUE NOT NULL,

• email: The name of the second column, intended to store the user's username.
• VARCHAR(50): A variable character string data type that can store up to 50 characters. It's used here to limit the length of the username.
• UNIQUE: This constraint ensures that all values in the username column are unique across the table. No two users can have the same username.
• NOT NULL: This constraint prevents null values from being inserted into the username column. Every row must have a username value.

4. password VARCHAR(255) NOT NULL