Testing a RESTful API with a database

Play Framework
1

Hi,

I created a RESTful API and I’d like to write tests for it. Each request to the API need an API key set in the header as a X-Api-Key field.

I wrote a class to be able to use an in-memory database with a default user:

public class WithDatabase extends WithApplication
{
    private static Database database;

    @BeforeClass
    public static void setupDatabase() throws SQLException
    {
        database = Databases.inMemory( "test",
        ImmutableMap.of(
            "MODE", "MYSQL"
        ),
        ImmutableMap.of(
            "logStatements", true
        ));
        Evolutions.applyEvolutions(database);

        StringJoiner values = new StringJoiner(", ");
        values.add("1");
        values.add("John");
        values.add("Doe");

        database.getConnection()
            .prepareStatement("INSERT INTO `users` VALUES(" + values.toString() + ")")
            .execute();
    }

    @AfterClass
    public static void shutdownDatabase()
    {
        Evolutions.cleanupEvolutions(database);
        database.shutdown();
    }
}

and to use it for example:

public class UserControllerTest extends WithDatabase
{
}

But when I run the test, it says:

Test UserControllerTest failed: org.h2.jdbc.JdbcSQLException: Table “USERS” not found;

It means that Evolutions.applyEvolutions(database) does nothing or is never actually executed?

2

To move forward on this issue, I decided to create a real database because in-memory database seems bugged.

I’ve created a test database with the right tables. To be able to repeat tests, I want to empty all the tables at the end of the tests. Here is the code:

import com.google.common.collect.ImmutableMap;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import play.db.Database;
import play.db.Databases;
import play.test.WithApplication;

import java.sql.SQLException;
import java.util.StringJoiner;

public class WithDatabase extends WithApplication
{
    private static final String[] TABLES =
    {
        "activities",
        "auth_tokens",
        "buildings",
        "devices",
        "events",
        "link_tokens",
        "machines",
        "packages",
        "package_tasks",
        "package_tasks_devices",
        "planning",
        "preferences",
        "reports",
        "units",
        "users"
    };

    private static Database database;

    @BeforeClass
    public static void setupDatabase() throws SQLException
    {
        database = Databases.createFrom(
            "test",
            "org.mariadb.jdbc.Driver",
            "jdbc:mariadb://localhost/test",
            ImmutableMap.of(
                "username", "root",
                "password", "123456"
            )
        );

        StringJoiner values = new StringJoiner(", ");
        values.add("1");
        values.add("John");
        values.add("Doe");

        database.getConnection()
            .prepareStatement("INSERT INTO `users` VALUES(" + values.toString() + ")")
            .execute();
    }

    @AfterClass
    public static void shutdownDatabase() throws SQLException
    {
        StringBuffer statement = new StringBuffer();
        statement.append("SET FOREIGN_KEY_CHECKS=0;\n");
        for (int i = 0; i < TABLES.length; i++)
        {
            statement.append("TRUNCATE `" + TABLES[i] + "`;\n");
        }
        statement.append("SET FOREIGN_KEY_CHECKS=1;");

        database.getConnection()
            .prepareStatement(statement.toString())
            .executeUpdate();

        database.shutdown();
    }
}

It disables the foreign key check and truncates each table.
When I run a test, it throws a SQLException:

Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATE `activities`;
[error] TRUNCATE `auth_to' at line 2
[error] Query is: SET FOREIGN_KEY_CHECKS=0;
[error] TRUNCATE `activities`;
[error] TRUNCATE `auth_tokens`;
[error] TRUNCATE `buildings`;
[error] TRUNCATE `devices`;
[error] TRUNCATE `events`;
[error] TRUNCATE `link_tokens`;
[error] TRUNCATE `machines`;
[error] TRUNCATE `packages`;
[error] TRUNCATE `package_tasks`;
[error] TRUNCATE `package_tasks_devices`;
[error] TRUNCATE `planning`;
[error] TRUNCATE `preferences`;
[error] TRUNCATE `reports`;
[error] TRUNCATE `units`;
[error] TRUNCATE `users`;
[error] SET FOREIGN_KEY_CHECKS=1;

But there is no syntax error.
Here is the full request:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `activities`;
TRUNCATE `auth_tokens`;
TRUNCATE `buildings`;
TRUNCATE `devices`;
TRUNCATE `events`;
TRUNCATE `link_tokens`;
TRUNCATE `machines`;
TRUNCATE `packages`;
TRUNCATE `package_tasks`;
TRUNCATE `package_tasks_devices`;
TRUNCATE `plannings`;
TRUNCATE `preferences`;
TRUNCATE `reports`;
TRUNCATE `units`;
TRUNCATE `users`;
SET FOREIGN_KEY_CHECKS=1;

When I execute it in phpMyAdmin everything works fine.

3

Nobody tests with a database?