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()

score 3 · Accepted Answer · answered Aug 18 '19 at 13:46

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()

This solution is nearly perfect, but if you want to quibble, you'll find room to do so. 1- Using a bare SQL statement breaks the spirit of SQLAlchemy. Aaron's solution may help here. 2- Hardcoding the database name precludes that it be used for another purpose. One solution for the latter is to pick a random string for the name (and perhaps skip that name if it exists, or else use an outlandishly long random string). — Calaf, Aug 18 '19 at 18:33

score 1 · Answer 2 · answered Aug 15 '19 at 18:32

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.

Automating database creation for testing

2 Answers2