pgrestorer 2.1.1
Simple, lightweight tool for postgres database management during integration tests.
License: MIT
Requires Python: >=3.6
Keywords: postgres,integration,sql,tests,restoring,database,psycopg2

# pgrestorer
> Simple, lightweight tool for postgres database management during integration tests.
>
[![Build Status](https://travis-ci.com/pyux/postgres_restorer.svg?branch=master)](https://travis-ci.com/pyux/postgres_restorer) [![codecov](https://codecov.io/gh/pyux/postgres_restorer/branch/master/graph/badge.svg)](https://codecov.io/gh/pyux/postgres_restorer)
 [![PyPI version](https://badge.fury.io/py/pgrestorer.svg)](https://badge.fury.io/py/pgrestorer)
 ![PyPI - Python Version](https://img.shields.io/pypi/pyversions/pgrestorer)
![PyPI - Wheel](https://img.shields.io/pypi/wheel/pgrestorer)
![GitHub](https://img.shields.io/github/license/pyux/postgres_restorer)

**pgrestorer** provides fast way of database test suit management.
 It enables setting up\tearing down databases and resetting/creating schemas before each test. It also
 wraps **psycopg2** enabling database querying, providing logging and
 shell access during tests.
 One instance of **PG_Restorer** encapsulates one database. To create fresh **PG_Restorer** object use factory method **get_new_db(settings_update: Dict)**:
```python
from pgrestorer import get_new_db
```
 If you need to connect to multiple databases, create multiple PG_Restorer instances, one per tested database.

## Installation
```sh
pip install pgrestorer
```

## Quick start

1. Import **get_new_db** factory method:
 ```python
 from pgrestorer import get_new_db
 ```

2. Create fixture/context that you wish to use during testing (**pytest** testing framework used in examples):
```python
@pytest.fixture(scope='module')
def db() -> PG_Restorer:
    with get_new_db(settings_update: Dict) as db:
        yield db
```

3. Accessing database via context (**with** statement) automatically creates test suit with accordance to predefined settings. Test suit teardown (dropping database or/and removing database backup) will be executed automatically after leaving declared context. That behaviour ensures that test suit will be cleaned every time, even if tests raise errors.
If test database already exists it will be dropped and recreated.

4. To reset all data and schemas to state right after setup, use:
```python
db.reset_db()
```
If you want to auto reset db before each test you can set up handy fixture:
```python
@pytest.fixture(autouse=True)
def auto_reset(db: PG_Restorer):
    db.reset_db()
```
# Querying tested database

1. To access querying tool use **db.query** submodule when within database context.

    a) To execute no-return query use:
    ```python
    db.query.execute(query: str, **params)
    ```
   b) To fetch all results:
   ```python
      db.query.fetchall(query: str, **params)
      ```
   c) To fetch only first result:
   ```python
      db.query.fetchone(query: str, **params)
      ```
Every time you access database connection **query** submodule creates and manages for you a dedicated **PG_Connection** instance, so you don't have to worry about disposing it. Every query is automatically *committed* if execution will succeed, or *roll backed* if it won't.

7. If you wish to execute series of queries in single transaction block, open connection by referencing **connection** property directly and use psycopg2 cursor/cursors from it:
```python
with get_new_db() as db:
    with db.query.connection as _conn:
        cursor = _conn.cursor()
        # do with cursor/cursors whatever you wish...
```
All executed within *_conn block* queries will be autocommitted after
**with** statement ends.

## Using shell
If you wish to execute shell commands during tests you can use **shell** submodule:
1. To execute no-return command:
```sh
db.shell.run(*args, **kwargs)
```

2. To execute query returning result (stdout, stderr):
```sh
err, out = db.shell.out(*args, **kwargs)
```

# Settings
1. **pg_config** encapsulates packages settings. Dictionary representation of default settings looks like this:
```python
DEFAULT_SETTINGS = {
    'workdir': os.getcwd(),
    'connection_info': {
        'host': 'localhost',
        'user': 'postgres',
        'password': 'postgres',
        'main_db': 'postgres',
        'cursor_type': CursorType.Default,
        'real_dict_to_dict': False
    },
    'restore_info': {
        'test_db_name': f'test_database.{datetime.now().timestamp()}',
        'restore_type': RestoreType.DBUP_SCRIPTS,
        'test_data_type': DataTestType.FROM_SCRIPT,
        'restore_path': 'dbup_scripts',
        'test_data_path': 'test_data/test_data.sql',
        'delete_autobackup_after_tests': True
    },
    'logging_config': {
        'logger_name': 'pglogger',
        'log_path': 'pg_log.txt',
        'file_logging': False,
        'full_config': {
            'version': 1,
            'formatters': {
                'default': {
                    'format': '%(asctime)s - %(levelname)s - %(message)s', 'datefmt': '%Y-%m-%d %H:%M:%S'
                }
            },
            'handlers': {
                'console': {
                    'level': 'DEBUG',
                    'class': 'logging.StreamHandler',
                    'formatter': 'default',
                    'stream': 'ext://sys.stdout'
                }
            },
            'loggers': {
                'pglogger': {
                    'level': 'DEBUG',
                    'handlers': ['console']
                }
            },
            'disable_existing_loggers': False
        }
    }
}
```

2. Factory method **get_new_db** takes a dictionary as an argument. Dictionary should contain only options that you wish to override, eg:

```python
config_update = {
    'connection_info': {
        'main_db': 'already_existing_db'
    }
}
with get_new_db(**config_update) as db:
    # do stuff
```

3. Settings list:

    <dl>
      <dt>workdir</dt>
      <dd>current working directory</dd>
      <dt>restore_info</dt>
      <dd>
        database restoration options:
        <ol>
            <li>
                <dt>test_db_name</dt>
                <dd>name of test database that should be created</dd>
            </li>
            <li>
                <dt>restore_type</dt>
                <dd>type of database restoration method:
                    <ul>
                        <li>
                            <dt>RestoreType.DBUP_SCRIPTS</dt>
                            <dd>restore from dbup_scripts folder,  default folder structure is <em>"dbup_scripts/subfolders/scripts"</em>, where
            subfolders are usually named like <em>"year.sprint"</em></dd>
                        </li>
                        <li>
                            <dt>RestoreType.SINGLE_SCRIPT</dt>
                            <dd>restore database from single script</dd>
                        </li>
                        <li>
                            <dt>RestoreType.TAR_BACKUP</dt>
                            <dd>restore database from <em>'.tar'</em> backup</dd>
                        </li>
                    </ul>
                </dd>
            </li>
            <li>
                <dt>test_data_type</dt>
                <dd>method of test data insert (relevant only if <em>RestoreType</em> is not <em>TAR_BACKUP</em>):
                    <ul>
                        <li>
                            <dt>DataTestType.FROM_SCRIPT</dt>
                            <dd>insert test data from single sql script</dd>
                        </li>
                        <li>
                            <dt>DataTestType.NONE</dt>
                            <dd>do not insert extra test data</dd>
                        </li>
                    </ul>
                </dd>
            </li>
            <li>
                <dt>restore_path</dt>
                <dd>path to appropriate restore resource (according to <em>RestoreType</em> option)</dd>
            </li>
            <li>
                <dt>test_data_path</dt>
                <dd>path to test data script</dd>
            </li>
            <li>
                <dt>delete_autobackup_after_tests</dt>
                <dd><em>True/False</em>, delete auto created <em>'.tar'</em> backup after test suit concludes (relevant only if not <em>RestoreType.TAR_BACKUP</em>)</dd>
            </li>
        </ol>
      </dd>
      <dt>connection_info</dt>
      <dd>
      postgres server connection options:
        <ol>
            <li>
                <dt>host</dt>
                <dd>host address</dd>
            </li>
            <li>
                <dt>user</dt>
                <dd>database user (should have <em>superuser</em>)</dd>
            </li>
            <li>
                <dt>password</dt>
                <dd>password to <em>'main'</em> database</dd>
            </li>
            <li>
                <dt>main_db</dt>
                <dd>already existing, non-test database, needed for establishing first connection</dd>
            </li>
            <li>
                <dt>cursor_type</dt>
                <dd>
                cursor type for <em>query</em> submodule connections:
                    <ul>
                        <li>
                            <dt>CursorType.Default</dt>
                            <dd>default database cursor, returns <em>tuples</em> as result</dd>
                        </li>
                        <li>
                            <dt>CursorType.RealDictCursor</dt>
                            <dd>cursor returning dictionary-like result encapsulated by <em>RealDictRow</em> <em>psycopg2</em> object</dd>
                        </li>
                    </ul>
                </dd>
            </li>
            <li>
                <dt>real_dict_to_dict</dt>
                <dd><em>True/False</em>, if <em>CursorType.RealDictCursor</em> chosen, results will be converted to dictionary</dd>
            </li>
        </ol>
      </dd>
      <dt>logging_config</dt>
      <dd>
        contains logger options:
        <ol>
            <li>
                <dt>logger_name</dt>
                <dd>name for logger</dd>
            </li>
            <li>
                <dt>log_path</dt>
                <dd>path to logging file, relevant only if <em>file_logging</em> set to <em>True</em></dd>
            </li>
            <li>
                <dt>file_logging</dt>
                <dd>logger logs also to file (just console by default)</dd>
            </li>
            <li>
                <dt>full_config</dt>
                <dd>
                    full dictionary settings for logger from logger package, described in detail in:
                    <a href="https://docs.python.org/3/howto/logging-cookbook.html">python_logging_cookbook</a><br>
                    touch this variable only if you intent to provide full, custom logging configuration
                </dd>
            </li>
        </ol>
      </dd>
    </dl>

Not passing any object to **get_new_db** factory method will instantiate *package* with **DEFAULT_SETTINGS**.

# Errors
1. **PG_Error** -> base error inherited by all others
2. **PG_ConnectionError** -> raised every time error occurs during working with database connection
3. **PG_ClientApplicationsError** -> raised when errors during *postgres client application* usage occur (*pg_dump, pg_restore, createdb, dropdb, etc...*). **Error most often thrown when system path does not include postgres bin folder**
4. **PG_ShellError** -> raised when error during shell command execution occurs.


# Development:
If you have any suggestions, or you wish to expand postgres restorer, clone repository(master branch)
```sh
git clone https://github.com/pyux/postgres_restorer.git
```

Install dependencies:
```sh
pip install -r requirements.txt
```

All interface methods reside in pgrestorer package, **PG_Restorer** object acts as entry point.

You can run tests for currently developed features by running:
```sh
sh tests/tests_running_script.sh
```

## Release History
* 1.0.0
  * First version released

* 2.1.0
  * Major stability improvement
  * Full autodeploy pipeline created

* 2.1.1
  * Hotfixing deployment issue

## License
Distributed under the **MIT License** license. See ``LICENSE`` file for more information.

# Github link
[https://github.com/pyux/postgres_restorer](https://github.com/pyux/postgres_restorer)

## Contributing

1. Fork it (<https://github.com/pyux/postgres_restorer/fork>)
2. Create your feature branch (`git checkout -b feature/fooBar`)
3. Commit your changes (`git commit -am 'Add some fooBar'`)
4. Push to the branch (`git push origin feature/fooBar`)
5. Create a new Pull Request

**Submits and improvement suggestions are most welcome!**