Got a database that's slower than a slug on sedatives?

Suffering from joins that make you want to split?

Well, I've got some tips to make you SQL sing!

This guide assumes you've got basic SQL skills down. If you don't know your 

, you might want to brush up first. I'll be slinging around terms like indexing, partitioning and materialized views - so make sure you're fluent in 

These practices can help you optimize your database queries and improve performance in real-world applications. However, their applicability may vary depending on the specific use case and database design.

Now let's optimize! I'll be dropping knowledge bombs to make your queries fast and nimble. With my tips, you can tune and tweak your database into a lean, mean-speed machine. Never again will you have to watch those painful loading screens when you run a query - with these tricks, you'll be cruising faster than a cheetah on an open highway!

So rev up those SQL skills and prepare for a high-speed joyride through the query optimization fast lane! This will take your database's performance from zero to sixty in no time flat. Now let's put the pedal to the metal, here's tip number one.

When dealing with large tables, creating appropriate indexes can significantly improve query performance. 

 when you need to check for the existence of related records.

SELECT specific columns over all columns in the table (SQL)

Here are some reasons why selecting specific columns is better than using 

 - Retrieving all columns requires more I/O and memory even if they aren't needed. 

 - Exposing unnecessary sensitive data in columns is a risk. Select just what is needed.

 - All columns may include outdated data if not regularly referenced. Select ensures freshness.

 - Query won't break if new columns are added. 

 - Makes the query intent clear by showing exactly what data is needed.

Avoid exposing unnecessary sensitive data

So in summary, listing columns explicitly improves the performance, security, accuracy, and maintainability of queries while also clarifying intent. 

There are a few reasons why it's better to avoid subqueries in the WHERE clause when possible:

 - Subqueries in the WHERE clause can often perform poorly since the subquery will be executed for each row in the outer query [

]. This leads to repetitive execution and slow performance. JOINs are usually faster since they allow combining data in a single pass.

 - Complex nested subqueries can make the query hard to understand and maintain. 

 often express the intent in a clearer way.

 - Some databases put restrictions on subqueries in 

 clauses, like not allowing correlation to outer query columns. 

Here are some example queries that could be improved by removing the subquery:

 can improve performance, and readability, and remove limitations.

Avoid SELECT DISTINCT where possible (SQL)

Here are a few reasons why it's better to avoid SELECT DISTINCT where possible:

 requires sorting and eliminating duplicates, which adds overhead. 

 can obscure the query's intent and make it less clear.

 may cover up poorly structured data or queries. Better to fix the root cause.

 eliminates duplicates but also removes valid repeated values. This can distort data.

Return unique column values only, like primary keys.

 can hurt performance, understanding, and data accuracy. Better to use specific filtering, grouping, constraints, and joins instead.

 filters rows before aggregation, reducing work. 

 filters on raw data, which is more intuitive. 

 conditions apply to grouped/aggregated data, not raw data. Can distort analysis.

 filters earlier, improves performance, keeps queries simple, and makes intent more clear. 

Create joins with INNER JOIN(not WHERE) (SQL)

Here are some reasons why using INNER JOIN is better than filtering in the WHERE clause:

 clearly show the connections between tables. 

 - It's easier to modify joins when they are separated out. 

 support outer joins and different types of connections. 

 allow the optimizer to better select the join order and strategy.

Unlock the full potential of your database with these expert tips and best practices for optimizing query performance. Whether you're dealing with large datasets, complex joins, or subqueries, our comprehensive guide covers it all. Learn how to create efficient indexes, make the most of SQL functions, choose the right joins, and much more. Boost your application's speed and responsiveness with these proven techniques.

💾 Database Optimization Tips for Improved Query Performance

If you have ever faced an issue where importing a library from npm that throws `window is undefined` in NextJS using the global object `window` that gives access to the browser window, you could have that in a `useEffect` and assign it to a ref


A quick fix to when NextJS return window is undefined when you import a lib that isn't a react library rather one that requires window

