Dolt is the world's first version controlled Database. I've been building software for more than 20 years, and building on top of Dolt makes application development fun again. As I built a little application using Java and Hibernate, it highlighted for me how much I could have used Dolt as developer tool to help me increase my productivity. The application is available for others to play with, and this blog is going to cover how it works in depth.

What we'll cover today:

• Building and Running a Java application based on Hibernate which uses a Dolt Database
• Clone and work with shared data
• Benefits of Developing with Dolt

If you want to jump right in and run some code, clone this repo and follow the README!

Game of Life

Conways's Game of Life is a cellular automata simulation which was first described by John Conway in 1970, and many variations of this this idea have been developed and discussed at length in the last 53 years. We'll go over the the rules of Conway's game and Dolt's variation in depth, but to give a little highlight of where we are headed, here is a comparison between the classic game, and the application we'll build today.

Conway's Game of Life	Dolt's Petri Dish

Rules of the Game

Conway's Rules are, given a turn based evaluation (tick):

• Each empty cell which has precisely 3 living neighbors will become alive.
• Each living cell with zero or one neighbors dies of isolation.
• Each living cell with four or more neighbors dies of overcrowding.

Similar to Conway's Game, The Dolt Petri Dish has a set of simple rules with define how cells live and die.

• Each Cell is empty or has a Species with health between 0.0 and 1.0. If a cell's health falls to 0.0, it dies, and the cell becomes empty again.
• Each Species ages at a consistent amount per tick. That amount varies per Species.
• Each tick in the 8 neighboring cells will impact the current cell. The health of the current cell will be reduced the sum of the damages caused by each neighbor.
• If a cell is unoccupied at the beginning of the tick, if it has 3-5 occupied neighbors, then that cell will be given life in the form of the species which has the highest count of neighbors. New cells are always born with 1.0 strength. If there is not a dominant neighboring cell species (one species count that is higher than all others), then the cell will remain empty.

Hibernate

Hibernate is one of the original Java Object Relational Mapping (ORM) solutions which has set the bar for similar solutions on other platforms and languages. It allows application developers to primarily work with objects in their applications. In Petri Dish, the schema consists of three tables:

Each table relates to Petri Dish in the following way:

• Species table represents the organisms in the Petri Dish. Each species has a primary UUID key (id), color as a hex string (color), and a double (tick_health_impact). This value is used as aging in the rules outlined above.
• The Damage table represents how much one species impacts another cell's health when the are next to each other. It has two columns, attacker and victim, which are references to the primary id of Species. These are assured to be valid thanks to a foreign key constraint. There is also a constraint that damage must be between 0.0 and 0.1 (all inclusive)
• The PetriDish table which represents the 40x40 grid of cells. The primary key of this table is a composite key which is the X and Y coordinate of the cell in question. The species column indicates the species type. Finally, there is a double value which represents the health of the cell at the given coordinates. This value must be 0.0 (exclusive) to 1.0 (inclusive).

Rather than list the SQL commands which create the tables described here, we'll cover that below.

Disclaimer

This application is meant to be used against a local dolt instance, and does not attempt to handle the myriad of problems which can arise with transactional and secure data. For example, we don't attempt to capture exceptions which happen as a result of concurrent writes. Furthermore, input sanitation doesn't really apply when you are working from an expectation that all interactions with the data are through one single threaded client. It's possible to do all of that correctly, but this sample application tries to keep it as simple as possible to work with Dolt.

Modeling with Entities

Our Hibernate application uses the Java Persistence API (JPA) standard which consists primarily of using @Annotations on classes to map from the schema to objects. Here is the top of the DaoSpecies class:

@Entity
@Table(name = "species")
public class DaoSpecies {
       @Id
       @Column(name = "id")
       @GeneratedValue(strategy = GenerationType.UUID)
       private String id;

       @Column(name = "color")
       private String hexColor;

       @Column(name = "tick_health_impact")
       private double tickHealthImpact;

