DEV Community: Jacob Lee

From Founder to Freelancer: How I Incorporated a Business and Onboarded My First Client

Jacob Lee — Fri, 10 Feb 2023 06:48:17 +0000

Hey readers! For those of you who don't know me, I was previously the co-founder and CTO of a startup called Autocode. We built and scaled a fantastic product that reached over 600,000 developers, but after six years, I felt it was time for a change of scenery and a new adventure. I've decided to do some writing to document my post-Autocode journey - if you're interested in reading more, follow me here!

So why did I decide to become a freelance developer? After making the decision to leave Autocode and looking back at my time there, I realized I was quite interested in seeing how other organizations function - how they're structured and managed, the problems they're solving (both technical and non-technical!), and how they overcome adversity. I also wanted to explore different product areas and meet interesting, passionate people. Freelancing ticked those boxes, and had the added benefit of flexibility and control over my schedule after years of constant late-night product launches and long on-call stretches.

It sounded great in theory, but I quickly found that getting started was more complex than just finding a client, writing some code, and cashing a check.

My first thought was to simply Google how to start consulting. Here's what I saw:

The results included pages of clickbait-y, generic content marketing articles published by companies looking for SEO, as well as an intimidating dropdown that said I should expect getting started to cost $10k-50k (it's much, much less!). The actual details of getting started as a freelancer - like how to incorporate, tax implications, how to set up a contract - proved scarce. And that's really too bad, since freelancing opens up opportunities for both full-time work and side-hustles.

Here's how I cut through all the noise, incorporated my business, and onboarded my first paying client!

Sidebar: I am not a lawyer or an accountant - don't take my experience as gospel and do your own research!

Deciding on a Corporate Structure

Confused by all the information online, I asked my cousin, who had done some freelancing in the past, for some advice on corporate structure. She replied, "Oh, you can just start out by using your Social Security Number!"

Wait, really? Sure, otherwise every part-time Uber and Lyft driver would need to incorporate! This seemed like the easiest approach, but I decided on an LLC for a few reasons (in no specific order of importance):

The corporation is treated as a separate entity from me. In an unlikely scenario where a client sues, my liability is limited in most cases (as the name Limited Liability Corporation implies!). I probably won't lose my house.
It gives the impression of professionalism to clients.
I thought it'd be cool.

There are more complicated structures as well, but since the business would just be me an LLC seemed like a good balance between ease of management and legal protection in the worst case. Single-member LLCs can also report income on the owner's tax return, so I still only need to file one tax return.

Incorporating an LLC

Note that your experience may vary depending on the state - I filed in California.

After deciding on an LLC, my next step was legally incorporating. As a California resident, I was unsure of the tax implications if I incorporated in a different state, so I chose to incorporate in California. The downside to this is that California has a very expensive Annual Tax on LLCs - $800 even if you make no money, and an additional tax if you make more than $250,000 a year!

The good news is that the $800 component of the first year tax is being waived for businesses that incorporate before January 1st, 2024.

Side note: I probably made a mistake here - I forgot about Stripe Atlas! They charge $500 to incorporate a business in Delaware (which has the most business-friendly laws for some reason), and $100 a year for management after. It's a bargain compared to fees in California, especially since they give Stripe credits that you can use to avoid payment processing fees when invoicing clients! There's a good chance I'll spin down my current LLC before the $800 tax kicks in and redo things in Delaware.

This process is run by the California Secretary of State's office. They have a surprisingly decent website, and all the filing can be done online.

There's a section where it'll asked if I was a professional service provider, but this only applies to professions that require a license, and writing software does not (yay?). The process required information like my Social Security Number, a business name, and a business address (like most freelancers, I don't have an office, so I used my current home address) and pay a small fee. A few days later my application was accepted, I had my Articles of Incorporation, and Remora Software, LLC was born!

After incorporation, I had to file a "Statement of Information" within 90 days of incorporation. You can do this from the Secretary of State's website, and it's required every two years afterward.

Opening a Business Checking Account

In software, there's a very important design concept called separation of concerns. it turns out, the same applies to LLCs. It becomes much easier to maintain a company's separate personhood and liability protection if all business income and expenses are separated from personal ones, so this is a must.

Plus, you'll feel like a boss when the bank sends a card with your company name on it.

Opening a business bank account requires an EIN (employer identification number). The IRS assigns EINs, and again, I was pleasantly surprised with how easy the process was. Everything is online, and I had an EIN a few minutes after applying using information from my LLC's Articles of Incorporation.

You can then transfer money from your business account to your personal account via owner's draw as often as you'd like - your checking account will act as a record of how much revenue your business is making for tax purposes.

I opened an account with Chase as I had used them in the past and they were running a $700 promotion for new accounts that meet certain requirements, but there are plenty of good options out there.

Setting Up a Contract

I was fortunate enough to be referred to a small startup interested in hiring someone with my skillset. In retrospect, I could have negotiated a much higher rate, but I was eager to get my first client and get a win on the board.

The next step was putting together a contract and figuring out how to get paid. I looked around for solutions and eventually found Honeybook, a client management platform. The week-long free trial and $1/month introductory rate drew me in, and I found it well-suited for my needs.

I used Honeybook's provided standard contract template, which I trusted because they've raised almost half a billion dollars. It conveniently included an invoicing page, payment processing, and automatic reminders. They charge a 1.5% fee for ACH transfers, so I may explore using Stripe, a wire transfer, or Zelle in the future, but all in all, it was a convenient and client-friendly way to get paid, though there was some difficulty verifying my payout account because that I had only incorporated a few days before.

The initial contract was for two weeks, and I structured payment so that half of the value was due up front and half at the end of the contract. The client signed and sent the money, I got to work, and it was a great feeling to have my first freelance contract in the books!

// Detect dark theme var iframe = document.getElementById('tweet-1620839324358160384-827'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1620839324358160384&theme=dark" }

Thank You!

If you've made it this far, thanks for reading! I'm not sure I'll remain a full-time freelancer forever, but incorporating was an extremely rewarding experience and opens up the potential to do part-time consulting on the side. I'd encourage more developers to give it a shot! And please let me know in the comments if anything in the article looks amiss.

If you're interested in hiring me, you can read more about me on my personal website - I specialize in cloud architecture/systems design, devops, and backend development, but started my career as a frontend engineer on Google Photos and can contribute to any part of a stack.

To stay up to date with my journey, follow me here or on Twitter @Hacubu. Happy hacking!

Build a Discord Bot in 6 Minutes With Node.js and Autocode

Jacob Lee — Thu, 22 Apr 2021 06:58:29 +0000

In this article, I'll show you how to set up a fully customizable Discord bot that you can have running in your guild in 6 minutes (or less!). I'll also give you some helpful tips you can use to further customize your bot after getting the basic example working.

We'll be using Autocode to do the heavy lifting around authenticating to the Discord API, handling incoming events, and hosting our bot's Node.js code — all for free. We've got an online editor as well, so the only other thing you'll need to get started is a Discord account!

Let's dive in!

Quickstart

Start by going to to the Discord starter app page on Autocode. The bot's source code is completely open, so if you're curious, feel free to take a look!

When you're ready, press the green Install Free button. If you haven't already, create an Autocode account, then select a name for your project.

On the next screen, you'll be prompted to link a Discord account:

Press Link, then Link New Resource in the modal that appears and follow the instructions to link a Discord app. This involves creating an app from Discord's developer portal and installing it into your guild, then pasting the app's authentication credentials into Autocode when prompted.

Finally, press Install App. And that's it! If your guild has a #general channel, you'll see a welcome message confirming installation. Mention your bot in a message by typing @BotName and your bot will respond!

How Does It Work?

Autocode listens for events from Discord using the bot credentials you supplied earlier. When it receives an event, Autocode triggers the appropriate endpoints in projects with the same bot linked.

In this specific case, Autocode triggers the functions/events/discord/bot_mention.js endpoint of the app you just installed. This endpoint contains a call to the messages.create method of the discord.channels API from Autocode's standard library, which sends the message. Here's what that code looks like:



// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});

let messageResponse = await lib.discord.channels['@0.0.6'].messages.create({
  channel_id: `${context.params.event.channel_id}`,
  content: [
    `Hey <@!${context.params.event.author.id}>! I'm a bot powered by Autocode.`,
    `You mentioned me in a message, so here I am!`
  ].join('\n'),
  embed: {
    title: 'Guild Information',
    type: 'rich',
    color: 0x00AA00, // Green color
    description: 'You could add some information here for guild members to view!',
    fields: [{
      name: 'Message Formatting',
      value: [
        'Check out this link for more details on formatting message embeds:',
        'https://discord.com/developers/docs/resources/channel#embed-object-embed-structure'
      ].join('\n')
    }, {
      name: 'Setting up Slash Commands',
      value: [
        'Check out the README for this bot on Autocode for help setting up slash commands:',
        'https://autocode.com/app/discord/basic-discord-example/'
      ].join('\n')
    }]
  },
  tts: false
});

return messageResponse;

Pretty straightforward! Autocode automatically populates and defines a context parameter containing details about the incoming event from Discord. We can access this data under context.params.event as shown above.

Slash Commands

The other endpoint in the app, functions/events/discord/command.js, sends a message containing the number of users and bots currently in the guild when slash command called /member-count is triggered:

However, before this will work, we need to register the slash command with Discord. Fortunately, this is easy with Autocode's built in Discord Slash Command Builder!

Navigate to the Discord Slash Command Builder, and link the same bot you created during the installation flow. Select the guild you installed your bot into, and then name the command member-count. Give it a description as well:

When you're ready, press Save All. Congratulations! You've created a slash command in your guild. Try it out in your guild by typing /member-count. If you've done everything correctly, you should see...

Whoops. There's one more thing we need to do!

Setting Up Privileged Intents

If you look at the code for the functions/events/discord/command.js endpoint, you'll notice that it calls the members.list method of the discord.guilds API. Discord treats this API as privileged, so to use it, you must manually grant access to your bot.

Go back to the Discord developer portal and open your bot settings. Open the Bot pane, and scroll down until you see the Privileged Gateway Intent settings:

Enable the toggle labeled Server Members Intent, then go back to your Discord server and run the command again. You should then see a proper message:

You've now fully set up the starter app! You can modify it's code to your heart's content, as well as add new endpoints for other Discord events, by reopening your project in Autocode.

Additional Tips

As you continue to build, here are some additional tips you might find useful:

Finding Your Guild Id

One way you can find your guild id (as well as ids for channels, users, and roles) is to enable Developer Mode. Open your User Settings by clicking the gear in the bottom left corner of your client, then click the Advanced menu item in the left sidebar and turn on the Developer Mode toggle:

Once enabled, exit your settings and right click the icon of the guild containing your bot in the left sidebar. The menu that appears will contain a new Copy ID item:

Select it, and the guild id will be copied to your clipboard. Right clicking users, channels, and roles will have a similar Copy ID option.

Formatting Messages

Posting simple messages to Discord is easy with the discord.channels API, and Discord even supports markdown for easy formatting. However, as messages get more complex, here are a few tips to keep in mind:

Mentioning a user or a bot in your message requires you to surround their id with brackets in a specific way. Here's an example: This message is tagging a user: <@!000000000000>.
Discord will automatically unfurl links in your messages, which you can use to your advantage to easily create useful notifications.
Adding rich embeds to your message can really help it stand out in your channel. You can use the messages in the sample app endpoints as a starting point for what's possible, and check out the full list of embed object fields in Discord's docs.
One gotcha with embeds is the color parameter — it takes a number, not a string. A convenient way to represent RGB colors as a number for this parameter is to use hexadecimal (0x00AA00 with no quotes for green instead of '#00AA00'.
tts stands for "text to speech" — setting it to true will make Discord read your message aloud to anyone with the right settings, so be careful when testing it in a guild with others!

Useful Links

Thank You!

If you have any questions, feedback, or if you just want to chat, join Autocode Discord! We've got a growing community who would be happy to help. You can get an invite under Docs > Join Discord in the topbar on autocode.com.

You can also reach out to me directly on Twitter @Hacubu. And for more Autocode updates, follow us on Twitter @AutocodeHQ. Until next time!

Build a Discord Bot in 6 Minutes With Node.js and Autocode

Jacob Lee — Thu, 22 Apr 2021 06:52:10 +0000

Let's dive in!

Quickstart

Start by navigating to the Discord starter app page on Autocode. The bot's source code is completely open, so if you're curious, feel free to take a look!

When you're ready, press the green Install Free button. If you haven't already, create an Autocode account, then select a name for your project.

On the next screen, you'll be prompted to link a Discord account:

How Does It Work?

Autocode listens for events from Discord using the bot credentials you supplied earlier. When it receives an event, Autocode triggers the appropriate endpoints in projects with the same bot linked.

// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});

/**
 * An HTTP endpoint that acts as a webhook for Discord message.create event
 * @param {object} event
 * @returns {any} result
 */
module.exports = async (event, context) => {

  let messageResponse = await lib.discord.channels['@0.0.2'].messages.create({
    channel_id: `${event.channel_id}`,
    content: [
      `Hey <@!${event.author.id}>! I'm a bot powered by Autocode.`,
      `You mentioned me in a message, so here I am!`
    ].join('\n'),
    embed: {
      title: 'Guild Information',
      type: 'rich',
      color: 0x00AA00, // Green color
      description: 'You could add some information here for guild members to view!',
      fields: [{
        name: 'Message Formatting',
        value: [
          'Check out this link for more details on formatting message embeds:',
          'https://discord.com/developers/docs/resources/channel#embed-object-embed-structure'
        ].join('\n')
      }, {
        name: 'Setting up Slash Commands',
        value: [
          'Check out the README for this bot on Autocode for help setting up slash commands:',
          'https://autocode.com/app/discord/basic-discord-example/'
        ].join('\n')
      }]
    },
    tts: false
  });

  return messageResponse;

}

Slash Commands

However, before this will work, we need to register the slash command with Discord. Discord doesn't currently have a built-in UI for this, but we can make the necessary API requests from Autocode's documentation pages to set it up.

Navigate to the create method of the discord.commands API page and fill out the parameters as shown below:

Fill out the guild_id parameter with your guild's id (see the Tips section later in the article if you need help finding it). Then, press Authenticate and Link Accounts and choose the Discord bot you linked earlier. Finally, press Run. Congratulations! You've created a slash command in your guild. Try it out in your guild by typing /member-count. If you've done everything correctly, you should see...

Whoops. There's one more thing we need to do!

Setting Up Privileged Intents

Go back to the Discord developer portal and open your bot settings. Open the Bot pane, and scroll down until you see the Privileged Gateway Intent settings:

Enable the toggle labeled Server Members Intent, then go back to your Discord server and run the command again. You should then see a proper message:

You've now fully set up the starter app! You can modify it's code to your heart's content, as well as add new endpoints for other Discord events, by reopening your project in Autocode.

Additional Tips

As you continue to build, here are some additional tips you might find useful:

Finding Your Guild Id

Once enabled, exit your settings and right click the icon of the guild containing your bot in the left sidebar. The menu that appears will contain a new Copy ID item:

Select it, and the guild id will be copied to your clipboard. Right clicking users, channels, and roles will have a similar Copy ID option.

Formatting Messages

Mentioning a user or a bot in your message requires you to surround their id with brackets in a specific way. Here's an example: This message is tagging a user: <@!000000000000>.
Discord will automatically unfurl links in your messages, which you can use to your advantage to easily create useful notifications.
Adding rich embeds to your message can really help it stand out in your channel. You can use the messages in the sample app endpoints as a starting point for what's possible, and check out the full list of embed object fields in Discord's docs.
One gotcha with embeds is the color parameter — it takes a number, not a string. A convenient way to represent RGB colors as a number for this parameter is to use hexadecimal (0x00AA00 with no quotes for green instead of '#00AA00'.
tts stands for "text to speech" — setting it to true will make Discord read your message aloud to anyone with the right settings, so be careful when testing it in a guild with others!

Useful Links

Thank You!

You can also reach out to me directly on Twitter @Hacubu. And for more Autocode updates, follow us on Twitter @AutocodeHQ. Until next time!

An Introduction to Scraping (Almost) Anything With Puppeteer and Node.js

Jacob Lee — Tue, 26 Jan 2021 07:46:39 +0000

Despite the macabre name, headless browsers aren't scary (at least, to most people). They're similar to standard web browsers, but are controlled through code instead of with a mouse and keyboard. You can do almost anything with a headless browser on a page that you can do with a normal web browser, including submit forms, wait for asynchronous JavaScript, and set cookies. When used in combination with modern cloud platforms, it's easier than ever to create automated scrapers.

In this article, I'll walk you through a few web scraping tricks and examples using Puppeteer, a headless browser based on Chromium that has become an industry standard, and Node.js. We'll also use Autocode to easily run and iterate on our scraper code.

All you need to get started is a free Autocode account. Let's dive in!

TL;DR (30s)

Getting a basic scraper working is simple. Start by going to https://autocode.com and creating or logging into your account. Create a new project, and paste the following code into the editor:

// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});
const puppeteer = require('autocode-puppeteer');

let browser = await puppeteer.launch();
let page = await browser.newPage();
await page.goto('https://www.youtube.com/'); // Any URL you'd like
let title = await page.title();
await browser.close();
return title;

Autocode will automatically add required dependencies, so all you need to do now is, press the Save or Run button to push your code live. And that's it! You're now scraping the title of the page (what you'd see in the tab bar when you open the site in Chrome) with Puppeteer.

Breaking It Down

Here's what happens in the above code:

We require a variant of Puppeteer configured to work in the Autocode environment. Note that the default Puppeteer package will not work due to dependency size constraints.
We prepare Puppeteer by launching it and opening a new page.
We navigate to the desired page with await page.goto().
Once on the right page, we use the page.title() method to scrape the page title.
We close the browser instance when we're finished.

This flow is analogous to opening Chrome on your computer, typing https://youtube.com/ in the navbar, checking the title of the page, and finally closing the browser. We'll follow this general pattern when web scraping with Puppeteer.

We're just scratching the surface of what's possible, but there's something important to emphasize first.

With Great Power...

Many websites disallow scraping, and use tools like reCAPTCHA or contain a robots.txt file containing guidelines for scrapers and other automated tools. You should always check and respect site rules before scraping.

Scraping Data From Page Content

Now that you've got the basics down, let's explore how to scrape more useful data from a page. One key piece of functionality Puppeteer provides is the ability to query a page for HTML elements with CSS selectors. For example, Puppeteer's page.$$eval() method takes a selector and allows you to run code in the context of the browser on all elements matching the selector.

Here's what it looks like in action:

// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});
const puppeteer = require('autocode-puppeteer');

let browser = await puppeteer.launch();
let page = await browser.newPage();
await page.goto('https://www.youtube.com/');
let videoData = await page.$$eval('a#video-title-link', (titleLinkEls) => {
  return titleLinkEls.map((titleLinkEl) => {
    return {
      title: titleLinkEl.getAttribute('title'),
      link: 'https://youtube.com' + titleLinkEl.getAttribute('href')
    };
  });
});
await browser.close();
return videoData;

Once we've loaded YouTube in a page, we can use the page.$$eval() function to query for each video link on the front page and return the name of the video and a link to it. We've essentially created a custom trending video API!

Crafting Selectors With Chrome Devtools

One tricky part about creating scrapers is figuring out what elements of the page contain relevant data - after all, it's not immediately obvious that a#video-title-link matches all the video links on YouTube. One convenient tool for this is the inspector in Chrome's devtools.

You can open the inspector under View > Developer > Inspect Elements in the topbar, or by using the keyboard shortcut CMD + Option + C:

Once you have the inspector open, you can mouse over elements on the page and see them highlighted. Clicking one will show the element in the devtools window, including its attributes and position in the DOM.

With the inspector, you should be able to figure out a way to reference the correct elements and scrape the data you want.

Debugging With Screenshots

Puppeteer allows you to take screenshots of pages using the page.screenshot() method. This can be especially useful to see the current page state when composing flows that involve interaction with page.click() or page.select(), much like a console.log statement.

For example, let's say you want to build a flow that involves clicking on the first video on the front page of YouTube to scrape its like and dislike count. You might try something like this:

// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});
const puppeteer = require('autocode-puppeteer');

/**
* An HTTP endpoint that acts as a webhook for HTTP(S) request event
* @returns {object.http} result
*/
module.exports = async (context) => {

  let browser = await puppeteer.launch();
  let page = await browser.newPage();
  await page.goto('https://www.youtube.com/');
  await page.click('a#video-title-link');
  let screenshot = await page.screenshot();
  await browser.close();
  return {
    statusCode: 200,
    body: screenshot,
    headers: {
      'Content-Type': 'image/png'
    }
  };

}

Note: To make the screenshot return as an image rather than just binary data, we must pass back the proper Content-Type header. The above code is an example of how to do this in Autocode with the object.http return type. To return other types of data, you'll need to remove the return type or change the return type to match the type of data you're returning. For more information, you can read about the FunctionScript specification Autocode uses for parameters and return types.

If you were to try running the above code a few times, you would notice that the screenshot is either not changing from the main page, or that you'd see something like this:

This would clue you in to the fact that you need to wait for parts of the page to load using something like page.waitForSelector():

// authenticates you with the API standard library
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});
const puppeteer = require('autocode-puppeteer');

/**
* An HTTP endpoint that acts as a webhook for HTTP(S) request event
* @returns {object.http} result
*/
module.exports = async (context) => {

  let browser = await puppeteer.launch();
  let page = await browser.newPage();
  await page.goto('https://www.youtube.com/');
  await page.click('a#video-title-link');
  await page.waitForSelector('#top-level-buttons');
  let screenshot = await page.screenshot();
  await browser.close();
  return {
    statusCode: 200,
    body: screenshot,
    headers: {
      'Content-Type': 'image/png'
    }
  };

}

Other use-cases include combining screenshots with the Autocode scheduler, a file hosting platform like Box, and a package like pixelmatch to create daily screenshot diffs for regression testing.

Other Tips

Tuning Your Scraper

The web is a wild place, and new standards are appearing all the time. If a technique doesn't work the way you expect on a first attempt, you may need to try a different method, like waiting for a selector or even just waiting for a set amount of time rather than a DOMContentLoaded event, or using a different selector. Luckily, Puppeteer has plenty of options!

Increasing Your Autocode Timeout

Complicated flows that involve visiting multiple pages or flows that visit slow pages may require more than the default 10 second timeout on Autocode. If your scraper is consistently timing out, you can try increasing the timeout under Advanced Settings in the bottom left corner of the Autocode editor.

Free accounts can increase their timeouts to up to 30 seconds, while Professional Tier accounts can go up to 2 minutes.

Simulating Logged-In States (Advanced)

Many websites show different data based on whether or not the current viewer is logged in. To simulate this state in Puppeteer, you might be able to use the page.setCookie() method using cookies obtained from the Chrome devtools. You can also attempt to use and submit a site's login form directly with your username and password, but many sites use CAPTCHAs to prevent this.

Be careful not to set cookies under the wrong domain if you try this!

Limitations

Because web scrapers usually rely on the structure of the DOM for pages they visit, frontend updates can break scrapers. For this reason, it's often better to use a supported API if it is available to you.

If your desired data follows a specific format and the page you're scraping obfuscates their CSS attributes or changes them frequently, Puppeteer does have a page.content() method that returns the current DOM structure of the page as a string. You can then use a regex or some other method to extract your data.

Thank You!

Puppeteer is an extremely powerful tool, and if you're interested in a deeper dive, I would encourage you to check out the official documentation.

If you have any questions or feedback, a good place to get in touch with me is the Autocode community Slack channel. You can get an invite under Docs > Ask for Help in Slack in the topbar on autocode.com. You can also reach out to me on Twitter @Hacubu.

If you want to stay up to date on the latest from Autocode, I would also encourage you to follow @AutocodeHQ. Happy hacking!

How to Use Airtable as a Production Database (Analyzing Airtable Performance)

Jacob Lee — Fri, 11 Dec 2020 07:56:03 +0000

I've been an Airtable user for around three years now, and over that span I've recommended it to more people than any other SaaS product. It "Just Works™" — while it has its limitations and doesn't provide all the flexibility of a relational database, the sheer quality of the UX often makes up for any shortcomings. Like Google Sheets, Airtable particularly shines as a backend for apps where non-technical team members might need to examine or edit data, and the structure it provides through features like types, access control, views, and computed fields make working with it pleasant.

In this article, we'll go over:

How to use Airtable as a database with some pseudo-join capabilities
How to set up an HTTP-accessible API for your Airtable base that you can send requests to from a frontend
Some trade-offs to consider when selecting Airtable for your project (like how the Airtable API scales)

# Returns all records from the "My Books" table with genre "Fantasy", case-sensitive
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/genre/is/?query=Fantasy'

[
  {
    "Id": 1,
    "Title": "Rhythm of War",
    "Genre": "Fantasy",
    "Published On": "2020-11-17",
    "Author Id": ["rec00000000000000"],
    "Author": ["Brandon Sanderson"]
  },
  {
    "Id": 5,
    "Title": "The Doors of Stone",
    "Genre": "Fantasy",
    "Published On": null,
    "Author Id": ["rec00000000000000"],
    "Author": ["Patrick Rothfuss"]
  }
]

To host the sample app and handle authentication to the Airtable API, we'll use Autocode, a Node.js app platform and editor with built-in autocomplete. Both Airtable and Autocode are free to start, so let's dive in!

TL;DR (30s)

First, create your own copy of the demo Airtable base by clicking here and following the prompt.

Once you've done that, click here to open the Airtable cookbook in Autocode, then press the green button to install the cookbook to your Autocode account. When asked to link an Airtable base, supply your API key and select the base you just cloned.

After that, you're live! You can try hitting some of the endpoints from your browser or cURL right away (the URLs will look something like https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/genre/is/?query=Fantasy. You can also check out the README for the Airtable cookbook for more concrete examples.

Limitations

Now that we've got a basic example set up, let's talk about some things you should consider when choosing between Airtable and other solutions.

Performance

To gauge performance, I ran three experiments: single record retrieval, full table retrieval, and a 50,000 record table query test.

Single Record Retrieval

Methodology

For this experiment, I created tables containing various numbers of records with a with an autonumber field called Key and a text field called Value. I populated each Value with a single character. I then changed an arbitrary record's Value field to the string "testing", then queried for records matching that Value using the airtable.query.

Results

The Airtable API performed spectacularly on this test, retrieving a single record in about the same time for table sizes from 10 to 50,000 records. There was a small increase in query time as the number of records in the table grew, but overall stayed fairly consistent. This result suggests that the Airtable API is well optimized for queries that return a small number of results.

Full Table Retrieval

Methodology

For this experiment, I used the same tables as the previous experiment, but left the where parameter blank in my airtable.query API call to select for all records in the table.

Results

The Airtable API performed worse on this test, crossing the 2s mark after retrieving 500 records and quickly becoming impractically slow after ~1,000 records.

50,000 Record Table Queries

Methodology

For this experiment, I wanted to see if querying with parameters would perform better than retrieving an entire table. Starting with the same table structure, I added a third field that I populated with the following (hacky) formula to simulate an even distribution of numbers (Airtable does not have a built-in random function):

MOD((MOD(VALUE(RECORD_ID()), 50000) + 1) * {Key}, 50000)

{Key} is the autonumbered field we defined earlier. RECORD_ID(), a function returning a random looking id that Airtable internally assigns each record, did not contain enough entropy on its own to evenly distribute numbers, and often evaluated to zero when evaluated as a number using the VALUE() Airtable formula operator, hence the hack of adding 1.

In practice, this formula returned values spread out sufficiently to ensure a roughly even distribution from 0 to 50,000 for each record, though using the generated values as random numbers for any important application is not recommended. I then queried the base for records where this third computed field was below a certain threshold to simulate a query matching a given number of "randomly selected" records.

Sidenote: For more on Airtable formula operators, check out their official reference here. The endpoints in the sample app all use KeyQL parameters to make queries, but if you'd prefer to query Airtable using formulas, you can use this endpoint on Autocode.

Results

The results for this test were similar to the full table retrieval test, showing that performance correlated mostly with the number of returned records.

Conclusions

The Airtable API's performance scales, surprisingly, with the number of records returned in a given query rather than the size of the table. Even at table sizes at plan limits, the Airtable API performs well for queries that return small numbers of records. Unfortunately, Airtable formulas don't have a built-in concept of pagination, and the KeyQL parameters translate to formulas under the hood, so the KeyQL limit parameter does not get around this issue.

Adding extra fields to the records in the table seemed to have a negligible impact on performance.

Capacity and Throughput

Airtable's top non-enterprise plan is $24 a month and has an upper limit of 50,000 records per base. For free plan users, this limit is 1,200 records per base.

Even for projects where you expect to exceed these limits, it may be better to avoid premature optimization and choose Airtable for ease-of-use, migrating if and when scale becomes a problem.

Airtable sets a rate limit of 5 requests per second per base.

Relationships Between Tables

Linked Record Fields

Airtable allows you to associate records in one table with records in another through a special type of field called a linked record field. In the example base from the TL;DR above, the Author Id field on the My Books table is an example of a linked record field, associating records from the My Books table with those from the Authors table.

If you're familiar with frameworks like Ruby on Rails or Django, you'll immediately think of a linked record as creating a relationship between the two tables. And it does, with the following caveats:

You can't select the field you're joining on. It must be the primary field for the table you're joining to.
When querying via API, all relationships are represented as many-to-many and are returned as arrays.
You can't make queries that use joins.

#3 is a particular bummer if you were hoping to use Airtable as a drop-in replacement for something like Postgres. However, there are some neat tricks we can use!

Lookup Fields

You may have also noticed in the example base that there is a field called Author in the My Books table. Author is another special type of field called a lookup field which, as the name implies, allows a record in one table to lookup data from another field. In this case, the Author field on records in My Books contains the value of the Name field for the proper linked record in the Authors table.

Those familiar with database theory will red-flag this as denormalization of data — essentially caching copies of a field in two places. This is usually difficult to deal with, but Airtable populates and updates lookup fields automatically, handling the complexity for you.

Airtable returns the value of lookup fields along with other types of fields on a record. You can use the airtable.query API to query records by the value of a lookup field like this:

let bookQueryResult = await lib.airtable.query['@1.0.0'].select({
  table: 'My Books',
  where: [{
    'Author__contains': query
  }]
});

Note: As previously mentioned, Airtable treats lookup fields as arrays, even if they only contain one value. Therefore, you should prefer contains or icontains when querying them with KeyQL.

Through smart use of lookup fields, you can achieve results similar to basic joins in a traditional relational database. Of course, the more lookup fields you add, the more complex your base will become. If you find yourself needing to do this too often, it might make sense to make multiple queries for data from different tables or to choose a different database solution.

How It Works

Autocode automatically handles authentication between your app and your Airtable account. When you link an Airtable base, Autocode associates your provided Airtable key and the linked base to your app's token (see the const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN}) line at the top of all the endpoints in the sample cookbook). When you call an Airtable API with await lib.airtable, Autocode makes the appropriate credentials available to the API, allowing it act as a proxy and use your credentials to call Airtable on your behalf.

You'll notice each endpoint contains Node.js code that calls a method from the airtable.query API.

let bookQueryResult = await lib.airtable.query['@1.0.0'].select({
  table: 'My Books',
  where: [{
    'Genre__is': query
  }]
});

Though there is an Airtable endpoint on Autocode that allows you to use Airtable formulas directly, the API calls in the sample project use the KeyQL query language. Check it out if you're interested in seeing more ways you can use KeyQL, including other available operators, and how it converts parameters to Airtable formulas.

Calling Endpoints

Because these API endpoints are accessible via HTTP, so you can make calls to them via fetch, cURL, or whatever other HTTP client you prefer. You can use your web browser directly:

And you can even use the same lib-node package that the endpoints use to call the Airtable APIs:

Your endpoints will respond to either GET or POST requests. Parameters are parsed from the querystring for GET requests and the request body for POST requests. Each endpoint has default parameters set for the sake of clarity.

Endpoints

For a full explanation of each endpoint in the example cookbook, along with usage examples, check out the Autocode Airtable cookbook page.

Thank You!

If you have any questions or feedback, the best thing to do is to join the Autocode community Slack channel. You can get an invite from the question mark tab in the top bar on autocode.com. You can also reach out to me directly on Twitter @Hacubu.

If you want to stay up to date on the latest from Autocode, you can follow @AutocodeHQ. Happy hacking!

How to Use Google Sheets as a Database (Responsibly)

Jacob Lee — Wed, 07 Oct 2020 07:00:16 +0000

While database technology and other tools have come a long way, it's still tough to beat the humble spreadsheet's versatility and intuitiveness. While using them for sensitive, critical applications like storing COVID-19 patient data is ill-advised, the fact that everyone knows how to use a spreadsheet means they're great for smaller cross-functional projects where a non-developer might need to examine or edit data.

In this guide, I'll show you how to use Google Sheets as a database, complete with an API interface accessible over HTTP. We'll use Autocode, a Node.js API development platform and editor with built-in autocomplete, to deploy a simple app and handle Google's authentication process. I'll also explore the limitations of Google Sheets, including scalability, and where it makes sense to start looking at more complex alternatives.

# Returns all people in the database whose names start 
# with "bil", case-insensitive
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

[
  {
    "Name": "Bilbo Baggins",
    "Job": "Burglar",
    "Fictional": "TRUE",
    "Born On": "9/21/1937",
    "Updated At": ""
  },
  {
    "Name": "Bill Nye",
    "Job": "Scientist",
    "Fictional": "FALSE",
    "Born On": "11/27/1955",
    "Updated At": ""
  },
  {
    "Name": "billie eilish",
    "Job": "Artist",
    "Fictional": "FALSE",
    "Born On": "12/18/2001",
    "Updated At": ""
  }
]

You don't need anything other than a Google account and a free Autocode account to get started. Let's go!

TL;DR (30s)

First, you'll need to clone your own copy of the template Google Sheet by clicking here, then pressing Use Template in the top right. This will add the sample spreadsheet to your personal Google account.

Once you've done that, click here to open the starter app in Autocode. Poke around the source code if you'd like, then install the app to your Autocode account by pressing the green button. When prompted to link a Google Sheet, follow the instructions in the flow to link your Google account, then select the spreadsheet you just cloned.

After that, your app should be ready to go! Try accessing a few of the endpoints via their URLs and see what is returned/what happens to your new spreadsheet database. You can check out the Endpoints section below for example calls.

Limitations

That tl;dr was easy, right? So why isn't everything built on Google Sheets?

While a backend that takes 30 seconds to set up and that everyone can interact with is extremely appealing, there are some obvious limitations. A spreadsheet as a database as described above doesn't naturally support multiple tables or relationships between rows. There's also no concept of enforcing types for a given column, transactions, built-in backups, or encryption, so sensitive/critical data (like COVID-19 patient data) is probably best stored elsewhere.

In terms of scalability, Google Sheets has a hard limit of 5,000,000 cells (including blank cells). When I tried to verify this by creating a spreadsheet with that many values, however, I encountered significant issues in performance before that threshold:

Mass-operations like pasting a large number of cells slowed, then began to fail at around 1m cells. Navigation was generally sluggish.

My experiments around making API calls yielded similar results. Query speed seemed to scale linearly with the number of cells:

Queries became impractically slow around the 500,000 cell mark, but were still below 2 seconds for a 100,000 cell query. Therefore, if you anticipate a dataset larger than a few hundred thousand cells, it would probably be smart to choose a more scalable option.

How It Works

When you link your cloned Google Sheet to your app and install it to your account, Autocode automatically handles authentication between your app and your Google account using your app's token (see the const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN}) line at the top of all the endpoints).

For the actual queries, each endpoint contains Node.js code that calls a method from the googlesheets.query API. These APIs take a parameter called range formatted in A1 notation that corresponds to the part of the spreadsheet the API call should consider as part of the database.

let queryResult = await lib.googlesheets.query['@0.3.0'].select({
  range: `A:E`,
  bounds: 'FULL_RANGE',
  where: [{
    'Name__istartswith': query
  }]
});

A range value of A:E is essentially shorthand for "use all rows in columns A through E in the spreadsheet as my database". The query interprets the first row of each column in that range as the field name of the values in that column. Given the template you cloned, the above query will check all values in the A column (named Name) for rows with a value matching the query.

These API calls use the KeyQL query language. If you're interested in a deep dive, you can check it out for more examples.

Calling Endpoints

As previously mentioned, these endpoints are accessible via HTTP, so you can make calls to them via fetch, cURL, or whatever other HTTP client you prefer. You can use your web browser directly:

And you can even use the same lib-node Node package that the endpoints use to call the Google Sheets APIs:

Endpoints

functions/select/job/contains.js

This endpoint is an example of a contains KeyQL query. It looks for rows in the linked Google Sheet where the Job field contains a substring (case-sensitive) matching the parameter query. From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/job/contains/?query=ist'

[
  {
    "Job": "Mistborn",
    "Born On": "2006-07-17",
    "Fictional": "TRUE",
    "Name": "Vin Venture",
    "Updated At": ""
  },
  {
    "Job": "Scientist",
    "Born On": "1955-11-27",
    "Name": "Bill Nye",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001-12-18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  }
]

functions/select/born_on/date_gt.js

This endpoint is an example of a date_gt KeyQL query. It looks for rows in the linked Google Sheet where the Born On field is after the query parameter, formatted as 2000/01/01. From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/born_on/date_gt/?query=2000/01/01'

[
  {
    "Job": "Mistborn",
    "Born On": "2006/07/17",
    "Fictional": "TRUE",
    "Name": "Vin Venture",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001/12/18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  }
]

functions/select/name/istartswith.js

This endpoint is an example of a istartswith KeyQL query. It looks for rows in the linked Google Sheet where the Name field starts with the query parameter (case-insensitive). From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

[
  {
    "Job": "Scientist",
    "Born On": "1955-11-27",
    "Name": "Bill Nye",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001-12-18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Burglar",
    "Born On": "1937-09-21",
    "Fictional": "TRUE",
    "Name": "Bilbo Baggins",
    "Updated At": ""
  }
]

functions/insert.js

This endpoint is an example of an insert query. It passes the input parameters into the fieldsets parameter of the googlesheets.query.insert
API. For example, to add Bill Gates to your spreadsheet, you could make the following request (all parameters are lower-case):

$ curl --request POST \
  --header "Content-Type: application/json" \
  --data '{"name":"Bill Gates","job":"CEO","fictional":false,"bornOn":"10/28/1955"}' \
  --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/insert/'

Note: Autocode APIs do not respond exclusively one HTTP method over another, and instead rely on descriptive naming to avoid confusion over functionality.

functions/update.js

This endpoint is an example of an update query. It sets the Updated At field of people whose names exactly match the name parameter, and updates other fields based on input parameters. It uses the googlesheets.query.update API.
For example, to update the Job field of Bilbo Baggins to Ring Bearer in your spreadsheet, you could make the following request (all parameters are lower-case):

$ curl --request POST \
  --header "Content-Type: application/json" \
  --data '{"name":"Bilbo Baggins","job":"Ring Bearer"}' \
  --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/update/'

Note: This can affect multiple rows if more than one row matches the query conditions.

functions/delete.js

This endpoint is an example of a delete query. It removes rows
of people whose names exactly match the name parameter. It uses the googlesheets.query.delete API. For example, to remove Bilbo Baggins from your spreadsheet, you could make the following request:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/delete/?name=Bilbo%20Baggins'

Note: This can affect multiple rows if more than one row matches the query conditions.

Thank You!

If you have any questions or feedback, the best thing to do is to join the Autocode community Slack channel. You can get an invite from the Community tab in the top bar on the website. You can also reach out to me directly on Twitter @Hacubu.

If you want to stay up to date on the latest from Autocode, you can follow @AutocodeHQ. Happy hacking!