
Build a shopping cart app with ScyllaDB– and learn how to use ScyllaDB’s Change Data Capture (CDC) feature to query and export the history of all changes made to the tables.
This blog post showcases one of ScyllaDB’s sample applications: a shopping cart app. The project uses FastAPI as the backend framework and ScyllaDB as the database. By cloning the repository and running the application, you can explore an example of an API server built on top of ScyllaDB for a CRUD app. Additionally, you’ll see how to use ScyllaDB’s Change Data Capture (CDC) feature to query and export the history of all changes made to the tables.
What’s inside the shopping cart sample app?
The application has two components: an API server and a database.
API server: Python + FastAPI
The backend is built with Python and FastAPI, a modern Python web framework known for its speed and ease of use. FastAPI ensures that you have a framework that can deliver relatively high performance if used with the right database. At the same time, due to its exceptional developer experience, you can easily understand the code of the project and how it works even if you’ve never used it before.
The application exposes multiple API endpoints to perform essential operations like:
- Adding products to the cart
- Removing products from the cart
- Uploading new products
- Updating product information (e.g. price)
Database: ScyllaDB
At the core of this application is ScyllaDB, a low-latency NoSQL database that provides predictable performance. ScyllaDB excels in handling large volumes of data with single-digit millisecond latency, making it ideal for large-scale real-time applications.
ScyllaDB acts as the foundation for a high-performance low-latency app. Moreover, it has additional capabilities that can help you maintain low p99 latency as well as analyze user behavior. ScyllaDB’s CDC feature tracks changes in the database and you can query historical operations. For e-commerce applications, this means you can capture insights into user behavior:
- What products are being added or removed from the cart and when?
- How do users interact with the cart?
- What does a typical journey look like for a user who actually buys something?
These and other insights are invaluable for personalizing the user experience, optimizing the buying journey, and increasing conversion rates.
Using ScyllaDB for an ecommerce application
As studies have shown, low latency is critical for achieving high conversion rates and delivering a smooth user experience. For instance, shopping cart operations – such as adding, updating, and retrieving products – require high performance to prevent cart abandonment.
Data modeling, being the foundation for high-performance web applications, must remain a top priority. So let’s start with the process of creating a performant data model.
Design a Shopping Cart data model
We emphasize a practical “query-first” approach to NoSQL data modeling: start with your application’s queries, then design your schema around them. This method ensures your data model is optimized for your specific use cases and the database can provide a reliable and single-digit p99 latency at any scale.
Let’s review the specific CRUD operations and queries a typical shopping cart application performs.
Products
List, add, edit and remove products.
- GET /products?limit=?
SELECT * FROM product LIMIT {limit} - GET /products/{product_id}
SELECT * FROM product WHERE id = ? - POST /products
INSERT INTO product () values () - PUT /products/{product_id}
UPDATE product SET ? WHERE id = ? - DELETE /products/{product_id}
DELETE FROM product WHERE id = ?
Based on these requirements, you can create a table to store products. You can notice what value is often used to filter products: product id. This is a good indicator that product id should be the partition key or at least part of it.
The Product table:
Our application is simple, so a single column will suffice as the partition key. However, if your use case requires additional queries and filtering by additional columns, you can consider using a composite partition key or adding a clustering key to the table.
Cart
List, add, remove products from user’s cart.
- GET /cart/{user_id}
SELECT * FROM cart_items WHERE user_id = ? AND cart_id = ? - POST /cart/{user_id}
INSERT INTO cart() VALUES ()
Here we don’t need cart id because the user can only have one active cart at a time. (You could also build another endpoint to list past purchases by the user – that endpoint would require the cart id as well) - DELETE /cart/{user_id}
DELETE FROM cart_items WHERE user_id = ? AND cart_id = ? AND product_id = ? - POST checkout /cart/{user_id}/checkout
UPDATE cart SET is_active = false WHERE user_id = ? AND cart_id = ?
The cart-related operations contain a slightly more complicated logic behind the scenes. We have two values that we use to query by: user id and cart id. Those can be used together as composite partition keys.
Additionally, one user can have multiple carts – one they’re using right now to shop and possibly other ones that they had in the past that they already paid for. For this reason, we need to have a way to efficiently find the user’s active cart. This query requirement will be handled by a secondary index on the is_active column.
The Cart table:
Additionally, we also need to create a table which connects the Product and Cart tables. Without this table, it would be impossible to retrieve products from a cart.
The Cart_items table:
We enable Change Data Capture for this table. This feature logs all data operations performed on the table into another table, cart_items_scylla_cdc_log. Later, we can query this log to retrieve the table’s historical operations. This data can be used to analyze user behavior, such as the products users add or remove from their carts.
Final database schema:
Now that we’ve covered the data modeling aspect of the project, you can clone the repository and get started with building.
Getting started
Prerequisites:
- Python 3.8+
- ScyllaDB cluster (with ScyllaDB Cloud or use Docker)
Connect to your ScyllaDB cluster using CQLSH and create the schema:
Then, install the Python requirements in a new environment:
Modify config.py to match your database credentials:
Run the server:
Generate sample user data:
This script populates your ScyllaDB tables with sample data. This is necessary for the next step, where you will run CDC queries to analyze user behavior.
Analyze user behavior with CDC
CDC records every data change, including deletes, offering a comprehensive view of your data evolution without affecting database performance. For a shopping cart application, some potential use cases for CDC include:
- Analyzing a specific user’s buying behavior
- Tracking user actions leading to checkout
- Evaluating product popularity and purchase frequency
- Analyzing active and abandoned carts
Beyond these business-specific insights, CDC data can also be exported to external platforms, such as Kafka, for further processing and analysis.
Here are a couple of useful tips when working with CDC:
- The CDC log table contains timeuuid values, which can be converted to readable timestamps using the toTimestamp() CQL function.
- The cdc$operation column helps filter operations by type. For instance, a value of 2 indicates an INSERT operation.
- The most efficient and scalable way to query CDC data is to use the ScyllaDB source connector and set up an integration with Kafka.
Now, let’s explore a couple of quick questions that CDC can help answer.
How many times did users add more than 2 of the same product to the cart?
How many carts contain a particular product?
Set up ScyllaDB CDC with Kafka Connect
To provide a scalable way for you to analyze ScyllaDB CDC logs, you can use Kafka to receive messages sent by ScyllaDB. Then, you can use an analytics tool, like Elasticsearch, to get insights. To send CDC logs to Kafka, you need to install the ScyllaDB CDC source connector, and create a new ScyllaDB connection in Kafka Connect.
Install the ScyllaDB source connector on the machine/container that’s running Kafka:
Then use the following ScyllaDB related parameters when you create the connection:
Make sure to enable CDC on each table you want to send messages from. You can do this by executing the following CQL:
Try it out yourself
If you are interested in trying out this application yourself, check out the dedicated documentation site: shopping-cart.scylladb.com and the GitHub repository.
If you have any questions about this project or ScyllaDB, submit a question in ScyllaDB the forum.