Integrity
Write
Loading...
Vishal Chawla

Vishal Chawla

3 years ago

5 Bored Apes borrowed to claim $1.1 million in APE tokens

Takeaway
Unknown user took advantage of the ApeCoin airdrop to earn $1.1 million.
He used a flash loan to borrow five BAYC NFTs, claim the airdrop, and repay the NFTs.

Yuga Labs, the creators of BAYC, airdropped ApeCoin (APE) to anyone who owns one of their NFTs yesterday.

For the Bored Ape Yacht Club and Mutant Ape Yacht Club collections, the team allocated 150 million tokens, or 15% of the total ApeCoin supply, worth over $800 million. Each BAYC holder received 10,094 tokens worth $80,000 to $200,000.

But someone managed to claim the airdrop using NFTs they didn't own. They used the airdrop's specific features to carry it out. And it worked, earning them $1.1 million in ApeCoin.

The trick was that the ApeCoin airdrop wasn't based on who owned which Bored Ape at a given time. Instead, anyone with a Bored Ape at the time of the airdrop could claim it. So if you gave someone your Bored Ape and you hadn't claimed your tokens, they could claim them.

The person only needed to get hold of some Bored Apes that hadn't had their tokens claimed to claim the airdrop. They could be returned immediately.

So, what happened?

The person found a vault with five Bored Ape NFTs that hadn't been used to claim the airdrop.

A vault tokenizes an NFT or a group of NFTs. You put a bunch of NFTs in a vault and make a token. This token can then be staked for rewards or sold (representing part of the value of the collection of NFTs). Anyone with enough tokens can exchange them for NFTs.

This vault uses the NFTX protocol. In total, it contained five Bored Apes: #7594, #8214, #9915, #8167, and #4755. Nobody had claimed the airdrop because the NFTs were locked up in the vault and not controlled by anyone.

The person wanted to unlock the NFTs to claim the airdrop but didn't want to buy them outright s o they used a flash loan, a common tool for large DeFi hacks. Flash loans are a low-cost way to borrow large amounts of crypto that are repaid in the same transaction and block (meaning that the funds are never at risk of not being repaid).

With a flash loan of under $300,000 they bought a Bored Ape on NFT marketplace OpenSea. A large amount of the vault's token was then purchased, allowing them to redeem the five NFTs. The NFTs were used to claim the airdrop, before being returned, the tokens sold back, and the loan repaid.

During this process, they claimed 60,564 ApeCoin airdrops. They then sold them on Uniswap for 399 ETH ($1.1 million). Then they returned the Bored Ape NFT used as collateral to the same NFTX vault.

Attack or arbitrage?

However, security firm BlockSecTeam disagreed with many social media commentators. A flaw in the airdrop-claiming mechanism was exploited, it said.

According to BlockSecTeam's analysis, the user took advantage of a "vulnerability" in the airdrop.

"We suspect a hack due to a flaw in the airdrop mechanism. The attacker exploited this vulnerability to profit from the airdrop claim" said BlockSecTeam.

For example, the airdrop could have taken into account how long a person owned the NFT before claiming the reward.

Because Yuga Labs didn't take a snapshot, anyone could buy the NFT in real time and claim it. This is probably why BAYC sales exploded so soon after the airdrop announcement.

More on NFTs & Art

CyberPunkMetalHead

CyberPunkMetalHead

2 years ago

Why Bitcoin NFTs Are Incomprehensible yet Likely Here to Stay

I'm trying to understand why Bitcoin NFTs aren't ready.

Ordinals, a new Bitcoin protocol, has been controversial. NFTs can be added to Bitcoin transactions using the protocol. They are not tokens or fungible. Bitcoin NFTs are transaction metadata. Yes. They're not owned.

In January, the Ordinals protocol allowed data like photos to be directly encoded onto sats, the smallest units of Bitcoin worth 0.00000001 BTC, on the Bitcoin blockchain. Ordinals does not need a sidechain or token like other techniques. The Ordinals protocol has encoded JPEG photos, digital art, new profile picture (PFP) projects, and even 1993 DOOM onto the Bitcoin network.

Ordinals inscriptions are permanent digital artifacts preserved on the Bitcoin blockchain. It differs from Ethereum, Solana, and Stacks NFT technologies that allow smart contract creators to change information. Ordinals store the whole image or content on the blockchain, not just a link to an external server, unlike centralized databases, which can change the linked image, description, category, or contract identifier.

So far, more than 50,000 ordinals have been produced on the Bitcoin blockchain, and some of them have already been sold for astronomical amounts. The Ethereum-based CryptoPunks NFT collection spawned Ordinal Punk. Inscription 620 sold for 9.5 BTC, or $218,000, the most.

Segwit and Taproot, two important Bitcoin blockchain updates, enabled this. These protocols store transaction metadata, unlike Ethereum, where the NFT is the token. Bitcoin's NFT is a sat's transaction details.

What effects do ordinary values and NFTs have on the Bitcoin blockchain?

Ordinals will likely have long-term effects on the Bitcoin Ecosystem since they store, transact, and compute more data.

Charges Ordinals introduce scalability challenges. The Bitcoin network has limited transaction throughput and increased fees during peak demand. NFTs could make network transactions harder and more expensive. Ordinals currently occupy over 50% of block space, according to Glassnode.

