Project: Building a Versioned Inventory System with Doltgres

Introduction: Versioning Your Business Data

Welcome to a hands-on journey where we’ll build a practical, version-controlled inventory system. Imagine a small business that constantly updates product prices, adds new items, and sometimes needs to look back at what a product cost last month, or even revert an erroneous update. Traditional databases make this challenging, often requiring complex auditing triggers or manual backups.

This chapter introduces you to Doltgres, Dolt’s PostgreSQL-compatible offering, allowing you to apply the powerful “Git-for-Data” paradigm to your familiar PostgreSQL-style workflows. We’ll set up a simple inventory database, track changes to product details, experiment with feature branches for updates, and even “time travel” to see historical data states. By the end, you’ll have a solid understanding of how data versioning can bring clarity, traceability, and collaborative power to your business applications.

To get the most out of this chapter, you should have:

• Dolt (and Doltgres) installed and configured from Chapter 2.
• Basic familiarity with SQL (creating tables, inserting, updating data).
• A conceptual understanding of basic Git commands like commit, branch, and merge from Chapters 3-5.

Let’s start building!

Core Concepts: Git for Your Inventory

Before we dive into the code, let’s understand how Doltgres helps manage our inventory.

Why Version Control for Inventory?

Consider a simple inventory: product_name, price, stock_quantity.

• Auditing: Who changed a price, when, and from what to what? Essential for compliance and accountability.
• Rollbacks: A price update was accidentally set too low. How do you quickly revert to the previous correct price without data loss?
• Experimentation: What if you want to test a new pricing strategy without affecting the live inventory?
• Collaboration: Multiple team members updating product descriptions and quantities without stepping on each other’s toes.

Doltgres provides a native solution for these challenges by treating your entire database, schema and data, like a Git repository.

Doltgres: PostgreSQL Compatibility Meets Git-for-Data

Doltgres brings Dolt’s unique versioning capabilities to the PostgreSQL ecosystem. This means you can use your favorite PostgreSQL tools and drivers while benefiting from Git-style operations.

📌 Key Idea: Doltgres is a PostgreSQL-compatible database that automatically versions every change, allowing you to use dolt commands for Git-like operations (commit, branch, merge, diff) on your SQL data.

When we interact with Doltgres, we’ll primarily use two interfaces:

1. SQL Client: For standard database operations (CREATE TABLE, INSERT, UPDATE, SELECT). Doltgres behaves just like a PostgreSQL server.
2. Dolt CLI: For version control operations (dolt commit, dolt branch, dolt merge, dolt diff). This is where the Git-for-Data magic happens.

The Versioning Flow: Commits, Branches, and Time Travel

Our inventory project will follow a typical Git-for-Data workflow:

1. Initialize: Create a new Doltgres database, which implicitly creates our main branch.
2. Schema & Initial Data: Define our inventory tables and populate them. Each set of changes will be dolt commit-ted.
3. Feature Branching: When we want to make a significant change (like a seasonal price adjustment), we’ll dolt branch off main.
4. Modify & Commit: On our feature branch, we’ll make SQL changes and dolt commit them.
5. Review Changes: We can use dolt diff to see exactly what changed between our branch and main.
6. Merge: Once satisfied, we’ll dolt merge our feature branch back into main.
7. Time Travel: At any point, we can use Doltgres’s AS OF syntax in our SQL queries to view the inventory’s state at any past commit or timestamp.

Let’s visualize this branching and merging process:

Step-by-Step Implementation: Building Our Inventory System

Let’s get our hands dirty and build this system. We’ll start by initializing our Doltgres database.

Step 1: Initialize the Doltgres Database

First, create a new directory for our project and initialize a Doltgres database within it.

mkdir versioned-inventory
cd versioned-inventory
dolt init --postgres

Explanation:

• mkdir versioned-inventory: Creates a new folder for our project.
• cd versioned-inventory: Navigates into the new folder.
• dolt init --postgres: This command initializes a new Dolt repository, but crucially, the --postgres flag configures it to operate with PostgreSQL compatibility. This means it will use PostgreSQL’s SQL dialect and data types. By default, Dolt is MySQL-compatible, so this flag is essential for Doltgres projects.

You should see output indicating a new Dolt repository was initialized. This also creates your initial main branch.

Step 2: Start the Doltgres Server

