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
  • [x] Windows 11
  • [ ] Windows 10
  • [ ] macOS
  • [ ] Linux (Please specify distro)
  • [ ] iOS
  • [ ] Android
  • Browser
  • [ ] Chrome
  • [ ] Edge
  • [ ] Firefox
  • [ ] Safari
  • Frontend
  • [ ] Jupyter Notebook
  • [ ] Jupyter Lab
  • [ ] nteract
  • [x] 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.