One of the protocols that supported Ordinals Taproot has also seen a huge uptick:

Taproot use increases block size and transaction costs.

This could cause network congestion but also support more L2s with Ordinals-specific use cases. Dune info here.

Storage Needs The Bitcoin blockchain would need to store more data to store NFT data directly. Since ordinals were introduced, blocksize has tripled from 0.7mb to over 2.2mb, which could increase storage costs and make it harder for nodes to join the network.

Use Case Diversity On the other hand, NFTs on the Bitcoin blockchain could broaden Bitcoin's use cases beyond storage and payment. This could expand Bitcoin's user base. This is two-sided. Bitcoin was designed to be trustless, decentralized, peer-to-peer money.

Chain to permanently store NFTs as ordinals will change everything.

Popularity rise This new use case will boost Bitcoin appeal, according to some. This argument fails since Bitcoin is the most popular cryptocurrency. Popularity doesn't require a new use case. Cryptocurrency adoption boosts Bitcoin. It need not compete with Ethereum or provide extra benefits to crypto investors. If there was a need for another chain that supports NFTs (there isn't), why would anyone choose the slowest and most expensive network? It appears contradictory and unproductive.

Nonetheless, holding an NFT on the Bitcoin blockchain is more secure than any other blockchain, but this has little utility.

Bitcoin NFTs are undoubtedly controversial. NFTs are strange and perhaps harmful to Bitcoin's mission. If Bitcoin NFTs are here to stay, I hope a sidechain or rollup solution will take over and leave the base chain alone.

Tora Northman

Tora Northman

3 years ago

Pixelmon NFTs are so bad, they are almost good!

Bored Apes prices continue to rise, HAPEBEAST launches, Invisible Friends hype continues to grow. Sadly, not all projects are as successful.
Of course, there are many factors to consider when buying an NFT. Is the project a scam? Will the reveal derail the project? Possibly, but when Pixelmon first teased its launch, it generated a lot of buzz.

With a primary sale mint price of 3 ETH ($8,100 USD), it started as an expensive project, with plenty of fans willing to invest in what was sold as a game. After it was revealed, it fell rapidly.
Why? It was overpromised and under delivered.

According to the project's creator[^1], the funds generated will be used to develop the artwork. "The Pixelmon reveal was wrong. This is what our Pixelmon look like in-game. "Despite the fud, I will not go anywhere," he wrote on Twitter. The goal remains. The funds will still be used to build our game. I will finish this project."

The project raised $70 million USD, but the NFTs buyers received were not the project's original teasers. Some call it "the worst NFT project ever," while others call it a complete scam.

But there's hope for some buyers. Kevin emerged from the ashes as the project was roasted over the fire.

A Minecraft character meets Salad Fingers - that's Kevin. He's a frog-like creature whose reveal was such a terrible NFT that it became part of history – and a meme.

If you're laughing at people paying $8K for a silly pixelated image, you might need to take it back. Precisely because of this, lucky holders who minted Kevin have been able to sell the now-memed NFT for over 8 ETH (around $24,000 USD), with some currently listed for 100 ETH.

Of course, Twitter has been awash in memes mocking those who invested in the project, because what else can you do when so many people lose money?

It's still unclear if the NFT project is a scam, but the team behind it was hired on Upwork. There's still hope for redemption, but Kevin's rise to fame appears to be the only positive outcome so far.

[^1] This is not the first time the creator (A 20-yo New Zealanders) has sought money via an online platform and had people claiming he under-delivered.  He raised $74,000 on Kickstarter for a card game called Psycho Chicken. There are hundreds of comments on the Kickstarter project saying they haven't received the product and pleading for a refund or an update.

Amelia Winger-Bearskin

Amelia Winger-Bearskin

3 years ago

Hate NFTs? I must break some awful news to you...

If you think NFTs are awful, check out the art market.

The fervor around NFTs has subsided in recent months due to the crypto market crash and the media's short attention span. They were all anyone could talk about earlier this spring. Last semester, when passions were high and field luminaries were discussing "slurp juices," I asked my students and students from over 20 other universities what they thought of NFTs.

According to many, NFTs were either tasteless pyramid schemes or a new way for artists to make money. NFTs contributed to the climate crisis and harmed the environment, but so did air travel, fast fashion, and smartphones. Some students complained that NFTs were cheap, tasteless, algorithmically generated schlock, but others asked how this was different from other art.

a digital Billboard showed during the 4th annual NFT.NYC conference, a four-day event that featured 1,500 speakers from the crypto and NFT space and hosted 14,000 attendees | Getty Images, Noam Galai / Contributor June 20th, 2022 in New York City Times Square

I'm not sure what I expected, but the intensity of students' reactions surprised me. They had strong, emotional opinions about a technology I'd always considered administrative. NFTs address ownership and accounting, like most crypto/blockchain projects.

Art markets can be irrational, arbitrary, and subject to the same scams and schemes as any market. And maybe a few shenanigans that are unique to the art world.

The Fairness Question

Fairness, a deflating moral currency, was the general sentiment (the less of it in circulation, the more ardently we clamor for it.) These students, almost all of whom are artists, complained to the mismatch between the quality of the work in some notable NFT collections and the excessive amounts these items were fetching on the market. They can sketch a Bored Ape or Lazy Lion in their sleep. Why should they buy ramen with school loans while certain swindlers get rich?

