Auto Generate Mysql Tables Startup Spring Data

Why would you want to auto-generate MySQL tables on startup with Spring Data? A lot of times, I will run into a project and see this “smart” way to automate schema and table creation where there is either a sidecar that executes a bash script or even worse, have it in the Dockerfile. As tempting as that hack is, it is totally unnecessary as we have everything we need to do that with out of the box. This is very useful setup as you want your applications to be self sufficient and don’t have to worry about making changes at two places (application and the database).

This applies to applications that don’t have a complex data management or data structure. For that, I might advise you go with Liquibase or Flyway as that offers a quite wide spectrum of features and options for keeping your DB schemas and tables in check.

But for managing small databases that are designed with no dependencies and a simple setup, this will work out quite neat. Let’s get into it!

As our example, we have a simple CRUD application that handles a very important business domain – Cats. Here is how our domain entity looks like (and we will also expose this entity to the end client for simplicity, but you should never do that in production due to separation of concerns):

@Entity
public class Cat {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", columnDefinition = "BIGINT(20)")
    private Long id;

    @Column(name = "name", columnDefinition = "VARCHAR(45)")
    private String name;

    @Column(name = "age", columnDefinition = "INT(4)")
    private int age;

    @Column(name = "color", columnDefinition = "VARCHAR(20)")
    private String color;

    @Enumerated(EnumType.STRING)
    @Column(name = "favorite_food")
    private FavoriteFood favoriteFood;

    @Column(name = "eye_color", columnDefinition = "VARCHAR(45)")
    private String eyeColor;

  //constructors, getters and setters

Important thing to notice is that we can have full control over the configuration of the fields here. Notice the @Column annotation and it’s options, you can see all the things that can be tweaked.

In our DAO layer, we have a simple CrudRepository implementation

public interface CatRepository extends CrudRepository<Cat, Long> {

}

And finally we have a controller with the usual CRUD methods

@RestController
public class CatController {

    final private static Logger LOG = LoggerFactory.getLogger("CatController");

    private CatRepository repository;

    public CatController(CatRepository repository) {
        this.repository = repository;
    }

    @GetMapping("/get")
    public Cat get(final Long id) {
        return repository.findById(id).orElseThrow(EntityNotFoundException::new);
    }

    @PostMapping("/create")
    @ResponseStatus(HttpStatus.CREATED)
    public Cat create(@RequestBody final Cat cat) {
        return repository.save(cat);
    }

    @PutMapping("/update/{id}")
    public Cat update(@RequestBody final Cat newCat, @PathVariable Long id) {
        if (repository.existsById(id)) {
            newCat.setId(id);
            return repository.save(newCat);
        } else {
            throw new EntityNotFoundException();
        }
    }

    @DeleteMapping("/delete/{id}")
    @ResponseStatus(HttpStatus.NO_CONTENT)
    public void delete(@PathVariable final Long id) {
        final Cat cat = repository.findById(id).orElseThrow(EntityNotFoundException::new);
        repository.delete(cat);
    }

}

But the important part to auto-generate a MySQL table with Spring Data is our application.properties file that holds all the magic to create our schema and table upon startup.

# Mysql
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# createDatabaseIfNotExist=true is the important part of the connection string below
spring.datasource.url=jdbc:mysql://localhost:3306/cat_schema?useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root

# Hibernate
spring.datasource.platform=mysql
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update

spring.data.jpa.repositories.enabled=true

So we create the schema directly in our connection string if it doesn’t exist and with the Hibernate configuration, we enable the automatic update of our tables (spring.jpa.hibernate.ddl-auto=update). The rest of the config is just setting the driver, dialect, platform and enabling our spring data repository.

For running our database we can use Docker for simplicity:

docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:5.7

Basically, we are running a mysql container detached (-d) exposing the port 3306 (-p 3306:3306) with an environment variable for the password (-e).

If we compile and run our application, we will not see anything special in the logs because all is being done internally by Hibernate but if we connect to our database, we will see our schema and table properly setup:

Here, we are connecting locally from within the docker container itself:

docker exec -it mysql bash

If we want to change something to the table, we could simply just edit our entity class and the database would be updated next time we connect to it. Let’s add a new field:

  @Column(name = "favorite_toy", columnDefinition = "VARCHAR(45)")
  private String favoriteToy;

Now, when we restart our application, it will update the table automatically:

That’s all there is to it if you want to generate a MySQL table with Spring Data. You can find the code in the examples here and play around or check out some of the other tutorials.

Keep on hacking!