Issue: Sqlite Memory DB gets closed after `with db` context manager
Author: mkmoisen
State: closed
Created: 2022-03-29 00:48:21

Calling `with db` on a SqliteDatabase(':memory:') will end up closing the database after the context manager exits. The code for this is [here](https://github.com/coleifer/peewee/blob/master/peewee.py#L3098).

This will result in destroying the database, such that it will be a brand new database next time it is used.

I think that for in memory `SqliteDatabase` the connection should never be closed unless the user calls `db.close()` explicitly. Actually I'm not sure if it should even be closed in that case. Maybe it would be best to only close on `__del__` ?

Can we modify `SqliteDatabase` to override `__exit__` and avoid calling `self.close()` if it is an in memory database? I'm not sure if there is a way to take a db.connection() and determine if it is in-memory. If not, then the SqliteDatabase constructor could set a flag like `self._in_memory = database == ':memory:'`.

```
from peewee import SqliteDatabase, Model, CharField
from peewee import SQL

# doesn't matter whether you use autoconnect=True or False
db = SqliteDatabase(':memory:', pragmas={'foreign_keys': 'ON'})


class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    email = CharField(max_length=30, null=False, unique=True)

with db:
    db.create_tables([User])
    result = db.execute(SQL("select name from sqlite_schema where type = 'table' and name not like 'sqlite_%'"))
    tables = list(result)
    print(tables)
    assert tables[0] == ('user',)

# The in memory database has now been closed. Next time we call `with db:` there will be no tables:

with db:
    result = db.execute(SQL("select name from sqlite_schema where type = 'table' and name not like 'sqlite_%'"))
    tables = list(result)
    print(tables)
    # The table is now missing
    assert not tables
    
```



In-memory databases are ephemeral and only last for the lifetime of the connection. This is by design.

Hi @coleifer 

In SQLAlchemy it doesn't behave this way. 

ORM style:

```

with Session() as session:
    session.execute(insert(User).values(name='foo'))
    session.commit()


with Session() as session:
    result = session.execute(select(User))
    print(result.scalars().one().name)

```

Core style:

```
with engine.connect() as conn:
    conn.execute(insert(User).values(name='foo'))
    conn.commit()

with engine.connect() as conn:
    result = conn.execute(select(User))
    print(result.fetchall()[0])
```

SQLAlchemy doesn't event destroy the DB when you call `close`:

```

from sqlalchemy import create_engine, text

engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)

conn = engine.connect()

conn.execute(text('create table foo (c1 int)'))
conn.execute(text("insert into foo values (1)"))
conn.commit()

conn.close()

conn = engine.connect()
conn.execute(text('select c1 from foo')).fetchall()
```

I think this approach makes sense. It is common to use Sqlite in memory for testing.

If the behavior is to "actually" close the database this makes it all but impossible to use in-memory sqlite for this use case.

E.g. if you are using the flask request style:

```
@app.before_request
def before_request():
    database.connect()

@app.after_request
def after_request(response):
    database.close()
    return response
```

This works fine for all databases, except for SQLite in memory.

There are other gotcha's when using an in-memory sqlite database, such as that it is specific to the connection, so it doesn't work in multi-thread environments either (based on how Peewee by default uses thread-per-connection (although this can be overridden, see threadsafe param [here](http://docs.peewee-orm.com/en/latest/peewee/api.html#Database))).

As I said, this is by design. Many other testers assume, when using in-mem db, that it starts as a blank slate every time. If you want `close()` to no-op, then just subclass and override. Alternatively use a named temporary file instead of an in-memory db.