in-memory sqlite odd behavior #75

Lawouach · 2019-04-02T15:19:39Z

Hi,

Thanks for the awesome package (happy to use it with starlette). For rapid dev purpose, I'm relying on the SQLite backend and I see an odd behavior.

When I create tables with an in-memory db, those tables can never be found in sqlite_master afterwards. But when I rely on a file database, then that works fine.

Is there any gotcha for in-memory sqllite I should be aware of?

Cheers,

tomchristie · 2019-04-02T15:36:28Z

Well, in memory will only be persisted for the duration of the python session.

You might also want to double check against what behaviour sqlite3 has. Does “creating the tables” for an in memory DB actually do anything?

tomchristie · 2019-04-02T15:37:59Z

I’m going to close this off - if you thing there’s a active issue, and (ideally) can build a test case for it then we could reconsider.

Lawouach · 2019-04-02T15:43:31Z

I meant within a python session.
Inside a given Python session, when using in-memory, the tables don't show up once created.

Does “creating the tables” for an in memory DB actually do anything?

I'm not sure if you are being sarcastic or actually asking a question.

Fair enough. Leave it close.

tomchristie · 2019-04-02T15:46:35Z

I’m not. I’d suggest start by comparing sqlite3’s behaviour vs ours. If you’ve double checked that it’s definitely different, and can give a minimal replicable example then i’ll happily reopen and put my time into it. I’d just like to have a clear confirmation that there’s an issue this side, before commiting time in.

Lawouach · 2019-04-02T15:58:50Z

Here is an example of what I mean:


from databases import Database
import sqlalchemy


metadata = sqlalchemy.MetaData()

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.String(length=100)),
    sqlalchemy.Column("text", sqlalchemy.String(length=100))
)

database = Database('sqlite:///:memory:')


async def run():
    await database.connect()

    await database.execute("""
    CREATE TABLE notes (
        id CHAR(32) NOT NULL, 
        user_id CHAR(100) NOT NULL
    );""")

    r = await database.fetch_all("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")

    # prints an empty list when in-memory, otherwise it shows the notes table
    print(r)

    await database.disconnect()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(run())
    loop.close()

If you switch to a file db, then the table will be printed. In-memory will not have the table listed.

On the other hand, plain sqlite3 works as expected:

import asyncio
import sqlite3


def run():

    conn = sqlite3.connect(':memory:')
    c = conn.cursor()

    c.execute("""
    CREATE TABLE notes (
        id CHAR(32) NOT NULL, 
        user_id CHAR(100) NOT NULL
    );""")
    c.close()

    c = conn.cursor()
    c.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    r = c.fetchall()
    c.close()

    # prints an empty list when in-memory, otherwise it shows the notes table
    print(r)


if __name__ == '__main__':
    run()

tomchristie · 2019-04-02T16:42:56Z

Gotcha, thanks!

tomchristie · 2019-04-02T16:48:35Z

Presumably something like create table not being effected until the connection is closed? It’s be worth trying to dig into exactly what set of operations are being performed against the underlying driver, and also worth checking if you can replicate in aiosqlite directly (or if their project has any issues similar to this)

Lawouach · 2019-04-02T17:33:24Z

Indeed. I wil try to see what I can dig up tomorrow.

Lawouach · 2019-04-04T08:59:48Z

This seems to play out as expected:

import asyncio
import aiosqlite


async def run():
   async with aiosqlite.connect(':memory:') as db:
       await db.execute("""
       CREATE TABLE notes (
           id CHAR(32) NOT NULL, 
           user_id CHAR(100) NOT NULL
       );""")

       async with db.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name") as cursor:
           async for row in cursor:
               print(row)


if __name__ == '__main__':
   loop = asyncio.get_event_loop()
   loop.run_until_complete(run())
   loop.close()

does output the table name.

I tried the idea of disconnecting and coinnecting again with databases:

import asyncio
from databases import Database
import sqlalchemy


