DEV Community: Dhawal Singh Panwar

How many rows would have been there without that LIMIT clause?

Dhawal Singh Panwar — Sat, 14 Aug 2021 15:10:15 +0000

You're familiar with the concept of modularity from OOPS, right? So, my introduction is not in this post. 😆

Article No Longer Available

Introduction
Explanation
- MySQL
- Postgres
Conclusion
References
Further Reads

Introduction

Let's say, you're building this software for an event management company to keep a track of all their events digitally. So, you have the front-end dev ready with the UI and all she needs from you is two things.

A list of all events that have already happened this year (10 rows at a time for the sake of pagination)
Total count of the same

Considering that there's an events table in your SQL Database, you could just get down to coding and provide two separate endpoints to accommodate her requirements.

SELECT id, organiser_id, venue_id, scheduled_date
FROM events
WHERE scheduled_date < NOW()
LIMIT 10 OFFSET 0;

The above query will serve you with the requirements of Task 1 in getting the desired details in a paginated manner given you provide a limit and an offset according to however many records the user wants to see on the GUI at a time.

SELECT COUNT(*) AS total
FROM events
WHERE scheduled_date < NOW();

And, this query will get you the total number of records there exist that match the provided condition.

This will suffice her requirements and also you'll have her impressed with how quickly you can implement this.

Explanation

Let's consider the following queries you'd write.

SELECT id, organiser_id, venue_id, scheduled_date
FROM events
WHERE scheduled_date < NOW()
LIMIT 10 OFFSET 0;

+------+--------------+----------+----------------+
| id   | organiser_id | venue_id | scheduled_date |
+------+--------------+----------+----------------+
|    4 |            6 |        2 | 2021-02-20     |
|    5 |            4 |        6 | 2021-05-29     |
|    6 |            4 |        7 | 2021-02-14     |
|    7 |            1 |        2 | 2021-06-23     |
|    8 |            2 |       10 | 2021-06-30     |
|   10 |            1 |       13 | 2021-08-01     |
|   11 |            5 |        6 | 2021-02-28     |
|   12 |            4 |        4 | 2021-07-07     |
|   19 |            3 |        5 | 2021-07-21     |
|   26 |            4 |        1 | 2021-02-12     |
+------+--------------+----------+----------------+
10 rows in set (0.01 sec)

SELECT COUNT(*) AS total
FROM events
WHERE scheduled_date < NOW();

+-------+
| total |
+-------+
|    54 |
+-------+
1 row in set (0.01 sec)

What'll happen in the first query is that the SQL Server will gather all the rows that satisfy the WHERE clause like in a one big superset and then return you the quantity mentioned in the LIMIT clause. This is completely fine.

But, where things go not so good for the server is when it'll run the second query. It'll again do the same steps as it did in the first query, make a superset and then count it for you.

So, it'll do the same thing, evaluate your whole query just to get you a total count? Isn't that overkill?

Well, how about another way of achieving the said tasks without burdening the SQL Server?

So, some SQL solutions provide you ways out of this. Let me show you how you could do this in an efficient manner for MySQL and Postgres.

MySQL

SQL_CALC_FOUND_ROWS is a query modifier or say its just a keyword like DISTINCT which you use in conjunction with the SELECT statement.

SELECT SQL_CALC_FOUND_ROWS id, organiser_id, venue_id, scheduled_date
FROM events
WHERE scheduled_date < NOW()
LIMIT 10 OFFSET 0;

+------+--------------+----------+----------------+
| id   | organiser_id | venue_id | scheduled_date |
+------+--------------+----------+----------------+
|    4 |            6 |        2 | 2021-02-20     |
|    5 |            4 |        6 | 2021-05-29     |
|    6 |            4 |        7 | 2021-02-14     |
|    7 |            1 |        2 | 2021-06-23     |
|    8 |            2 |       10 | 2021-06-30     |
|   10 |            1 |       13 | 2021-08-01     |
|   11 |            5 |        6 | 2021-02-28     |
|   12 |            4 |        4 | 2021-07-07     |
|   19 |            3 |        5 | 2021-07-21     |
|   26 |            4 |        1 | 2021-02-12     |
+------+--------------+----------+----------------+
10 rows in set, 1 warning (0.01 sec)