       @OneToMany(cascade = CascadeType.ALL)
       @JoinColumn(name = "attacker")
       @MapKey(name = "victim")
       private Map<String, DaoDamage> damageMap;

There are a few things to call out here:

• @Entity Required on any primary object used to represent data loaded by Hibernate.
• @Table Refers to the table mentioned above
• @Id Required for every Hibernate object. Should map the the primary key in the database.
• @Column Specify which columns you want to build the object from. The class does not need to use all columns
• @OneToMany This helps us make using the relationship between the Species and Damages table easy. This is one of the superpowers of Hibernate. It allows us to load the Species and have its list of damages to other species close at hand to the application developer. We'll cover this in more detail below.

In our example code, all classes which start with Dao (stands for Data Access Object) are some variation of the above. As you look through them, you'll see there are a lot of things you don't need to specify. For example, we don't need to concern ourselves with the fact that the id is a char(36) - Hibernate concerns itself with how to read objects from the database and put them in the write form for a Java Object. There are more interesting cases like long <-> bigint, Timestamps, and so forth.

Of particular interest is that your Dao classes can have additional methods to further massage the data as the application needs. As an example, Java has a native Color type which is more convenient than hex string. To solve this, we add the following method on the DaoSpecies class:

       public Color getColor() {
           return Color.decode(this.hexColor);
       }

We can go much further with this, and the damageMap mentioned above is such an example. It makes use of that @OneToMany object, damageMap to enable us to pass one DaoSpecies in as an argument and get a simple double back. This is surprisingly simple given that you didn't just need to go load the object - Hibernate did lazy loading of the object without ever getting in your way.

       public double getDamage(DaoSpecies other) {
           DaoDamage dmg = this.damageMap.get(other.getId());
           if (dmg == null) {
                return 0.0;
           }
           return dmg.getDamage();
       }

Another Dao object which is interesting is how we create a composite key. The DoaPetriDishCell class starts like this:

@Entity
@Table(name = "petridish")
public class DaoPetriDishCell {

    @EmbeddedId
    private DaoPetriDishPrimaryKey id;

    @ManyToOne
    @JoinColumn(name = "species_id")
    private DaoSpecies species;

    @Column(name = "strength")
    private double strength;

Call outs:

• @EmbeddedId is used instead of an @Id. This is a composite key. See the DaoPetriDishPrimaryKey which has the @Embeddable to indicate it can be used as a composite primary key. It's pretty straight forward, but important that you have confidence in the equals and hashCode implementations because Hibernate depends on the primary key to make any of this work (it's impossible to create a Hibernate object without a Id)
• @ManyToOne Similar to @OneToMany mentioned before, this is used to indicate that there is a relationship between objects. This states that there are many DaoPetriDishCells which refer to each unique DaoSpecies object. The @JoinColumn states that the value in the species_id columns refers to primary IDs of DaoSpecies objects. Again, this is the superpower of Hibernate. Your application doesn't need to do join queries manually, and the relationships between different tables can be modeled with common objects.

You can read all of the Doa* classes in the source. It's pretty straight forward, and, true to Java form, it's a lot of boiler plate.

Loading Objects and Persisting Them

Now that the shape of our data has been modeled in Java Objects, how do we run queries and record updates in the database?

All of the application code which does any loading or persisting has been confined to our main class, PetriDishMain. This isn't necessarily the best way to factor production code, but the allows the reader to not get lost in the sea of Dao and Gui classes which make up the application.

This is the full load of the petridish table and automatically converting it into DaoPetriDishCell objects requires two lines of code:

        Query<DaoPetriDishCell> q = currentBranchSession.createQuery("FROM DaoPetriDishCell", DaoPetriDishCell.class);
        List<DaoPetriDishCell> petridish = q.list();

We happen to have an application where we need to load the full tables, so we don't have great examples of how to model complex queries. Worth calling out you can do arbitrary SQL queries, and map them in to whatever objects you need. For example, the application has several Dolt specific queries, like the following:

