How to send Sqlite PRAGMA statements using Entity Framework #10567

2 Answer(s)

• 

  0

  AI-Bot created 5 months ago

  Support Team ABP Agent

  • ABP doesn’t apply any SQLite PRAGMA tuning by default. When you switch an ABP solution to SQLite, ABP essentially expects you to configure EF Core with UseSqlite(...) (connection string / options) and it doesn’t automatically set WAL, busy_timeout, cache_size, etc.
  • To ensure those PRAGMAs are always applied, configure them yourself at the ADO.NET/EF Core level.

  Option 1 (recommended): Put PRAGMAs into the SQLite connection string

  Microsoft.Data.Sqlite supports many PRAGMAs via connection string keywords. In an ABP app, this means you can set them in appsettings.json:

  {
    "ConnectionStrings": {
      "Default": "Data Source=app.db;Cache=Shared;Pooling=True;Foreign Keys=True;Journal Mode=WAL;Synchronous=Normal;Busy Timeout=5000"
    }
  }
  

  Then ABP/EF Core will use it as usual (your existing UseSqlite(configuration.GetConnectionString("Default"))).

  Notes:

  • This cleanly applies settings for every connection created by EF Core.
  • Not every PRAGMA you listed is available as a connection string keyword (depends on provider support).

  Option 2: Execute PRAGMAs when a DbConnection is opened (covers all PRAGMAs)

  If you need PRAGMAs like mmap_size, cache_size, journal_size_limit reliably, run raw SQL PRAGMA statements right after the connection opens. The simplest ABP-friendly place is your DbContext:

  using Microsoft.EntityFrameworkCore;
  
  public class MyDbContext : AbpDbContext<MyDbContext>
  {
      public MyDbContext(DbContextOptions<MyDbContext> options)
          : base(options)
      {
          // Ensure PRAGMAs are set when DbContext is created
          Database.OpenConnection();
  
          // Apply PRAGMAs
          Database.ExecuteSqlRaw("PRAGMA journal_mode = WAL;");
          Database.ExecuteSqlRaw("PRAGMA synchronous = NORMAL;");
          Database.ExecuteSqlRaw("PRAGMA mmap_size = 134217728;");
          Database.ExecuteSqlRaw("PRAGMA cache_size = 2000;");
          Database.ExecuteSqlRaw("PRAGMA busy_timeout = 5000;");
          Database.ExecuteSqlRaw("PRAGMA foreign_keys = ON;");
          Database.ExecuteSqlRaw("PRAGMA journal_size_limit = 67108864;");
      }
  }
  

  Important:

  • PRAGMA journal_mode=WAL and similar settings are connection-level / database-level behaviors; ensure they’re applied consistently for all app instances.
  • If you use migrations/DbMigrator, make sure the migrator app also applies the same PRAGMAs (it has its own startup/DbContext creation).

  Option 3: Interceptor (centralized, applied per-connection)

  You can implement an EF Core DbConnectionInterceptor that runs PRAGMAs on ConnectionOpened and register it in your module where you configure AbpDbContextOptions. This avoids putting it into the DbContext constructor and makes it easier to reuse across multiple DbContexts.

  ---

  • https://abp.io/docs/10.1/framework/data/entity-framework-core/sqlite

  ---

  This response was generated by AI. If it didn’t solve your problem, feel free to post a new answer — our support team will jump in to help you soon.

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel
• 

  0

  drenton created 5 months ago

  Note. You wrote that option 1 is recommended, however the answer is wrong. ABP uses Microsoft.Data.Sqlite and that library does not support journal mode as a parameter of the connection string.

  Comparison of System.Data.Sqlite to Microsoft.Data.Sqlite: https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/compare

  Microsoft.Data.Sqlite connection string options: https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings

  I implemented option 2.

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel