Repository Pattern and db connections

We are in the process of moving away from entity framework to ormlite. In doing so we are currently running into a problem where we get the following exception.

Npgsql.NpgsqlOperationInProgressException: 'A command is already in progress

We are using postgres with ormlite. We have also implemented a repository pattern with the simple class as follows.

public class Repository<T> : IRepository<T> where T : class
    {
        private readonly IDbConnectionFactory dbConnectionFactory;
        private readonly string connectionName =DatabaseSettings.ConnectionName;

        public Repository(IDbConnectionFactory dbConnectionFactory)
        {
            this.dbConnectionFactory = dbConnectionFactory;
        }

        private IDbConnection db;

        protected Repository(IDbConnectionFactory dbConnectionFactory, string connectionName):this(dbConnectionFactory)
        {
            this.connectionName = connectionName;
        }

        public IDbConnection Db => db ?? (db = dbConnectionFactory.OpenDbConnection(connectionName));

        public T Get(long id)
        {
            return Db.LoadSingleById<T>(id);
        }

        public IDbTransaction StartTransaction()
        {
            return Db.OpenTransaction();
        }

        public List<T> Find(Expression<Func<T, bool>> predicate)
        {
            return Db.LoadSelect<T>(predicate);
        }

        public long Count(Expression<Func<T, bool>> predicate)
        {
            return Db.Count<T>(predicate);
        }

        public List<T> List(Expression<Func<T, bool>> predicate, int offset, int limit)
        {
            var q = Db.From<T>().Where(predicate).Limit(offset, limit);
            return Db.LoadSelect(q);
        }
        
        public void Save(T model)
        {
            Db.Save<T>(model);
        }

        public int Delete(T model)
        {
            return Db.Delete<T>(model);
        }

        public void Delete(Expression<Func<T, bool>> predicate)
        {
            Db.Delete<T>(predicate);
        }

        public void DeleteByIds(List<long> ids)
        {
            Db.DeleteByIds<T>(ids);
        }

        public void Dispose()
        {
            db?.Dispose();
        }

        public void InsertAll(List<T> models)
        {
            Db.InsertAll(models);
        }
    }

The DbConnection factory is registered as a singleton in Autofac.