The results look the same. But, what'll happen here is that between making a superset and returning you the LIMITed records, SQL engine will count it all and store the count in the memory somewhere. Well, how does one access it?

SELECT FOUND_ROWS() AS total;

+-------+
| total |
+-------+
|    54 |
+-------+
1 row in set, 1 warning (0.00 sec)

Running the above query just after any query which has used the SQL_CALC_FOUND_ROWS query modifier will return the count it stored while processing the previous query. Thus, its quicker than evaluating the whole query again without the LIMIT clause. 😄

Although MySQL deprecated this feature due to performance issues, you can still use it till they actually remove it.

Postgres

For this particular case the initial queries won't change.

SELECT id, organiser_id, venue_id, scheduled_date
FROM events
WHERE scheduled_date < NOW()
LIMIT 10 OFFSET 0;

 id | organiser_id | venue_id | scheduled_date
----+--------------+----------+----------------
  4 |            6 |        2 | 2021-02-20
  5 |            4 |        6 | 2021-05-29
  6 |            4 |        7 | 2021-02-14
  7 |            1 |        2 | 2021-06-23
  8 |            2 |       10 | 2021-06-30
 10 |            1 |       13 | 2021-08-01
 11 |            5 |        6 | 2021-02-28
 12 |            4 |        4 | 2021-07-07
 19 |            3 |        5 | 2021-07-21
 26 |            4 |        1 | 2021-02-12
(10 rows)

SELECT COUNT(*) AS total
FROM events
WHERE scheduled_date < NOW();

 total
-------
    54
(1 row)

Same thing will happen here, as well. The WHERE clause would be required to get evaluated twice.

However, Postgres offers this concept of window functions which you could use to achieve both the tasks in a single query itself.

SELECT id, organiser_id, venue_id, scheduled_date, COUNT(*) OVER () AS total_count
FROM events
WHERE scheduled_date < NOW()
LIMIT 10 OFFSET 0;

 id | organiser_id | venue_id | scheduled_date | total_count
----+--------------+----------+----------------+-------------
  4 |            6 |        2 | 2021-02-20     |          54
  5 |            4 |        6 | 2021-05-29     |          54
  6 |            4 |        7 | 2021-02-14     |          54
  7 |            1 |        2 | 2021-06-23     |          54
  8 |            2 |       10 | 2021-06-30     |          54
 10 |            1 |       13 | 2021-08-01     |          54
 11 |            5 |        6 | 2021-02-28     |          54
 12 |            4 |        4 | 2021-07-07     |          54
 19 |            3 |        5 | 2021-07-21     |          54
 26 |            4 |        1 | 2021-02-12     |          54
(10 rows)

We have used the OVER clause over here which tells the engine to evaluate the preceding function over those rows that fall in to the same partition as the current row which we can mention in a following PARTITION BY clause just like how you'd pair up LIMIT and OFFSET clauses together.

We'll cover the PARTITION BY clause in a future post but in this case, the COUNT(*) function will be evaluated over all the rows as we have not provided any PARTITION BY clause to the ORDER clause like how LIMIT 10 OFFSET 10 returns different output than just LIMIT 10 without any OFFSET clause.

Thus, the COUNT(*) OVER () will run over all the rows that match the condition specified in the WHERE clause and be included in the superset which when further gets LIMITed you'll already have a dedicated column holding that total count for you. 😄

Conclusion

This isn't a write once use everywhere strategy as there will be cases where there are hardly any inserts between multiple paginated selects like this won't be efficient for when I keep refreshing my bank's app constantly when the month begins to see that salary credit record. If my banking institution uses this strategy, its a bad one (for now at least). 😛

For cases where there are or can be frequent inserts between multiple paginated selects like the bank refreshing my spends history, this is how you can optimize your database process and impress that front-end dev. 😉

References

Further Reads

