consbio/pg-database-utils

A suite of utilities for PostgreSQL database queries and operations built on sqlalchemy

pg-database-utils's Issues

Support connection options

It would be helpful to support connection options so that SSL connections could be made.

Here's an example of using connect_args in SQLAlchemy:

db = create_engine('postgresql+pg8000://user:pass@hostname/dbname', connect_args={'sslmode':'require'})

On the Django side, these should come from the [OPTION](https://docs.djangoproject.com/en/3.0/ref/settings/#std:setting-OPTIONS) param in the DATABASE settings. For example:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': "db_name",
        'USER': "db_username",
        'PASSWORD': "db_password",
        'HOST': "db_host",
        'OPTIONS': {'sslmode': 'require'},
    },
}

Add CASCADE option for drop_table

Not as simple as it may seem. Requires something like:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import DropTable

@compiles(DropTable, "postgresql")
def _compile_drop_table(element, compiler, **kwargs):
    return compiler.visit_drop_table(element) + " CASCADE"

https://stackoverflow.com/questions/38678336/sqlalchemy-how-to-implement-drop-table-cascade

`get_engine` fails when using GeoDjango database engine

In order to use spatially-enabled Django models, the Django DATABASE settings must use django.contrib.gis.db.backends.postgis as the engine. However, pg-database-utils then requests a postgis dialect from SQLAlchemy, which fails with:

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgis

Since this library is specific to postgresql databases, is it even necessary to get the engine/dialect from settings? Couldn't 'postgres` be hard-coded?

Add support for SQLAlchemy 1.4

The most recent version of SQLAlchemy is 1.4.32. This library excludes 1.4 with it's dependency spec: sqlalchemy = "1.3.*". I'm not sure if there is code that would need to be changed to support 1.4, or if updating the dependency would be enough.