SQL is one of the most widely used languages for managing and manipulating data stored in a relational database.

In this article, we'll cover some SQL cheat codes that can help you save time and simplify complex queries.

Use Aliases for Table and Column Names

You can use aliases to make your SQL code more readable and reduce the amount of typing required when working with long tables and column names.

You can also use aliases to differentiate between multiple instances of the same table in a query. Here's an example:

-- Without aliases
SELECT employees.employee_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id

-- With aliases
SELECT emp.employee_name, dept.department_name
FROM employees AS emp
JOIN departments AS dept ON emp.department_id = dept.department_id

In the example above, we used the aliases "emp" and "dept" for the "employees" and "departments" tables, respectively. This makes the code more readable and reduces the amount of typing required.

Use the IN Operator with Subqueries

You can use the IN operator to quickly filter data based on a list of values. This is especially useful when working with subqueries. Here's an example:

-- Retrieve all customers who have placed an order
SELECT *
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
)

In the example above, the subquery returns a list of distinct customer IDs from the "orders" table. The outer query uses the IN operator to retrieve all customers whose IDs are in the list returned by the subquery.

Use Wildcards for Pattern Matching

You can use wildcards such as % and _ with the LIKE operator to quickly search for patterns in string data. Here's an example:

-- Retrieve all products that contain the word "widget" in their name
SELECT *
FROM products
WHERE product_name LIKE '%widget%'

In the example above, we used the % wildcard to match any number of characters before or after the word "widget". This retrieves all products that contain the word "widget" in their name.

Use the HAVING Clause with GROUP BY

You can use the HAVING clause to filter data based on aggregate functions such as COUNT, SUM, AVG, and so on. Here's an example:

-- Retrieve all customers who have placed more than 10 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10

In the example above, the GROUP BY clause groups orders by customer ID. The COUNT(*) function counts the number of orders for each customer. The HAVING clause is then used to filter the results to only include customers who have placed more than 10 orders.

Use the EXISTS Operator for Existence Checks

You can use the EXISTS operator to quickly check if a subquery returns any rows. This is useful for existence checks. Here's an example:

-- Retrieve all customers who have placed an order
SELECT *
FROM customers AS c
WHERE EXISTS (
    SELECT *
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)

In the example above, the EXISTS operator checks if the subquery returns any rows for each customer in the "customers" table. If the subquery returns at least one row, the customer is included in the result set.

Use the CASE Statement for Conditional Logic

You can use the CASE statement for conditional logic in SQL queries. Here's an example:

-- Assign a customer tier based on order count
SELECT customer_id, COUNT(*) AS order_count,
    CASE
        WHEN COUNT(*) < 5 THEN 'Bronze'
        WHEN COUNT(*) >= 5 AND COUNT(*) < 10 THEN 'Silver'
        WHEN COUNT(*) >= 10 THEN 'Gold'
    END AS customer_tier
FROM orders
GROUP BY customer_id

Use the UNION Operator to Combine Results

You can use the UNION operator to combine the results of two or more SELECT statements. This is useful when you need to combine data from multiple tables or sources. Here's an example:

-- Retrieve all customers and employees
SELECT 'customer' AS record_type, customer_name AS name, email
FROM customers
UNION
SELECT 'employee' AS record_type, employee_name AS name, email
FROM employees

In the example above, two SELECT statements are combined using the UNION operator. The result set includes a "record_type" column to differentiate between customers and employees.

Use the INNER JOIN Operator to Combine Tables

You can use the INNER JOIN operator to combine data from two or more tables based on a common column or set of columns. Here's an example:

-- Retrieve all orders with customer and product details
SELECT o.order_id, c.customer_name, p.product_name, o.order_date
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN products AS p ON o.product_id = p.product_id

In the example above, the INNER JOIN operator combines data from the "orders", "customers", and "products" tables based on their common IDs. The result set includes the order ID, customer name, product name, and order date.

Use the LEFT JOIN Operator for Missing Data

You can use the LEFT JOIN operator to include data from one table even if there is no corresponding data in another table.