Now, let’s start the Doltgres server so we can connect to it with a PostgreSQL client.

dolt sql-server --port 5432 --postgres

Explanation:

• dolt sql-server: This command starts the Dolt SQL server.
• --port 5432: Specifies that the server should listen on port 5432, which is the standard default port for PostgreSQL.
• --postgres: Again, this flag ensures the server runs in PostgreSQL compatibility mode.

Keep this terminal window open. You’ll need to open a new terminal window or tab for the following steps.

Step 3: Connect with a PostgreSQL Client and Create Schema

We’ll use the psql command-line client (standard for PostgreSQL) to connect to our Doltgres server. If you don’t have psql installed, you might need to install PostgreSQL client tools for your OS.

In your new terminal:

psql -h 127.0.0.1 -p 5432 -U dolt -d dolt

Explanation:

• psql: The PostgreSQL command-line client.
• -h 127.0.0.1: Connects to the host at this IP address (your local machine).
• -p 5432: Connects to the server on port 5432.
• -U dolt: Connects using the default Dolt user (no password needed by default).
• -d dolt: Connects to the default dolt database.

Once connected, you’ll see a dolt=> prompt. Let’s create our products table.

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• We define a products table with common inventory fields.
• UUID PRIMARY KEY DEFAULT gen_random_uuid(): Uses PostgreSQL’s UUID type and gen_random_uuid() function for unique IDs.
• DECIMAL(10, 2): Standard for currency.
• TIMESTAMP WITH TIME ZONE: For recording creation and update times.

After running the CREATE TABLE command, you should see CREATE TABLE as the output.

Step 4: Add Initial Inventory Data and Commit

Now, let’s populate our products table with some initial items.

INSERT INTO products (name, description, price, stock_quantity) VALUES
('Laptop Pro X', 'High-performance laptop for professionals', 1200.00, 50),
('Wireless Mouse', 'Ergonomic wireless mouse', 25.50, 200),
('USB-C Hub', '7-in-1 USB-C adapter', 49.99, 100);

You should see INSERT 0 3 as output, indicating 3 rows were inserted.

Now, exit the psql client by typing \q and pressing Enter.

Back in your second terminal (where you ran dolt init), check the status and then commit your changes.

dolt status

You’ll see output like:

Untracked tables:
  products

This tells us that Dolt sees the products table but hasn’t started tracking its history yet. It’s like a new file in Git that hasn’t been git add-ed.

Let’s add and commit:

dolt add products
dolt status # Verify it's now staged
dolt commit -m "Initial inventory setup and data"

Explanation:

• dolt add products: Stages the products table for the next commit.
• dolt status: Shows that products is now Staged for commit.
• dolt commit -m "...": Creates a new commit, saving the current state of the products table to the database’s history. The commit message describes the change.

You’ll see output confirming the commit, including the commit hash.

Step 5: Create a Feature Branch for a Price Adjustment

Our marketing team wants to run a promotion, adjusting the price of the “Laptop Pro X”. We’ll do this on a new branch to keep our main inventory stable.

dolt branch feature/promo-prices
dolt checkout feature/promo-prices

Explanation:

• dolt branch feature/promo-prices: Creates a new branch named feature/promo-prices based on the current main branch.
• dolt checkout feature/promo-prices: Switches our working directory (and the active database context) to this new branch.

You’ll see output confirming the branch creation and checkout.

Step 6: Update Data on the Feature Branch

Now that we’re on the feature/promo-prices branch, let’s update the price. Reconnect to Doltgres with psql.

psql -h 127.0.0.1 -p 5432 -U dolt -d dolt

At the dolt=> prompt:

UPDATE products
SET price = 1100.00, updated_at = CURRENT_TIMESTAMP
WHERE name = 'Laptop Pro X';

You should see UPDATE 1. Let’s verify the change:

SELECT name, price FROM products WHERE name = 'Laptop Pro X';

Output:

     name     |  price
--------------+---------
 Laptop Pro X | 1100.00
(1 row)

Exit psql (\q).

Back in the terminal, commit this change to our feature/promo-prices branch:

dolt commit -m "Reduced Laptop Pro X price for promotion"

Step 7: View Differences and Merge Changes

Before merging, let’s see the difference between our feature/promo-prices branch and main.

dolt diff main

Explanation:

