Forem: vinodchauhan7

Migrations SQL script file generated using dotnet ef command is not valid for Azure MySQL DB

vinodchauhan7 — Tue, 29 Jun 2021 13:17:18 +0000

Migrations SQL script file generated using dotnet ef command is not valid for Azure MySQL DB

Jun 29 '21 Comments: 1 Answers: 0

We have setup build pipeline for automatic generations of the migration scripts. And in release pipeline, We are using above attached migration script file to deploy changes on Azure MySQL DB but its giving us an error.

MySQL version on Azure : 8.0 DotNet version…

Open Full Question

PostgreSQL Tutorial for beginners to Advance level.

vinodchauhan7 — Fri, 24 Jan 2020 09:28:08 +0000

Hi All, Today I gonna try cloning dev.to DB at very small level. In this article, we will learn how to make relationship between tables and how to query within tables to get desired results.

At first we will have 'users' tables in which we can save our user data.

create table users (
    id serial primary key not null,
    first_name varchar(255) not null,
    last_name text not null,
    age int,
    email text unique not null
)

/*
1) create table 'tableName' is a syntax for creating tables.

2) 'id' will be our unique key. Its like unique identification number of 
   user. It will advance in positive direction and cannot be null. To fullfill
   this requirement we need below keywords together.
   'serial' will move from 1 to n position.
   'primary key' will sure that 'id' field will be unique always for this table.
   'not null' also assures that this field cannot be null.

3) 'first_name' will be the first name of our users. It will have string to save 
    and it cannot be null.To fullfill we need below keywords.
    'varchar(255) to save string.
    'not null' to assure that this field will not be null.

NOTE : we are using 'snake_case' for first_name instead of 'camelCase'. To save 
       fieldname in 'camelCase' we need to give field name in double quotes like
       "firstName", if we dont give name in double quotes then it became 
       'firstname'.
4) 'last_name' is similar to 'first_name' for saving last_name of the user.
   We use 'text' for saving strings which is similar to varchar without limit.

5) 'age' will be a integer value to save users age. 'int' keyword will be use for 
   this.

6) 'email' will be a unique field for logging and communicating to user. It 
    cannot be null also.
   'text' for saving email in string format.
   'unique' to save unique emails only.
*/

Our 'users' table is created now but it is empty as of now, lets create some data in it.

insert into users (first_name,last_name,age,email) 
values ('Earnestine','Fahey',45,'Bernita17@gmail.com');

/*
   'insert into tableName(fields) values(data)' to insert data.

NOTE : We need not to specify 'id' field here because we have provided 'serial' property to it so that it will initialize and increase it automatically.

//Lets insert some more rows.
insert into users (first_name,last_name,age,email) values ('Michaela','Grant',24,'Abbie_Hintz@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jessie','Murphy',9,'Ibrahim58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Eddie','Homenick',60,'Lea.Schuster@gmail.com');
insert into users (first_name,last_name,age,email) values ('Osbaldo','Mueller',23,'Jayden_Brekke@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Garth','Murray',50,'Mikel85@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Corene','Kilback',71,'Shyanne.Kling44@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Alexane','Fritsch',28,'Karli.Zemlak39@gmail.com');
insert into users (first_name,last_name,age,email) values ('Luz','Pouros',75,'Giovanny.Parisian@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Lavinia','Breitenberg',79,'Don55@gmail.com');
insert into users (first_name,last_name,age,email) values ('Euna','Schuppe',33,'Odessa95@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Della','Shields',38,'Brielle_Rippin58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Loyce','Powlowski',68,'Ernest56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Cecelia','Morissette',50,'Glennie_Moore39@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jody','Swift',40,'Caden56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Malachi','Marks',40,'Terence.Crona88@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Claud','Hegmann',29,'Madelynn_Lueilwitz65@gmail.com');

*/

In order to see our data, let hit the below query :


  select * from users;
  'select' means to select the fields from table.

NOTE: Here * can be replaced with fieldName such as first_name,last_name. For ease and visual reprenstation of all fields, I have used *.

If we see the values in table, some users are might less than 12 years of age, which we dont want to register. We might want to set age limit greater than 15 years. For this, we first need to delete all users who have age less than 15 years and set constraint on age to accept age only 18+.

//delete users whose age is less than 18
  delete  from users where age < 18;

//Set constraints on age to accept only user who are 18+
  alter table users ADD CHECK (age >= 18);

//After this if we try to add user who has age less than 18 will not be allowed to register.E.g
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');

//However if user grown up and try to register then it will be able to register.
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',18,'Kayleigh_Jaskolski@yahoo.com');

However, we can add or drop column even after table creation.

alter table users drop column age;
alter table users add column age int default 18;

WHERE keyword

With 'where' we can set condition in our select query to return only those values which satisfied the condition given.For example :

select * from users where id = 43;
select * from users where id=45 or id=2;
select * from users where id=45 and first_name = 'Eddie';
select * from users where id in (42,43,44); //Array
select * from users where age > 38;

UPDATE ROW

To update any user data, we can use update as follows:

update users set age = 20 where id = 1;

update users set age = age + 1, last_name =  last_name || ' tom' where id = 1;

NOTE: To concatenate String in postgresql we can use '||'.

Now we are done with 'users' basic details, we can add if we want more but this tutorial I am advancing further with this only.We now need a 'posts' table where user's post detail can be saved in DB. Lets start with it.

Foreign Key Concept

Before moving further, we must keep in mind one thing in order to create 'posts' table that it must be linked with 'users', so that we know which user has posted that particular post. To achieve this we are going to use references which are also called foreign key concept to link both the tables.Let look how we can achieve this.

Its also called "one to many relationship"

create table posts(
        id serial primary key,
        title text not null,
        body text default '...', //default will set '...' by default if 
                                         //no value is given.
        "creatorId" int references users(id) not null
//with references we are attaching users's table primary key 'id' to 'creatorId' 
//so both are tied together and our application can identify which user has created the post.
        )

NOTE: we are using double quotes for showing the camelCase in postgresql.

Insert with references

insert into posts (title,body,"creatorId")
 values (
'global extend supply-chains', //title
'Voluptas natus iste hic nihil ut. Ipsam delectus sed ut et blanditiis non et. Consequuntur cumque beatae quae sint voluptas nulla voluptate delectus quo. Sit libero neque est distinctio enim neque et consequuntur aut. Odio fuga voluptatem non minus.

Vero nisi non saepe occaecati illo. Delectus neque ut sint eius rerum. Expedita quo voluptas porro aut libero itaque.

Aliquid nulla similique aperiam atque assumenda ea sint cum. Est qui repellendus quia aliquid accusantium. Ea dolores perferendis libero ipsam qui. Autem itaque excepturi adipisci asperiores eaque eaque doloribus consequatur dolorum.',//body
48); // ###references ID which is userID


//Other Posts with references.