We have a custom requestLogger that is a carbon copy of one I found in servicestack code, but we are using our own Entity and service that calls the underlying repository.

 Plugins.Add(new RequestLogsFeature
        {
            RequestLogger = new PostgresRequestLogger(container.Resolve<IRequestLoggerService>()),}

Once deployed, it works fine for a while, then starts throwing the exception listed above.

Perhaps we have a misunderstanding of how connections are handled. Should all calls (as seen in the Repository class above) actually be wrapped in usings that directly open and close the db? I would think there’s a big performance hit there unless the guts are using some sort of connection pool internally?

Since you have IDbConnection as a field the Repository needs to be registered as a transient dependency as the IDbConnection instance is not ThreadSafe to be used in a singleton. I’m assuming that’s the issue, without seeing the registration code or have a way to reproduce the Error. The full Exception StackTrace would also be helpful in determining the source of the error.

I’ll give that a try. We were just going by the statement on the ormlite github page to register it as a singleton. Didn’t really think to then register the IDbConnection itself as transient as we expected the DbFactory to be all we needed. But, makes sense. I’ll give it a try. ANd if that doesn’t work I’ll get the full stacktrack and more code.

There should only be a single instance of the OrmLiteConnectionFactory from which you use to create ADO.NET IDbConnection from, but they themselves are not ThreadSafe. This is the same for all Connection Pooling/Factories which create non-ThreadSafe instance connections.

So you can’t share an IDbConnection across multiple threads, so if you’re using it in a singleton dependency you’ll need to open and dispose of it when you use it:

public T Get(long id)
{
    using (var db = dbConnectionFactory.OpenDbConnection(connectionName))
    {
        return db.LoadSingleById<T>(id);
    }
}

Basically you should never have a IDbConnection db; field in a singleton instance.

Well, you posted just as I was typing up something else.

Yeah, that totally makes sense, and what I obviously didn’t clearly ask in the first post when I mentioned usings.

What kind of performance hit is there opening and closing a connection every time with ormlite? I have one developer that thinks this approach is going to drastically slow things down.

We currently register our Repositories as transient. So we wouldn’t be using a singleton pattern for those - which is why i’m originally confused as to why we are getting issues.

Registration we currently use

 builder.Register(c =>
            {
                var databaseSettings = c.Resolve<DatabaseSettings>();
                var requestDatabaseSettings = c.Resolve<RequestLoggerDatabaseSettings>();

                var dbFactory = new OrmLiteConnectionFactory();
                dbFactory.RegisterConnection(DatabaseSettings.ConnectionName,
                    new OrmLiteConnectionFactory(databaseSettings.ConnectionString, PostgreSqlDialect.Provider));
                
                dbFactory.RegisterConnection(RequestLoggerDatabaseSettings.ConnectionName,
                    new OrmLiteConnectionFactory(requestDatabaseSettings.ConnectionString, PostgreSqlDialect.Provider));

                using (var db = dbFactory.Open(RequestLoggerDatabaseSettings.ConnectionName))
                {
                    db.CreateTableIfNotExists<RequestLogEntry>();
                }

                return dbFactory;

            }).As<IDbConnectionFactory>().SingleInstance();

Repositories

 builder.RegisterAssemblyTypes(assembly).Where(t => typeof(IBaseRepository).IsAssignableFrom(t)).AsImplementedInterfaces();

It’s ultimately up to the underlying ADO.NET Connection Factories but most RDBMS ADO.NET Providers enable connection pooling by default so you’re only retrieving an open connection from the connection pool, i.e. not opening a new DB connection and disposing the connection just means returning it to the connection pool.

But there’s no question on whether you should Open a connection or not since IDbConnection instances can’t be shared across multiple threads, so each Request or new Thread always needs to use a new connection.

I’d like to chime in here because I run into this repo pattern a lot and i have a certain way of handling this myself, and if there’s a better way, I’d love to know about it.

What I like to do for any repository I create is keep the IDbConnectionFactory completely decoupled from the repository itself. By using OrmLite, 80 to 90% of methods can be handled independent of the dialect, on an IDbConnection exclusively. For the remainder of the 10%, it’s easy to create a dialect specific override of an abstract repo class if the codebase has to support multiple providers. I typically have a IContext object that I pass in to every method in the repo as the first parameter (the IContext has at minimum the user if one exists, and an IDbConnection). Sometimes I’ll just pass in the IDbConnection and IAuthSession as 2 params. While this may seem like it creates some bloat, it’s actually very effective for maintaining and decoupling responsibilities. The Db (Open/Dispose) is easiest handled at the Service level by ServiceStack this way.

One of the biggest reasons I like this pattern is because I can create a transaction boundary around multiple repos, and it works as well with the local in-proc gateway.send pattern built-into ServiceStack which re-uses the same Db connection (I believe), which means I can wrap many service calls into 1 transaction and if anything fails I’m not stuck with changes that would be hard to recover from.

An alternative I’ve used is to add an optional parameter at the end of every method to bypass the usage of an internal IDbConnectionFactory, and override the db with an existing IDbConnection …

So instead of:

public class MyRepo /* Least favorite */
{
    public MyRepo(IDbConnectionFactory dbFactory) { ... }
    public Task DoSomethingAsync() { ... }
}

I’d do:

public class MyRepo /* Slightly better, but more complex implementation within the method to use or instantiate a db connection, but doable */
{
    public MyRepo(IDbConnectionFactory dbFactory) { ... }
    public Task DoSomethingAsync(IDbConnection dbOverride = null) { ... }
}

or

public class MyRepo /* very effective */
{
    public Task DoSomethingAsync(IContext context) { ... }
}

Or:

public class MyRepo /* also effective */
{
    public Task DoSomethingAsync(IDbConnection db, IAuthSession session) { ... }
}

The last 2 are my preferred. The 1st is my least favorite, but seems to be the most common. The 2nd is ok but better just do 3 and 4, imo.

In the code above, the IContext could be:

public interface IContext
{
    Func<IDbConnection> Db { get; }
    // OR simply: IDbConnection Db { get; }
    IAuthSession Session { get; }
}

Curious your thoughts.

Yeah passing the open IDbConnection to keep the repository stateless is fine, but as it adds an extra param on every call in which case I’d probably just use extension methods and use the static class for grouping of related data access methods, e.g:

public static class SomethingRepoApi
{
    public static Task DoSomethingAsync(this IDbConnection db, IAuthSession session) {}
}

Which reduces code coupling and improves discoverability as the shared namespace is imported and intelli-sense will show available APIs without needing explicitly to explicitly reference the Repository, e.g:

await Db.DoSomethingAsync(GetSession());

I’d probably only consider using a IContext for 3 or more properties, i.e. not 2 as there’s some overhead in creating a context and makes APIs less reusable in that you either need the context instance available or have all instances available to create the context whilst the API may not need to use them all.

I personally take a gradual approach where initially all my data access is kept in the Service, when I need to reuse data access implementations I’ll refactor it out into static extension methods, when it grows large enough that it needs more modular organization I’d use a “Repository” which inherits a custom RepositoryBase to reduce the boilerplate (but needs to be registered as a transient dependency so the open connection is disposed at the end of the request). So it’s essentially like option 1 but using a base class to reduce boilerplate. I’m not seeing the benefit from decoupling from IDbConnectionFactory as it’s defined in ServiceStack.Common which ServiceStack.OrmLite references so it’s not adding any additional dependencies/coupling.

Point taken on the extension methods. Also, I agree on IContext, I use a similar rule of 3 or more, so for me it might be a minimum of .Cache, .Session, .Db, .EventDispatcher or something like that.

But regarding the RepositoryBase, my concern is primarily with transactions across repos and the ability to guarantee a rollback on any previous Db transactions wherever they may have happened within a broader transaction boundary:

public class X1: RepositoryBase
public class X2: RepositoryBase
public class X3: RepositoryBase

public class MyService: Service
{
    // X1, X2, X3 loaded via DI
    
    public object Post(MyRequest request)
    {
        X1.DoSomething()
        X2.DoSomething()
        X3.DoSomething() // If this fails, the X1.DoSomething and X2.DoSomething need to rollback
    }
}

so what I usually do then:

public class X1
public class X2
public class X3

public class MyService: Service
{
    // X1, X2, X3 loaded via DI
    
    public object Post(MyRequest request)
    {
        using(var tx = Db.OpenTransaction())
        {
           X1.DoSomething(Db) 
           X2.DoSomething(Db) // these would get rolled back if an error happens within the transaction boundary
           Gateway.Send(new AnotherRequest())
           X3.DoSomething(Db) 
           if (somecondition) Gateway.Send(new DispatchRequest()) 
        }
    }
}

Maybe there’s a better way …

Right if you need to share a transaction then you’d want to pass the same IDbConnection instance, but I’d personally never have a transaction spanning repositories.

Thanks!! :smile: :no_mouth:

So, back to my original problem. I thought the problem was fixed by simply adding polling=true to my connection string allowing the underlying provider (npgsql) to enable pooling. However, we realized yesterday we didn’t re-enable RequestLogging. Once we did, we started to get errors again. This time slightly different and much more vague.

[RequestLogs: 2/8/2018 11:55:44 AM]: [REQUEST: {skip:0}] System.InvalidOperationException: Timeouts are not supported on this stream. at System.IO.Stream.get_ReadTimeout()

We then realized that our RequestLogger registration was instantiated with a singleton (see code above) and thought that possible that was the issue. It seemed to be at least part of it. Once we fixed that it appeared to run locally fine, but once up on dev servers where there is more action going on, it started failing with the above exception.

We’ve since passed in a factory that will create the db connection and close it on each write.