• dolt diff main: Shows the differences between the current branch (feature/promo-prices) and the main branch. Dolt will show you a Git-style diff, detailing which rows were changed and which columns within those rows were modified.

You’ll see output similar to this (simplified):

--- a/products
+++ b/products
@@ -1,4 +1,4 @@
- id                                   name           description                              price    stock_quantity created_at                       updated_at
- ------------------------------------ -------------- ---------------------------------------- -------- -------------- -------------------------------- --------------------------------
- 550e8400-e29b-41d4-a716-446655440000 Laptop Pro X   High-performance laptop for professionals 1200.00  50             2026-06-06 10:00:00.000000 +00:00 2026-06-06 10:00:00.000000 +00:00
+ id                                   name           description                              price    stock_quantity created_at                       updated_at
+ ------------------------------------ -------------- ---------------------------------------- -------- -------------- -------------------------------- --------------------------------
+ 550e8400-e29b-41d4-a716-446655440000 Laptop Pro X   High-performance laptop for professionals 1100.00  50             2026-06-06 10:00:00.000000 +00:00 2026-06-06 10:05:00.000000 +00:00

(Note: Actual UUIDs and timestamps will vary.)

This diff clearly shows the price change from 1200.00 to 1100.00 and the updated_at timestamp.

Now, let’s merge these changes back into main.

dolt checkout main
dolt merge feature/promo-prices
dolt commit -m "Merged feature/promo-prices branch" # Dolt will often auto-commit, but explicit is good practice.

Explanation:

• dolt checkout main: Switches back to the main branch.
• dolt merge feature/promo-prices: Integrates the changes from feature/promo-prices into main. Dolt performs a 3-way merge, just like Git.
• dolt commit -m "...": While Dolt often generates a merge commit message automatically, it’s good practice to confirm or add a descriptive message.

You’ll see output confirming the merge. The main branch now has the updated price.

Step 8: Time Travel Queries

This is where Doltgres truly shines! We can query our database as it existed at any previous commit.

Reconnect to psql:

psql -h 127.0.0.1 -p 5432 -U dolt -d dolt

First, let’s see the current price on main:

SELECT name, price FROM products WHERE name = 'Laptop Pro X';

Output (current price):

     name     |  price
--------------+---------
 Laptop Pro X | 1100.00
(1 row)

Now, let’s use a time travel query to see the price before our promotion. We need the commit hash of the “Initial inventory setup and data” commit. You can get this from dolt log in your terminal.

In your second terminal, run:

dolt log

Find the commit message “Initial inventory setup and data” and copy its Commit ID (the long hexadecimal string). It will look something like 0s3n06b12a8626i2e3l213r4c1p2k2a1.

Back in psql, replace YOUR_INITIAL_COMMIT_HASH with the actual hash you found:

SELECT name, price FROM products AS OF 'YOUR_INITIAL_COMMIT_HASH' WHERE name = 'Laptop Pro X';

Explanation:

• SELECT ... FROM products AS OF 'YOUR_INITIAL_COMMIT_HASH': The AS OF clause is a powerful Doltgres extension to SQL. It allows you to specify a point in time (a commit hash, branch name, or timestamp) and query the data as it existed at that exact moment.

Output (historical price):

     name     |  price
--------------+---------
 Laptop Pro X | 1200.00
(1 row)

Amazing, right? You just “time traveled” your data! You can also use branch names:

SELECT name, price FROM products AS OF 'main' WHERE name = 'Laptop Pro X';
SELECT name, price FROM products AS OF 'feature/promo-prices' WHERE name = 'Laptop Pro X';

AS OF 'main' will show the latest price on main (1100.00). AS OF 'feature/promo-prices' will also show 1100.00 because the feature branch was merged. If you queried the feature branch before the merge, it would show the updated price there, while main still had the old price.

Exit psql (\q).

Step 9: Versioning Schema Changes

What if we need to add a new column, like supplier_id? Doltgres versions schema changes just like data changes.

Reconnect to psql:

psql -h 127.0.0.1 -p 5432 -U dolt -d dolt

Add the new column:

ALTER TABLE products
ADD COLUMN supplier_id UUID;

Exit psql (\q).

Now, commit the schema change:

dolt commit -am "Added supplier_id column to products table"

Explanation:

• dolt commit -am "...": The -a flag (for “all”) tells Dolt to automatically stage any modified or deleted tables. This is handy for both data and schema changes.