Long Beach, California the sign for the Bored Ape Yacht Club NFT Themed Restaurant, Getty Images, Mario Tama / Staff April 9th 2022

I understand students. Art markets are unjust. They can be irrational, arbitrary, and governed by chance and circumstance, like any market. And art-world shenanigans.

Almost every mainstream critique leveled against NFTs applies just as easily to art markets

Over 50% of artworks in circulation are fake, say experts. Sincere art collectors and institutions are upset by the prevalence of fake goods on the market. Not everyone. Wealthy people and companies use art as investments. They can use cultural institutions like museums and galleries to increase the value of inherited art collections. People sometimes buy artworks and use family ties or connections to museums or other cultural taste-makers to hype the work in their collection, driving up the price and allowing them to sell for a profit. Money launderers can disguise capital flows by using market whims, hype, and fluctuating asset prices.

Almost every mainstream critique leveled against NFTs applies just as easily to art markets.

Art has always been this way. Edward Kienholz's 1989 print series satirized art markets. He stamped 395 identical pieces of paper from $1 to $395. Each piece was initially priced as indicated. Kienholz was joking about a strange feature of art markets: once the last print in a series sells for $395, all previous works are worth at least that much. The entire series is valued at its highest auction price. I don't know what a Kienholz print sells for today (inquire with the gallery), but it's more than $395.

I love Lee Lozano's 1969 "Real Money Piece." Lozano put cash in various denominations in a jar in her apartment and gave it to visitors. She wrote, "Offer guests coffee, diet pepsi, bourbon, half-and-half, ice water, grass, and money." "Offer real money as candy."

Lee Lozano kept track of who she gave money to, how much they took, if any, and how they reacted to the offer of free money without explanation. Diverse reactions. Some found it funny, others found it strange, and others didn't care. Lozano rarely says:

Apr 17 Keith Sonnier refused, later screws lid very tightly back on. Apr 27 Kaltenbach takes all the money out of the jar when I offer it, examines all the money & puts it all back in jar. Says he doesn’t need money now. Apr 28 David Parson refused, laughing. May 1 Warren C. Ingersoll refused. He got very upset about my “attitude towards money.” May 4 Keith Sonnier refused, but said he would take money if he needed it which he might in the near future. May 7 Dick Anderson barely glances at the money when I stick it under his nose and says “Oh no thanks, I intend to earn it on my own.” May 8 Billy Bryant Copley didn’t take any but then it was sort of spoiled because I had told him about this piece on the phone & he had time to think about it he said.

Smart Contracts (smart as in fair, not smart as in Blockchain)

Cornell University's Cheryl Finley has done a lot of research on secondary art markets. I first learned about her research when I met her at the University of Florida's Harn Museum, where she spoke about smart contracts (smart as in fair, not smart as in Blockchain) and new protocols that could help artists who are often left out of the economic benefits of their own work, including women and women of color.

Cheryl Finley on the right, with Hank Thomas and Dr. Deborah Willis attending the 2018 Aperture Gala at Ceder Lake on October 30th, 2018 in NYC, Photo by Patrick Mullan via Getty Images.

Her talk included findings from her ArtNet op-ed with Lauren van Haaften-Schick, Christian Reeder, and Amy Whitaker.

NFTs allow us to think about and hack on formal contractual relationships outside a system of laws that is currently not set up to service our community.

The ArtNet article The Recent Sale of Amy Sherald's ‘Welfare Queen' Symbolizes the Urgent Need for Resale Royalties and Economic Equity for Artists discussed Sherald's 2012 portrait of a regal woman in a purple dress wearing a sparkling crown and elegant set of pearls against a vibrant red background.

Amy Sherald sold "Welfare Queen" to Princeton professor Imani Perry. Sherald agreed to a payment plan to accommodate Perry's budget.

Amy Sherald rose to fame for her 2016 portrait of Michelle Obama and her full-length portrait of Breonna Taylor, one of the most famous works of the past decade.

As is common, Sherald's rising star drove up the price of her earlier works. Perry's "Welfare Queen" sold for $3.9 million in 2021.

Amy Sherald speaking about her work in front of her painting “Miss Everything (Unsuppressed Deliverance) | Getty Images
Raleigh News & Observer / Contributor May 2018

Imani Perry's early investment paid off big-time. Amy Sherald, whose work directly increased the painting's value and who was on an artist's shoestring budget when she agreed to sell "Welfare Queen" in 2012, did not see any of the 2021 auction money. Perry and the auction house got that money.

Sherald sold her Breonna Taylor portrait to the Smithsonian and Louisville's Speed Art Museum to fund a $1 million scholarship. This is a great example of what an artist can do for the community if they can amass wealth through their work.

NFTs haven't solved all of the art market's problems — fakes, money laundering, market manipulation — but they didn't create them. Blockchain and NFTs are credited with making these issues more transparent. More ideas emerge daily about what a smart contract should do for artists.

NFTs are a copyright solution. They allow us to hack formal contractual relationships outside a law system that doesn't serve our community.

Amy Sherald shows the good smart contracts can do (as in, well-considered, self-determined contracts, not necessarily blockchain contracts.) Giving back to our community, deciding where and how our work can be sold or displayed, and ensuring artists share in the equity of our work and the economy our labor creates.