This is useful when you need to include all data from one table, even if there are missing values in another table. Here's an example:

-- Retrieve all customers and their orders (even if they haven't placed an order)
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id

In the example above, the LEFT JOIN operator includes all customers from the "customers" table, even if they haven't placed an order. The result set includes the customer ID, customer name, and order ID.

Use the GROUP_CONCAT Function to Concatenate Strings

You can use the GROUP_CONCAT function to concatenate strings from multiple rows into a single row. This is useful when you need to combine multiple values into a single string. Here's an example:

-- Retrieve all products and their categories
SELECT p.product_id, p.product_name, GROUP_CONCAT(c.category_name SEPARATOR ', ') AS categories
FROM products AS p
JOIN product_categories AS pc ON p.product_id = pc.product_id
JOIN categories AS c ON pc.category_id = c.category_id
GROUP BY p.product_id

In the example above, the GROUP_CONCAT function concatenates the category names for each product into a single string, separated by commas.

Wrapping Up

SQL is an essential language for both data analysts and data scientists. By mastering some of the fundamental SQL commands and cheats, you can perform complex data analysis with ease, and extract valuable insights from your data.

From selecting and filtering data to grouping and joining tables, these commands are designed to help you manipulate your data in various ways, and ultimately make informed decisions.

Whether you're new to SQL or a seasoned user, these tips can help you save time, streamline your workflow, and take your data analysis skills to the next level.

Let’s connect on Twitter and Linkedin

Think of Emmet as shorthand. With it, you can easily write a lot of code quickly. It dramatically speeds up your HTML & CSS workflow.

Understanding how to use Emmet is literally a superpower. Some have even called it a coding cheat-code. ?

And it's just one of many amazing built-in features of VS Code.

Recently, I launched a comprehensive and detailed course called Become A VS Code SuperHero.**** It covers all aspects of VS Code in great depth.

This article is based on one of the 11 sections of the course: Writing & Formatting Code.

HTML

With Emmet you can quickly create an HTML boilerplate in the blink of an eye. In an HTML file, simply type ! and you’ll see that Emmet has popped up as a suggestion. Now hit Enter. There you have it, a basic, blank HTML web page ready to go.

If you’ve never seen HTML before and have no clue what all of this is, don’t worry. I’m just demonstrating the capabilities of VS Code and Emmet. You don’t have to know what any of this means right now.

Basic Tags

To create basic HTML tags, just type the tag's name and hit Enter. Notice how Emmet puts your cursor inside the tag. You are now set up to continue typing inside the tag.

• Try typing: div then Enter, or h1 Enter, or p Enter.

• These work as well: bq for a <blockquote>, hdr for a <header>, ftr for a <footer>, btn for a <button>, and sect for a section.

Classes

If you are familiar with CSS, Emmet uses the same way to refer to classes using a .. To define a class along with the tag simply add it like this:

• div.wrapper —> <div class="wrapper"></div>

• h1.header.center —> <h1 class="header center"></h1>

ID’s

Id's work very much the same:

• div#hero —> <div id="hero"></div>

Combining

You can string these together:

• div#hero.wrapper —> <div id="hero" class="wrapper"></div>

Attributes

We can also specify attributes for tags:

• img[src="cat.jpg"][alt="Cute cat pic"] —> <img src="cat.jpg" alt="Cute cat pic" />

Content

To include content within the tag, we simply wrap the content in curly braces, { }.

• p{This is a paragraph} —> <p>This is a paragraph</p>

Siblings & Children

It just keeps getting better. We can also specify siblings and children using the + and > characters.

• section+section —> <section></section><section></section>

• ul>li —> <ul><li></li></ul>

Climbing Up

You have to try to picture what you are building in your head as you type the Emmet shorthand. In this example we'll "climb up" the tree by using ^.

div+div>p>span+em^bq

Result:

<div></div>
<div>
    <p><span></span><em></em></p>
    <blockquote></blockquote>
</div>

Here, we wanted the blockquote to be on the same level as the paragraph. Because of that, we needed to "climb up". Otherwise, it would be inside of the paragraph.

Grouping

If your structure is very complex, you may want to group tags instead of traversing by climbing.

In this example, we'll create a header and footer without climbing using parenthesis ( ).

div>(header>ul>li>a)+footer>p

Result:

<div>
    <header>
        <ul>
            <li><a href=""></a></li>
        </ul>
    </header>
    <footer>
        <p></p>
    </footer>
</div>

Multiplication and $

We can generate multiple tags by multiplying (*) and number items in sequence using a dollar sign ($).

• ul>li*5 —> <ul><li></li><li></li><li></li><li></li><li></li></ul>

• ul>li{Item $}*3 —> <ul><li>Item 1</li><li>Item 2</li><li>Item 3</li></ul>

You can even customize the numbering sequence by padding with zeros, starting with a specific number, and even reverse direction.

Pad with zeros: ul>li.item$$$*5

Result:

<ul>
    <li class="item001"></li>
    <li class="item002"></li>
    <li class="item003"></li>
    <li class="item004"></li>
    <li class="item005"></li>
</ul>

Start with a specific number: ul>li.item$@3*5

Result:

<ul>
    <li class="item3"></li>
    <li class="item4"></li>
    <li class="item5"></li>
    <li class="item6"></li>
    <li class="item7"></li>
</ul>

Reverse direction: ul>li.item$@-*5

Result:

<ul>
    <li class="item5"></li>
    <li class="item4"></li>
    <li class="item3"></li>
    <li class="item2"></li>
    <li class="item1"></li>
</ul>

Reverse direction from a specific number: ul>li.item$@-3*5

Result:

<ul>
    <li class="item7"></li>
    <li class="item6"></li>
    <li class="item5"></li>
    <li class="item4"></li>
    <li class="item3"></li>
</ul>

Implicit Tag Names

There are certain tags that do not need to be typed and can be implied.

• A class defined initially with no tag will be implied as a <div>.
  .wrapper —> <div class="wrapper"></div>

• A class defined within an emphasis tag will be implied as a <span>.
  em>.emphasis —> <em><span class="emphasis"></span></em>

• A class defined within a list will be implied as a list item.
  ul>.item —> <ul><li class="item"></li></ul>

• A class defined within a table will be implied as a <tr> and within a row would be a <td>.
  table>.row>.col —> <table><tr class="row"><td class="col"></td></tr></table>

Wrap with Tags

There will be times when you have existing code that you want to wrap with a tag. We can do with easily with Emmet.

Just highlight the code that you want to wrap and open the command pallet (F1). Then search for Emmet: Wrap with Abbreviation. You'll then be presented with a dialog box where you can type in the element.

test —> <div>test</div>

You can also use standard Emmet syntax in this dialog. Try wrapping some text with span.wrapper.

By default, this functionality is not assigned to a keyboard shortcut. So if you find yourself using it often, you may want to add a custom shortcut for it.

Lorem Ipsum

"Lorem Ipsum" is dummy text used by developers to represent data on a page. Just type lorem and hit Enter. Emmet will generate 30 words of fake text that you can use as a filler in your project.

The amount of words generated can be defined as well.

• lorem10 will give you 10 words of random text.

Putting It All Together

Let's use several things that we've learned so far. Give this a try:

ul.my-list>lorem10.item-$*5

Result:

<ul class="my-list">
  <li class="item-1">Lorem ipsum dolor sit amet.</li>
  <li class="item-2">Numquam repudiandae fuga porro consequatur?</li>
  <li class="item-3">Culpa, est. Tenetur, deleniti nihil?</li>
  <li class="item-4">Numquam architecto corrupti quam repudiandae.</li>
</ul>

CSS

In CSS, Emmet has an abbreviation for every property. I'm not going to list all of them, but I will point out my most used ones. To see the full list, refer to the Emmet cheat-sheet.

Position

• pos —> position:relative; (defaults to relative)

• pos:s —> position:static;

• pos:a —> position:absolute;

• pos:r —> position:relative;

• pos:f —> position:fixed;

Display

• d —> display:block; (defaults to block)

• d:n —> display:none;

• d:b —> display:block;

• d:f —> display:flex;

• d:if —> display:inline-flex;

• d:i —> display:inline;

• d:ib —> display:inline-block;

Cursor

• cur —> cursor:pointer;

Color

• c —> color:#000;

• c:r —> color:rgb(0, 0, 0);

• c:ra —> color:rgba(0, 0, 0, .5);

• op —> opacity: ;

Margin & Padding

• m —> margin: ;

• m:a —> margin:auto;

• mt —> margin-top: ;

• mr —> margin-right: ;

• mb —> margin-bottom: ;

• ml —> margin-left: ;

• p —> padding: ;

• pt —> padding-top: ;

• pr —> padding-right: ;

• pb —> padding-bottom: ;

• pl —> padding-left: ;

Box Sizing

• bxz —> box-sizing:border-box;

Width

• w —> width: ;

• h —> height: ;

• maw —> max-width: ;

• mah —> max-height: ;

• miw —> min-width: ;

• mih —> min-height: ;

Border

• bd —> border: ;

• bd+ —> border:1px solid #000;

• bd:n —> border:none;

Emmet Is Awesome!

With Emmet, you can create a really complex HTML structure with one line. It’s really awesome. And, it also works with CSS.

You can see how Emmet can drastically increase your efficiency and speed when writing HTML and CSS.

If you want to further increase your efficiency and speed while using VS Code, check out my course Become A VS Code SuperHero.

The course dives much deeper into these concepts and helps you to become a fast, efficient superhero developer :)

