Issue: string_agg / group_concat in a multi-engine environment
Author: RickMeasham
State: closed
Created: 2020-12-02 07:18:58

I'm running postgres in public, but my unit tests are running sqlite. 

postgres provides `string_agg(field, 'joiner')` while sqlite (and mysql) has `group_concat(field, 'joiner')`

Therefore any queries I write with `fn.GROUP_CONCAT` only work in testing, and any I write with `fn.string_agg` only work in production.

To solve this I have tried a number of things, the most sane two are below.

## 1. Check what engine we are using:
```
group_agg = fn.string_agg if isinstance(database, PostgresqlExtDatabase) else fn.GROUP_CONCAT
Model.select( group_agg( Model.field, ', ' ) ).where( ... )
```

But `isinstance` returns true even when the database is `SqliteExtDatabase`.

Is there a way to get the database engine reliably?

## 2. Create a custom aggregation function for SQLite 

This means that `fn.string_agg` exists in both databases. Postgres naturally, and SQLite via the custom function below.

```
@test_db.aggregate('string_agg')
class StringAgg(object):
    def __init__(self):
        print("Here A")
        self.string_agg = []

    def step(self, value):
        print("Here B")
        self.string_agg.append(value)

    def finalize(self):
        return ', '.join(self.string_agg)
```

However, this results in an exception:
```peewee.OperationalError: user-defined aggregate's 'step' method raised error```

Even if I wrap the method in a `try` I still get the above exception.

## Questions

* Is there a way to get the database engine?
* What is wrong with the user-defined aggregate's 'step' method?
* Is there a better way to do this? (And once I work it out, is there any interest in a patch so that there's a way to do a string aggregation in all supported engines without knowing/caring what engine it is)








>But isinstance returns true even when the database is SqliteExtDatabase

That doesn't sound correct to me:

```python
In [1]: from playhouse.sqlite_ext import SqliteExtDatabase

In [2]: from playhouse.postgres_ext import PostgresqlExtDatabase

In [3]: d1 = SqliteExtDatabase(':memory:')

In [4]: d2 = PostgresqlExtDatabase('peewee_test')

In [5]: isinstance(d1, PostgresqlExtDatabase)
Out[5]: False

In [6]: isinstance(d2, SqliteExtDatabase)
Out[6]: False
```

Re: custom aggregate, it seems working fine for me:

```python
@db.aggregate('string_agg')
class StringAgg(object):
    def __init__(self):
        self._data = []

    def step(self, value):
        self._data.append(value)

    def finalize(self):
        return ', '.join(self._data)

class Reg(Model):
    key = TextField()
    class Meta:
        database = db

db.create_tables([Reg])

Reg.insert_many([{'key': 'k%02d' % i} for i in range(10)]).execute()

print(Reg.select(fn.string_agg(Reg.key)).scalar())
# prints:
#k00, k01, k02, k03, k04, k05, k06, k07, k08, k09
```

So, in short, I don't know how to advise you because I cannot reproduce either issue you're describing.

To follow up, the problem in solution one was that `database` is the initialised database, but my tests are wrapped in a closure. The solution was to use `Model._meta.database` so I had the actual database instance being used. That returned the right answer for `isinstance`.

As for the second problem, I'm still not sure why it wasn't working for me, but I ended up not using a string aggregation anyway.