Photo of Amy Sherald during New York Fashion Week attending Ulla Johnson at the Brooklyn Botanic Garden, Getty Images
Dominik Bindl / Stringer September 2021

You might also like

Sofien Kaabar, CFA

Sofien Kaabar, CFA

2 years ago

Innovative Trading Methods: The Catapult Indicator

Python Volatility-Based Catapult Indicator

As a catapult, this technical indicator uses three systems: Volatility (the fulcrum), Momentum (the propeller), and a Directional Filter (Acting as the support). The goal is to get a signal that predicts volatility acceleration and direction based on historical patterns. We want to know when the market will move. and where. This indicator outperforms standard indicators.

Knowledge must be accessible to everyone. This is why my new publications Contrarian Trading Strategies in Python and Trend Following Strategies in Python now include free PDF copies of my first three books (Therefore, purchasing one of the new books gets you 4 books in total). GitHub-hosted advanced indications and techniques are in the two new books above.

The Foundation: Volatility

The Catapult predicts significant changes with the 21-period Relative Volatility Index.

The Average True Range, Mean Absolute Deviation, and Standard Deviation all assess volatility. Standard Deviation will construct the Relative Volatility Index.

Standard Deviation is the most basic volatility. It underpins descriptive statistics and technical indicators like Bollinger Bands. Before calculating Standard Deviation, let's define Variance.

Variance is the squared deviations from the mean (a dispersion measure). We take the square deviations to compel the distance from the mean to be non-negative, then we take the square root to make the measure have the same units as the mean, comparing apples to apples (mean to standard deviation standard deviation). Variance formula:

As stated, standard deviation is:

# The function to add a number of columns inside an array
def adder(Data, times):
    
    for i in range(1, times + 1):
    
        new_col = np.zeros((len(Data), 1), dtype = float)
        Data = np.append(Data, new_col, axis = 1)
        
    return Data

# The function to delete a number of columns starting from an index
def deleter(Data, index, times):
    
    for i in range(1, times + 1):
    
        Data = np.delete(Data, index, axis = 1)
        
    return Data
    
# The function to delete a number of rows from the beginning
def jump(Data, jump):
    
    Data = Data[jump:, ]
    
    return Data

# Example of adding 3 empty columns to an array
my_ohlc_array = adder(my_ohlc_array, 3)

# Example of deleting the 2 columns after the column indexed at 3
my_ohlc_array = deleter(my_ohlc_array, 3, 2)

# Example of deleting the first 20 rows
my_ohlc_array = jump(my_ohlc_array, 20)

# Remember, OHLC is an abbreviation of Open, High, Low, and Close and it refers to the standard historical data file

def volatility(Data, lookback, what, where):
    
  for i in range(len(Data)):

     try:

        Data[i, where] = (Data[i - lookback + 1:i + 1, what].std())
     except IndexError:
        pass
        
  return Data

The RSI is the most popular momentum indicator, and for good reason—it excels in range markets. Its 0–100 range simplifies interpretation. Fame boosts its potential.

The more traders and portfolio managers look at the RSI, the more people will react to its signals, pushing market prices. Technical Analysis is self-fulfilling, therefore this theory is obvious yet unproven.

RSI is determined simply. Start with one-period pricing discrepancies. We must remove each closing price from the previous one. We then divide the smoothed average of positive differences by the smoothed average of negative differences. The RSI algorithm converts the Relative Strength from the last calculation into a value between 0 and 100.

def ma(Data, lookback, close, where): 
    
    Data = adder(Data, 1)
    
    for i in range(len(Data)):
           
            try:
                Data[i, where] = (Data[i - lookback + 1:i + 1, close].mean())
            
            except IndexError:
                pass
            
    # Cleaning
    Data = jump(Data, lookback)
    
    return Data
def ema(Data, alpha, lookback, what, where):
    
    alpha = alpha / (lookback + 1.0)
    beta  = 1 - alpha
    
    # First value is a simple SMA
    Data = ma(Data, lookback, what, where)
    
    # Calculating first EMA
    Data[lookback + 1, where] = (Data[lookback + 1, what] * alpha) + (Data[lookback, where] * beta)    
 
    # Calculating the rest of EMA
    for i in range(lookback + 2, len(Data)):
            try:
                Data[i, where] = (Data[i, what] * alpha) + (Data[i - 1, where] * beta)
        
            except IndexError:
                pass
            
    return Datadef rsi(Data, lookback, close, where, width = 1, genre = 'Smoothed'):
    
    # Adding a few columns
    Data = adder(Data, 7)
    
    # Calculating Differences
    for i in range(len(Data)):
        
        Data[i, where] = Data[i, close] - Data[i - width, close]
     
    # Calculating the Up and Down absolute values
    for i in range(len(Data)):
        
        if Data[i, where] > 0:
            
            Data[i, where + 1] = Data[i, where]
            
        elif Data[i, where] < 0:
            
            Data[i, where + 2] = abs(Data[i, where])
            
    # Calculating the Smoothed Moving Average on Up and Down
    absolute values        
                             
    lookback = (lookback * 2) - 1 # From exponential to smoothed
    Data = ema(Data, 2, lookback, where + 1, where + 3)
    Data = ema(Data, 2, lookback, where + 2, where + 4)
    
    # Calculating the Relative Strength
    Data[:, where + 5] = Data[:, where + 3] / Data[:, where + 4]
    
    # Calculate the Relative Strength Index
    Data[:, where + 6] = (100 - (100 / (1 + Data[:, where + 5])))  
    
    # Cleaning
    Data = deleter(Data, where, 6)
    Data = jump(Data, lookback)

    return Data
