Migrate an E-commerce backend from Airtable to PostgreSQL

Migrate an E-commerce backend from Airtable to PostgreSQL: A Quick Guide

Airtable offers an impressive blend of spreadsheet ease with database functionality. However, as you near its 50,000 record limit, you might find yourself looking for an alternative. One powerful choice is PostgreSQL, a relational database system that effortlessly manages millions of rows. Let’s explore how to make the transition.

The Airtable Structure

Airtable uses tables and linked records, making it easy to set up and modify relationships. This way, you can intuitively understand how your data connects. In this article we'll cover how to translate the following Airtable schema to a Postgres database (or really, any RDBMS):

Suppose our base was a simple e-commerce backend, with the following tables and fields:

Customers

• ID: A simple text to identify each customer.
• Email Address: The primary communication channel.
• First Name: The customer's first name.
• Last Name: The customer's last name.
• Orders: Links to orders placed by the customer.
• Address: Saved shipping address.
• Totals: Summarized total dollar amount from their orders.

Orders

• ID: A unique identifier for each order.
• Customer ID: Ties the order back to the purchasing customer.
• Date: The date the order was placed.
• Order Items: Links to the detailed items within the order.

Order Items

• ID: Unique identifier for each item.
• Order ID: Associates the item with an order.
• Product ID: Refers to the product details.
• Quantity: Quantity of this particular product in the order.
• Price Per Unit: Cost for a single unit of the product.
• Price Total: Calculated total price for the product based on quantity.

Products

• ID: Unique identifier for each product.
• Name: Product's name.
• Price: Listed price of the product.
• Order Items: Links to orders containing this product.

Crafting a Schema in PostgreSQL

In PostgreSQL, creating an equivalent schema means defining each table and its relationships. Since not all Airtable fields are directly mapable to Postgres columns, we'll have to make decisions about what to remove. For example, in the customers table, I didn't include the calculated totals field.

customers

CREATE TABLE Customers (
    id TEXT,
    email_address TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    address TEXT NOT NULL,
    PRIMARY KEY ("id")
);

orders

CREATE TABLE Orders (
    id TEXT,
    customer_id TEXT NOT NULL,
    date DATE,
    CONSTRAINT "customer_places_order_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id"),
    PRIMARY KEY ("id")
);

order_items

CREATE TABLE order_items (
    id TEXT,
    order_id TEXT NOT NULL,
    product_id TEXT NOT NULL,
    quantity INT NOT NULL,
    price_per_unit int8 NOT NULL,
    CONSTRAINT "order_contains_order_item_fk" FOREIGN KEY ("order_id") REFERENCES "public"."customers"("id"),
    CONSTRAINT "product_contained_in_order_item_fk" FOREIGN KEY ("product_id") REFERENCES "public"."customers"("id"),
    PRIMARY KEY ("id")
);

products

CREATE TABLE products (
    "id" text NOT NULL,
    "name" text NOT NULL,
    "price" int8 NOT NULL,
    PRIMARY KEY ("id")
);

Understanding the E-commerce PostgreSQL Schema

Transitioning from Airtable to PostgreSQL for your e-commerce backend involves a few nuances. Let's dive into the specific choices made in the PostgreSQL schema:

1. Selecting Fields: Not all fields from Airtable have a direct counterpart in PostgreSQL. For example, in our PostgreSQL schema, the customers table doesn’t include the totals field from Airtable. This is due to differences in how each system is designed and what they prioritize. If we want to include something like a roll-up field in Airtable, it's better to perform that sort of aggregation inside a view.

2. Prices as Integers (int8): Instead of using decimals for prices (like $10.99), we've chosen to represent them as whole numbers in cents (1099). Why? This makes arithmetic operations simpler and helps sidestep potential rounding errors. Imagine you decide to offer a 3% discount. With decimals, you might end up with awkward prices like $10.6597. By converting everything to its smallest unit (in this case, cents), we can use whole numbers, or integers.

3. The Role of price_per_unit: The order_items table has a noteworthy field: price_per_unit. At first glance, it might seem redundant since products have their prices. But consider the consequences if this field didn't exist. Since each product has one active price and the order_items table connects orders to products, changing a product's price would inadvertently modify the total cost of all previous orders, leading to inaccuracies. To prevent this, we capture the exact price charged at the time of the sale.

4. Relationships & Foreign Keys: One of PostgreSQL's strengths is managing relationships between data. Our schema uses this feature extensively. For instance, the order_items table has connections (or links) to both the orders and products. These connections, known as foreign keys in database jargon, ensure our data stays consistent and meaningful.

Foreign Keys in PostgreSQL vs. Linked Records in Airtable

Both Airtable's linked records and PostgreSQL's foreign keys serve to establish and maintain relationships between data spread across different tables. However, how they go about achieving this and the nuances they bring differ. Let's dive deeper into their similarities and distinctions:

Airtable's Linked Records:

1. Intuitiveness: Airtable's visual and user-friendly interface allows users to easily create linked records by merely selecting the relevant record from another table.

2. Dynamic Interface: When viewing linked records, Airtable provides a clickable link that takes you directly to the associated record in the other table.

3. Flexibility: Linked records can be used to link multiple records between tables, acting somewhat like a many-to-many relationship.

PostgreSQL's Foreign Keys:

1. Rigidity with Benefits: Foreign keys in PostgreSQL enforce strict relational integrity. This means if you try to input data that doesn't have a corresponding record in the referenced table, PostgreSQL will throw an error, preventing data inconsistencies.

2. Explicit Definitions: Unlike Airtable's intuitive GUI, PostgreSQL requires you to explicitly define foreign key relationships with specific SQL commands. This explicitness ensures clear definitions but demands a better understanding of your data structure.

3. Cascade Operations: One of PostgreSQL’s advantages is its support for cascade operations. If a record in a primary table is deleted or updated, you can define cascading rules to automatically make corresponding changes in related tables, ensuring data integrity.

Comparing Benefits and Challenges

Airtable:

• Advantages:
  • Highly user-friendly.
  • Visual interface.
  • Dynamic schema makes it easy to adjust and change.
• Drawbacks:
  • Limited to 50,000 records.

PostgreSQL:

• Advantages:
  • Handles massive datasets.
  • Robust and has extensive features.
• Drawbacks:
  • More rigid schema. Changing structures is not straightforward.
  • Implementing formula fields, akin to Airtable, requires additional effort and might involve using functions or triggers.

For those frequently adjusting Airtable field types, the rigidity of PostgreSQL might prove challenging. However, if data volume is your primary concern, PostgreSQL provides a robust solution.

Is Partial Migration an Option?

If you're nearing Airtable's limit but aren't ready for a full migration, consider a partial move. Migrate some records to PostgreSQL to free up space in Airtable. This hybrid approach gives you the best of both worlds: the dynamic flexibility of Airtable and the vast storage capacity of PostgreSQL.

Concluding Thoughts

Switching databases is a significant move. It requires careful planning, especially when transitioning between two platforms as different as Airtable and PostgreSQL. With this guide, you have a roadmap to make informed decisions. Here's to efficient data management!