Auto initialization of tables isn't supported from the read journal side.

[OnurGumus]

Version Information
Version of Akka.NET?
1.5.14
Which Akka.NET Modules?
Akka.Persistence.Sql

Describe the bug
I am using the new LINQ to db persistence with hocon config. I am initializing my tables with auto-initialize = true.
But this only happens if any actors attempt to persist something. In my case I also start a read journal. Then read journal complains the tables don't exist yet. Since no events are persisted yet. Surprisingly though the tables are created after read journal throws.

I have configured auto-initialize for the read journal as below

akka {
    persistence {
      journal {
        plugin = "akka.persistence.journal.sql"
        sql {
           class = "Akka.Persistence.Sql.Journal.SqlWriteJournal, Akka.Persistence.Sql"
            connection-string = ${config.connection-string}
            provider-name =  "SQLite.MS"
            auto-initialize = true
        }
      }
      query.journal.sql {
        class = "Akka.Persistence.Sql.Query.SqlReadJournalProvider, Akka.Persistence.Sql"
        connection-string = ${config.connection-string}
        provider-name = "SQLite.MS"
         auto-initialize = true
      }
      snapshot-store {
        plugin = "akka.persistence.snapshot-store.sql"
        sql {
          class = "Akka.Persistence.Sql.Snapshot.SqlSnapshotStore, Akka.Persistence.Sql"
            connection-string = ${config.connection-string}
            provider-name = "SQLite.MS"
            auto-initialize = true
        }
      }
...
    

But I saw that auto-initialize isn't a supported config option for query.journal.sql. From the code I saw that, initilializing is done by
SqlWriteJournal. So either provide a programmatic way to force generating tables or better make sure read journal has also ability to generate the tables in case it is started before.

To Reproduce
Just try to use a read journal without persisting any events.

Links to working reproductions on Github / Gitlab are very much appreciated

Expected behavior
I would like readjournal not to fail with table don't exist error without persisting a dummy event.

Actual behavior
Read journal throws an exception when reading events. But somehow tables are created then.

[18:05:38 ERR] An exception occured inside SelectAsync while executing Task. Supervision strategy: Stop
System.AggregateException: One or more errors occurred. (SQLite Error 1: 'no such table: journal'.)
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: journal'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteDataReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteDataReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteReaderAsync(CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.ExecuteQueryAsync[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber, Func`2 func, TakeSkipDelegate skipAction, TakeSkipDelegate takeAction, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.ExecuteQueryAsync[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber, Func`2 func, TakeSkipDelegate skipAction, TakeSkipDelegate takeAction, CancellationToken cancellationToken)
   at LinqToDB.Linq.ExpressionQuery`1.GetForEachAsync(Action`1 action, CancellationToken cancellationToken)
   at LinqToDB.Linq.ExpressionQuery`1.GetForEachAsync(Action`1 action, CancellationToken cancellationToken)
   at LinqToDB.AsyncExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken token)
   at Akka.Persistence.Sql.Query.Dao.BaseByteReadArrayJournalDao.<>c__DisplayClass6_0.<<JournalSequence>b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Akka.Persistence.Sql.Extensions.ConnectionFactoryExtensions.ExecuteWithTransactionAsync[T](AkkaPersistenceDataConnectionFactory factory, IsolationLevel level, CancellationToken token, Func`3 handler)
   at Akka.Persistence.Sql.Extensions.ConnectionFactoryExtensions.ExecuteWithTransactionAsync[T](AkkaPersistenceDataConnectionFactory factory, IsolationLevel level, CancellationToken token, Func`3 handler)
   at Akka.Persistence.Sql.Extensions.ConnectionFactoryExtensions.ExecuteWithTransactionAsync[T](AkkaPersistenceDataConnectionFactory factory, IsolationLevel level, CancellationToken token, Func`3 handler)
   at Akka.Persistence.Sql.Extensions.ConnectionFactoryExtensions.ExecuteWithTransactionAsync[T](AkkaPersistenceDataConnectionFactory factory, IsolationLevel level, CancellationToken token, Func`3 handler)
   at Akka.Persistence.Sql.Query.Dao.BaseByteReadArrayJournalDao.<JournalSequence>b__6_0(<>f__AnonymousType4`3 input)
   --- End of inner exception stack trace ---
   at Akka.Actor.PipeToSupport.PipeTo[T](T

Environment
Linux, docker .NET 8
Are you running on Linux? Windows? Docker? Which version of .NET?

[OnurGumus]

I suspect this line should be moved above on top of the method

Akka.Persistence.Sql/src/Akka.Persistence.Sql/Journal/SqlWriteJournal.cs

Line 116 in 155e8fb

await _journal.InitializeTables(_pendingWriteCts.Token);

[to11mtm]

Two questions for @Aaronontheweb and @Arkatufus:

1. Should we be auto-initializing Journals from the Query Side?
2. If Yes, should we have that as a separate config option?

[Arkatufus]

There are problems implementing database table initializer for the read journal.

In the old Sql.Common implementation, all table operations (both read and write) were implemented inside the journal actor, this allows for command stashing for all operations, allowing the journal to complete any database initialization before processing any database operations.

In Akka.Persistence.Sql, the read journal accesses the database directly, giving it a performance boost. The problem with this design is that there's no mechanism we can tap into to make sure that the database tables are initialized.

[OnurGumus]

I understand the challenges how ever I don't agree with the outcome at least the exception should be handled in someway. Please reopen @Aaronontheweb @Arkatufus

[Aaronontheweb]

Fair point, we should handle the exception at least