I'm Jesse from Texas. Check out my other content and let me know how I can help you on your journey to becoming a web developer.

• Subscribe To My YouTube

• Say Hello! Instagram | Twitter

• Sign Up For My Newsletter

Excel has many applications, like keeping track of inventory, maintaining a mailing list, making sales reports, and many others.

As the database grows, one main issue many users encounter is getting duplicate values and rows. This can make your calculations inaccurate and will make people question your competency.

You could end up giving a summary report with duplicate values or even mailing a letter to the same person twice. So you'll need to find and remove the duplicates to avoid these small mistakes that could have serious implications.

Remove Duplicates

Option 1: Using the remove duplicate tool

Since this problem happens a lot, there is a dedicated command on a ribbon that makes it easy to deal with duplicates. This is the case with recent versions of Microsoft Office suite, such as excel 2007 up to 2016.

Select the table you'll work on

First, you need to select the cells that are targeted, as the tool can be used to remove duplicates in entire rows or partially matching records.

You can do this by selecting the table and then pressing Ctrl + A. You should make sure the original file is saved as the process deletes the dupes permanently.

Click on the data tab at the top of the screen

Once you have selected the range, check the top of the screen and click the data tab. The different commands will be shown, and you should then check for ‘remove duplicates’ and click on it.

A small dialogue box will pop up on the screen. The first row is automatically selected as the ‘my data has header’ is ticked. If there is no header and the data starts at the 1^st row, deselect that option.