metadata = sqlalchemy.MetaData()

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.String(length=100)),
    sqlalchemy.Column("text", sqlalchemy.String(length=100))
)

database = Database('sqlite:///:memory:')


async def run():
    await database.connect()

    await database.execute("""
    CREATE TABLE notes (
        id CHAR(32) NOT NULL, 
        user_id CHAR(100) NOT NULL
    );""")

    await database.disconnect()

    await database.connect()
    r = await database.fetch_all("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")

    # prints an empty list when in-memory, otherwise it shows the notes table
    print(r)

    await database.disconnect()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(run())
    loop.close()

But with no luck.

tomchristie · 2019-04-04T09:28:26Z

Okay - that's really strange.

I guess the next level would be to get a debug log of exactly what sequence of calling into the aiosqlite API we end up with. If it's not replicating with aiosqlite directly, then we kinda need to figure out exactly how we're using their API differently, than in the direct case.

Lawouach · 2019-04-04T09:48:35Z

Yeah, I cannot see that jumps out of the ordinary in the code.

ryananguiano · 2019-04-15T22:43:48Z

@Lawouach The disconnect and connect calls in between db calls would give you an empty db if you are using in-memory.

    await database.disconnect()

    await database.connect()

Lawouach · 2019-04-16T05:59:02Z

Agreed. However that's not the initial case I reported. It was an hypothesis.

reillysiemens · 2019-04-24T01:09:32Z

@tomchristie: I ran into this issue recently with a co-worker. Hopefully this debug output is helpful to you.

Using in-memory SQLite with `databases`

Here's a simple example using databases for in-memory table creation, inserting values into said table, and then retrieving them.

import asyncio
import logging

import databases

logging.basicConfig(level=logging.DEBUG)

db = databases.Database('sqlite:///:memory:')


async def main():
    await db.connect()
    await db.execute('CREATE TABLE foo(bar INTEGER);')
    await db.execute('INSERT INTO foo VALUES (1), (2), (3);')
    async for row in db.iterate('SELECT * FROM foo;'):
        print(row)

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

Here's the debug output from the resulting failure:

DEBUG:asyncio:Using selector: EpollSelector
DEBUG:aiosqlite:executing functools.partial(<function connect.<locals>.connector at 0x7fe42467e598>)
DEBUG:aiosqlite:returning <sqlite3.Connection object at 0x7fe4254b48f0>
DEBUG:databases:Query: CREATE TABLE foo(bar INTEGER);
Args: []
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Connection object at 0x7fe4254b48f0>, 'CREATE TABLE foo(bar INTEGER);', [])
DEBUG:aiosqlite:returning <sqlite3.Cursor object at 0x7fe424bd2b20>
DEBUG:aiosqlite:executing functools.partial(<built-in method close of sqlite3.Cursor object at 0x7fe424bd2b20>)
DEBUG:aiosqlite:returning None
DEBUG:aiosqlite:executing functools.partial(<built-in method close of sqlite3.Connection object at 0x7fe4254b48f0>)
DEBUG:aiosqlite:returning None
DEBUG:aiosqlite:executing functools.partial(<function connect.<locals>.connector at 0x7fe42467e6a8>)
DEBUG:aiosqlite:returning <sqlite3.Connection object at 0x7fe424c03c70>
DEBUG:databases:Query: INSERT INTO foo VALUES (1), (2), (3);
Args: []
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Connection object at 0x7fe424c03c70>, 'INSERT INTO foo VALUES (1), (2), (3);', [])
ERROR:aiosqlite:returning exception no such table: foo
Traceback (most recent call last):
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/aiosqlite/core.py", line 153, in run
    result = function()
