Question 90411 in PostgreSQL Question posted in
The official documentation can be found here.

Is there a way to notify backend server from a postgreSQL database on some trigger?

Is there a proper way of triggering an event from my PostgreSQL database upon insert/update on a certain table that sends a notification to a separate HTTP server?
For a standard REST API application, we mostly interact with the DB unidirectionally. However, if the backend relies on a certain field value on a certain table from the DB, then it would make sense for the DB to send the data to the backend server, instead of the backend repeatedly polling the DB table for any changes which keeps the DB unnecessarily occupied.

Is there a better or standard approach to this?

Tags:

2

Answers


  1. 0

    That’s what LISTEN/NOTIFY is for. The application that should be notified connects to the database, issues LISTEN channel_name, then waits for data to arrive on the network socket. The trigger runs NOTIFY channel_name, which sends a message to the waiting session.

    Login or Signup to reply.
  2. 0

    You can use SQL Dependency and SignalR like below:
    First you have to register in sql dependency on change event handler.

    public List<Employee> GetAllEmployees()
    {
        var employees = new List<Employee>();

        using(SqlConnection conn = new SqlConnection(connectionString)){
            conn.Open();

            SqlDependency.Start(connectionString);

            string commandText = "select Id, Name, Age from dbo.Employees";

            SqlCommand cmd = new SqlCommand(commandText,conn);

            SqlDependency dependency = new SqlDependency(cmd);

            dependency.OnChange+=new OnChangeEventHandler(dbChangeNotification); // Register into onChange Event

            var reader = cmd.ExecuteReader();

            while(reader.Read()){
                var employee = new Employee{
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Age = Convert.ToInt32(reader["Age"])
                };

                employees.Add(employee);
            }
        }

        return employees;
    }

    private void dbChangeNotification(object sender, SqlNotificationEventArgs e)
    {
        _context.Clients.All.SendAsync("refreshEmployees");
    }

    After that you have to make a signalR connection in your application and the event which is being generated by signalR employee register class that is by the method which is _context.Clients.All.SendAsync("refreshEmployees"), On change of refreshEmployees you have to fetch the updated data.

    Here is the reference video which I referred:
    SignalR Part1
    SignalR Part2

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search