Article No Longer Available

What the fetch() is an API ?

Dhawal Singh Panwar — Mon, 07 Sep 2020 16:03:54 +0000

You're familiar with the concept of modularity from OOPS, right? So, my introduction is not in this post. 😆

Article No Longer Available

Introduction

An application programming interface (API) is a computing interface which defines interactions between multiple software intermediaries. It defines the kinds of calls or requests that can be made, how to make them, the data formats that should be used, the conventions to follow, etc. - Wikipedia

There's this fuss about APIs. Everyone has their own definition of what an API is or at least what it's like. But, let me tell you this. Don't trust anyone 🙉

Introduction
Explanation
- The Dictionary Analogy
- The Restaurant Analogy
Conclusion
References
Further Reads

Explanation

There is a plethora of analogies. Let's break some of them down.

The Dictionary Analogy

This one's quite right, that an API is kinda like a dictionary. But, according to Wikipedia, there are more than 15 types of dictionaries.

So, it's not just a dictionary. It's a bilingual dictionary; the one we use when we travel to a foreign country.

The dictionary tells us how to communicate with a person who does not understand the language we speak.

This is very much similar to the articles we find on Google when we search for "what does it mean when a dog wags its tail in a clockwise manner". Those articles tell us how to understand the dog. 😁

The Restaurant Analogy

This one might hold true for an MVC architecture but not for an API. Let's see why not.

What do we do when we go to a restaurant? Do we just walk straight into the kitchen and tell the chef what we would like to have for dinner? Well, I certainly don't. 😆

The expectations from us guests are, that we let the waiter know what we would like to have and he will take our order and forward it to the chef. The waiter's job is to keep a track of and serve the orders.

Here, the waiter is just a middleman. The actual message is the dish(es) we order. That is the request which we make.

Coming back to the internet.

GET https://pokeapi.co/api/v2/pokemon/jigglypuff

This is a request. But how could we know that URL?

How do you know that the restaurant serves noodles?

The Menu Card 🎉

That is the API. We can select which dish would we like and tell the waiter to get it served for us. Remember, he's not the one who cooks it.

Similarly, this is an API. It tells us how to talk to the Pokedex and how will it respond to us.

GET https://pokeapi.co/api/v2/pokemon/{id or name}/

Just like that URL has {id or name} in it to tell us that either we could pass the name of the pokemon in the URL or its id, some restaurants also provide us with methods to order, either we could call them or walk-in.

Conclusion

Documentation is important. Developers actually use those. 😁

References

Further Reads

Understanding the Simple Express App | Node.js

Dhawal Singh Panwar — Mon, 31 Aug 2020 18:15:06 +0000

You're familiar with the concept of modularity from OOPS, right? So, my introduction is not in this post. 😆

Article No Longer Available

Introduction

Express is a framework which makes it quite easy to build an API rapidly.

Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications.

With a myriad of HTTP utility methods and middleware at your disposal, creating a robust API is quick and easy.

The agenda is to understand each line of code it took to develop the Simple Express App 🌱

dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.

Introduction
Setup
- Windows
- Linux
Usage
- Starting the Server
- Interacting with the Server
Explanation
Conclusion
References
Further Reads

Setup

Before getting down to the development part, let's set up our development environment.

Windows

Get VS Code
Get Node.js
Download Simple Express App by clicking here : ⬇️
dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.
Simple Express App

Use this app with this article to understand each line of code it takes to set up a basic API with an Express App on Node.js 🌱

Usage

An npm install is all it takes.

Tutorial - Check out this dev post!

Explanation - Check out this gist!

Documentation

These are the available open routes.
View on GitHub
Unzip it and open the folder in VS Code for a convenient experience 😁

Linux

Install VS Code

sudo snap install --classic code

Install Node.js and npm

sudo apt-get update
sudo apt-get install nodejs
sudo apt-get install npm

Clone this repo

git clone https://github.com/dsp9107/Simple-Express-App.git

Open the directory with VS Code for a convenient experience 😁

cd Simple-Express-App
code .

Usage