EURUSD in the first panel with the 21-period RVI in the second panel.
def relative_volatility_index(Data, lookback, close, where):

    # Calculating Volatility
    Data = volatility(Data, lookback, close, where)
    
    # Calculating the RSI on Volatility
    Data = rsi(Data, lookback, where, where + 1) 
    
    # Cleaning
    Data = deleter(Data, where, 1)
    
    return Data

The Arm Section: Speed

The Catapult predicts momentum direction using the 14-period Relative Strength Index.

EURUSD in the first panel with the 14-period RSI in the second panel.

As a reminder, the RSI ranges from 0 to 100. Two levels give contrarian signals:

  • A positive response is anticipated when the market is deemed to have gone too far down at the oversold level 30, which is 30.

  • When the market is deemed to have gone up too much, at overbought level 70, a bearish reaction is to be expected.

Comparing the RSI to 50 is another intriguing use. RSI above 50 indicates bullish momentum, while below 50 indicates negative momentum.

The direction-finding filter in the frame

The Catapult's directional filter uses the 200-period simple moving average to keep us trending. This keeps us sane and increases our odds.

Moving averages confirm and ride trends. Its simplicity and track record of delivering value to analysis make them the most popular technical indicator. They help us locate support and resistance, stops and targets, and the trend. Its versatility makes them essential trading tools.

EURUSD hourly values with the 200-hour simple moving average.

This is the plain mean, employed in statistics and everywhere else in life. Simply divide the number of observations by their total values. Mathematically, it's:

We defined the moving average function above. Create the Catapult indication now.

Indicator of the Catapult

The indicator is a healthy mix of the three indicators:

  • The first trigger will be provided by the 21-period Relative Volatility Index, which indicates that there will now be above average volatility and, as a result, it is possible for a directional shift.

  • If the reading is above 50, the move is likely bullish, and if it is below 50, the move is likely bearish, according to the 14-period Relative Strength Index, which indicates the likelihood of the direction of the move.

  • The likelihood of the move's direction will be strengthened by the 200-period simple moving average. When the market is above the 200-period moving average, we can infer that bullish pressure is there and that the upward trend will likely continue. Similar to this, if the market falls below the 200-period moving average, we recognize that there is negative pressure and that the downside is quite likely to continue.

lookback_rvi = 21
lookback_rsi = 14
lookback_ma  = 200
my_data = ma(my_data, lookback_ma, 3, 4)
my_data = rsi(my_data, lookback_rsi, 3, 5)
my_data = relative_volatility_index(my_data, lookback_rvi, 3, 6)

Two-handled overlay indicator Catapult. The first exhibits blue and green arrows for a buy signal, and the second shows blue and red for a sell signal.

The chart below shows recent EURUSD hourly values.

Signal chart.
def signal(Data, rvi_col, signal):
    
    Data = adder(Data, 10)
        
    for i in range(len(Data)):
            
        if Data[i,     rvi_col] < 30 and \
           Data[i - 1, rvi_col] > 30 and \
           Data[i - 2, rvi_col] > 30 and \
           Data[i - 3, rvi_col] > 30 and \
           Data[i - 4, rvi_col] > 30 and \
           Data[i - 5, rvi_col] > 30:
               
               Data[i, signal] = 1
                           
    return Data
Signal chart.

Signals are straightforward. The indicator can be utilized with other methods.

my_data = signal(my_data, 6, 7)
Signal chart.

Lumiwealth shows how to develop all kinds of algorithms. I recommend their hands-on courses in algorithmic trading, blockchain, and machine learning.

Summary

To conclude, my goal is to contribute to objective technical analysis, which promotes more transparent methods and strategies that must be back-tested before implementation. Technical analysis will lose its reputation as subjective and unscientific.

After you find a trading method or approach, follow these steps:

  • Put emotions aside and adopt an analytical perspective.

  • Test it in the past in conditions and simulations taken from real life.

  • Try improving it and performing a forward test if you notice any possibility.

  • Transaction charges and any slippage simulation should always be included in your tests.

  • Risk management and position sizing should always be included in your tests.

After checking the aforementioned, monitor the plan because market dynamics may change and render it unprofitable.

Gajus Kuizinas

Gajus Kuizinas

3 years ago

How a few lines of code were able to eliminate a few million queries from the database

I was entering tens of millions of records per hour when I first published Slonik PostgreSQL client for Node.js. The data being entered was usually flat, making it straightforward to use INSERT INTO ... SELECT * FROM unnset() pattern. I advocated the unnest approach for inserting rows in groups (that was part I).

Bulk inserting nested data into the database

However, today I’ve found a better way: jsonb_to_recordset.

jsonb_to_recordset expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause.

jsonb_to_recordset allows us to query and insert records from arbitrary JSON, like unnest. Since we're giving JSON to PostgreSQL instead of unnest, the final format is more expressive and powerful.

SELECT *
FROM json_to_recordset('[{"name":"John","tags":["foo","bar"]},{"name":"Jane","tags":["baz"]}]')
AS t1(name text, tags text[]);
 name |   tags
