Creating Database Table

Before getting into action with respect to CRUD operations, we need to prepare tables.

  • We can either use psql or SQL Workbench to connect to the database and create table directly.

  • Let us drop and create the table users if it already exists.

  • I am using Jupyter based environment for demo - feel free to use what ever is relevant to your project or comfortable for you.

One need to have decent database and SQL skills to be comfortable with all types of application development. Feel free to Master SQL using Postgresql as target database using this course or playlist.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%%sql 

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10

%%sql

DROP TABLE IF EXISTS users CASCADE;

%%sql 

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
AND table_name = 'users'
LIMIT 10

%%sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

  • Let us validate the objects that are created in the underlying database. We can either run query against information_schema or use Database Explorer in SQL Workbench or even psql.

%%sql 

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
AND table_name = 'users'
LIMIT 10

%%sql 

SELECT * FROM information_schema.columns 
WHERE table_name = 'users'
LIMIT 10

%sql SELECT * FROM users
Function - Get Database Connection Inserting Data into Table