We'll get the server running and then move to the interaction part.

Starting the Server

Once VS Code opens, open the integrated terminal with Ctrl + ` if you do not see it at the bottom
Install the dependencies

npm install

While the dependencies are being installed, let's tweak our VS Code a bit 😀

Article No Longer Available

Once done, let's start the server

npm start

We can see Listening At somenumber ..., in the terminal, which we'll call PORT from here on.

Interacting with the Server

Open any browser
Go to localhost:PORT/pathVars/helloWorld

You can see the response on the page. Try replacing helloWorld with somethingElse in the URL. Those are path variables that are explained below.

Using the URL bar of browsers, you can only make GET requests.

In this post, I've explained how you can use Postman to interact with our Simple Express App. 😄

Article No Longer Available

Check out Postman for a broader set of HTTP methods and API testing functionalities.

Explanation

There are comments which explain the most basic stuff.

This is api.js explained!

There are links down below which you can use to explore further. 😄

And whenever you save either of the files with or without any changes, the server will restart, thanks to nodemon 🔥

Conclusion

It's easier to catch up with a framework when you are familiar with its base programming language than to directly jump to the framework.

References

Further Reads

Interacting with the Simple Express App using Postman

Dhawal Singh Panwar — Mon, 31 Aug 2020 18:12:08 +0000

You're familiar with the concept of modularity from OOPS, right? So, my introduction is not in this post. 😆

Article No Longer Available

Introduction

Postman is an API client which we are gonna use to interact with this Simple Express App.

dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.

Postman is a collaboration platform for API development. Postman's features simplify each step of building an API and streamline collaboration so you can create better APIs—faster.

We'll be referring to this post to set up our Simple Express App.

Article No Longer Available

The agenda is to understand how an API makes client-server interaction convenient 🌱

Introduction
Setup
- Prerequisites
- Windows
- Linux
Usage
- Building a Request
- Receiving a Response
- Another Request
Conclusion
References
Further Reads

Setup

Before getting down to the interaction part, let's set up our workspace.

Prerequisites

VS Code
Node.js
Simple Express App
Postman

Windows

Follow these steps if you do not have the first three prerequisites.
Get Postman

Linux

Follow these steps if you do not have the first three prerequisites.
Install Postman

sudo snap install postman

Usage

Open Postman
Sign up if you want or just click on “Skip signing in and take me straight to the app”
Click on the + button up in the bar which looks like the Browser Tabs bar.

Let's take a look around and figure out what all components of Postman we actually need to care about as beginners.

We'll be utilizing Simple Express App's API Documentation to get familiar with Postman 😁

Building a Request

HTTP Method

See that GET in the extreme left? Clicking on that drops down a list of all the HTTP methods Postman supports. Leave it as it is.

Just right to the Methods Dropdown is the URL bar. Enter this URL

localhost:9107/queryParams?someKey=someValue

Receiving a Response

Clicking on Send will get you this response on the right of your window.

You can see that the server returned what we sent it as query parameters. So, we can send it as many query parameters as we want and it'll just send those back in the response body.

At the top, we can see some information in green. Those are

HTTP Response Code - HTTP has some response codes defined. 200 indicates that the request has succeeded.
Response Time - The time it took for the server to reply to our request.
Response Size - This includes the headers, body, and if any cookies returned by the server.

We can also hover the cursor over the respective tags for more details.

Another Request

Let's send the same data, we sent as a query parameter, in the request body so that it isn't visible in the URL of our request.

Change the method to POST
Change the URL

localhost:9107/bodyJSON

Just below the URL bar is Body, put {"someKey":"someValue"} in it.
Hit Send

It should look like this. 😁

Conclusion

This is how you can use Postman to communicate with any server. You just need to be able to interpret the documentation of the API. I don't think there's any convention for an API Doc but they all have pretty much the same information which we have seen above. Remember to have fun 😁

References

Postman Documentation

Further Reads

How to Use Postman to Manage and Execute Your APIs

Setting up VS Code for JavaScript Development

Dhawal Singh Panwar — Sat, 22 Aug 2020 18:41:27 +0000

You're familiar with the concept of modularity from OOPS, right? So, my introduction is not in this post. 😆

Article No Longer Available

Introduction

In this post, I'll tell you about how you can set up or tweak VS Code to boost productivity and quadruple your development speed, specifically for JavaScript Development.

Visual Studio Code

TL;DR Pretty good text editor. blah blah blah

Installation (All Platforms)

After installation, you can just start writing code. That's it! For JavaScript, of course. For some languages, you need to add extensions for support and IntelliSense to work.

Either you open a .js file to begin with or

Press Ctrl + N to open a new blank file
Click on Plain Text down right in the taskbar at the bottom
Find or type in JavaScript and the option shall appear, click on it!

You're set!

You've already doubled your productivity. For more, keep reading. 😀

Features you will actually love

There are plenty of features but I'll share just those which have actually helped boost my productivity.

Integrated Terminal

Pressing Ctrl + ` will open a terminal in your workspace's directory. You can then run npm installations or your node servers right from within VS Code.

