What is the recommended way to seed an in-memory Sqlite database?

Question

I am trying to implement an in-memory Sqlite database on my C# application to use for testing purposes. I would like to seed the database during startup so that I can control the data used in my Cypress end to end tests. From my research online, I see 2 ways to do this.

The first is to add data to the context using the DbContext.AddRange() method, as shown in Mircosoft's example here.

The second is within the OnModelCreating() method, where you can add data to a model like this:

modelBuilder.Entity<Book>().HasData(
    new Book { BookId = 1, AuthorId = 1, Title = "Hamlet" },
    new Book { BookId = 2, AuthorId = 1, Title = "King Lear" },
    new Book { BookId = 3, AuthorId = 1, Title = "Othello" }
);

Documentation on Microsoft: https://learn.microsoft.com/en-us/ef/core/modeling/data-seeding

In either case, I believe you also have to use context.Database.EnsureCreated();, but I am not sure where to put it.

What is the reccommended way to seed my database with test data?

I ended up writing a small separate application to create, update and seed the database, including a special seed dedicated for testing. But you would typically not add a database just for testing. What is the actual use case? Are you sure plain files are not more suitable? — JonasH, Aug 07 '23 at 08:48
I am writing end to end tests using Cypress and I need my database set up for the tests so that they can run predictably. — Sora Teichman, Aug 07 '23 at 09:11
No, for prod we use sql server, but we want to use an in memory db for testing. — Sora Teichman, Aug 07 '23 at 09:46
That is not my issue, I can check if I am in test or prod. I need help setting up my sqlite db. — Sora Teichman, Aug 07 '23 at 09:58

score 4 · Answer 1 · answered Aug 07 '23 at 11:09

In-memory would seem to be a limiting factor from the perspective of the test, since Cypress can't directly interop with a database created by EF purely in it's memory space.

But you can create a file-based Sqlite db via node-sqlite3, which you can seed by calling a Cypress task.

There is a sample recipe here server-communication__seeding-database-in-node

This has a cy.task('seed:db') set up in cypress.config.js

const { defineConfig } = require('cypress')

const { seed } = require('./server/db')

module.exports = defineConfig({
  e2e: {
    baseUrl: 'http://localhost:7082',
    supportFile: false,
    setupNodeEvents (on, config) {
      on('task', {
        'seed:db' (data) {
          return seed(data).then(() => {
            return data
          })
        },
      })
    },
  },
})

In the Cypress recipe, ./server/db.js would need some code from the node-sqlite3 package instead of what it currently has (using json files as the db), for example

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
    db.run("CREATE TABLE lorem (info TEXT)");

    const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
    for (let i = 0; i < 10; i++) {
        stmt.run("Ipsum " + i);
    }
    stmt.finalize();

    db.each("SELECT rowid AS id, info FROM lorem", (err, row) => {
        console.log(row.id + ": " + row.info);
    });
});

db.close();

Your tests would probably want another task to create/update/delete database records at certain points in the test flow. You should be able to adapt the above code to handle that.

Thanks, I am not looking to test the db from Cypress. I am looking to write end to end tests that can act like a user and do non flaky tests, for example update a form, approve a form etc. I would need data in my db to represent a form that is waiting for approval. — Sora Teichman, Aug 07 '23 at 16:46
Why do you think this is just to test the db? `seed:db` means just that - set up initial conditions for the test. — Lola Ichingbola, Aug 07 '23 at 20:10

score 1 · Answer 2 · answered Aug 07 '23 at 09:13

I am trying to implement an in-memory Sqlite database on my C# application to use for testing purposes.

Since the goal is to use the app for unit-testing then to use HasData approach you will need to create a class which will inherit from the application context (or you will have the seeded data in actual context).

Also the HasData is more limited approach due to the data being applied to all instances of context for tests. It can make sense to but there some shared data which is used for all cases but I personally tend to introduce such data just via some shared method (which will do the AddRange and SaveChanges) which is called in some kind of one time setup (depending on test framework used) which is more flexible approach and allows more fine-graned control of needed.

Also one more thing I would suggest to look into - using testcontainers for such tests instead of in-memory SQLite, i.e. spin up database container set it up as needed and use it, which will result in more relevant test results by using specific database server.

I updated my question to clarify: I am writing end to end tests using the Cypress framework so I need the data to be setup from the back end and my tests will not be dealing with the database setup at all. — Sora Teichman, Aug 07 '23 at 09:22
@SoraTeichman then more info would be needed about deployment, but personally I still would go with the second approach for the same reasons - for test setup check if environment is a test one, call `EnsureCreated` and fill and save data on startup ([as here](https://stackoverflow.com/questions/71461296/how-do-you-do-database-ensurecreated-in-aspnet-core-web-application-using-net/71461320#71461320)) — Guru Stron, Aug 07 '23 at 10:09

What is the recommended way to seed an in-memory Sqlite database?

2 Answers2