Shopify with Alopex DB

What the service does

Shopify builds and runs online stores. Products carry descriptions, images, prices, inventory, variants and tags, and inventory is tracked per variant such as size and color. The data is products, variants, inventory, orders and customers.

Where the data lives

SQL tables: products, variants, inventory, orders and customers live in tables.
JSON: attributes that vary by product go in a JSON column.
Vectors: embeddings of product descriptions and images in a vector column drive similar-product recommendations.
Columnar tables: order lines also go in a columnar table to aggregate sales by day and product.

Placing an order and reserving inventory commit in the same transaction.

Deployment

Alopex DB runs as a server that answers the storefront and admin over HTTP and gRPC. As stores grow, it scales out to an Alopex DB cluster.

Why Alopex DB fits

An online store has to get orders and inventory right, then suggest what sells. Alopex DB handles transactions, vectors and columnar aggregation in one database.

See it in code

Products, inventory and orders are kept; an order and its inventory allocation commit in one transaction, and similar products are recommended by vector. Just the core.

import alopex
import numpy as np

db = alopex.Database.open("shopify.alopex")

db.execute_sql("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        title TEXT,
        price INTEGER,
        embedding VECTOR(4)
    )
""")
db.execute_sql("CREATE TABLE IF NOT EXISTS inventory (product_id INTEGER, stock INTEGER)")
db.execute_sql("CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER)")

products = [
    (1, "青いTシャツ", 2000, [0.9, 0.1, 0.0, 0.0]),
    (2, "赤いTシャツ", 2000, [0.85, 0.15, 0.0, 0.0]),
    (3, "革の財布", 8000, [0.0, 0.0, 0.9, 0.1]),
]
for pid, title, price, emb in products:
    db.execute_sql("INSERT INTO products (id, title, price, embedding) VALUES (?, ?, ?, ?)", [pid, title, price, np.array(emb, dtype=np.float32)])
    db.execute_sql("INSERT INTO inventory (product_id, stock) VALUES (?, ?)", [pid, 10])

# 注文の確定と在庫の引き当てを、同じトランザクションでコミットする
tx = db.begin(alopex.TxnMode.READ_WRITE)
try:
    tx.execute_sql("INSERT INTO orders (id, product_id, qty) VALUES (?, ?, ?)", [1, 1, 3])
    tx.execute_sql("UPDATE inventory SET stock = stock - ? WHERE product_id = ?", [3, 1])
    tx.commit()
except Exception:
    tx.rollback()
    raise

print("在庫:", db.execute_sql("SELECT stock FROM inventory WHERE product_id = ?", [1])[0]["stock"])

# 「青いTシャツ」に似た商品を、ベクトルでおすすめする
query = np.array([0.9, 0.1, 0.0, 0.0], dtype=np.float32)
rows = db.execute_sql("""
    SELECT title, vector_similarity(embedding, ?, 'cosine') AS score
    FROM products
    WHERE id <> ?
    ORDER BY score DESC
    LIMIT 1
""", [query, 1])
for row in rows:
    print("おすすめ:", round(row["score"], 3), row["title"])

The order and the inventory land in one transaction, and similar-product recommendations come from the same SQL.

We recommend Alopex DB as the base for a store, with transactions, vectors and columnar aggregation in one database. You can start from the quickstart.