SQLite in memory, create a table and write a request in different cells

[marckruzik]

Describe the bug

I would like to do different SQLite requests, on different cells, on the same SQLite in memory database.
But it looks like the database I am creating in a cell no longer exists the cell after.

I am under the impression that the table I am creating in a cell could then be accessed in any further cell.
Maybe I did not understand correctly some limitations about in memory SQLite databases?

Note: Here I am using the C# Kernel. I also tried using the SQL kernel (in the bottom right of the cells), and the sql-hellodb kernel, but it doesn't work.

Code

#!meta

{"kernelInfo":{"defaultKernelName":"csharp","items":[{"aliases":[],"languageName":"csharp","name":"csharp"},{"aliases":[],"languageName":"SQLite","name":"sql-hellodb"}]}}

#!csharp

#r "nuget: Microsoft.DotNet.Interactive.SQLite, 1.0.0-beta.24229.4"

#!csharp

#!connect sqlite --kernel-name hellodb "Data Source=helloDB;Mode=Memory;Cache=Shared"

#!csharp

#!sql-hellodb

CREATE TABLE IF NOT EXISTS CITY (
  city_id INTEGER PRIMARY KEY,
  city_name TEXT
);

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

#!csharp

#!sql-hellodb
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

Please complete the following:

Which version of .NET Interactive are you using? (In a notebook, run the #!about magic command. ):

Version: 1.0.522904+cdfa48b2ea1a27dfe0f545c42a34fd3ec7119074

• OS
  • Windows 11
  • Windows 10
  • macOS
  • Linux (Please specify distro)
  • iOS
  • Android
• Browser
  • Chrome
  • Edge
  • Firefox
  • Safari
• Frontend
  • Jupyter Notebook
  • Jupyter Lab
  • nteract
  • Visual Studio Code
  • Visual Studio Code Insiders
  • Visual Studio
  • Other (please specify)

[jonsequitur]

This looks like a bug in the SQLite kernel. I would also expect it to retain state across cells.