insert into posts (title,body,"creatorId") values ('efficient implement platforms','Quam aliquid odit inventore est ipsa. Nulla odio accusantium repellendus in inventore repellat laudantium ea atque. Quas aliquid et et totam. Dolor corporis sequi sequi dolorem hic. Voluptas veniam quia.

Eveniet non vitae quia sed. Alias sunt ut consequatur aut quas eius eum facilis. Molestiae quaerat optio quia aut quia nobis.

Consequatur ut iure sunt quam eum deleniti et. Minima non perspiciatis dolore nesciunt ut sit. Aliquid dignissimos tempora enim.',56);
insert into posts (title,body,"creatorId") values ('world-class reinvent web-readiness','Sit magnam vero qui. Eos quibusdam accusamus exercitationem qui et. Ratione rerum ut consequatur quibusdam id omnis quia consequatur. Quae et rem veniam beatae aut ipsum quas laudantium ex.

Sit ullam eos. Aut quae ut ipsam doloremque dolor et praesentium omnis. Et ea et beatae repudiandae neque et modi earum sed.

Impedit nesciunt dolorem maiores ut iste. Quod aliquam asperiores reprehenderit ut ad aperiam et architecto est. Eaque blanditiis quasi ut enim blanditiis qui.',53);
insert into posts (title,body,"creatorId") values ('seamless repurpose e-commerce','Est et modi sed consequatur beatae aut ad. Excepturi voluptate amet odit occaecati minus enim. Maiores non consectetur ipsam.

Delectus quod aperiam velit cum quibusdam aut totam sit impedit. Aut quis veniam enim maiores ut. Quos tempora ducimus delectus qui. Quas minima ipsum.

Necessitatibus quis labore occaecati consequatur alias autem et. Sed et nulla et vitae quidem itaque odio hic nesciunt. Ut qui itaque pariatur beatae voluptatem laborum.',56);
insert into posts (title,body,"creatorId") values ('frictionless incubate e-commerce','Deserunt facilis alias animi qui voluptatum. Amet dolor iusto fuga tempora. Et deleniti odit et. Est autem vel harum et quis laudantium. Quam quia at vel non eveniet dignissimos impedit. Dolor necessitatibus blanditiis maxime ea.

Aperiam ab aliquam ad impedit dolorem perspiciatis asperiores. Quia qui quo est eveniet eligendi maxime sequi sed reprehenderit. Officia perferendis qui animi et odit repellendus beatae. Enim qui modi omnis impedit.

Qui voluptas sequi aut assumenda. Nemo a expedita dolore. Eum quis sed ut eos. Aut nulla officia eos rerum sed optio tempore.',43);
insert into posts (title,body,"creatorId") values ('frictionless grow synergies','Esse et quos magni. Qui laborum fugiat itaque in ipsum. Ex officiis reprehenderit et quae ratione sit. Error illum nam beatae similique. Eligendi voluptatibus sint nesciunt aut iure.

Consectetur sequi ut. Possimus sit expedita omnis at. Et aut dignissimos facere non ducimus itaque. Non expedita quas sit odit. Quo aperiam eum assumenda. Consequuntur deserunt voluptate repudiandae autem officia harum totam enim minus.

Esse autem perferendis. Et illum non perspiciatis sit ex. Sed animi at optio totam nobis quas dignissimos dolorem ut.',48);
insert into posts (title,body,"creatorId") values ('leading-edge disintermediate e-commerce','Delectus et accusantium nostrum. Est et illo rerum voluptatum autem. Explicabo qui et pariatur ut. Explicabo non sit sint autem ut. Quaerat numquam rerum.

Ut odit tempora explicabo totam similique enim consequatur. Qui tempora deserunt iusto praesentium. Atque illum facilis et accusamus sint. Facilis deserunt ipsam culpa laboriosam tenetur libero. Aspernatur itaque doloribus. Assumenda qui voluptatem sequi.

Eveniet eum dolor corrupti facilis. Repellendus cum sapiente tempora illo eos. Sint omnis saepe quisquam et omnis tenetur. Voluptas quod voluptatem.',52);
insert into posts (title,body,"creatorId") values ('cutting-edge integrate methodologies','Adipisci quia veniam. Eos minus reiciendis. Cum aut nesciunt ipsa accusantium et minima aut quasi similique. Natus illo debitis saepe ut est. Sapiente nostrum sunt.

Consequuntur tenetur quidem reiciendis. Voluptatem corporis nulla asperiores ea ut quo. Sint consequatur aperiam voluptatem blanditiis earum reiciendis dolor id id. Et fugit et sed reprehenderit. Dolores mollitia pariatur molestiae et tempora ratione.

Deleniti unde sequi nesciunt. Officia itaque ratione harum sit quisquam ut illo ullam accusantium. Est illo et mollitia quo repellat.',51);
insert into posts (title,body,"creatorId") values ('seamless visualize methodologies','Est voluptatem dolorem unde omnis ipsam sunt ipsum. Ab optio esse. Quos sed sint officiis dicta sit dolorem eum. Porro itaque delectus cumque error. Voluptatem earum error nisi aut qui sunt eos.

Dolores id quia nesciunt deleniti. Consequatur sint sit provident omnis eveniet at. Sed odio suscipit minus non minima perspiciatis. Voluptate distinctio illum aliquid.

Voluptas esse omnis ea. Iusto adipisci animi id. Adipisci ut aliquam commodi qui consequatur nam minima.',45);
insert into posts (title,body,"creatorId") values ('compelling evolve synergies','Nesciunt quia quis voluptatum vel molestiae velit. Iste optio mollitia. Est a sapiente ut facere veniam quia illum molestias. Impedit fugit delectus. Voluptatem harum fugiat quis est. Minima sint qui.

Quia dicta voluptas adipisci odio beatae rem minus id. Explicabo sapiente nam quidem qui ut nisi. Autem laboriosam dicta consequatur illo sunt eos et. Tempore aut quia impedit quia neque voluptas qui sed commodi. Cumque placeat magni quia blanditiis molestiae. Occaecati aut quia perspiciatis omnis modi asperiores est possimus sit.

Sed enim consequatur non repellendus est. Laborum veniam eaque unde quae in dolore. Modi qui ut omnis exercitationem suscipit nisi. Suscipit eligendi maiores et consequatur voluptas neque sapiente. Voluptas aut aut architecto ut nisi et expedita. Cumque voluptate commodi.',47);
insert into posts (title,body,"creatorId") values ('end-to-end utilize solutions','Omnis ut vitae nihil perspiciatis sequi consequatur. Facere deleniti voluptatem et. At aut inventore porro rerum. Tempora aut est repudiandae rerum est non nam possimus iste.

Aut vel rerum repellat alias. Laudantium et reprehenderit neque vel repudiandae excepturi sed saepe rem. Quis iste reprehenderit dolores. Minus delectus nihil.

Perspiciatis porro et dignissimos soluta molestiae. Deleniti mollitia neque architecto recusandae dicta vero voluptas ullam voluptas. Asperiores magnam et enim nihil.',51);
insert into posts (title,body,"creatorId") values ('bleeding-edge matrix e-markets','Veniam et laboriosam. Quisquam odio hic iste et nihil ut quo quia quisquam. Ut et est et. Quae sequi molestias dolor modi consectetur repellendus.

Recusandae mollitia voluptate. Aut illo earum consequatur reiciendis autem est cum quos. Cupiditate vel alias. Voluptatem et omnis omnis quis vero officiis. Dolorum hic a libero sunt praesentium vero inventore non.

Doloremque at ut commodi. Illum deserunt alias rerum perspiciatis rerum deserunt dicta iste necessitatibus. Dolor porro qui ut ea.',46);
insert into posts (title,body,"creatorId") values ('intuitive orchestrate web services','Laboriosam vitae amet aut vitae quibusdam eos. Voluptatem temporibus totam aperiam consequatur. Sit non alias voluptate reprehenderit dolorum. Esse ut qui quisquam sed eum eum illum. At consectetur dolor nam cupiditate magnam et laboriosam.

Adipisci in consequatur sapiente ea ut laudantium unde. Accusamus explicabo ut. Qui occaecati sit. Numquam excepturi et aspernatur. Ipsum cupiditate optio id fugiat nostrum ea voluptatem numquam totam.

Voluptate ut praesentium. Quis eaque sequi laboriosam ad optio. Qui magni quia voluptatibus eaque excepturi. Voluptatem quo voluptas voluptatem repudiandae. A sunt illo sint dolores rem omnis.',52);
insert into posts (title,body,"creatorId") values ('B2C reinvent methodologies','Numquam aut sint pariatur quam quo dignissimos quia in. Tempore officiis suscipit ut ullam ipsa id. Minima reprehenderit culpa maxime cum quidem quia qui. Pariatur in molestias deleniti omnis provident id reprehenderit aspernatur. At exercitationem similique veniam id ut.

Maiores est odio quia dolorum voluptatem doloremque et. Omnis omnis rerum nesciunt ad quaerat cum quasi. Et voluptatum deserunt cumque consequatur. Deleniti cumque alias qui ratione.

Facere a nihil placeat ea deleniti aut ut repellat. Est ut deleniti est consequatur. Incidunt similique dicta quis suscipit aliquid pariatur voluptatem cumque.',53);
insert into posts (title,body,"creatorId") values ('killer morph initiatives','Nostrum aliquam molestias iusto est ab nostrum et. Et ratione nobis sunt. Qui autem sit porro quo. Commodi in molestiae modi vel qui accusantium. Quibusdam ullam ut nulla quia autem. Eos ut temporibus nobis aspernatur et.

Necessitatibus vitae odio necessitatibus ut. Aut amet qui explicabo assumenda quisquam ex omnis. Quis dolor debitis eos enim totam vitae totam consequuntur itaque.

Fugit at id odio fuga officiis reprehenderit odit rerum. Neque iure aut et at sit dolorum laborum asperiores debitis. Omnis quidem quis. Culpa libero animi.',45);
insert into posts (title,body,"creatorId") values ('leading-edge whiteboard e-commerce','Ab ratione officiis quis sunt et. Enim id vel iste temporibus. Deleniti est veritatis quia magni consequatur. Sint inventore odio nesciunt explicabo. Est dignissimos ducimus deleniti sit.

In in ut qui. Ut in placeat blanditiis tempora cum minus veritatis. Quasi architecto eligendi atque debitis ut autem accusantium excepturi sed.

Vel possimus aut totam est quo consequatur sed consequatur. Cum voluptas dolores distinctio aut aut aut laborum et eaque. Voluptatem reprehenderit ut omnis voluptas eius in magni blanditiis. Ut totam non dolorem est similique aperiam magni tempora quaerat.',50);
insert into posts (title,body,"creatorId") values ('collaborative repurpose initiatives','Asperiores sapiente quos eaque error id aut perferendis fuga dicta. Voluptatem magnam in suscipit distinctio esse officia a nemo. Vitae vero voluptate.

Quia veniam placeat delectus et magnam placeat nam libero blanditiis. Blanditiis et sapiente saepe animi. Enim enim dolorem nobis adipisci eos omnis fuga a blanditiis. Ratione animi voluptas earum in provident asperiores nihil nemo omnis. Asperiores debitis minima eveniet corrupti.

Expedita omnis expedita laboriosam vel. Distinctio harum mollitia deserunt tempora explicabo distinctio repellendus ut numquam. Qui provident aperiam tenetur recusandae.',43);
insert into posts (title,body,"creatorId") values ('compelling deliver technologies','Praesentium quisquam rerum dolore corporis hic dolor nihil delectus. Possimus quam modi iusto sit deleniti praesentium. Enim sint deleniti vel cupiditate cum aspernatur. Aliquam occaecati ullam est expedita odio pariatur corrupti molestiae accusamus.

Ut ad numquam sed beatae assumenda. Enim animi voluptas non magni. Voluptatem consequatur rerum ea.

Dolores vero et eius aut iusto quasi. Modi sit molestiae adipisci ex ut aut animi libero. Autem at culpa velit debitis sit cumque.',47);
insert into posts (title,body,"creatorId") values ('revolutionary redefine e-markets','Repellendus commodi officiis qui fugiat aut qui perspiciatis omnis vero. Fugiat totam eaque. Voluptatem saepe laboriosam quo atque rerum eaque dolores. Quidem doloremque repellendus et nostrum dolore possimus ut adipisci. Porro corrupti quae et sit. Alias ipsum est aut molestias cum perspiciatis aspernatur doloribus.

Impedit et beatae placeat consequuntur aut id aperiam. Sed molestiae consectetur necessitatibus iure qui itaque hic. Modi et et quia neque nisi aut error.

Et modi maiores reprehenderit doloribus accusamus culpa ab saepe voluptatem. Quod quas quia eaque qui eveniet facere quidem. Non nihil laboriosam et adipisci omnis minus voluptate sit facere. Occaecati sunt velit doloremque facere et ea repudiandae. Dolores soluta ex deleniti non. Exercitationem id at qui quae non aut molestiae autem saepe.',43);
insert into posts (title,body,"creatorId") values ('web-enabled recontextualize partnerships','Iste id est. Est sed expedita nam explicabo nam et enim. Nam labore exercitationem in repellat aut. Corporis ratione aspernatur sit voluptatibus molestiae sit aut.

Aut nihil nulla accusamus quam praesentium voluptas minus voluptatem. Voluptate fugit aut debitis ducimus deserunt doloribus recusandae fugit quaerat. Voluptates tempora tenetur minima voluptatem non. Occaecati aut id vero consequatur maiores excepturi.

Veritatis ratione iste dolorem quisquam aliquam quod nostrum exercitationem. Cumque sapiente id ut eum saepe. Magnam expedita porro exercitationem ut consequuntur.',57);
insert into posts (title,body,"creatorId") values ('leading-edge matrix bandwidth','Voluptas ut quam ut dolorum. Voluptatem nam consectetur facere dicta nostrum minus. Saepe facere laborum veritatis voluptate iste aut.

Fuga qui aut laboriosam atque dolore qui ipsum repudiandae et. Quos deleniti occaecati alias nobis voluptatem libero enim. Vel non ea. Earum ut sequi.

Esse repellat eum sit. Saepe rerum tempora voluptatibus quia ipsum. Quam accusantium id unde aliquam. Explicabo dolores minima et modi consequuntur fugit ducimus. Maiores numquam magnam.',49);
insert into posts (title,body,"creatorId") values ('collaborative harness infomediaries','Illum fugiat dicta quae cupiditate eum voluptas cum eos optio. Voluptas aut voluptatem iste. Sunt non sint illum.

Quos fugit fugiat et beatae molestiae expedita eveniet sit. Asperiores officia nihil aut. Eum et dolor quasi. Repellendus doloremque aut officia error quis amet molestiae. Maxime perferendis quibusdam unde magnam. Ea voluptas unde qui velit magnam aliquam.

Quia voluptas est sed veritatis neque modi. Voluptas cum consectetur recusandae ullam. Sed culpa provident eos quae et animi sit quo harum. Cum enim eligendi.',50);
insert into posts (title,body,"creatorId") values ('24/365 strategize markets','Dolor cumque adipisci sequi ipsum sunt. Quo voluptate qui deserunt placeat facere aut ullam. Temporibus enim facere voluptas cum doloremque eos ut. Odit quia ea laborum dicta voluptatem eum sed et aspernatur.

Possimus reiciendis saepe fugit nihil est quos voluptatibus unde quis. Enim saepe amet. Ut quos doloribus dolorem vitae dolor corporis reiciendis. Architecto accusantium velit eos perferendis optio. Totam sunt placeat quos reprehenderit. Sit quia rem exercitationem ex praesentium autem eligendi animi quasi.

Non praesentium fugit et perferendis aspernatur nisi nesciunt. Magni fuga rerum. Nisi eum blanditiis illo temporibus sit quae. Earum in repudiandae eius voluptatem ullam qui.',55);
insert into posts (title,body,"creatorId") values ('open-source embrace networks','Consectetur in recusandae natus aut unde ad provident harum repudiandae. Omnis nam aut autem quia voluptas repudiandae asperiores voluptate atque. Totam sapiente accusamus eum velit tempora porro velit.

Voluptatibus aut fuga incidunt et cumque. Ut reprehenderit repudiandae quia. Reiciendis dolor explicabo quis incidunt consequatur nostrum quam. Hic quos sed. Eum incidunt sunt distinctio aspernatur quia est.

Et omnis eos accusantium omnis architecto a. Consequatur commodi consequuntur. Id in doloremque quisquam at iusto. Natus autem sed temporibus voluptatem sit. Explicabo impedit molestiae nulla velit veniam accusantium.',52);
insert into posts (title,body,"creatorId") values ('back-end target action-items','Quo voluptatem libero adipisci et dolor laboriosam in. Magni ex facilis. Unde laudantium laborum aut sequi aut eos et commodi. Illo sed aut dolorem. Voluptates nihil consequatur.

Officia quisquam commodi eaque laborum doloremque magnam cupiditate. Et maiores cumque rerum magnam beatae. Reiciendis voluptatem et. Totam modi quae velit quo blanditiis facilis veritatis quibusdam. Harum est aut maiores eos distinctio necessitatibus quia non. A necessitatibus facere at culpa ducimus maxime maiores.

Recusandae aut suscipit est. Sint et quae. Et animi amet et laboriosam iste eum est. Quia omnis laudantium eum voluptatem labore quos impedit commodi vel.',52);
insert into posts (title,body,"creatorId") values ('B2C envisioneer users','Ullam et quae deserunt repellat earum. Delectus voluptates consequatur corrupti dolorem culpa. Ducimus velit impedit voluptatem.

Officiis odio voluptate eaque ad laboriosam esse aut. Est atque mollitia veritatis. Tempore exercitationem distinctio repudiandae voluptatum illo nihil possimus sequi aliquid.

Qui rerum hic. Nulla vel architecto quaerat minima non et qui suscipit. Sed eaque ut.',44);

Now as we have successfully created posts for some of our user. Now we want to see data which combines both posts and users data together. For this we will be using 'join'.

Join can be of many types:
1) Inner Join
2) Left Join
3) Right Join
4) Outer Join
5) cross join
6) natural join

Inner Join

With the help of Inner Join keyword we can get the from both the table on which join has been called. For example

select * from users u inner join posts p on u.id = p."creatorId";
//Inner Join defines the type of Join
//ON decides the condition.
//This command join left table with right table.
//In this query, we are matching users table id with posts table "creatorId" as //we have a foreign key relationship in between them.
// Here we takes users as 'u' , posts as 'p'.

select u.id, p.id,first_name,title from users u left join posts p on u.id = p."creatorId";

select u.id, p.id,first_name,title from users u inner join posts p on u.id = p."creatorId";

//Difference between left and inner join is, left join will show you values of //left table even condition not satisfy.

Check Post of particular user with all details.

select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where u.id = 45;

Check post from particular user with particular title.

select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where p.title like '%killer%';

NOTE : --- word% to find after the word.
       --- %word% to find in between.
       --- %word find before the word
       --- To get Case insensitive we can use 'ilike'

Before moving further lets create our new table 'comments' who can handle comments of post.

create table comments(
            id serial primary key,
            message text not null,
            post_id int references posts(id) not null,
            creator_id int references users(id) not null
        )

//Insert comments
insert into comments (message,post_id,creator_id) values ('Odit cupiditate aliquid aut quaerat tempora ut.',93,43);
insert into comments (message,post_id,creator_id) values ('Hic et et quas doloribus id repellat aut asperiores.',100,49);
insert into comments (message,post_id,creator_id) values ('Officiis magnam dolorum qui nihil et qui rerum consequatur accusantium.',94,50);
insert into comments (message,post_id,creator_id) values ('Adipisci blanditiis quis.',103,48);
insert into comments (message,post_id,creator_id) values ('Repellendus id natus.',94,55);
insert into comments (message,post_id,creator_id) values ('Quas corrupti mollitia dolorem explicabo.',97,50);
insert into comments (message,post_id,creator_id) values ('Commodi iusto est et.',103,51);
insert into comments (message,post_id,creator_id) values ('Vero maxime inventore delectus voluptatum sapiente doloremque distinctio alias.',106,52);
insert into comments (message,post_id,creator_id) values ('Ipsum maiores maiores sit distinctio nobis velit ut et.',106,46);
insert into comments (message,post_id,creator_id) values ('Quo illo sit quasi temporibus.',104,52);
insert into comments (message,post_id,creator_id) values ('Animi aperiam sed voluptas.',108,47);
insert into comments (message,post_id,creator_id) values ('Et quaerat quod quisquam quaerat consectetur fugiat accusantium.',95,44);
insert into comments (message,post_id,creator_id) values ('Amet eum sit consequatur accusantium sapiente temporibus a.',99,55);
insert into comments (message,post_id,creator_id) values ('Et aut fuga quod.',105,52);
insert into comments (message,post_id,creator_id) values ('Eveniet temporibus dolorem in cumque placeat et vitae.',95,53);
insert into comments (message,post_id,creator_id) values ('Explicabo velit quae sapiente adipisci omnis et consequatur accusantium.',98,45);
insert into comments (message,post_id,creator_id) values ('Ab dolorem cum aspernatur quia.',102,47);
insert into comments (message,post_id,creator_id) values ('Saepe debitis sint sunt.',101,53);
insert into comments (message,post_id,creator_id) values ('Exercitationem non voluptatem sed nostrum libero.',99,57);

Now lets make a multiple inner joins call upon comments,posts,users table.

//Basic Call
select * from comments;

//Lets find post title where comment has been given.
select c.id, c.message,c.creator_id commentor_id,p.title,p.id post_id from comments c
inner join posts p on c.post_id = p.id;

//Lets find post creator Id on which comment has been given.
select c.message,
       p.title,p.id,
       u.id user_id_for_post,u2.id user_id_for_comment 
from comments c
inner join posts p on c.post_id = p.id
inner join users u on p."creatorId" = u.id
inner join users u2 on c.creator_id = u2.id;

Till now we have done 1 to many relationships, Now we are going to do Many to Many relationships.

create table favorites (
            user_id int references users(id),
            post_id int references posts(id),
            primary key (user_id,post_id)   --- Composite keys
        )

//favorites is same as like or loved anything you can say.
// It is many to many relationship.
//Because a single user can favorited multiple post and vice-versa.

create table friends (
            user_id1 int references users(id),
            user_id2 int references users(id),
            primary key (user_id1,user_id2) //composite keys
        )

//Same for friends table.
//One user can be friends with other user.

First of all lets delete entire data of our tables and create new data for aligning all tables.

TRUNCATE TABLE users,posts,comments,favorites,friends RESTART IDENTITY;

//Truncate can be used to delete tables data.
//RESTART IDENTITY is used to start primary key from start.

Use Data and paste it in your postgresql.

Count number of comments on particular post

select  count(*) from comments c 
    inner join users u on c.creator_id = u.id
    where c.post_id = 7;

Order By,Limit, Offset

Order By
Whenever we want our results from query to follow a particular order based on any field, we can use 'order by' for this.

select * from users order by age;

//Above query will give us results based on age in increasing order, If we want //to change the order we can use **asc or desc** at the end to change order.

select * from users order by age asc;

select * from users order by age desc;

Limit
Whenever we want to limit the result of our query we can use 'limit' for this.

select * from users order by age limit 10;

offset
If we want to set pagination in our app, then we can use 'limit' and 'offset' together for showing this functionality like below example.

select * from users order by age offset 20 limit 10;

//offset will start reading query from that particular point in our query, it 
//will start reading from '20' upto limit '10'

Find a details about post with its post_title, post_writer,comments_writer , comments_message and user_id who favorites it.

select p.title,u.first_name post_writer,c.message,u2.first_name comment_writer,

f.user_id from posts p

inner join users u on p."creatorId" = u.id

inner join comments c on p.id = c.post_id

inner join users u2 on c.creator_id = u2.id

left join favorites f on f.post_id = p.id

where p.id = 7;

For Some more advance queries please look for my article on becauselifeisonce

React Calculator!!

vinodchauhan7 — Wed, 08 Jan 2020 19:19:25 +0000

WebPack : Zero To Production Part-1

vinodchauhan7 — Thu, 26 Dec 2019 18:01:39 +0000

Webpack: Getting Started

The way we write JavaScript today is different from the code that the browser can execute. We frequently rely on other types of resources, transpiled languages, and experimental features which are yet to be supported in modern browsers.

Webpack is a module bundler for JavaScript that can bridge this gap and produce cross browser–compatible code at no expense when it comes to developer experience.

Before we get started, you should keep in mind that all code presented in this Webpack tutorial is also available in the form of a complete Webpack example configuration file on GitHub. Please feel free to refer to it there and come back to this article if you have any questions.

I have made several commits in the repo for understanding webpack step by step. You can see particular on this link. And checkout to those commit e.g:

Please go through repo for understanding of the below examples link.

git checkout 89637f7ed458d8280c4542a2a87a6b2397594e7d

GitHub Webpack tutorial commits

First Commit : Simple App

=> git checkout 89637f7ed458d8280c4542a2a87a6b2397594e7d

I have made a simple application which takes username and userId from end-user and prints it on screen. It also have some validation for name and userId and gives error to end user. Also there is an image of webpack logo which we will optimize using webpack in further commits.

WebPack Tutorial Example

Index.html
https://gist.github.com/vinodchauhan7/497643664382ace761034ee6ae080afe

Open index.html file.

Second Commit : Broke Js code into 6 files

=> git checkout 1ca9ee0e85566146f477c530f4d4114f5e22372f

In this commit, I have divided the js code into 6 files so that we can simulate to a large example which will have many js files and can be used to make a single bundle file with webpack.

Third Commit : Add Webpack and package.json file

=> git checkout b9e7eb70469d5f6bd11c64cd54ba8a94532fb8a2

In this commit, I have added a package.json file and then added webpack. For package.json : npm init -y and then add : npm install — save -dev webpack webpack-cli. To start our application with webpack we have add a script in package.json file :

“scripts”: { “start”: “webpack” }

When we hit a command on npm start on terminal. It will give us error that we need to include ‘src/index.js’ file for webpack to work. On successful working of the npm start, a newFolder with dist including main.js will be there. We can include that main.js in our index.html. It will be working and webpack is handling our application. Open index.html to see on browser.

Fourth Commit : Webpack now bundle all our app

=> git checkout a08072f552066e02859a21c170ae243112bbcb0d

In this commit, We have used Import/Export for injecting the dependencies from one file to another and also remove tags from html. <h3> <a name="fifth-commit-add-webpack-config-file" href="#fifth-commit-add-webpack-config-file" class="anchor"> </a> Fifth Commit : Add webpack config file </h3> <h4> <a name="gt-git-checkout-194985095f6e24628a8c69e05f2d586bea96634c" href="#gt-git-checkout-194985095f6e24628a8c69e05f2d586bea96634c" class="anchor"> </a> => git checkout 194985095f6e24628a8c69e05f2d586bea96634c </h4> In this commit, we have added our own webpack.config.js file for doing configurations. Path module is used to get the path of the system, so that we can dynamically get the path of the user system without any confusion. It is given to us by npm. <div class="highlight"><pre class="highlight javascript"><code>const path = require("path"); module.exports = { mode: "development", devtool: "none", entry: "./src/index.js", output: { path: path.resolve(__dirname, "dist"), filename: "main.js" } }; </code></pre></div> 1) module.exports is the configuration which webpack will look for. 2) mode : There can be two value “development” & “production”. We are using “development” for now, to make the things working without any complexity. 3) entry : It is the entry file to where webpack have to look for. 4) output.path : It will tell webpack where to create the output folder. __dirname is something which will get the current path where your project is currently on your system. You can give any name to your ouput folder. I have given ‘dist’. 5) output.filename : Here we will telling webpack, what our output file name will be. 6) Also we are updating package.json “scripts”: {“start”: “webpack — config webpack.config.js”} <h3> <a name="sixth-commit-add-loader-to-handle-css" href="#sixth-commit-add-loader-to-handle-css" class="anchor"> </a> Sixth Commit : Add Loader to handle css </h3> <h4> <a name="gt-git-checkout-b296566130db6559264bda7e7423f2feab64a782" href="#gt-git-checkout-b296566130db6559264bda7e7423f2feab64a782" class="anchor"> </a> => git checkout b296566130db6559264bda7e7423f2feab64a782 </h4> In this commit, we are introducing loaders. webpack only understands JavaScript and JSON files. Loaders allow webpack to process other types of files and convert them into valid modules that can be consumed by your application and added to the dependency graph. We are adding a new css file ‘index.css’ in which we are changing background & foreground color of the webpage and including it in our index.js file. And to load it in our webpack we need to use some loaders which can be found on webpack website. We are using “css-loader”,”style-loader”. <blockquote> npm install — save-dev css-loader style-loader 1) css-loader : It will convert css into commonJs. 2) style-loader : It will inject js into dom. </blockquote> Include below code in package.json <div class="highlight"><pre class="highlight javascript"><code>module: { rules: [ { test: /\.css$/, use: [“style-loader”, // Inject commonjs into dom “css-loader” //convert css into commonJs ] } ] } </code></pre></div> In module tag, include rules tag. test : Here we need to add the extension of those files on which we need to add loader for webpack to build its dependency graph. use : It is dependency array where we need to add all our loaders. Note : We need to add loader in a proper order for them to work. For further understanding please visit <a href="https://medium.com/@vinodc45/webpack-zero-to-production-via-react-part-1-b478be7b7f29">Medium link</a>

Optimize Redux before it kills your Application

vinodchauhan7 — Fri, 13 Dec 2019 06:28:42 +0000

First of all, let’s take a look at how a React-Redux application works. What Redux does internally, It provide us with a store for our app’s state and with ways to communicate with the store. One of these ways is the connect() function. After calling connect() on a custom component you get a wrapper that passes state from a store as props to your component. This happens by means of mapStateToProps() function which is called on every state change.

After mapStateToProps() yields recalculated props, the new props are shallow compared to the old ones and if they differ, component gets rerendered. Again, reference equality (===) is used to compare the props.

React and Redux compliment each other well. React Components subscribe to Redux store due to which the child components re renders when the state (Redux state) is changed every time. So any component which is subscribed to Redux re renders when there is a state change (unless and until we mention not to do so).

This is a prime reason for which there is a need for optimizing React Redux applications in order to avoid unnecessary re renders.

To avoid all such conflicts, there are certain ways which are described below :

1) Using React’s PureComponent, React shallow compares previous props and current props and avoid re rendering if there is no change.
2) Using shouldComponentUpdate lifecycle we can selectively avoid re rendering of a React Component.
3) Using functional components when there is no use of internal state.

This can help in avoiding re rendering unless and until there is a prop change which is consumed by the component.

To handle this problem we have reselect library which works flawlessly in this case:

Reselect Concept

According to the library’s homepage
Selectors can compute derived data, allowing Redux to store the minimal possible state. Which can be considered as keep the store as minimal as possible. Compute any derived data through the selectors.
Selectors are efficient. A selector is not recomputed unless one of its arguments change.

For code Example and full article please check on [https://medium.com/javascript-in-plain-english/optimize-redux-before-it-kills-your-application-6b73cf5f520b]

Top Performance Tips for React App 2019

vinodchauhan7 — Mon, 25 Nov 2019 15:26:23 +0000

Picture this : You have to increase the performance of your React App. To achieve a better performance what measures you should take care while developing and deploying the app?
React does a great job in terms of performance, but, if you have a complex application, you may start to see issues with it. You can still do something to improve its performance which are categorized as below:

1.Development

2.Deployment

Tips for Development Process

1.Avoiding & Measuring Render Time

First of all we must avoid re-rendering of our components without any requirement. Also we need to calculate the rendering time so that we can decrease it. A simple way is to use Use-Timing-API. Additionally you can check for react rendering in browser with below options.

2. Use Pure Components

To avoid the Reconciliation process of React, We must use Pure-Components as , there is already a default implementation of, shouldComponentUpdate() with a shallow prop and state comparison. So, a pure component is a component that only re-renders if props/state is different from the previous props and state.

3. Dynamically Load Long List

If you have a long list of items which you want to render it on your page, then breakdown it in smaller list and render it on scrolling or any other event listener. You can do it with react-window & react-virtualize.

4. React Concurrent Mode

If Performance is the utmost priority in your react app, then you must go for react concurrent mode. One of the simplest way to achieve concurrency is with React suspense and Lazy loading of components.

For full post you can read it on : Medium Post

Top Tricks Hackers do to infect your system on Social Internet

vinodchauhan7 — Sat, 16 Nov 2019 16:07:17 +0000

In Recent years, number of hackers and cyber techies are increasing day by day as users of social networking sites grown. Out there, Many attackers use their technical knowledge to infect other’s computers while compromising sensitive data. You may hear about a variety of social engineering tricks that can harm your system by a great extent. They may use social media, phone calls, or any other method and lure victims to provide sensitive personal or corporate information.

**Phishing
In this case, hackers try to obtain personal information of target individuals; it may include names, social security numbers, and addresses as well. They make use of some embed links to redirect users towards some suspicious URLs that usually appear to be legitimate. In this case, attackers create a sense of urgency to the victims so that they can act promptly and get trapped.

**Fake Authorities
In this case, Attackers try to create some good pretext or fabricated set up with an aim to steal personal information of victims. Such types of attacks are executed by some scammer who pretends that they require a specific type of sensitive information for your welfare. They often try to manipulate targets and force them to take some actions that could exploit the structural weakness of a company or organization.

To read more : Medium Article

Authenticate GraphQL Queries With JsonWebTokens(JWT) in Orm-NodeJS environment using Typescript.

vinodchauhan7 — Sun, 10 Nov 2019 13:37:11 +0000

Hello Everyone, Today I am writing an article to authenticate Graphql queries with JWT tokens. To achieve this, first I am going to setup server in which user able to do Registration/Login. After successful login, we make a query which need authentication.

So the technology stack I am going to use is :: Nodejs, Expressjs, Typeorm, Typegraphql, graphql, jsonwebtoken,ApolloServerExpress and much more.

GitHub Link

Authenticate Graphql Query

@Query(() => String)
@UseMiddleware(isAuth)
async Me(@Ctx() { payload }: MyContext) {
    return `Your user id : ${payload!.userId}`;
}

Implement isAuth.ts middleware

import { MiddlewareFn } from "type-graphql";
import { verify } from "jsonwebtoken";
import { MyContext } from "./MyContext";

//format like bearer 21321n2bmbbj

export const isAuth: MiddlewareFn<MyContext> = ({ context }, next) => {
  const authorization = context.req.headers["authorization"];

  if (!authorization) {
    throw new Error("Not authenticated");
  }

  try {
    const token = authorization.split(" ")[1];
    const payload = verify(token, "MySecretKey");
    console.log(payload);
    context.payload = payload as any;
  } catch (err) {
    console.log(err);
    throw new Error("Not authenticated");
  }
  return next();
};

However, if anyone wants to understand everythin from basics then please follow :

My Medium Article

dev.to clone using React-Node, Apollo-Cache, apollo-link-token-refresh, One-2-Many relation in Typeorm [Part-3]

vinodchauhan7 — Wed, 23 Oct 2019 17:45:39 +0000

Hello Community, Today I am writing 3 part of this article series in which I am trying to clone dev.to with minimum feature.It will be a prototype, in which user can signUp/signIn, create post & other features.

I am doing it for just learning purpose.
If you have not visited the Part-1 & Part-2, please understand those first for better clearity of this one.

Part- 1
Part -2

Code Repo : GitHub Link

Howdy! Friends, In this article I am able to complete many things such as Login/Registration functionality with token refresh feature. Also able to maintain cache using Apollo state management and at last loggedIn user can post its articles see other users articles and profiles.

Login

When user login a refresh_token saved in user browser cookies.For user smooth usage of application we need to maintain its authentication even if user reload the page or if its token get expired. We have take care of both the features with RefreshToken and using Apollo-link-token-Refresh module in case of token got expired.

//Getting access Token and passing it in request headers
const requestLink = new ApolloLink(
  (operation, forward) =>
    new Observable(observer => {
      let handle: any;
      Promise.resolve(operation)
        .then(operation => {
          const accessToken = getAccessToken();
          if (accessToken) {
            operation.setContext({
              headers: {
                authorization: `bearer ${accessToken}`
              }
            });
          } //accessToken is defined
        }) //then operation ends here
        .then(() => {
          handle = forward(operation).subscribe({
            next: observer.next.bind(observer),
            error: observer.error.bind(observer),
            complete: observer.complete.bind(observer)
          }); //handle ends here
        })
        .catch(observer.error.bind(observer));

      return () => {
        if (handle) handle.unsubscribe();
      };
    })
);

const client = new ApolloClient({
  link: ApolloLink.from([
    new TokenRefreshLink({
      accessTokenField: "accessToken",
      isTokenValidOrUndefined: () => {
        const token = getAccessToken();

        if (!token) {
          return true;
        }

        try {
          const { exp } = jwtDecode(token);
          if (Date.now() >= exp * 1000) {
            return false;
          } else {
            return true;
          }
        } catch (err) {
          console.log(err);
          return false;
        }
      },
      fetchAccessToken: () => {
        return fetch("http://localhost:4000/refresh_token", {
          method: "POST",
          credentials: "include"
        });
      },
      handleFetch: accessToken => {
        setAccessToken(accessToken);
      },
      handleError: err => {
        console.warn("Your refresh token is invalid. Try to relogin");
        console.error(err);
      }
    }),
    onError(() => {}),
    requestLink,
    new HttpLink({
      uri: "http://localhost:4000/graphql",
      credentials: "include"
    }) //new HttpLink ends here
  ]),
  cache
});

Apollo-Cache

When user loggedIn, we are updating our cache for MeQuery so that it can be used throughout the application to know who is currently loggedIn without hitting the actual graphql query.

 const LoginComponentUser = withFormik<MyFormProps, FormValues>({
    mapPropsToValues: props => ({
      email: props.initialEmail || "",
      password: props.initialPassword || ""
    }),
    validationSchema: Yup.object().shape({
      email: Yup.string()
        .email("Email is not valid")
        .required("Email is required"),
      password: Yup.string().required("Password is required")
    }),
    async handleSubmit({ email, password }: FormValues) {
      console.log(email, password);

      const response = await login({
        variables: {
          data: {
            email,
            password
          }
        },
        update: (store, { data }) => {
          //updating cache so that it will not hit again and again
          if (!data) {
            return null;
          }
          store.writeQuery<MeQuery>({
            query: MeDocument,
            data: {
              __typename: "Query",
              me: data.login.user
            }
          });
        }
      });

      console.log(response);
      if (response && response.data) {
        setAccessToken(response.data.login.accessToken);
      }

      console.log(getAccessToken());
      window.location.replace("http://localhost:3000/");
    }
  })(LoginForm);

Styled-Components

I am also adding little-bit styled-components usage for beginners to showcase how to use css in JS.

 const WritePost = styled.a`
    width: 118px;
    display: block;
    margin-top: 10px;
    padding: 3px;
    text-align: center;
    font-weight: bold;
    border-radius: 3px;
    border: 2px solid #0a0a0a;
    color: #0a0a0a;
    background: #66e2d5;
    font-size: 11px;
    text-decoration: none !important;
    font-stretch: condensed;
    &:hover {
      color: #0b0b0b;
      background: #66e2e5;
    }
  `;

  const ShowSvg = styled.div`
    margin-top: 10px;
  `;

One-to-Many Relations

I am also showing relations between schemas to show how we can implement this TypeORM feature using Graphql in our application by showing [user-post] relationship.

 @Query(() => [Post])
  @UseMiddleware(isAuth)
  async getAllPostById(@Arg("userId") userId: number): Promise<Post[]> {
    const post = await Post.find({
      where: { user: { id: userId } },
      relations: ["user"]
    });
    console.log(JSON.stringify(post, null, 2));
    return post;
  }

I have not made FrontEnd similar to dev.To fully because it will took time for matching all the things and also all feature.

The purpose of this article series is to familiar beginners or intermediate react developers with Token refresh,apollo and typeorm features.

Will come back with new features and articles, Till then bye guys..

dev.to clone using react-Hooks,Graphql-CodeGen, Apollo, Refresh-token, Authentication MiddleWare [Backend + FrontEnd](Part-2)

vinodchauhan7 — Fri, 18 Oct 2019 13:52:08 +0000

Hello Community, Today I am writing 2 part of this article series in which I am trying to clone dev.to with minimum feature.It will be a prototype, in which user can signUp/signIn, create post & other features.

I am doing it for just learning purpose.

If you have not visited the first Part, please understand it first Part-1

Technology Stack:

NodeJs, ReactJs, Graphql, TypeOrm,TypeGraphql, Typescript, JWT, Apollo-server-express, Jest, Apollo-client, Apollo-link & much more..

GitHub Link

In second part of this series, I am able to make logic for Refreshing tokens, Authenticated middlewares for backend server. Additionally, I have also make my setup for React frontEnd with Apollo-Client, React-Bootstrap, Styled-components, Graphql-CodeGen && much more.

For ease, I have made 2 branches in my Git Repository to understand things more clearly :
* Part-4 Add authentication for graphql API and revoking refresh token logic
* Part-5 FrontEnd & backend setup

Part-4(GitHub Branch)

In this branch, We have done mainly 4 things which are as below :

Token Version : This 'tokenVersion' is saved with each user with default value of 0. It will be helpful to identify the authentication for Refresh-tokens, how many times user loggedIn and other things like ending all sessions of user.

//For authentication, I am changing createRefreshToken method.
export const createRefreshToken = (user: User) => {
  return sign(
    { userId: user.id, tokenVersion: user.tokenVersion },
    process.env.REFRESH_TOKEN_SECRET!,
    {
      expiresIn: "7d"
    }
  );

Refresh Token : As we know, when we hit 'loginMutation' query then server send us 'Refresh token' back as cookies. Now picture this, If a user loggedIn our dev.to clone and refresh the page then we need to make a mechanism to make it loggedIn and provide all services which he/she is authenticated for. To make this possible, I am making a 'POST' api at our server which accepts cookies for the refresh-token and then it verifies it. If refresh-token verified successfully at server end then we again send the latest 'refresh-token' and 'access-token' to user so that user have not to login again and again unnecessarily.

app.use(cookieParser());

  app.post("/refresh_token", async (req, res) => {
    const token = req.cookies.devId;
    if (!token) {
      console.log("token is not valid " + token);
      return res.send({ ok: false, accessToken: "" });
    }

    let payload: any = null;
    try {
      payload = await verify(token, process.env.REFRESH_TOKEN_SECRET!);
    } catch (err) {
      console.log(err);
      return res.send({ ok: false, accessToken: "" });
    }
    console.log("payload :: " + payload.userId);
    //token is valid and we can send him access token now.abnf
    const user = await User.findOne({ id: payload.userId });

    if (!user) {
      console.log("User not found");
      return res.send({ ok: false, accessToken: "" });
    }

    if (user.tokenVersion !== payload.tokenVersion) {
      return res.send({ ok: false, accessToken: "" });
    }

    //Referesh Token
    res.cookie("devId", createRefreshToken(user), {
      httpOnly: true
    });

    return res.send({ ok: true, accessToken: createAccessToken(user) });
  });

Steps to follow :
1) Login and get RefreshToken.

2) Use postman for hitting REST API, and set refreshToken as cookie in it.

3) Get the new AccessToken based on refresh-token.

Authentication Middleware :

Suppose we have some graphql queries which we want to be available for authenticated user only. To achieve this task, I am using middlewares from 'type-graphql'.

 @Query(() => String)
  @UseMiddleware(isAuth) //Below is implementation
  me(@Ctx() { payload }: MyContext) {
    return `${payload!.userId}`;
  }

//isAuth.ts
export const isAuth: MiddlewareFn<MyContext> = ({ context }, next) => {
  const authorization = context.req.headers["authorization"];

  if (!authorization) {
    throw new Error("Not Authenticated");
  }

  try {
    const token = authorization.split(" ")[1];
    const payload = verify(token, process.env.ACCESS_TOKEN_SECRET!);
    context.payload = payload as any;
  } catch (err) {
    console.error(err);
    throw new Error("Not Authenticated");
  }
  return next();
};

Revoking Refresh-token :

Picture this, Your "refresh-token" is not expired & you "forgot-password", then you dont want that anyone can authenticate himself for protected graphql queries or simple remove all its loggedIn sessions, then you can update the tokenVersion of the specific user so that he needs to verify himself with refreshTokens' tokenVersion.

 @Mutation(() => Boolean)
  async revokeRefreshToken(@Arg("userId", () => Int) userId: number) {
    await getConnection()
      .getRepository(User)
      .increment({ id: userId }, "tokenVersion", 1);

    return true;
  }

Part-5 Branch

In this branch, We have setup our react frontend app with command
"npx create-react-app devto --typescript". After installation add following modules :

yarn add apollo-boost @apollo/react-hooks graphql
yarn add -D @types/graphql

** Update your app.tsx file **

import React from "react";
import ReactDOM from "react-dom";
import ApolloClient from "apollo-boost";
import { ApolloProvider } from "@apollo/react-hooks";
import "bootstrap/dist/css/bootstrap.min.css";
import App from "./App";

const client = new ApolloClient({
  uri: "http://localhost:4000/graphql",
  credentials: "include"
});

ReactDOM.render(
  <ApolloProvider client={client}>
    <App />
  </ApolloProvider>,
  document.getElementById("root1")
);

Add graphql/CodeGen

yarn add -D @graphql-codegen/cli
//then
npx graphql-codegen init

Steps to setup :

Make your first graphql Query

query Hello {
  hello
}

useHelloQuery() in app.tsx

import React from "react";
import { BrowserRouter as Router, Switch, Route } from "react-router-dom";
import { HeaderComponent } from "./components/Header/header.component";
import { GlobalStyle } from "./ui_components/GlobalStyle";
import { ColStyle, RowStyle } from "./ui_components/RowColStyle";
import { RegisterComponent } from "./components/User/Register.component";
import { useHelloQuery } from "./generated/graphql";
const App: React.FC = () => {
  const { data, loading } = useHelloQuery();

  if (loading || !data) {
    return <div>...</div>;
  }

  return (
    <>
      <GlobalStyle></GlobalStyle>
      <RowStyle>
        <ColStyle md={12}>
          <HeaderComponent />
        </ColStyle>
      </RowStyle>
      <RowStyle>
        <ColStyle md={12}>
          <Router>
            <Switch>
              <Route exact path="/" render={() => <div>{data.hello}</div>} />
              <Route exact path="/register" component={RegisterComponent} />
            </Switch>
          </Router>
        </ColStyle>
      </RowStyle>
    </>
  );
};

export default App;

Hope you will like this article, I will come back soon with more code :).

Made Portfolio website with React and GitHub pages

vinodchauhan7 — Wed, 16 Oct 2019 20:15:44 +0000

Hello guys, just made my portfolio website with a custom design in react. Very much excited to make it live with 'gh-pages'. After some minimal efforts, I am successfully able to make it. You can also look for my portfolio on Portfolio on given link.

Happy Coding!!

dev.to clone using react-node via typescript, typegraphql & TypeOrm[Backend](Part-1)

vinodchauhan7 — Sun, 13 Oct 2019 18:35:10 +0000

Hello Community, Today I am trying to clone dev.to website using ReactJs and NodeJs. It will be a prototype, in which user can signUp/signIn, create post & other features.

I am doing it for just learning purpose.

Technology Stack :

NodeJs, ReactJs, Graphql, TypeOrm,TypeGraphql, Typescript, JWT, Apollo-server-express, Jest, Apollo-client, Apollo-link & much more..

GitHub Link for dev.to clone

Part-2

To commence with, I have just started with typegraphql-typescript-node setup using typeorm. Till now, I have completed Registration and login functionality with jest setup. For Authentication I am using 'JWT'. And I am saving password using bcryptjs for hashing purpose.

It will be difficult for me to make proceed with step by step as this article series can be large enough, So for that I have made branches in git.
Till the date(13/10/2019), I have made 3 branches in Git for following purpose :

If anyone having problems in running any of above stated branches then please check for 'master' branch, it will be in running condition always.

Project Structure

Package.json

{
    "name": "server",
    "version": "1.0.0",
    "main": "index.js",
    "license": "MIT",
    "dependencies": {
        "apollo-server-express": "^2.9.6",
        "bcryptjs": "^2.4.3",
        "class-validator": "^0.10.1",
        "dotenv": "^8.1.0",
        "express": "^4.17.1",
        "graphql": "^14.5.8",
        "jsonwebtoken": "^8.5.1",
        "pg": "^7.12.1",
        "reflect-metadata": "^0.1.13",
        "type-graphql": "^0.17.5",
        "typeorm": "^0.2.19"
    },
    "devDependencies": {
        "@types/bcryptjs": "^2.4.2",
        "@types/express": "^4.17.1",
        "@types/graphql": "^14.5.0",
        "@types/jest": "^24.0.18",
        "@types/jsonwebtoken": "^8.3.4",
        "@types/node": "^12.7.12",
        "jest": "^24.9.0",
        "nodemon": "^1.19.3",
        "ts-jest": "^24.1.0",
        "ts-node": "^8.4.1",
        "ts-node-dev": "^1.0.0-pre.43",
        "typescript": "^3.6.4"
    },
    "scripts": {
        "start": "ts-node-dev --respawn src/index.ts",
        "db:setup": "ts-node ./src/test-utils/setup.ts",
        "test": "npm run db:setup && jest"
    }
}

I am using 'yarn' package manager for running all my scripts.
1) yarn start (It will run your backend server at localhost:4000/graphql)
2) yarn test ( To test all the run cases)

Index.ts

import "dotenv/config";
import "reflect-metadata";
import { ApolloServer } from "apollo-server-express";
import Express from "express";
import { createConnection } from "typeorm";
import { createSchema } from "./utils/createSchema";

const server = async () => {
  await createConnection();

  const schema = await createSchema();

  const apolloServer = new ApolloServer({
    schema,
    context: ({ req, res }) => ({ req, res })
  });

  const app = Express();

  apolloServer.applyMiddleware({ app });

  app.listen(4000, () => {
    console.log("Dev.to server started on localhost:4000/graphql");
  });
};

server();

I am using postgres as my DB.. The ormConfiguration for db will be like this.

{
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "root",
    "database": "dev-to-clone",
    "synchronize": true,
    "logging": false,
    "entities": [
        "src/entity/*.*"
    ]
}

For testing my resolvers, I am using jest and different db.

import "dotenv/config";
import "reflect-metadata";
import { createConnection } from "typeorm";
import { User } from "./../entity/User";

export const testConn = (drop: boolean = false) => {
  return createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "root",
    database: "dev-to-clone-test",
    synchronize: drop,
    dropSchema: drop,
    logging: false,
    entities: [User]
  });
};

Till now my 'User' entity has following attributes, I can update them If I need to manipulate it in future.

import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from "typeorm";
import { ObjectType, Field, ID } from "type-graphql";

@ObjectType()
@Entity()
export class User extends BaseEntity {
  @Field(() => ID)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column()
  name: string;

  @Field()
  @Column("text", { unique: true })
  email: string;

  @Column()
  password: string;

  @Field()
  @Column({ nullable: true, default: null })
  workPlace?: string;

  @Field()
  @Column({ nullable: true, default: null })
  about?: string;

  @Field()
  @Column({ nullable: true, default: null })
  linkedIn?: string;

  @Field()
  @Column({ nullable: true, default: null })
  github?: string;

  @Field(() => [String])
  @Column("simple-array", { nullable: true, default: null })
  tags?: string[];

  @Field()
  @Column()
  joinedDate: string;

  @Field()
  @Column({ nullable: true, default: null })
  location?: string;

  @Field()
  @Column({ nullable: true, default: null })
  isActive?: boolean;
}

Some snapshots are :

Register user

Login With Access Token

Refresh Token while login.

Test result

Those who dont understand the logic for Access & Refresh token, It will be properly understandable when we are covering its frontend part using React with Typescript & Apollo.

Till then Bye-Bye community, will come back with other functionality asap.