NextJS: Window returns undefined

Basic understanding of the Canvas API supported on all modern browsers.

Create a canvas element in your HTML file or panel if using an online editor.

Create or open a javascript panel so we can draw our triangle

Left comments on each line that describes what each line does but for some further reading checkout this 

A simple way that enables you to create a rounded triangle with the Canvas API.

How to create a rounded triangle with Canvas in Javascript, [The easy way]

Check for your fonts locally, only download them as a fallback.

A list of tips to make your CSS life easier.

CSS Pro Tips

am new to this build-in public thing, I did not want to reveal the name at first, because domains were not acquired yet. 

One to point to the backend and frontend i.e. api.***.*** and just the base domain for the frontend.

The other for the custom emails i.e. jane-doe@*****.**
I will not mention the domains in this article, Rua is not quite finished yet, but it's close enough to launch.

 server to use the new domain, and all new emails going forward will use that.

The frontend is hosted on Vercel, a great platform

The backend is hosted on a linux server on AWS, specifically the Lightsail product. I plan to move it to an EC2 instance and cache it using the GraphCDN for faster requests on the client.

What's the current progress on the Saas am working on, the name I gave it and so much more.

The Newsletter Service: The Progress

In this article, I am assuming a couple of things that you have already done:

You already have a domain pointing to your VPS this can be on

Vultr --> Basically any platform that enables you to spin up your own Linux server

You already have Caddy installed and it's running on port 

The port you want to point the domain to is currently running in the background, use something like 

Update your Caddyfile to add a new entry something like this


A short description on how to update a new entry in your Caddyfile and load the changes with caddy

How to update caddy's new reverse proxy items

I have been working on a Saas product, it's a 

. I decided to use Nextjs for its frontend. 

I wanted to protect some routes like the dashboard unlike SPA React where you can write up a quick HOC and wrap your component and redirect if the token in local storage or wherever you are saving has expired, not valid or non-existent redirects to login to generate a new one. 

you can do the same in Next but I did not want that approach.

Basically what I wanted to achieve was to make all API reads in the 

, meaning I get everything I want before the page is loaded, which makes me avoid having spinners and stuff.

I was able to achieve that by making a profile API call based on the token I saved in the cookie but that meant I will have to replicate the same logic to always get a profile on each page so I wrote up this HOF(High order function) to handle that and here it is:

Basically create a file to house our HOF this can be 

update your getServerSideProps lifecycle to this

And that's it future me or someone out there that might need this.

How to implement route protection in Next.JS with getServerSideProps

How to protect Next.js routes  with getServerSideProps

As mentioned in the previous article, I will outline the feature requirements and tech to build them.

I have started so many projects that I never finished and the main reason was the scope was too huge, I had so many features on the backlog and most of it was on the backend stuff so some I never reached a phase where I saw things coming together. So in this saas, I decided to limit the scope to launch an MVP as fast as possible.

A user should be able to get a custom email address on signup based on their username, which can be Twitter or GitHub or their choice.

A user should be able to get all newsletters received in a week's range.

A user is able to mark an issue as read or programmatically handle that on the client based on scroll events.

A user is able to mark newsletter email as spam which would be saved to avoid future issues received.