Press the OK button to remove duplicates

The entire table is now selected, and thus you should go ahead and press the OK button. This will delete all duplicates. The deletion details appear on the screen with unique values remaining and the number of duplicate entries removed shown.

However, if you want to remove partial duplicates based on specific columns, you should select them, leaving the rest. If the table contains many columns, it is best to unselect all and then just select those that need dupes removal.

Once done, you should click OK. Then the duplicated information will be removed, and the details will be shown on the screen.

Option 2: Using advanced filters in Excel

The advanced filter icon helps you to identify and delete duplicates in Excel. This can be used in the latest Microsoft Office suite and the 2003 version. You need to open your Excel spreadsheet and select all by clicking Ctrl + A.

Click the data tab then advanced button under the sort and filter section

You should then click on the Data tab at the top of the screen where different sections appear beneath it. Look for the sort & filter section and click on the advanced button.

A dialogue box will pop up on the screen where you can either select ‘copy to another location’ or ‘filter the list in-place.’ The latter hides all rows with duplicates while the former generates a copy of the records.

Adjust the range of data under the "list range"

In the dialogue box, there is a list range field with data populated by Excel. If you want to change the range, you can do it by adjusting it under the ‘list range.’

Leaving the criteria range blank and copying to the field is only useful if you had chosen to copy to another place. Otherwise, leave it blank for the ‘filter the list in-place’ option.

