SqLite with memory storage is a very good option when it comes to unit test data access layer.

There is a main trick you should know about in memory database, that might effect your success in implementing a unit test with it.
The database exists in the scope of a data connection.
So, if you close the connection, then the database is lost.
The solution to this problem is to create the schema, and do all your unit test in a scope of one connection.
I will list here how to do that by creating a main class, which will do that, and all what you have to do is to create unit test fixtures that inherits from that class.

First of all:
1 - Download SqLite
There is a one dll, that contains SqLite database, with a .NET provider
you can download it from this site.

2 - Add a reference to that dll in your unit test project.

3 - Add the following code

public static class NHConfigurer
{
    private const string CONN_STR = "Data Source=:memory:;Version=3;New=false;";
    private static readonly Configuration _configuration;
    private static readonly ISessionFactory _sessionFactory;
    
    static NHConfigurer()
    {
        _configuration = new Configuration()
            .AddProperties(new Dictionary{
                { Environment.ConnectionDriver, typeof( SQLite20Driver ).FullName },
                { Environment.Dialect, typeof( SQLiteDialect ).FullName },
                { Environment.ConnectionProvider, typeof( DriverConnectionProvider ).FullName },
                { Environment.ConnectionString, CONN_STR },
                { Environment.ShowSql, true.ToString() },
                { Environment.ProxyFactoryFactoryClass, typeof( ProxyFactoryFactory ).AssemblyQualifiedName },
                { Environment.CurrentSessionContextClass, "thread_static"}
        });
        var nhConfig = Fluently.Configure(_configuration);
        nhConfig.Mappings(m => m.FluentMappings.AddFromAssemblyOf<vendormapping>())
            .ExposeConfiguration(x => x.EventListeners.PreInsertEventListeners = new IPreInsertEventListener[] { new AuditEventListener() })
            .ExposeConfiguration(x => x.EventListeners.PreUpdateEventListeners = new IPreUpdateEventListener[] { new AuditEventListener() })
            .BuildConfiguration();
        _sessionFactory =  nhConfig.BuildSessionFactory();
    }
    public static Configuration Configuration { get { return _configuration; } }
    public static ISessionFactory SessionFactory { get { return _sessionFactory; } }
}

[TestFixture]
public class RepositoryTestBase<t> where T : new()
{
    protected T repository;
    protected ISession session;
    protected ITransaction transaction;
    
    protected static ISessionFactory SessionFactory {get { return NHConfigurer.SessionFactory; }}
    
    [TestFixtureSetUp]
    public void SetupFixture()
    {
        session = SessionFactory.OpenSession();
        CurrentSessionContext.Bind(session);
        var cfg = NHConfigurer.Configuration;
        var schemaExport = new SchemaExport(cfg);
        schemaExport.Execute(true, true, false, session.Connection, Console.Out);
    }
    
    [SetUp]
    public virtual void Setup()
    {
        repository = new T();
        session.FlushMode = FlushMode.Always;
        transaction = session.BeginTransaction();
    }
    
    [TearDown]
    public virtual void Teardown()
    {
        transaction.Rollback();
        repository = default(T);
    }
    
    [TestFixtureTearDown]
    public void TearDownTests()
    {
        var _session = CurrentSessionContext.Unbind(SessionFactory);
        _session.Close();
    }
}

The above class RepositoryTestBase, will create a session in start of a Test Fixture, and create all database schema , and then close it at the end of the fixture.


Then by just inheriting from that class , you can write unit tests that will test your repository transactions to the database.