A user should be able to highlight sections of a newsletter and save them [not on 

So what about the tech, how am I going to achieve building out this saas app:

is an SMTP server open-source project that enables receiving, sending, and managing emails.

to handle monthly/yearly subscription payments from users. Stripe is not available in my country.

Been using it for a while now and love using it over (used to hate it way back, but I find the developer experience, also the community has matured over the years, with tools such GraphCDN that enable developers to take advantage of Fastly's CDN for fast responses

It's not replacing REST at all but it's a handy tool to actually play around with.

For the backend, I work with Golang, and Node sometimes Python on a day to day but picked Node, just a preference you can use any language or framework "

You can achieve a thin client unlike SPA's

Fast build times, making the developer experience amazing

I can build custom APIs within it and take advantage of Vercel's edge computing.

Those emails need to be stored somewhere and the database isn't one of them, never save HTML stuff on the database for read-performance reasons.

Well all that data from users needs to be saved somewhere and Postgres is the right database for this job because most of the data will be related and Postgresql handles that perfectly, trust me I tried MongoDB it does not work great with relationships[its a whole mess to achieve that might do an article in the future], I think it only has 

And that's it for the requirements side of things, next will be a log of what I have done so far and probably the launch date.

Outlines the system requirements for the newsletter service

The Newsletter Service: The Requirements

So the story is I was going through my email and realized I had like 150 emails received, most of them were:

Transactional emails, e.g. password resets, login alerts, GitHub

Email from products I signed up for but clearly unchecked the "

I want to receive product alerts or news alerts

So going through all these emails was a hustle, and I like reading newsletters my favorites being 

 by Colin & Samir team. At that point I thought of the idea:

What if I could have an app that receives all of my newsletter issues, and I can easily access the app and just go through a week's issues. That would be a step closer to decluttering my inbox.

Are there potential customers for this app?

This is not a new idea, a similar product called Mailbrew aggregates content from Twitter follows, RSS feed, & Email issues to generate a weekly or daily digest tailored to you. They are making money from such a feature, at the time of this writing Mailbrew is making close to $15,000 MRR. 

The other reason is well we are getting too many emails for sure, last year alone close to 320 billion emails were sent and received in 2021, according to Statista.

Every single day there is a newsletter launching from celebrities like Dua Lipa to popular youtube channel TMG. The Substack reader can only do so much, and it's strictly Substack stuff, what about the Mailchimp issues or Buttondownmail issues.

So after knowing the product could potentially get paying customers, I decided to research the core tech of it, and that was receiving emails. 

For me to receive emails I needed 3 things:

Domain name: to generate addresses to emails

Storage: the emails need to be stored somewhere and a database isn't the best place for that. 

I am 80% done, was actually supposed to launch on 10th Feb 2022, but got some hurdles with the domain stuff and a lot of things from my personal life.

A platform that houses all your newsletter issues in one place.

The Newsletter Service: The Idea

So I recently changed laptops, from being a 

user for the past 2.5 years. I was running Parrot during that time, great os by the way if you are into cyber security stuff or just another full-stack developer like me.

I switch over to Mac, and all my ssh keys were in my previous laptop, they access all of my servers in Digital Ocean and AWS, to cloning and pushing to Github and so much more. I could not convince myself to update all servers with the new ssh keys and I had some issues with Github SSH cloning. So here are the steps I took, enabling me to move all my keys to my new laptop.

Copy over the ssh keys from your old laptop to the new one, they should be located in the 

folder. You can use a flash drive or a cloud platform, whatever works for you.

Paste the files in your new laptop in the same directory.

If you do that you will get an error like this:

A simple step-by-step process on how to transfer your ssh keys from one Unix-based os to another.

How to move your .ssh generated keys to a new laptop.

 or government controlling the flow of money which is 

in Defi, there are no banks or government thus no censorship and rules are defined in code.

Decentralized finance is made up of five pillars these are:

Stable coins are basically cryptocurrency tokens/coins pegged to a dollar in the physical world, meaning when you buy a stable coin from a decentralized/centralized exchange a dollar is minted in the physical world to back it, which in turn brings stability to the asset.

was created by the biggest centralized exchange in the world, 

Our physical financial system is built on lending and borrowing, and because Defi aims to replace Cefi, lending and borrowing do exist but with some rules. 

A simple example in the real world is when you borrow from a bank, you provide collateral for security so that if you default on your loan and the bank can sell the asset and at least get back close or all of the amount borrowed, it can be a car or house. 

In Defi it's different, since there is no bank, you borrow from a pool of funds provided by lenders part of the network who expect to get a profit from lending their assets. To enable that crypto/Defi loans are overcollateralized meaning in the case of a bank you can get a loan of $1.5 million dollars collateralized by an asset of the same or lesser value than that but in Defi, you will have to collateralize an asset may be close to $2.5 million to get the $1.5 million dollar asset, that is ~66% more. 

You might be thinking why would I borrow then, it doesn't make sense. Most of the time borrowers provide volatile assets like Ethereum, and to get a stable coin, in our previous example they put down $2.5 million of Ethereum and get $1.5 million of USDC, they can use that for emergencies or trading and within that time Ethereum's price could go up by 10%, that means the Ethereum they collateralized is now worth $2.75 million, the person could pay back the loan of $1.5 million and get back their Ethereum that is worth $2.75 million so they make a profit. So what happens if the price of Ethereum goes down, well in that case the smart contract (which is literally code) liquidates the collateralized Ethereum to give back the money to the lenders with interest thus the over-collateralization.

The good thing about Defi loans they don't have a fixed time limit like banks do.

Some great lending and borrowing platforms include:

These are exchanges run by code, they only change when the code changes meaning no government can shut it down or an organization. They allow traders to exchange tokens or coins example exchange Ethereum with Tether. 

DEXs are really cool because you can trade coins/tokens at any time provided there is a liquidity pool for them. What is a liquidity pool? it's a crowdsourced pool of cryptocurrency/token pairs like 

that allows other people to trade that asset. 

More on liquidity pools in the next article.

You can easily make money from such exchanges with strategies like 

which is trading that exploits the tiny differences in price between identical assets in two or more markets. 

Some of the advantages of DEXs over CEXs is:

You control your wallet, unlike in CEXs like Coinbase where you share a wallet

DEXs can't be shut down as they are decentralized and run by code(code is the law in Defi) unlike CEXs like Binance that shutdown trading on assets during mass-selloffs.

No need to provide KYC(know your customer) info such as picture, national id, or passport in order to use them.

Insurance is a means of protection from financial loss. It is a form of risk management, primarily used to hedge against the risk of a contingent or uncertain loss.

This sector also exists in Defi where you can protect both your coins/tokens/NFTs to physical assets like a farm/house and even hurricane insurance.

Some platforms that exist to facilitate this include:

Margin trading refers to the process whereby individual investors can invest more than they can afford to, an example I can have $20 dollars and I want to invest in Ethereum, 

which at the time of this writing costs around ~$4300, the platform can allow you to trade with 10x your amount, so that means I put my $20 dollars but with 10x leverage, I am basically trading with $200 dollars, so if the price of Ethereum goes up by 4% I make 40% and vice-versa. 

An example of a platform that allows margin trading is 

Because code is the law, there are instances where the smart contracts deployed contained bugs that hackers exploit and get away with millions of dollars example a Defi platform called 

, the flash loan(basically is a loan with zero collateral that you have to pay back within 10 seconds) feature got exploited, with the hacker getting away with $45 million, this ended up tanking its token.

Some developers create Defi platforms to make away with people's money, what is known as a 

An example a developer can add specific instructions to the smart contract to prevent users from selling their token/coin after an exchange with a valuable token. 

This leads to a lack of trust for newbies getting into Defi because of the bad reputation.

Defi is mostly open-source, meaning developers expose their whitepapers, smart contracts, and code to the world for other developers to fork or even track bugs, this gives newbies to go through to better make confident decisions.

There are audit firms some paid or open-source that audit Defi platforms' smart contracts, grade their security, and expose their findings publicly, this also helps to make confident decisions.

within the platform and take note of how many addresses control liquidity in a specific 

pair, the more the better. You can get such info from analytic platforms like 

--------------------------------------------

There are other sectors that make up the Defi such as gambling, an example is 

 that allows users to socially bet on football, soccer, tennis and so much more.

Stay tuned for more articles on the Defi category, if you have any questions join my 

What is Defi, what's the difference between Dapps and how to get started