Tick the "unique records only" box

Under the same dialogue box, there is a field labeled "unique records only" - tick that box. This tells Excel to filter out duplicates while retaining unique entries.

Click OK to remove duplicates

After you've followed this process, you can click the OK button to get rid of the duplicates. The document then contains duplicate data except for the ones which have been removed.

As the process assumes there are headers in the document, if the 1^st row contains a duplicate, it will not be removed. Delete it manually if it is not a header.

When you're using the advanced filter process, you can only remove duplicates in the entire table as there is no choice of doing it partially. However, you can remove duplicates and, at the same time, create a copy of the data.

Option 3: Using a duplicate remover tool with 2 mouse clicks

Apart from the in-built duplicate removers, you can use add-ons such as Ablebits duplicate remover to get rid of dupes. The tool is multi-purpose and can perform other functions as well. It works on all operating systems, and all Excel versions. Here we will focus on one way of using the tool that takes just 2 mouse clicks.

Select the cell in the table of interest

Check the table where you need to dedupe records, and on the Ablebits data click "dedupe table". This selects the the entire table, and the dialogue box will open with all columns chosen automatically.

Select delete duplicates from the drop-down list under "select the action" field

On the lower-right, there is a drop-down list under the select action field. Choose the delete duplicate option and press OK. The duplicate values will be deleted except for 1^st occurrences.

Removing duplicates in key columns

The 2 mouse click process can be used to remove duplicates on specific rows under crucial columns. To do this, uncheck the other columns leaving the ones you want to dedupe checked. Follow two points above, and the duplicates will be removed.

The select action can be used for other operations such as copying duplicates to another location without deleting them. Use the drop-down menu to select accordingly so your Excel sheet is neat and without errors.

Final Word

Wiping duplicates on Excel is simple using the three options highlighted above. The process is easy, and with this guide you can do it with a few clicks to get the ultimate assignment help and work assistance from Excel.

When you're working in Excel you should always clean your data set, eliminating any unprofessional errors. If you come across any challenges, check that the data being processed is not subtotaled or outlined.

In such cases, you need to remove the subtotals and outline then use any of the methods above. The data will then be cleaned, making it much easier to work on them.

In computer science, analysis of algorithms is a very crucial part. It is important to find the most efficient algorithm for solving a problem. It is possible to have many algorithms to solve a problem, but the challenge here is to choose the most efficient one.

Now the point is, how can we recognize the most efficient algorithm if we have a set of different algorithms? Here, the concept of space and time complexity of algorithms comes into existence. Space and time complexity acts as a measurement scale for algorithms. We compare the algorithms on the basis of their space (amount of memory) and time complexity (number of operations).

The total amount of the computer's memory used by an algorithm when it is executed is the space complexity of that algorithm. We’ll not discuss space complexity in this article (to make this article a bit smaller).

Time Complexity

So, the time complexity is the number of operations an algorithm performs to complete its task (considering that each operation takes the same amount of time). The algorithm that performs the task in the smallest number of operations is considered the most efficient one in terms of the time complexity. However, the space and time complexity are also affected by factors such as your operating system and hardware, but we are not including them in this discussion.

Now to understand the time complexity, we will take an example in which we’ll compare two different algorithms which are used to solve a particular problem.

The problem is searching. We have to search for an element in an array (in this problem, we are going to assume that the array is sorted in ascending order). To solve this problem we have two algorithms:

1. Linear Search.

2. Binary Search.

Let’s say the array contains ten elements, and we have to find the number ten in the array.

const array = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
const search_digit = 10;