------+-----------
 John | {foo,bar}
 Jane | {baz}
(2 rows)

Let’s demonstrate how you would use it to insert data.

Inserting data using json_to_recordset

Say you need to insert a list of people with attributes into the database.

const persons = [
  {
    name: 'John',
    tags: ['foo', 'bar']
  },
  {
    name: 'Jane',
    tags: ['baz']
  }
];

You may be tempted to traverse through the array and insert each record separately, e.g.

for (const person of persons) {
  await pool.query(sql`
    INSERT INTO person (name, tags)
    VALUES (
      ${person.name},
      ${sql.array(person.tags, 'text[]')}
    )
  `);
}

It's easier to read and grasp when working with a few records. If you're like me and troubleshoot a 2M+ insert query per day, batching inserts may be beneficial.

What prompted the search for better alternatives.

Inserting using unnest pattern might look like this:

await pool.query(sql`
  INSERT INTO public.person (name, tags)
  SELECT t1.name, t1.tags::text[]
  FROM unnest(
    ${sql.array(['John', 'Jane'], 'text')},
    ${sql.array(['{foo,bar}', '{baz}'], 'text')}
  ) AS t1.(name, tags);
`);

You must convert arrays into PostgreSQL array strings and provide them as text arguments, which is unsightly. Iterating the array to create slices for each column is likewise unattractive.

However, with jsonb_to_recordset, we can:

await pool.query(sql`
  INSERT INTO person (name, tags)
  SELECT *
  FROM jsonb_to_recordset(${sql.jsonb(persons)}) AS t(name text, tags text[])
`);

In contrast to the unnest approach, using jsonb_to_recordset we can easily insert complex nested data structures, and we can pass the original JSON document to the query without needing to manipulate it.

In terms of performance they are also exactly the same. As such, my current recommendation is to prefer jsonb_to_recordset whenever inserting lots of rows or nested data structures.

Sanjay Priyadarshi

Sanjay Priyadarshi

2 years ago

Using Ruby code, a programmer created a $48,000,000,000 product that Elon Musk admired.

Unexpected Success

Photo of Tobias Lutke from theglobeandmail

Shopify CEO and co-founder Tobias Lutke. Shopify is worth $48 billion.

World-renowned entrepreneur Tobi

Tobi never expected his first online snowboard business to become a multimillion-dollar software corporation.

Tobi founded Shopify to establish a 20-person company.

The publicly traded corporation employs over 10,000 people.

Here's Tobi Lutke's incredible story.

Elon Musk tweeted his admiration for the Shopify creator.

30-October-2019.

Musk praised Shopify founder Tobi Lutke on Twitter.

Happened:

Screenshot by Author

Explore this programmer's journey.

What difficulties did Tobi experience as a young child?

Germany raised Tobi.

Tobi's parents realized he was smart but had trouble learning as a toddler.

Tobi was learning disabled.

Tobi struggled with school tests.

Tobi's learning impairments were undiagnosed.

Tobi struggled to read as a dyslexic.

Tobi also found school boring.

Germany's curriculum didn't inspire Tobi's curiosity.

“The curriculum in Germany was taught like here are all the solutions you might find useful later in life, spending very little time talking about the problem…If I don’t understand the problem I’m trying to solve, it’s very hard for me to learn about a solution to a problem.”

Studying computer programming

After tenth grade, Tobi decided school wasn't for him and joined a German apprenticeship program.

This curriculum taught Tobi software engineering.

He was an apprentice in a small Siemens subsidiary team.

Tobi worked with rebellious Siemens employees.

Team members impressed Tobi.

Tobi joined the team for this reason.

Tobi was pleased to get paid to write programming all day.

His life could not have been better.

Devoted to snowboarding

Tobi loved snowboarding.

He drove 5 hours to ski at his folks' house.

His friends traveled to the US to snowboard when he was older.

However, the cheap dollar conversion rate led them to Canada.

2000.

Tobi originally decided to snowboard instead than ski.

Snowboarding captivated him in Canada.

On the trip to Canada, Tobi encounters his wife.

Tobi meets his wife Fiona McKean on his first Canadian ski trip.

They maintained in touch after the trip.

Fiona moved to Germany after graduating.

Tobi was a startup coder.

Fiona found work in Germany.

Her work included editing, writing, and academics.

“We lived together for 10 months and then she told me that she need to go back for the master's program.”

With Fiona, Tobi immigrated to Canada.

Fiona invites Tobi.

Tobi agreed to move to Canada.

Programming helped Tobi move in with his girlfriend.

Tobi was an excellent programmer, therefore what he did in Germany could be done anywhere.

He worked remotely for his German employer in Canada.

Tobi struggled with remote work.

Due to poor communication.

No slack, so he used email.

Programmers had trouble emailing.

Tobi's startup was developing a browser.

After the dot-com crash, individuals left that startup.

It ended.

Tobi didn't intend to work for any major corporations.

Tobi left his startup.

He believed he had important skills for any huge corporation.

He refused to join a huge corporation.

Because of Siemens.

Tobi learned to write professional code and about himself while working at Siemens in Germany.

Siemens culture was odd.

Employees were distrustful.

Siemens' rigorous dress code implies that the corporation doesn't trust employees' attire.