sqlite3.OperationalError: no such table: foo
DEBUG:aiosqlite:executing functools.partial(<built-in method close of sqlite3.Connection object at 0x7fe424c03c70>)
DEBUG:aiosqlite:returning None
Traceback (most recent call last):
  File "fails.py", line 20, in <module>
    loop.run_until_complete(main())
  File "/usr/lib64/python3.6/asyncio/base_events.py", line 468, in run_until_complete
    return future.result()
  File "fails.py", line 14, in main
    await db.execute('INSERT INTO foo VALUES (1), (2), (3);')
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/databases/core.py", line 122, in execute
    return await connection.execute(query, values)
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/databases/core.py", line 204, in execute
    return await self._connection.execute(self._build_query(query, values))
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/databases/backends/sqlite.py", line 112, in execute
    cursor = await self._connection.execute(query, args)
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/aiosqlite/core.py", line 209, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/aiosqlite/core.py", line 167, in _execute
    return await future
  File "/home/rsiemens/.virtualenvs/databases/lib/python3.6/site-packages/aiosqlite/core.py", line 153, in run
    result = function()
sqlite3.OperationalError: no such table: foo

Using in-memory SQLite with `aiosqlite`

Here's my best attempt at reproducing the databases code above using plain aiosqlite. It's possible I've done something that doesn't map perfectly as I'm still learning both codebases.

import asyncio
import logging

import aiosqlite

logging.basicConfig(level=logging.DEBUG)


async def main():
    async with aiosqlite.connect(':memory:') as db:
        await db.execute('CREATE TABLE foo(bar INTEGER);')
        await db.execute('INSERT INTO foo VALUES (1), (2), (3);')
        async with db.execute('SELECT * from foo;') as cursor:
            async for row in cursor:
                print(row)


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

Here's the debug output from the resulting success:

DEBUG:asyncio:Using selector: EpollSelector
DEBUG:aiosqlite:executing functools.partial(<function connect.<locals>.connector at 0x7f94510a81e0>)
DEBUG:aiosqlite:returning <sqlite3.Connection object at 0x7f9455711f10>
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Connection object at 0x7f9455711f10>, 'CREATE TABLE foo(bar INTEGER);', [])
DEBUG:aiosqlite:returning <sqlite3.Cursor object at 0x7f945112b810>
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Connection object at 0x7f9455711f10>, 'INSERT INTO foo VALUES (1), (2), (3);', [])
DEBUG:aiosqlite:returning <sqlite3.Cursor object at 0x7f945112b7a0>
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Connection object at 0x7f9455711f10>, 'SELECT * from foo;', [])
DEBUG:aiosqlite:returning <sqlite3.Cursor object at 0x7f945112b880>
DEBUG:aiosqlite:executing functools.partial(<built-in method fetchone of sqlite3.Cursor object at 0x7f945112b880>)
DEBUG:aiosqlite:returning (1,)
(1,)
DEBUG:aiosqlite:executing functools.partial(<built-in method fetchone of sqlite3.Cursor object at 0x7f945112b880>)
DEBUG:aiosqlite:returning (2,)
(2,)
DEBUG:aiosqlite:executing functools.partial(<built-in method fetchone of sqlite3.Cursor object at 0x7f945112b880>)
DEBUG:aiosqlite:returning (3,)
(3,)
DEBUG:aiosqlite:executing functools.partial(<built-in method fetchone of sqlite3.Cursor object at 0x7f945112b880>)
DEBUG:aiosqlite:returning None
DEBUG:aiosqlite:executing functools.partial(<built-in method close of sqlite3.Cursor object at 0x7f945112b880>)
DEBUG:aiosqlite:returning None
DEBUG:aiosqlite:executing functools.partial(<built-in method close of sqlite3.Connection object at 0x7f9455711f10>)
DEBUG:aiosqlite:returning None

micmarty · 2019-12-17T11:48:51Z

I experience exactly the same issue. Although this repo is awesome 🚀 , I think that in-memory databases are essential for unit testing.

Have you any progress on that (maybe some code or tips)? Otherwise I would have to go with file database 🐿️