Linear search algorithm will compare each element of the array to the search_digit. When it finds the search_digit in the array, it will return true.

Now let’s count the number of operations it performs. Here, the answer is 10 (since it compares every element of the array). So, Linear search uses ten operations to find the given element (these are the maximum number of operations for this array; in the case of Linear search, this is also known as the worst case of an algorithm).

In general, Linear search will take n number of operations in its worst case (where n is the size of the array).

Let’s examine the Binary search algorithm for this case.

Binary search can be easily understood by this example:

Source: Learneroo

If we try to apply this logic on our problem then, first we’ll compare search_digit with the middle element of the array, that is 5. Now since 5 is less than 10, then we will start looking for the search_digit in the array elements greater than 5, in the same way until we get the desired element 10.

Now, try to count the number of operations binary search took to find the desired element. It took approximately four operations. Now, this was the worst case for binary search. This shows that there is a logarithmic relation between the number of operations performed and the total size of the array.

number of operations = log(10) = 4(approx) for base 2

We can generalize this result for Binary search as:

For an array of size n, the number of operations performed by the Binary Search is: log(n)

The Big O Notation

In the above statements, we saw that for an array of size n, linear search will perform n operations to complete the search. On the other hand, Binary search performed log(n) number of operations (both for their worst cases). We can represent this as a graph (x-axis: number of elements, y-axis: number of operations).

Source: Techtud

It is quite clear from the figure that the rate by which the complexity increases for Linear search is much faster than that for binary search.

When we analyse an algorithm, we use a notation to represent its time complexity and that notation is Big O notation.

For Example: time complexity for Linear search can be represented as O(n) and O(log n) for Binary search (where, n and log(n) are the number of operations).

The Time complexity or Big O notations for some popular algorithms are listed below:

1. Binary Search: O(log n)
2. Linear Search: O(n)
3. Quick Sort: O(n * log n)
4. Selection Sort: O(n * n)
5. Travelling salesperson : O(n!)

Conclusion

I really appreciate your efforts if you are still reading this article. Now, you must be thinking - why is time complexity so important to understand?

We know that for a small number of elements (say 10), the difference between the number of operations performed by binary search and linear search is not so big. But in the real world, most of the time, we deal with problems that have big chunks of data.

For example, if we have 4 billion elements to search for, then, in its worst case, linear search will take 4 billion operations to complete its task. Binary search will complete this task in just 32 operations. That’s a big difference. Now let’s assume that if one operation takes 1 ms for completion, then binary search will take only 32 ms whereas linear search will take 4 billion ms (that is approx. 46 days). That’s a significant difference.

This is the reason why studying time complexity becomes important when it comes to such a big amount of data.

Resources

Grokking Algorithms- by Aditya Y Bhargava

Introduction to Big O notation and Time Complexity- by CS Dojo

Using Apollo Server and Postgres — Sequelize, we’ll create a proof of concept exploiting the info parameter of the resolver function for a 94% reduction in database load on Type queries*.

If you are already familiar with the Apollo Server resolver signature and its info parameter and want to skip to the hack, click here. Thanks to my inquisitive friend Sloan Brantley Gwaltney for tipping me off to the info parameter’s potential.

Background — The resolver function signature

Apollo Server provides the following resolver function signature from their docs:

fieldName(obj, args, context, info) { result }

Back in April, I wrote some notes on the signature to teach to some teammates who were new to GraphQL / Apollo Server. Below is my (slightly) modified version of the signature:

(instance, arguments, context, info) { ...returning data... }

**instance**

obj / root / instance
— GraphQL Type associated with the resolver
— only used in Type custom field resolvers (for other Types / renamed fields)
— exists as an instance of the Type’s corresponding database Model

ex:
Type: User
Model: User, instance is ‘user’
Type custom field resolver:
user => user.property/.relationshipGetter()

**arguments**

arguments / Input object
— arguments to the Query or Mutation
— typically in the form of an Input Type object [defined in the Schema]
— Inputs are reusable objects that may contain many fields, a subset of which are appropriate for each resolver
— flexible inputs that can be used for both Query and Mutation resolvers
— destructuring in the resolver allows selectivity of the Input object fields

