CODEWITHSUNDEEP
sqlitepostgresqlunit-testingsqlalchemynose
user3078608
user3078608

Reputation: 103

How to correctly unit test (using nose) a sqlalchemy Model by creating a new database

I currently have an application using flask-sqlalchemy. My model is connected to a postgresql database, and now I would like to write unit tests (using nose). I was told to use SQLite to create a new database for testing, and after a lot of searching (and looking at the texting section on the flask-sqlalchemy website) I'm still confused as how to do it. Each class in my model.py looks something like the following: 

db = SQLAlchemy(app)
class Prod(db.Model):
   __tablename__ = 'prod'
   id = db.Column(db.Integer, primary_key=True)
   desc = db.Column(db.String)
   def __init__(self, id, desc):
     self.id = id
     self.desc = desc

My config.py:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://name:pass@server/db

and I would like to test my insert functions in a new file by setting up and tearing down a new database for each test. If anyone can give me some example code that would be great. Thanks!

Upvotes: 1

Views: 1204

Answers (1)

jdhildeb
jdhildeb

Reputation: 3811

I can't answer your specific question, but will provide some general advise:

You will find that setting up and tearing down the complete database for each test will be too slow. Imagine in the future when you might have hundreds of tests or even thousands.

The approach we take is:

  1. For testing purposes we have a database populated with test data. We have a script which creates a fresh database and populates it with this test data.
  2. We run this script prior to running our test suite. All tests can assume this data exists.
  3. Each test may create additional records if necessary, but it is their responsibility to undo any changes they make (delete new records, undo changes) - in order words to leave the database in the same state as it was before the test began. This prevents tests from interfering with each other.

In a project I manage we have a test suite of 1070 tests which runs in about 5 minutes using this approach.

What if we had taken your approach? Let's assume that 50% of these tests actually exercise the database (and need a fresh reload). That's 1070 * .50 * 20 seconds for the reload / 3600 = 2.97 hours. Oops - that's far too slow to be useful.

Even at a much smaller scale though, you'll be much happier if your test suite runs in 1 minute instead of 20 minutes.

Upvotes: 2

Related Questions