tomchristie · 2019-12-17T11:59:07Z

No extra feedback here, no. 🤷‍♂️

Lawouach · 2019-12-17T12:54:08Z

Me neither. I went the route of file backend and that's okay for my tests I guess.

BookGin · 2020-01-10T03:34:10Z

+1 for experiencing the same issue.

brendanlong · 2020-04-26T19:38:10Z

The problem is that Database.connect opens a new connection for each query:

    async def execute(
        self, query: typing.Union[ClauseElement, str], values: dict = None
    ) -> typing.Any:
        async with self.connection() as connection:
            return await connection.execute(query, values)

So once the statement finishes, the connection is closed and the in-memory DB is deleted:

import asyncio

from databases import Database

async def example():
    async with Database("sqlite://") as db:
        await db.execute("CREATE TABLE Example(id INT)")
        await db.fetch_all("SELECT * FROM Example") # sqlite3.OperationalError: no such table: Example

asyncio.run(example())

To fix this, you need to share a single Connection:

import asyncio

from databases import Database

async def example():
    async with Database("sqlite://") as db:
        async with db.connection() as conn:
            await conn.execute("CREATE TABLE Example(id INT)")
            res = await conn.fetch_all("SELECT * FROM Example")
            assert res == []

asyncio.run(example())

Once #196 is done it should be possible to share in-memory databases using cache=shared.

brendanlong · 2020-04-26T20:02:18Z

Oh, and for testing purposes, the workaround I'm using is a file database in a temp directory:

@pytest.fixture
async def db(tmpdir: Path) -> AsyncIterator[Database]:
    # Need a file DB since we can't currently share memory DB's between connections
    db_path = tmpdir / "example.db"
    async with Database(f"sqlite:///{db_path}") as db:
        yield db

yingshaoxo · 2020-06-01T14:24:06Z

await database.execute("""
CREATE TABLE notes (
    id CHAR(32) NOT NULL, 
    user_id CHAR(100) NOT NULL
);""")

Hello there!

I have noticed you have used codes like this:

    await database.execute("""
    CREATE TABLE notes (
        id CHAR(32) NOT NULL, 
        user_id CHAR(100) NOT NULL
    );""")

But you have already defined your table with:

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.String(length=100)),
    sqlalchemy.Column("text", sqlalchemy.String(length=100))
)

So I'd like to ask you a question, can the sqlalchemy automatically generate the table creating commands in sqlite for us?

I don't think it's a smart thing to create the table again with low level sql commands.

I hope to see a clear answer to it.

yingshaoxo · 2020-06-02T05:59:29Z

await database.execute("""
CREATE TABLE notes (
    id CHAR(32) NOT NULL, 
    user_id CHAR(100) NOT NULL
);""")
Hello there!

I have noticed you have used codes like this:
    await database.execute("""
    CREATE TABLE notes (
        id CHAR(32) NOT NULL, 
        user_id CHAR(100) NOT NULL
    );""")
But you have already defined your table with:
notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.String(length=100)),
    sqlalchemy.Column("text", sqlalchemy.String(length=100))
)
So I'd like to ask you a question, can the sqlalchemy automatically generate the table creating commands in sqlite for us?

I don't think it's a smart thing to create the table again with low level sql commands.

I hope to see a clear answer to it.

No Bother, No need to answer me, I just figured out how to work with databases and sqlalchemy and sqlite3, here is the link, hope it helps for new beginners:

https://yingshaoxo.blogspot.com/2020/06/how-to-work-with-databases-and.html

