Hi folks,

I'm trying to use two :memory: databases to test users concurrency.

I only can achieve this creating one :memory: db and attach a file db.

I already tried:
• create two databases with "file::memory:?cache=shared" but it creates only one db.
• create two databases with "dbName; Mode = Memory; Cache = Shared" but it creates two files with name "db_i; Mode = Memory; Cache = Shared".
• "file:dbName?mode=memory&cache=shared" but it not attach the second database.

Is there a way to create a :memory: db with a name and use both in-memory with/without attach without creating a file?

Thank you

create two databases with "file::memory:?cache=shared" but it creates only one db.

cache=shared is presumably the reason you're getting only a single instance. Try removing that argument.

Is there a way to create a :memory: db with a name and use both in-memory with/without attach without creating a file?

sqlite> attach ':memory:' as 'foo';
sqlite> attach ':memory:' as 'bar';
sqlite> create table foo.t(a,b,c);
sqlite> create table bar.t(a,b,c);

Thank you for your reply. As I saw on other discussions, "cache=shared" is kind of deprecated. It was useful on low-end machines. And also "file:something" create a file on filesystem with an "internal db" but I am trying to work only in memory without use of filesystem.

Since 3.36.0 there is another approach - you can use memdb vfs

ATTACH DATABASE 'file:/memdb1?vfs=memdb' AS aux1;

ATTACH DATABASE 'file:/memdb2?vfs=memdb' AS aux2;

This method doesn't use shared cache, which is considered deprecated (however, it is unclear from your question whether you need shared cache at all - e.g. do you want share your many in-memory dbs between many db connections)

Unfortunately, this feature is not documented in memdb docs, but mentioned in release notes. At this forum post @drh advices to use memdb if you want share in-memory db between connections.

I tried, thank you. But it creates a file on filesystem named "file".

Hm... strange. It works for me (python3) and doesn't create any files on disk:

from sqlite3 import connect, sqlite_version
"""
If your sqlite_version less than 3.36.0 
use https://github.com/coleifer/pysqlite3 
which allows to build DBAPI python module with required sqlite3 version
"""
#from pysqlite3 import connect, sqlite_version

assert sqlite_version >= '3.36.0'

script = """
attach database 'file:/memdb1?vfs=memdb' as aux1;
attach database 'file:/memdb2?vfs=memdb' as aux2;

create table aux1.t(id integer primary key);
insert into aux1.t values (1), (2);

create table aux2.t(id integer primary key);
insert into aux2.t values (3), (4);

"""

c = connect(":memory:")
c.executescript(script)

print("from aux1", c.execute("select * from aux1.t").fetchall())
print("from aux2", c.execute("select * from aux2.t").fetchall())

It prints

from aux1 [(1,), (2,)]

from aux2 [(3,), (4,)]

I tried, thank you. But it creates a file on filesystem named "file".

It seems that you have URI support disabled.

For me connection to 'file:mem1?vfs=memdb' with disabled uri support creates file 'file:mem1?vfs=memdb' on disk (ext4 filesystem). May be your filesystem strips it to 'file' because ':' is not allowed in filename...

oh, I'm sorry.

So you open a single connection with :memory: and then attach of two databases with vfs=memdb. Ok, it works.

It works like
attach database ':memory:' as aux1; attach database ':memory:' as aux2;
but with vfs=memdb and /name so I can use db cross connections (of same process)

However, even if aux databases have '/' before their names, I cannot attach them to a second connection because it said "'SQL logic error database aux1 is already in use"

Then you have done something wrong because it works perfectly.

from sqlite3 import connect, sqlite_version
"""
If your sqlite_version less than 3.36.0
use https://github.com/coleifer/pysqlite3
which allows to build DBAPI python module with required sqlite3 version
"""
#from pysqlite3 import connect, sqlite_version

assert sqlite_version >= '3.36.0'

script = """
attach database 'file:/memdb1?vfs=memdb' as aux1;
attach database 'file:/memdb2?vfs=memdb' as aux2;
"""

scriptdata = """
create table aux1.t(id integer primary key);
insert into aux1.t values (1), (2);

create table aux2.t(id integer primary key);
insert into aux2.t values (3), (4);
"""

c = connect(":memory:")
c.executescript(script)
c.executescript(scriptdata)

print("from c.aux1", c.execute("select * from aux1.t").fetchall())
print("from c.aux2", c.execute("select * from aux2.t").fetchall())

d = connect(":memory:")
d.executescript(script)

print("from d.aux1", d.execute("select * from aux1.t").fetchall())
print("from d.aux2", d.execute("select * from aux2.t").fetchall())

which outputs:

from c.aux1 [(1,), (2,)]
from c.aux2 [(3,), (4,)]
from d.aux1 [(1,), (2,)]
from d.aux2 [(3,), (4,)]

you were right!!
Dude, I'm married but I think I love you!
I was using variables such "fooConnection1" and "fooConnection2" but when I tried to attach to connection2 I used variable fooConnection1.
Everything it's worked now, in memory.
Thanks a lot, you made my day!

SQL logic error database aux1 is already in use

This error appears when the name of the attached db aux1 is already in use in the current connection, for example:

attach database 'db1' as repeat

attach database 'db2' as repeat

So check your code first.

The script by @kmedcalf works for me.