github.com/wfusion/gofusion@v1.1.14/common/infra/watermill/docs/content/pubsubs/sql.md (about)

     1  +++
     2  title = "SQL"
     3  description = "Pub/Sub based on MySQL or PostgreSQL."
     4  date = 2019-07-06T22:30:00+02:00
     5  bref = "Pub/Sub based on MySQL or PostgreSQL."
     6  weight = -50
     7  type = "docs"
     8  toc = false
     9  +++
    10  
    11  ### SQL
    12  
    13  SQL Pub/Sub executes queries on any SQL database, using it like a messaging system. At the moment, **MySQL** and **PostgreSQL** are supported.
    14  
    15  While the performance of this approach isn't the best, it fits many use cases, where eventual consistency is acceptable.
    16  It can also be useful for projects that are not using any specialized message queue at the moment, but have access to a SQL database.
    17  
    18  The SQL subscriber runs a `SELECT` query within short periods, remembering the position of the last record. If it finds 
    19  any new records, they are returned. One handy use case is consuming events from a database table, that can be later published
    20  on some kind of message queue.
    21  
    22  The SQL publisher simply inserts consumed messages into the chosen table. A common approach would be to use it as a persistent
    23  log of events that were published on a queue with short message expiration time.
    24  
    25  SQL Pub/Sub is also a good choice for implementing Outbox pattern with [Forwarder](/docs/forwarder/) component.
    26  
    27  See also the [SQL example](https://github.com/ThreeDotsLabs/watermill/tree/master/_examples/pubsubs/sql).
    28  
    29  ### Installation
    30  
    31      go get github.com/ThreeDotsLabs/watermill-sql/v2
    32  
    33  #### Characteristics
    34  
    35  | Feature             | Implements | Note                                      |
    36  |---------------------|------------|-------------------------------------------|
    37  | ConsumerGroups      | yes        | See `ConsumerGroup` in `SubscriberConfig` |
    38  | ExactlyOnceDelivery | yes*       | Just for MySQL implementation             |
    39  | GuaranteedOrder     | yes        |                                           |
    40  | Persistent          | yes        |                                           |
    41  
    42  #### Schema
    43  
    44  SQL Pub/Sub uses user-defined schema to handle select and insert queries. You need to implement `SchemaAdapter` and pass
    45  it to `SubscriberConfig` or `PublisherConfig`.
    46  
    47  {{% render-md %}}
    48  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/schema_adapter_mysql.go" first_line_contains="// DefaultMySQLSchema" last_line_contains="type DefaultMySQLSchema" %}}
    49  {{% /render-md %}}
    50  
    51  There is a default schema provided for each supported engine (`DefaultMySQLSchema` and `DefaultPostgreSQLSchema`).
    52  It supports the most common use case (storing events in a table). You can base your schema on one of these, extending only chosen methods.
    53  
    54  ##### Extending schema
    55  
    56  Consider an example project, where you're fine with using the default schema, but would like to use `BINARY(16)` for storing
    57  the `uuid` column, instead of `VARCHAR(36)`. In that case, you have to define two methods:
    58  
    59  * `SchemaInitializingQueries` that creates the table.
    60  * `UnmarshalMessage` method that produces a `Message` from the database record.
    61  
    62  Note that you don't have to use the initialization queries provided by Watermill. They will be run only if you set the
    63  `InitializeSchema` field to `true` in the config. Otherwise, you can use your own solution for database migrations.
    64  
    65  {{% render-md %}}
    66  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/schema_adapter_mysql.go" first_line_contains="// DefaultMySQLSchema" last_line_contains="type DefaultMySQLSchema" %}}
    67  {{% /render-md %}}
    68  
    69  #### Configuration
    70  
    71  {{% render-md %}}
    72  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/publisher.go" first_line_contains="type PublisherConfig struct" last_line_contains="}" %}}
    73  {{% /render-md %}}
    74  
    75  {{% render-md %}}
    76  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/subscriber.go" first_line_contains="type SubscriberConfig struct" last_line_contains="}" %}}
    77  {{% /render-md %}}
    78  
    79  ### Publishing
    80  
    81  {{% render-md %}}
    82  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/publisher.go" first_line_contains="func NewPublisher" last_line_contains="func NewPublisher" %}}
    83  
    84  Example:
    85  {{% load-snippet-partial file="src-link/_examples/pubsubs/sql/main.go" first_line_contains="publisher, err :=" last_line_contains="panic(err)" padding_after="1" %}}
    86  {{% /render-md %}}
    87  
    88  {{% render-md %}}
    89  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/publisher.go" first_line_contains="// Publish " last_line_contains="func (p *Publisher) Publish" %}}
    90  {{% /render-md %}}
    91  
    92  #### Transactions
    93  
    94  If you need to publish messages within a database transaction, you have to pass a `*sql.Tx` in the `NewPublisher`
    95  constructor. You have to create one publisher for each transaction. 
    96  
    97  Example:
    98  {{% render-md %}}
    99  {{% load-snippet-partial file="src-link/_examples/real-world-examples/transactional-events/main.go" first_line_contains="func simulateEvents" last_line_contains="return pub.Publish(" padding_after="3" %}}
   100  {{% /render-md %}}
   101  
   102  #### Subscribing
   103  
   104  To create a subscriber, you need to pass not only proper schema adapter, but also an offsets adapter.
   105  
   106  * For MySQL schema use `DefaultMySQLOffsetsAdapter`
   107  * For PostgreSQL schema use `DefaultPostgreSQLOffsetsAdapter`
   108  
   109  {{% render-md %}}
   110  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/subscriber.go" first_line_contains="func NewSubscriber" last_line_contains="func NewSubscriber" %}}
   111  
   112  Example:
   113  {{% load-snippet-partial file="src-link/_examples/pubsubs/sql/main.go" first_line_contains="subscriber, err :=" last_line_contains="panic(err)" padding_after="1" %}}
   114  {{% /render-md %}}
   115  
   116  {{% render-md %}}
   117  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/subscriber.go" first_line_contains="func (s *Subscriber) Subscribe" last_line_contains="func (s *Subscriber) Subscribe" %}}
   118  {{% /render-md %}}
   119  
   120  #### Offsets Adapter
   121  
   122  The logic for storing offsets of messages is provided by the `OffsetsAdapter`. If your schema uses auto-incremented integer as the row ID,
   123  it should work out of the box with default offset adapters.
   124  
   125  {{% render-md %}}
   126  {{% load-snippet-partial file="src-link/watermill-sql/pkg/sql/offsets_adapter.go" first_line_contains="type OffsetsAdapter" %}}
   127  {{% /render-md %}}