It wasn't Tobi's place.

“There was so much bad with it that it just felt wrong…20-year-old Tobi would not have a career there.”

Focused only on snowboarding

Tobi lived in Ottawa with his girlfriend.

Canada is frigid in winter.

Ottawa's winters last.

Almost half a year.

Tobi wanted to do something worthwhile now.

So he snowboarded.

Tobi began snowboarding seriously.

He sought every snowboarding knowledge.

He researched the greatest snowboarding gear first.

He created big spreadsheets for snowboard-making technologies.

Tobi grew interested in selling snowboards while researching.

He intended to sell snowboards online.

He had no choice but to start his own company.

A small local company offered Tobi a job.

Interested.

He must sign papers to join the local company.

He needed a work permit when he signed the documents.

Tobi had no work permit.

He was allowed to stay in Canada while applying for permanent residency.

“I wasn’t illegal in the country, but my state didn’t give me a work permit. I talked to a lawyer and he told me it’s going to take a while until I get a permanent residency.”

Tobi's lawyer told him he cannot get a work visa without permanent residence.

His lawyer said something else intriguing.

Tobis lawyer advised him to start a business.

Tobi declined this local company's job offer because of this.

Tobi considered opening an internet store with his technical skills.

He sold snowboards online.

“I was thinking of setting up an online store software because I figured that would exist and use it as a way to sell snowboards…make money while snowboarding and hopefully have a good life.”

What brought Tobi and his co-founder together, and how did he support Tobi?

Tobi lived with his girlfriend's parents.

In Ottawa, Tobi encounters Scott Lake.

Scott was Tobis girlfriend's family friend and worked for Tobi's future employer.

Scott and Tobi snowboarded.

Tobi pitched Scott his snowboard sales software idea.

Scott liked the idea.

They planned a business together.

“I was looking after the technology and Scott was dealing with the business side…It was Scott who ended up developing relationships with vendors and doing all the business set-up.”

Issues they ran into when attempting to launch their business online

Neither could afford a long-term lease.

That prompted their online business idea.

They would open a store.

Tobi anticipated opening an internet store in a week.

Tobi seeks open-source software.

Most existing software was pricey.

Tobi and Scott couldn't afford pricey software.

“In 2004, I was sitting in front of my computer absolutely stunned realising that we hadn’t figured out how to create software for online stores.”

They required software to:

  • to upload snowboard images to the website.

  • people to look up the types of snowboards that were offered on the website. There must be a search feature in the software.

  • Online users transmit payments, and the merchant must receive them.

  • notifying vendors of the recently received order.

No online selling software existed at the time.

Online credit card payments were difficult.

How did they advance the software while keeping expenses down?

Tobi and Scott needed money to start selling snowboards.

Tobi and Scott funded their firm with savings.

“We both put money into the company…I think the capital we had was around CAD 20,000(Canadian Dollars).”

Despite investing their savings.

They minimized costs.

They tried to conserve.

No office rental.

They worked in several coffee shops.

Tobi lived rent-free at his girlfriend's parents.

He installed software in coffee cafes.

How were the software issues handled?

Tobi found no online snowboard sales software.

Two choices remained:

  1. Change your mind and try something else.

  2. Use his programming expertise to produce something that will aid in the expansion of this company.

Tobi knew he was the sole programmer working on such a project from the start.

“I had this realisation that I’m going to be the only programmer who has ever worked on this, so I don’t have to choose something that lots of people know. I can choose just the best tool for the job…There is been this programming language called Ruby which I just absolutely loved ”

Ruby was open-source and only had Japanese documentation.

Latin is the source code.

Tobi used Ruby twice.

He assumed he could pick the tool this time.

Why not build with Ruby?

How did they find their first time operating a business?

Tobi writes applications in Ruby.

He wrote the initial software version in 2.5 months.

Tobi and Scott founded Snowdevil to sell snowboards.

Tobi coded for 16 hours a day.

His lifestyle was unhealthy.

He enjoyed pizza and coke.

“I would never recommend this to anyone, but at the time there was nothing more interesting to me in the world.”

Their initial purchase and encounter with it

Tobi worked in cafes then.

“I was working in a coffee shop at this time and I remember everything about that day…At some time, while I was writing the software, I had to type the email that the software would send to tell me about the order.”

Tobi recalls everything.

He checked the order on his laptop at the coffee shop.

Pennsylvanian ordered snowboard.

Tobi walked home and called Scott. Tobi told Scott their first order.

They loved the order.

How were people made aware about Snowdevil?

2004 was very different.

Tobi and Scott attempted simple website advertising.

Google AdWords was new.

Ad clicks cost 20 cents.

Online snowboard stores were scarce at the time.

Google ads propelled the snowdevil brand.

Snowdevil prospered.

They swiftly recouped their original investment in the snowboard business because to its high profit margin.

Tobi and Scott struggled with inventories.

“Snowboards had really good profit margins…Our biggest problem was keeping inventory and getting it back…We were out of stock all the time.”

Selling snowboards returned their investment and saved them money.

They did not appoint a business manager.

They accomplished everything alone.

Sales dipped in the spring, but something magical happened.

Spring sales plummeted.

They considered stocking different boards.

They naturally wanted to add boards and grow the business.

However, magic occurred.

Tobi coded and improved software while running Snowdevil.