This includes the initial migration to create the table and minimal setup of the tests to have the database around. Ideally the test database would be in memory, however there seem to be issues within the databases library around that (encode/databases#75).

frjonsen · 2020-08-17T09:31:42Z

I would suggest that this issue be reopened. The link in the reply from @yingshaoxo uses an file databases, and as such doesn't actually resolve this.

With sqlite I would run db_model.Base.metadata.create_all(engine), but I can't think of a way to then "transfer" the connection to databases, and as soon as the connection is closed the database is dropped. Is there another way to create the database based on the metadata, using just databases and sqlalchemy core, except for raw CREATE TABLE commands?

estan · 2021-09-08T09:02:20Z

Agreed.

oskarlaasik · 2023-01-05T10:38:30Z

Oh, and for testing purposes, the workaround I'm using is a file database in a temp directory:

@pytest.fixture
async def db(tmpdir: Path) -> AsyncIterator[Database]:
    # Need a file DB since we can't currently share memory DB's between connections
    db_path = tmpdir / "example.db"
    async with Database(f"sqlite:///{db_path}") as db:
        yield db

@brendanlong could you please show hot to use it in context in a test with imports?

tnlogy · 2023-02-01T07:25:49Z

I had the same issue. By looking at the code, it seems like a possible fix for running unittests are to connect to the database with databases.Database("sqlite:///:memory:", force_rollback=True) since the code reuses the connection self._global_connection internally when adding the argument force_rollback.

tomchristie closed this as completed Apr 2, 2019

tomchristie reopened this Apr 2, 2019

tomchristie added the bug Something isn't working label Apr 4, 2019

sm-Fifteen mentioned this issue Jul 2, 2019

Temporary tables in SQLite aren't kept between queries #119

Open

brendanlong mentioned this issue Apr 26, 2020

Support SQLite URI mode and arguments #196

Open

vmarkovtsev closed this as completed Jun 2, 2020

nvjoshi2 mentioned this issue Jan 24, 2022

Open up SQLite query param, cached database functionality, inspired by #196 #451

Closed

codemation mentioned this issue Oct 5, 2023

Unit testing with in memory db codemation/pydbantic#73

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

in-memory sqlite odd behavior #75

in-memory sqlite odd behavior #75

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

Lawouach commented Apr 4, 2019

tomchristie commented Apr 4, 2019

Lawouach commented Apr 4, 2019

ryananguiano commented Apr 15, 2019

Lawouach commented Apr 16, 2019

reillysiemens commented Apr 24, 2019

micmarty commented Dec 17, 2019

tomchristie commented Dec 17, 2019

Lawouach commented Dec 17, 2019

BookGin commented Jan 10, 2020

brendanlong commented Apr 26, 2020

brendanlong commented Apr 26, 2020

yingshaoxo commented Jun 1, 2020 •

edited

Loading

yingshaoxo commented Jun 2, 2020

frjonsen commented Aug 17, 2020

estan commented Sep 8, 2021

oskarlaasik commented Jan 5, 2023

tnlogy commented Feb 1, 2023

in-memory sqlite odd behavior #75

in-memory sqlite odd behavior #75

Comments

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

tomchristie commented Apr 2, 2019

tomchristie commented Apr 2, 2019

Lawouach commented Apr 2, 2019

Lawouach commented Apr 4, 2019

tomchristie commented Apr 4, 2019

Lawouach commented Apr 4, 2019

ryananguiano commented Apr 15, 2019

Lawouach commented Apr 16, 2019

reillysiemens commented Apr 24, 2019

Using in-memory SQLite with databases

Using in-memory SQLite with aiosqlite

micmarty commented Dec 17, 2019

tomchristie commented Dec 17, 2019

Lawouach commented Dec 17, 2019

BookGin commented Jan 10, 2020

brendanlong commented Apr 26, 2020

brendanlong commented Apr 26, 2020

yingshaoxo commented Jun 1, 2020 • edited Loading

yingshaoxo commented Jun 2, 2020

frjonsen commented Aug 17, 2020

estan commented Sep 8, 2021

oskarlaasik commented Jan 5, 2023

tnlogy commented Feb 1, 2023

Using in-memory SQLite with `databases`

Using in-memory SQLite with `aiosqlite`

yingshaoxo commented Jun 1, 2020 •

edited

Loading