I know the mess it becomes with all those Alt + Tab window or even desktop switches just to keep a terminal up and keep checking the output of your blood and sweat.

Detailed Usage

IntelliSense

I'll keep it simple. IntelliSense basically offers code completions and quick info on the function's parameter list which means it will help you arrange your parameters which you pass when you call a function in the order in which the arguments are originally defined in the function's definition.

You can trigger the IntelliSense in any editor window by typing Ctrl + Space or by typing the dot character (.) in JavaScript.

IntelliSense for JavaScript is provided out of the box with some other languages too. Although, some languages do need extensions to be downloaded from VS Code marketplace.

Detailed Usage

Code Navigation

This comes in handy when your project grows. Forgot how the exported module from your other .js file imported into your currently open .js file works?

You can jump to the definition with ctrl + click or open the definition to the side with ctrl + alt + click.

You'll be taken to the imported/required module's function or object will take you to the origin file where the function is defined or object is declared.

The best part. Matching brackets will be highlighted as soon as the cursor is near one of them.

Pressing Ctrl + Shift + \ will take your cursor to the closing bracket of the current section of code.

Detailed Usage

Integrated Version Control

Don't remember all those git commands? No worries. VS Code comes with integrated version control with Git support in-the-box. That means you don't have to download any extension for Git-based version control systems to work collaboratively on any project.

Pressing Ctrl + Shift + G will open the Source Control pane.

Many other source control providers are available through extensions on the VS Code Marketplace.

Git Installation (All platforms)

Detailed Usage

Support for Emmet snippets

Inspired by CSS Selectors, Emmet allows you to type shortcuts that are then expanded into full pieces of code. Emmet abbreviation and snippet expansions are enabled by default in html, css, jsx, xml among other files.

When you start typing an Emmet abbreviation, you will see the abbreviation displayed in the suggestion list.

Emmet In Action

Detailed Usage

Extensions specific to .js development

Apart from the in-built features, you can also extend VS Code's functionality with Extensions available in VS Code Marketplace.

ESlint

Simply said, it keeps your code clean. If you don't want to spend hours debugging your code, just use this.

Besides checking style, linters are also excellent tools for finding certain classes of bugs, such as those related to variable scope. Assignment to undeclared variables (these leak into the global scope, contaminating it and possibly causing very difficult to find bugs) and the use of undefined variables are examples of errors that are detectable at lint time.

This story was written by Sam Roberts, a Senior Software Engineer at IBM Canada.

Running a linter against your code can tell you many things:

if the code adheres to a certain set of syntax conventions
if the code contains possible sources of problems
if the code matches a set of standards you or your team define

It will raise warnings that you, or your tools, can analyze and give you actionable data to improve your code.

Try It

Prettier

There are times when we developers forget about indentation, use wrong quotes, etc. and have to show our code to our mates and it's embarrassing when they have to ask you to use proper spacing and blah blah blah.

Just press Alt + Shift + F and let Prettier do its amazing job.

Try It

Summary