      Query<DaoBranch> q = currentBranchSession.createNativeQuery("select name,hash from dolt_branches", DaoBranch.class);
      List<DaoBranch> branches = q.list();

The DaoBranch class requires the name and hash of the branch, and Hibernate does all the mapping for you.

Persisting objects is a little more involved, only because you need to start the transaction and commit it. To write a DaoSpecies object to the database, do the following:

        currentBranchSession.beginTransaction();
        currentBranchSession.persist(species);
        currentBranchSession.getTransaction().commit();

Session Per Branch

Above, we make use of the currentBranchSession instance to talk to the database. Dolt's branching mechanism confuses Hibernate, and really all ORMs for that matter. The HibernateUtil class is used to produce the session factory which is how you create new sessions. You can see here that this is not the boiler plate you'll find on the internet:

    private static SessionFactory buildSessionFactory(String branch) {
        try {
            // Create the SessionFactory from hibernate.cfg.xml
            Configuration cfg = new Configuration().configure();

            if (branch == null) {
                return cfg.buildSessionFactory();
            }

            String baseUrl = cfg.getProperty(urlProp);
            String newUrl = baseUrl + "/" + branch;

            cfg.setProperty("hibernate.connection.url", newUrl);
            return cfg.buildSessionFactory();

When you specify a /branch at the end of your connection URL, Dolt knows to create your session on that branch. For this reason, checkout in our application doesn't call the dolt_checkout, we switch what session we are using instead:

    private final Map<String, Session> branchSessions = new HashMap<>();

    @Override
    public void checkout(String branch) {
        if (branch != null) {
            Session session = branchSessions.get(branch);
            if (session == null) {
                session = HibernateUtil.getSessionFactoryForBranch(branch);
                branchSessions.put(branch, session);
            }
            currentBranchSession = session;
        }

Hibernate Odds and Ends

Harkening back to the early 2000s, most hibernate applications have an .xml file. Our application is no different. Nothing particularly interesting to discuss here other than you need to have a mapping definition for each Dao class, like the following:

      <mapping class="com.dolthub.DaoSpecies" />
      <mapping class="com.dolthub.DaoDamage" />
      <mapping class="com.dolthub.DaoSeed" />
      <mapping class="com.dolthub.DaoBranch" />
      <mapping class="com.dolthub.DaoPetriDishCell" />

Also, there is a password in there. You are welcome to change that. If you create an application which actually has security requirements, you should create separate SQL user than root, and give it the appropriate privileges.

To repeat the disclaimer above, this code doesn't demonstrate all the best practices to run a Hibernate client in the wild against a sensitive database.

Sharing Petri Dish Game Data

Now it's time to grab Dolt, and take it for a spin! If you haven't done it yet, clone this repository on to your local machine:

$ git clone https://github.com/dolthub/hibernate-sample.git

Getting the Database

The example database for this application can be found on DoltHub.

If you don't have Dolt installed, install it, then clone the example database:

$ dolt clone dolthub/HibernatePetriDish
$ cd HibernatePetriDish

The application works with local branches currently, so you can create branches as you see fit. To see all remote branches, run dolt branch -a. Create branches as follows:

$ dolt branch clean_slate origin/clean_slate
$ dolt branch red_background origin/red_background

Start a dolt sql-server:

$ dolt sql-server -u root -p r00tr00t --loglevel debug

This will create a local server on port 3333. You can interact with the server using a MySQL client or by running dolt sql in the root directory. The password matters - it is coded into the hibernate configuration, and you are welcome to change it.

Once this server is running, leave the terminal open so you can see the logs. The --loglevel is specified so you can see all queries that are being performed by the application when it is running.

Building and Running the PetriDish Application

Prerequisites are Java and Maven:

• Install Java. I used OpenJDK, but Oracle's JVM will work as well.
• Install Maven

Then clone and build the code in the hibernate-sample repository. In the root directory of that repository, package then run the code with the following commands:

$ mvn package
$ mvn exec:java

At this point, you should see something like this:

Really the only thing which should be different on your screen is the look and feel of the gui because that can vary by platform. The content of the board should spell out d01t, because that is the state on the main branch in DoltHub. Specifically, this commit: 7b8b5gui8r4s6vubsk6et90knch6rt37.

Since this is a deterministic game, you should be able to hit the Run button and end up with something like this:

When you run the simulation, then stop it, the full state of the petri dish is written to the database. In order to play with the database, and see what's happening, connect to it, and look at what was changed:

$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
HibernatePetriDish> select * from dolt_status;
+------------+--------+----------+
| table_name | staged | status   |
+------------+--------+----------+
| petridish  | 0      | modified |
+------------+--------+----------+
1 row in set (0.00 sec)

Rules of Petri Dish (Recap) in the UI

Two of the Four games rules are configurable: Aging and Damage. Remember:

• Each Species ages, and each tick will decrease the health of the cell as is determined by the Species table data.
• Each tick the 8 neighboring cells will impact this cell by decreasing health of the cell as determined by the damage caused by the neighboring cell.

The application has the ability modify the aging and damage of a species. I'm not a great UI designer, so I need to spell it out with a picture:

• (A) Is the selected species (indicated by it's color)
• (B) Is the amount of health that each cell loses with each tick.
• (C) The table of damage that the selected species does to neighboring species.

By adjusting these values used for the experiment you will see some obvious results - if a species never ages (or has a negative aging value! We can fix that with a constraint, exercise for the reader!), and all other species age then that species will win out over time. Also The damage values can be tweaked to allow for some cycles in damages (Red destroys Blue destroys Yellow destroys Red). It can be kind of mesmerizing watching the battles play out.

Developing with Dolt

I mentioned at the very beginning that Dolt made it fun to write code again. I'll give some examples.

The Power of Reset

As I was debugging the application, I was constantly starting and stopping the application and wanted to be able to restart from the same board state. dolt reset to the rescue!

$ dolt reset --hard main

OR

HibernatePetriDish> call dolt_reset("--hard");

Randomize the Damages

The UI is fine for switching a single damage, but I wanted to reset the game and give every species a random damage to every other species. I dropped all of the existing values and randomized new ones with these two queries:

DELETE FROM damage; -- full delete of all rows. Almost as easy as `rm -rf /`, Thanks SQL!
INSERT INTO damage (attacker, victim, damage)
SELECT
  IDPairs.id1 AS attacker,
  IDPairs.id2 AS victim,
  RAND() * 0.1 AS damage
FROM
  (
    -- Your IDPairs CTE here
    WITH IDPairs AS (
      SELECT
        s1.id AS id1,
        s2.id AS id2
      FROM
        species s1
        CROSS JOIN species s2
      WHERE
        s1.id != s2.id
    )
    SELECT * FROM IDPairs
  ) IDPairs;

What this query does is creates a Common Table Expression (CTE) which is a two column virtual table with every combination of attacker and victim, then inserts to each one with: RAND() * 0.1 AS damage. Doing this I realized how biased my test values had been. Humans are not good random number generators.

Using Merge to give a Background

After I created the d01t experiment that is on main, I wanted to fill all the empty cells with red. Rather than writing a query that found all the empty cells, I used dolt merge instead. And you can see this in the Dolt history of the database you cloned. See the full graph on DoltHub

The merge started from a commit which is the clean_slate branch on DoltHub. It's a commit with no rows in the petridish table. I then build up the d01t word tediously with SQL commands to fill rectangles, the details of which can be found in the commits themselves. That is all on the main branch. The on red_background branch, which was started at clean_slate, I filled every cell with the Red Species:

INSERT INTO petridish (x, y, species_id, strength)
WITH RECURSIVE x AS (
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 FROM x WHERE n < 39
),
y AS (
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 FROM y WHERE n < 39
)
SELECT
  x.n AS x,
  y.n AS y,
  "013a3611-a398-429f-816a-3339872ae8c9" AS species_id,
  RAND() AS strength
FROM x, y;

Then, from the red_background branch I executed the following in the SQL Shell

$ dolt --branch red_background sql
HibernatePetriDish/red_background> SET @@dolt_allow_commit_conflicts = 1;
HibernatePetriDish/red_background> CALL dolt_merge("main");
+------+--------------+-----------+
| hash | fast_forward | conflicts |
+------+--------------+-----------+
|      | 0            | 1         |
+------+--------------+-----------+
1 row in set (0.03 sec)

HibernatePetriDish/red_background> CALL dolt_conflicts_resolve('--theirs', 'petridish');
HibernatePetriDish/red_background> CALL dolt_add(".");
HibernatePetriDish/red_background> CALL dolt_commit("-m", "Merged `dolt` and `red_background` to fill all empty cells with something");

Sharing a Database

Much earlier in this blog I mentioned the schema's of the table in question and said that I wasn't going to bother telling you the SQL commands used to create those tables. I still haven't told you want they are, but you can run the application and work with the code anyway. That's because by cloning the database you skipped the step of setting up your database. The tables you needed were already there. Their foreign constraints were already defined so you couldn't "break" the data. The benefits of this are only multiplied with a production size database with many tables, indexes, and so forth. We get to skip that step, and get working more quickly. This is real - even when asking other Dolts to try this application out before sharing it they spent more time installing Java than worrying about getting the schema set up correctly in their test.

Sharing personal development branches easily is a huge part of what makes Git valuable, and Dolt does the same for databases. I don't need to list the SQL to create the tables if I can just send you the entire table outright. If you want to know the schemas, they are at your fingertips!

HibernatePetriDish> show create table petridish;
+-----------+--------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                               |
+-----------+--------------------------------------------------------------------------------------------+
| petridish | CREATE TABLE `petridish` (                                                                 |
|           |   `x` int NOT NULL,                                                                        |
|           |   `y` int NOT NULL,                                                                        |
|           |   `species_id` char(36) NOT NULL,                                                          |
|           |   `strength` double NOT NULL,                                                              |
|           |   PRIMARY KEY (`x`,`y`),                                                                   |
|           |   KEY `species_id` (`species_id`),                                                         |
|           |   CONSTRAINT `rut3uhbb` FOREIGN KEY (`species_id`) REFERENCES `species` (`id`),            |
|           |   CONSTRAINT `petridish_chk_dqo5incp` CHECK (((`x` >= 0) AND (`x` < 40))),                 |
|           |   CONSTRAINT `petridish_chk_grccg2qi` CHECK (((`y` >= 0) AND (`y` < 40))),                 |
|           |   CONSTRAINT `petridish_chk_s0rsi9a0` CHECK (((`strength` > 0.0) AND (`strength` <= 1.0))) |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin                           |
+-----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Conclusion

It's probably been about 10 years since I owned a production Hibernate application. I am a little rusty on that topic, but having a database I could kick around like I do with Git made me desperately want a time machine so I could take Dolt back to my past self. Dolt is so light weight to run it just felt easier than managing a virtual machine or Docker image. I strongly suggest anyone looking to start a new project that needs tabular data, use Dolt. You'll have more fun. Come talk to us about what you're building on Discord!

PS

The keen observer will note that there is no limit to the number of species, or Colors, on the board. I ran out of time to take a 40x40 pixel image and loading that into the game. I'm pretty sure this is trivial to pull off with a little more time, but unfortunately I don't have any. Pull Requests Welcome!

PPS

Playing God! Lighting strikes the middle of the board killing everything in a 10 cell radius:

HibernatePetriDish/red_background> DELETE FROM petridish WHERE SQRT((x - 20)*(x - 20) + (y - 20)*(y - 20)) <= 10;