Automating database creation for testing

Question

For speedier testing it's nicer to use a memory-based sqlite, but it's still necessary once in a while to use MySQL for testing that more closely matches production. To avoid a dry discussion/abstract question, the code below inserts a couple of words and confirms they're in the database, for both types of SQL databases just mentioned.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import unittest


db = SQLAlchemy()
TEST_DATABASE_URL_MEMORY = 'sqlite:///:memory:'
TEST_DATABASE_URL_MYSQL = 'mysql+pymysql://root:@127.0.0.1:3306/somewords'


def create_app(db_url):
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = db_url
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    db.init_app(app)
    return app


class Word(db.Model):
    __tablename__ = 'words'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    word = db.Column(db.String(32), index=True)


class TestInsertion(unittest.TestCase):
    def manual_set_up(self, db_url):
        self.app = create_app(db_url)
        self.app_context = self.app.app_context()
        self.app_context.push()
        db.drop_all()
        db.create_all()

    def insert(self):
        words = ['hello', 'world']
        for word in words:
            w = Word(word=word)
            db.session.add(w)
        db.session.commit()
        for word in words:
            assert Word.query.filter_by(word=word).first() is not None

    def test_dbs(self):
        for db_url in [TEST_DATABASE_URL_MEMORY,
                       TEST_DATABASE_URL_MYSQL]:
            self.manual_set_up(db_url)
            self.insert()

The first (sqlite) passes. The second fails:

E       sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1049, "Unknown database 'somewords'")

We can, everytime we run the tests, create the database

> mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> create database somewords;
Query OK, 1 row affected (0.02 sec)
mysql> quit;

but this precludes automated testing, and so I'm guessing I'm missing something basic.

How can a test such as the one above be run unattended, by automating the creation of the database?

Update

The test examples for 1.0, 1.1, and the tutorial (flask/examples/tutorial/tests/conftest.py) use tempfile.mkstemp(), which seems like a nice way to go. One needs neither to worry about setting the name and creating the database, nor even to care about the (random and discardable) db name. How/where is the database creation part done?

import tempfile
db_fd, db_path = tempfile.mkstemp()

Answer 1

With some modification to manual_set_up and test_dbs I was able to run code.

For mysql database, I remove database name from the db_url. And db.drop_all() also fails because the database does not exist so I put in try/except and pass the exception here. Then before db.create_all() I create a sqlachemy engine bypassing db_url which does not have database name db.create_engine(db_url).

# your imports ...

import sqlalchemy.exc

#...
TEST_DATABASE_URL_MYSQL = 'mysql+pymysql://root:@127.0.0.1:3306/'

def manual_set_up(self, db_url, db_kind, db_name=None):
    if db_kind == "mysql":
        self.app = create_app(db_url + db_name)
    else:
        self.app = create_app(db_url)
    self.app_context = self.app.app_context()
    self.app_context.push()
    try:
        db.drop_all()
    except sqlalchemy.exc.InternalError as e:
        if "unknown database" in str(e.args[0]).lower():
            pass
    try:
        db.create_all()
    except sqlalchemy.exc.InternalError as e:
        if "unknown database" in str(e.args[0]).lower():
            db.create_engine(db_url, {}).execute(f"CREATE DATABASE IF NOT EXISTS {db_name};")
            db.create_all()

def test_dbs(self):
    for args in [(TEST_DATABASE_URL_MEMORY, "sqlite"),
                 (TEST_DATABASE_URL_MYSQL, "mysql", "somewords")]:
        self.manual_set_up(*args)
        self.insert()

Answer 2

Creating and dropping databases doesn't seem to be directly supported by sqlalchemy, but it is supported by sqlalchemy-utils: https://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/functions/database.html#create_database.

This will require you to already have a mysql server running, so it won't allow you to control the file placement with tempfile.mkstemp().

Another approach would be to run the mysql command as part of the automated testing.