4

For my database project, I am using SQL Alchemy. I have a unit test that adds the object to the table, finds it, updates it, and deletes it. After it goes through that, I assumed I would call the session.rollback method in order to revert the database changes. It does not work because my sequences are not reverted. My plan for the project is to have one database, I do not want to create a test database.

I could not find in the documentation on SQL Alchemy on how to properly rollback the database changes. Does anyone know how to rollback the database transaction?

ColinMc
  • 1,238
  • 3
  • 16
  • 33
  • 1
    `session.rollback` does rollback all the stmts inside a transaction. Maybe you have created your engine with `autocommit=True`? – lbolla Sep 15 '12 at 18:41
  • 3
    I usually use an in memory sqlite (url: `sqlite:///:memory:`) database for unit tests which is fast enough to be simply recreated from scratch for each unit test. – Pedro Romano Sep 15 '12 at 18:46
  • @Ibolla The session by default is autocommit=False and autoFlush=True. – ColinMc Sep 15 '12 at 18:50
  • 1
    What @Pedro said. You should not use a production (or even development) DB for testing. Use a separate DB for each. See this [answer](http://stackoverflow.com/a/833915/1599111) for an example. – Lukas Graf Sep 15 '12 at 18:50
  • @PedroRomano This is my first time using Python and SQL Alchemy is that the best practice for unit testing databases. The database I am using is Postgres. – ColinMc Sep 15 '12 at 18:51
  • 2
    @ColinMc it is perfectly acceptable to use Postgres for testing, although I would **NOT** recommend using a dev or (heaven help us all) a production instance. sqlite is a popular choice for testing because you don't have to worry about making sure every last thing gets rolled back, because you can run it in-memory (and I'm pretty sure you can do so even if you want to seed it with initial data from an old sqlite db). – Hank Gay Sep 15 '12 at 19:01
  • If you are not using any PostgreSQL specific features your SQLAlchemy models should be mostly DBMS agnostic. Are you defining the model in SQLAlchemy or reflecting (introspecting) an already existing PostgreSQL database? – Pedro Romano Sep 15 '12 at 19:02
  • You do _not_ want to test the database, you want to test your own code. The database has already been tested and is not your responsibility. – Lukas Graf Sep 15 '12 at 19:02
  • @PedroRomano The models are defined in SQL Alchemy. – ColinMc Sep 15 '12 at 19:08
  • Thanks everyone for the input. I am going to use the SQLite for unit testing. :) – ColinMc Sep 15 '12 at 19:09

1 Answers1

-3

Postgres does not rollback advances in a sequence even if the sequence is used in a transaction which is rolled back. (To see why, consider what should happen if, before one transaction is rolled back, another using the same sequence is committed.)

But in any case, an in-memory database (SQLite makes this easy) is the best choice for unit tests.

wberry
  • 18,519
  • 8
  • 53
  • 85
  • Thanks for the explanation. I tried the SQLite in memory database but I cannot because my models are using a sequence for auto-incrementing the primary key and using SQLite it returns a None type and fails the unit tests. – ColinMc Sep 16 '12 at 19:10
  • Look into setting up a sequence for the SQLite tables. http://www.sqlite.org/autoinc.html – wberry Sep 17 '12 at 21:01
  • You might want to pose your SQLite vs Postgres issue as a new question, but the primary key column will be `None` until you commit. After commit the value will be populated in the object. – wberry Sep 19 '12 at 14:50
  • The but sentence at the end is not a good answer. If you are going to use postgres you should test with Postgres as well. There are lots of differences between Postgres and SQLLite that will cause tests to pass in one and not the other. You will also be limiting a lot of your functionality since there are lots of features in Postgres that are not going to be in SQLLite – Michael Robellard Jul 14 '15 at 20:27
  • My opinion is, for unit tests, it's ok to use a different SQL engine. For functional and integration tests, I agree that you need full test/production parity, and there it will be necessary to have the same database engine and schema, same constraints and sequences, etc. But that's far too heavyweight an expectation for unit tests that run at build time on workstations and in your CI setup. – wberry Jul 14 '15 at 21:56
  • I wear these downvotes like a badge of honor. Good luck setting up those Oracle instances for your unit tests! – wberry Aug 13 '15 at 16:39
  • "My opinion is, for unit tests, it's ok to use a different SQL engine. " - The DB APIs for SQLite and PostgreSQL behave differently. Unit tests should not even connect to a database since they are _unit_ tests. – Cochise Ruhulessin Dec 01 '20 at 22:49
  • I agree, the vast majority of code in a Python application will be testable with mocks. Only the data layer will use sqlalchemy calls. But OP is not testing sqlalchemy. Unless the code uses direct SQL, the choice of engine is just configuration. SQLite will be fine for that small subset of unit tests. Integration tests need a production-like DB. – wberry Dec 02 '20 at 16:28
  • There are a small set of operations that have to be coded differently for Postgres and SQLite even when using the ORM layer of sqlalchemy. I've run into those cases. For those, there's really nothing you can do, those methods have to be separated and left untested at the unit level and covered only by integration tests that use Postgres. – wberry Dec 02 '20 at 16:42