ex:
UserInput: { id, username, avatar, githubID }
resolver: (root, { id }) => User.findById(id);
// destructures 1 of 4 UserInput fields

**context**

context / ctx
— the context object is injected at runtime in the Apollo middleware declaration of app.js
— it is the most versatile of the resolver parameters
— allows you to pass in things like utility functions, database models, authenticated User, and so on
— by passing these in the context, you no longer need ‘require’ statements for models and helpers. They are accessible directly from the resolver.
— typically defined as a nesting object with each subcontext having its own object

**info**

no idea?

…That was until a serendipitous conversation with Sloan led to discussing this useless parameter. Sloan mentioned that it contained information about the incoming Query. This got my gears turning on improving resolver efficiency.

The info parameter

The info object holds details about your entire API Schema and other bits that I assume Apollo Server uses for processing. In particular, it holds information about the Query itself — specifically the set of Type fields requested.

Sequelize and the Tale of Gross Inefficiency

As it turns out, when Sequelize (and I believe every other OR/DM*) resolves rows or documents, it does so in their entirety. On the front / receiving end the data is indeed trimmed to specifications. But on the back end, the process appears to be:

DB query for **entire** row/doc → map / custom resolver requested fields → filter data and resolve requested fields

This was proven with a Postgres echo I ran from Sequelize on a User query:

SELECT "id", "role", "email", ...wtf Sequelize..., "timezone", "country_id", "city_id", "created_at", "updated_at" FROM "users" AS "User" WHERE "User"."github_username" = 'the-vampiire' LIMIT 1;

The Postgres query selects all 17 fields while the API Query itself only requests 1:

user(username:”the-vampiire”) { id }

Digging Into the info Object

With a bit of digging into the info object, I was able to get to my target: the requested fields. My theory was that if I knew the fields, I could pass them in as the attributes property of the Sequelize query object to reduce the load on the Postgres server.

info.fieldNodes[].selectionSet.selections[].name.value

notes

• fieldNodes is an array with the 0th element is the first query. My guess is that it’s an array to support batch queries.
• selections is an array with objects for each requested field of the Type
• the field name itself is buried under selections.name.value

The Hack

So what does all of this mean? Well with a couple of lines of code, a Model (from the context of course!), and the info argument, I wrote the following utility and proof of concept. It uses the Sequelize attributes property of the query object to get data only from the requested columns.

Using this utility resulted in a 94% decrease in the size of the query. This, of course, scales with the number of fields requested.

One of the major known benefits of using a GraphQL API is that it allows the front end to request a payload of the exact shape and size needed.

Using my utility allows the back end to reflect the same benefits by similarly reducing the load on the database server.

The first and last lines of the mapAttributes function ensure only directly mapped Model fields are passed as attributes to the query object.

This prevents errors that arise from requesting fields that do not exist as columns on the Model.

These would arise from fields that require custom Type field resolvers (like Type relationships or custom Type field names).

Proof of Concept

User Type query: user(username:”the-vampiire”) { id }

before → all 17 fields of the User table are queried

SELECT "id", "role", "email", ...wtf Sequelize..., "timezone", "country_id", "city_id", "created_at", "updated_at" FROM "users" AS "User" WHERE "User"."github_username" = 'the-vampiire' LIMIT 1;

after → only the single requested field is queried

Executing (default): SELECT "status", "id" FROM "users" AS "User" WHERE "User"."github_username" = 'the-vampiire' LIMIT 1;

This hack is officially abided by The Dude

Caveats*

• I have not tested this with Mongoose or other popular OR/DMs, but in principle, the effect should be the same. The mapAttributes function and query object would just need a few customizations.
• I have not tested this with batch queries with different fields being requested (it may affect the fieldNodes property of info).
• Does not work with custom Type field resolvers for renamed fields
• The benefits will scale by the ratio of requested Type fields to total fields on the corresponding Model.

— Vamp