Dolt is the world's first SQL Database which enables users to branch and merge. Similar to source code version control systems, this allows for the ability to modify your data in an isolated way before finishing your work and merging in the results to the primary branch.

Monolithic SQL databases are the norm. In fact, they are so normal you probably don't realize there could be a different way to do it. SQL servers have an open port, you connect to it, and voila, you're making queries.

Dolt, due to it's branching mechanism, does things a little differently. Conceptually, each branch is a database. It's possible to have thousands of branches in Dolt - or said another way - thousands of different, yet related, databases.

Today we'll talk about how best to connect to a Dolt database so that your application can best leverage this core capability of Dolt!

A Sudoku Sample Application - SoDOLTku

As a demonstration, we've created a small three tier application which allows you to play Sudoku. The application is pretty classic in its separation of concerns:

1. Backend database server stores all state - Dolt
2. Application server manipulates data as required - Express.js
3. Frontend web application presents the data to user and provides UI elements to alter data - React

This is a fully functional game. claude-code made this pretty easy. Cost $28.55. Some screenshots:

When you select an existing game, behind the scenes, you are selecting a branch in Dolt.

There are four gameplay levels. Impossible level is pretty hard. You can enable Show Errors if you need an assist.

When you win, you get a pretty banner. It even jiggles when it shows up thanks to CSS tricks I don't know but Claude thought was appropriate. Thanks Claude!

Just want the code? Get it here: https://github.com/dolthub/sudoku-node-app

Using a Branch for Each Game

As mentioned before, branches in Dolt can be considered individual databases. Here is how you would typically connect to a database, and how we connect to our game database on the main branch. Really, this connects to the default branch, which happens to be main

class Database {
  private mainPool: mysql.Pool;
  private branchPools: Map<string, mysql.Pool> = new Map();

  constructor() {
    // Main connection pool for branch management
    this.mainPool = mysql.createPool({
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '3306'),
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: 'sudoku_games',                // Connecting to database with the default branch - main
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
    });
  }

You can see that the Database class has two ways of tracking pools. The mainPool is constructed at the start, but the branchPools are connection pools are left to be created on demand.

When a new game is created, or when any client attempts to use the branch, the branch specific connection pool is seeded with the following code:

  // Get or create connection pool for a specific branch
  private getBranchPool(branch: string): mysql.Pool {
    if (!this.branchPools.has(branch)) {
      const branchPool = mysql.createPool({
        host: process.env.DB_HOST || 'localhost',
        port: parseInt(process.env.DB_PORT || '3306'),
        user: process.env.DB_USER || 'root',
        password: process.env.DB_PASSWORD || '',
        database: `sudoku_games/${branch}`,          // As far as the pool is concerned, this is a unique database.
        waitForConnections: true,
        connectionLimit: 5,                          // notice you can have a smaller connection limit.
        queueLimit: 0,
      });
      this.branchPools.set(branch, branchPool);
    }
    return this.branchPools.get(branch)!;
  }

Now that we have the ability to dynamically create pools as needed, our application code is pretty straightforward. The frontend tracks which branch, or "game," the user is on. Then the API requests use that to grab the right branch before performing any queries:

      const pool = this.getBranchPool(branch);
      
      // Get current board state
      const [rows] = await pool.execute(
        'SELECT row_num, col_num, value FROM sudoku_cells ORDER BY row_num, col_num'
      );

Finally, all branch management and merging should be performed from the default branch. Especially merges:

      await this.mainPool.execute('CALL DOLT_MERGE(?)', [branchName]);

Technically speaking, you can create and delete branches from any branch, but it seems a lot cleaner to use the primary connection pool to do that. Merging should always be performed on the branch you wish to merge into, so almost always that is the main branch.

There is another access pattern which can be used from the mainPool as well, which is the AS OF SQL statement. AS OF Allows you to specify a commit or branch which you want to query. So you can perform reads against branches you aren't on, like so:

      await this.mainPool.execute('SELECT row_num,col_num,value from sudoku_cells AS OF ?', [branchName]);

And there is yet another option which is fully qualified references which allow you to specify a database/branch in the query it self, like so:

      await this.mainPool.execute('SELECT row_num,col_num,value from `sudoku_games/game_branch`.sudoku_celßls;');

What NOT to Do

Do not call dolt_checkout('branch'). The built-in procedure that you might think is the best way to use branches is not actually what you want to use. The primary reason this procedure even exists is to allow a human to navigate Dolt with a little more comfort. When you are connected using dolt sql, which is an interactive shell, it's reasonable to switch to another branch for any number of reasons. As a little side note, dolt_checkout is just a git-like way to do use, which is just more evidence that branches are just databases:

> use `mydb/branch`;

Applications should not use dolt_checkout(). Many, well ALL, ORM and client libraries which connect to SQL databases assume that the database behaves in a pretty consistent way that has been standardized by 50 years of industry habit. The libraries can have caching or other optimizations which fall apart with Dolt's branching model.

If you find yourself calling dolt_checkout() in application code, you need to re-think your approach.

Connect to each branch like it is its own database, and you'll be good to go.

More Work To Do

There was a user request about how to do this last week, and one facet of their application required that they delete branches. Branch deletions should definitely be done with the mainPool, but that doesn't answer the question of when is it safe to delete a branch.

Currently, if you have an application where there are open connections to a branch and you attempt to delete it, you will receive the following error:

Error 1105 (HY000): unsafe to delete or rename branches in use in other sessions; use --force to force the change

If you have a single application server, you could keep track of how many connections you have to the branch, but that solution falls apart when you have multiple application servers which is the norm.

What we have here is a feature gap! We don't currently expose a way in Dolt for users to know if a branch is active or when it was last active. We need to build something to address that, so you'll be sure to hear more about this in the next couple weeks.

Until then, come tell us what you are going to build using Dolt on our very friendly Discord server!