If you have this setup, you'll be writing clean and beautiful JavaScript code in no time and will also make you hate debugging less. 😁

References

How did I start my tech journey?

Dhawal Singh Panwar — Sat, 22 Aug 2020 18:37:07 +0000

I'm a software developer with a bias towards JavaScript. I can use JavaScript in its entirety of usefulness to do pretty much everything it can. But, the secret is ...

to not skip this part and read each and every word

I'm just kidding. Feel free to scroll through my journey.

We had HTML in grade 8 followed by JavaScript in grade 9 which was around 2012-2013. I started with gedit, a text editor that comes with Ubuntu. Linux? Then? Yes. Our teacher was obsessed with Ubuntu at the time.

We started with the basics as any other programming language, i.e., variable, tokens, operators, I/O, and some arithmetic functions. Then came loops, conditions, and we were saturated even before the patterns came along; m types of triangles and n types of ASCII Art.

Moved to C++ after then till college then JSON caught my eye with its beauty and simplicity. When I dug deep, I found it kinda originated from JavaScript. So frustrated or maybe overwhelmed with C++, I switched back to my old flame, JavaScript, and it was like seeing your school crush after years all grown up and still single. I did what probably you too would do. I caught up with its versions and features.

When I got mature and finally had a better understanding of web technologies, I realized I still need a server-side language to make sense of my Front-End skills and all I knew was PHP, however efficient or powerful you might say it is, we just don't get along. It was like JavaScript and I were made for each other but her parents wouldn't agree, I'm also very good with analogies, so again I started leaning away from it and looking for other technologies to try out.

Python too was around for a while but just as a rebound. I wanted to start developing software but keep away from the traps like Java, .NET, C#, and other general-purpose ones. Quite soon, another very good teacher of mine introduced me to Node.js. I thought this would also come with a huge learning curve but anyways I gave it a try.

Pretty soon, I was already into Microservices and APIs. This was it. Although Node.js is just a framework, JavaScript is literally all I needed all along. I had a few projects up and running even before I knew promises and the whole asynchronous aspect of Node.js.

All this time when I was looking around and trying different languages, I thought I was wasting my time so I did one thing which still helps me and will definitely help you, too. I wrote down my ideas.

Ideas are shit without execution!

It's true. But, till you become capable of executing them, WRITE THEM DOWN. As soon as I got caught up with ES6 and Node.js libraries like Express, I already had plans for projects to build. Guess what? I started developing actual things that now I use in my day to day life and even other people utilize my work.

So, it all comes down to one thing. Till you're figuring out your path to freedom, plan out what will you do when you achieve it. The answer to that question will then become your purpose to wake up the next day. 😄

DEV Community: Dhawal Singh Panwar

How many rows would have been there without that LIMIT clause?

Article No Longer Available

Contents

Introduction

Explanation

MySQL

Postgres

Conclusion

References

Further Reads

Article No Longer Available

What the fetch() is an API ?

Article No Longer Available

Introduction

Contents

Explanation

The Dictionary Analogy

The Restaurant Analogy

Conclusion

References

Further Reads

Understanding the Simple Express App | Node.js

Article No Longer Available

Introduction

dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.

Contents

Setup

Windows

dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.

Simple Express App

Usage

Documentation

Linux

Usage

Starting the Server

Article No Longer Available

Interacting with the Server

Article No Longer Available

Explanation

Conclusion

References

Further Reads

Interacting with the Simple Express App using Postman

Article No Longer Available

Introduction

dspsolves / Simple-Express-App

A very simple Express app for demonstration purposes.

Article No Longer Available

Contents

Setup

Prerequisites

Windows

Linux

Usage

Building a Request

Receiving a Response

Another Request

Conclusion

References

Further Reads

Setting up VS Code for JavaScript Development

Article No Longer Available

Introduction

Visual Studio Code

Features you will actually love

Integrated Terminal

IntelliSense

Code Navigation

Integrated Version Control

Support for Emmet snippets

Extensions specific to .js development

ESlint

Prettier

Summary

References

How did I start my tech journey?