How to add a POSTGIS extension in a test database ?

[AasheeshT]

I am writing unit test for a function that makes a database connection and retrieves the SRID and Geometry type from a Postgres database ( having POSTGIS extension ) , How can i make an instance with a PostGIS extension?

[cleppanen]

installing PostGIS can be done in code. Here is a snippet from how we've achieved this. tmp_database is another fixture that does the initialization of the postgres database.

def tmp_postgis_db_(tmp_database):
    """
    This fixture initialize the postgis extension in the temporary database
    """

    # Creates a connection with autocommmit to save us from a few lines.
    conn = connect(url=tmp_database.url, autocommit=True)
    cur = conn.cursor()
    cur.execute("CREATE EXTENSION postgis")
    cur.close()
    conn.close()

    yield tmp_database

[robertlayton]

Posting here for those coming from google. The code above works, but if you are using different database connection code, ensure you commit after the CREATE EXTENSION postgis line.

For example, using sqlalchemy:

        with db.engine.connect() as connection:  # or however you do it
            connection.execute(text("CREATE EXTENSION postgis;"))
            connection.commit()  # Very important!