He modified software constantly. He wanted speedier software.

He experimented to make the software more resilient.

Tobi received emails requesting the Snowdevil license.

They intended to create something similar.

“I didn’t stop programming, I was just like Ok now let me try things, let me make it faster and try different approaches…Increasingly I got people sending me emails and asking me If I would like to licence snowdevil to them. People wanted to start something similar.”

Software or skateboards, your choice

Scott and Tobi had to choose a hobby in 2005.

They might sell alternative boards or use software.

The software was a no-brainer from demand.

Daniel Weinand is invited to join Tobi's business.

Tobis German best friend is Daniel.

Tobi and Scott chose to use the software.

Tobi and Scott kept the software service.

Tobi called Daniel to invite him to Canada to collaborate.

Scott and Tobi had quit snowboarding until then.

How was Shopify launched, and whence did the name come from?

The three chose Shopify.

Named from two words.

First:

  • Shop

Final part:

  • Simplify

Shopify

Shopify's crew has always had one goal:

  • creating software that would make it simple and easy for people to launch online storefronts.

Launched Shopify after raising money for the first time.

Shopify began fundraising in 2005.

First, they borrowed from family and friends.

They needed roughly $200k to run the company efficiently.

$200k was a lot then.

When questioned why they require so much money. Tobi told them to trust him with their goals. The team raised seed money from family and friends.

Shopify.com has a landing page. A demo of their goal was on the landing page.

In 2006, Shopify had about 4,000 emails.

Shopify rented an Ottawa office.

“We sent a blast of emails…Some people signed up just to try it out, which was exciting.”

How things developed after Scott left the company

Shopify co-founder Scott Lake left in 2008.

Scott was CEO.

“He(Scott) realized at some point that where the software industry was going, most of the people who were the CEOs were actually the highly technical person on the founding team.”

Scott leaving the company worried Tobi.

Tobis worried about finding a new CEO.

To Tobi:

A great VC will have the network to identify the perfect CEO for your firm.

Tobi started visiting Silicon Valley to meet with venture capitalists to recruit a CEO.

Initially visiting Silicon Valley

Tobi came to Silicon Valley to start a 20-person company.

This company creates eCommerce store software.

Tobi never wanted a big corporation. He desired a fulfilling existence.

“I stayed in a hostel in the Bay Area. I had one roommate who was also a computer programmer. I bought a bicycle on Craiglist. I was there for a week, but ended up staying two and a half weeks.”

Tobi arrived unprepared.

When venture capitalists asked him business questions.

He answered few queries.

Tobi didn't comprehend VC meetings' terminology.

He wrote the terms down and looked them up.

Some were fascinated after he couldn't answer all these queries.

“I ended up getting the kind of term sheets people dream about…All the offers were conditional on moving our company to Silicon Valley.”

Canada received Tobi.

He wanted to consult his team before deciding. Shopify had five employees at the time.

2008.

A global recession greeted Tobi in Canada. The recession hurt the market.

His term sheets were useless.

The economic downturn in the world provided Shopify with a fantastic opportunity.

The global recession caused significant job losses.

Fired employees had several ideas.

They wanted online stores.

Entrepreneurship was desired. They wanted to quit work.

People took risks and tried new things during the global slump.

Shopify subscribers skyrocketed during the recession.

“In 2009, the company reached neutral cash flow for the first time…We were in a position to think about long-term investments, such as infrastructure projects.”

Then, Tobi Lutke became CEO.

How did Tobi perform as the company's CEO?

“I wasn’t good. My team was very patient with me, but I had a lot to learn…It’s a very subtle job.”

2009–2010.

Tobi limited the company's potential.

He deliberately restrained company growth.

Tobi had one costly problem:

  • Whether Shopify is a venture or a lifestyle business.

The company's annual revenue approached $1 million.

Tobi battled with the firm and himself despite good revenue.

His wife was supportive, but the responsibility was crushing him.

“It’s a crushing responsibility…People had families and kids…I just couldn’t believe what was going on…My father-in-law gave me money to cover the payroll and it was his life-saving.”

Throughout this trip, everyone supported Tobi.

They believed it.

$7 million in donations received

Tobi couldn't decide if this was a lifestyle or a business.

Shopify struggled with marketing then.

Later, Tobi tried 5 marketing methods.

He told himself that if any marketing method greatly increased their growth, he would call it a venture, otherwise a lifestyle.

The Shopify crew brainstormed and voted on marketing concepts.

Tested.

“Every single idea worked…We did Adwords, published a book on the concept, sponsored a podcast and all the ones we tracked worked.”

To Silicon Valley once more

Shopify marketing concepts worked once.

Tobi returned to Silicon Valley to pitch investors.

He raised $7 million, valuing Shopify at $25 million.

All investors had board seats.

“I find it very helpful…I always had a fantastic relationship with everyone who’s invested in my company…I told them straight that I am not going to pretend I know things, I want you to help me.”

Tobi developed skills via running Shopify.

Shopify had 20 employees.

Leaving his wife's parents' home

Tobi left his wife's parents in 2014.

Tobi had a child.

Shopify has 80,000 customers and 300 staff in 2013.

Public offering in 2015

Shopify investors went public in 2015.

Shopify powers 4.1 million e-Commerce sites.

Shopify stores are 65% US-based.

It is currently valued at $48 billion.