You can now dolt diff between commits to see the schema change, or psql into an older commit AS OF the commit before this ALTER TABLE to see the schema without supplier_id.

Mini-Challenge: Add a New Product Category

It’s your turn! The business wants to introduce a new category of products: “Accessories.”

Challenge:

1. Create a new Dolt branch called feature/accessories.
2. Checkout this new branch.
3. Connect to Doltgres via psql.
4. Insert two new products into the products table that fall under an “Accessories” theme (e.g., “Gaming Headset”, “Ergonomic Keyboard”).
5. Exit psql.
6. Commit your changes to the feature/accessories branch with a descriptive message.
7. View the dolt diff between main and feature/accessories.
8. Merge feature/accessories back into main.
9. Verify the new products are on main by querying the database.

Hint: Remember the dolt add command if you’re not using -a with dolt commit after an INSERT.

What to observe/learn: This exercise reinforces the branching, modification, and merging workflow, helping you internalize how new data additions are managed with Doltgres. You’ll see how dolt diff shows added rows.

Common Pitfalls & Troubleshooting

Even in a simple project, you might encounter issues. Here are a few common ones:

• Forgetting to Commit: You make changes in psql, exit, and then wonder why dolt status shows nothing, or why dolt diff doesn’t reflect your updates.

  • Solution: Always remember that SQL changes (INSERT, UPDATE, DELETE, ALTER TABLE) are applied to the working set of your Doltgres database. To persist them in the version history, you must dolt add the affected tables and dolt commit.
  • Tip: dolt status is your best friend. Use it frequently to see what changes are pending.

• Merge Conflicts: While less likely in this simple project, if two branches modify the same row and column differently, Dolt will report a merge conflict.

  • Symptoms: dolt merge will tell you there are conflicts and the merge failed.
  • Solution: Use dolt status to identify conflicting tables. Then, use dolt diff --merge <table_name> to see the conflict markers directly in your terminal. You’ll need to manually resolve the conflict by editing the data (often using dolt checkout --ours <table_name> or dolt checkout --theirs <table_name> for simpler resolutions, or manual SQL UPDATE followed by dolt resolve). We’ll cover advanced conflict resolution in a later chapter, but for now, if you hit one, try to revert your changes and re-attempt the merge after careful planning.

• Incorrect AS OF Syntax: Trying to use AS OF with an invalid commit hash, branch name, or timestamp.

  • Symptoms: SQL errors like “unknown commit” or “invalid timestamp format.”
  • Solution: Double-check your dolt log for exact commit hashes. Ensure branch names are spelled correctly. For timestamps, Dolt generally accepts ISO 8601 formats (e.g., '2026-06-06 10:30:00').

• Dolt SQL Server Not Running: Trying to connect with psql when dolt sql-server isn’t active.

  • Symptoms: psql: error: connection to server at "127.0.0.1", port 5432 failed: Connection refused.
  • Solution: Ensure you have the dolt sql-server --port 5432 --postgres command running in a separate terminal window.

Summary

Congratulations! You’ve successfully built a version-controlled inventory system using Doltgres. In this chapter, you learned:

• How to initialize a Doltgres database and start its PostgreSQL-compatible server.
• To create tables and insert data using standard psql commands.
• The fundamental dolt add and dolt commit workflow for saving database states.
• How to create and checkout feature branches (dolt branch, dolt checkout) for isolated development.
• To modify data on a branch and then view the differences (dolt diff) before merging.
• The process of merging changes (dolt merge) back into your main branch.
• The power of time travel queries using the AS OF clause to retrieve historical data states.
• That schema changes are also versioned and can be committed like data changes.

This beginner-friendly project demonstrates the core power of Doltgres: bringing the collaborative, auditable, and rollback-friendly benefits of Git to your relational data. You’re no longer just managing data; you’re managing its entire history.

What’s next? In the upcoming chapters, we’ll delve deeper into more advanced Dolt features, including handling complex merge conflicts, integrating Dolt with application code, exploring DoltHub for collaboration, and tackling enterprise-scale challenges with much larger datasets.

References

• DoltHub Documentation
• Dolt CLI Reference
• Dolt PostgreSQL Compatibility
• PostgreSQL psql Documentation

This page is AI-assisted and reviewed. It references official documentation and recognized resources where relevant.