Code sample to show how to use MySQL?

[siddjain]

we are planning to use swoole to host a web api. we have seen this code:
https://www.swoole.co.uk/docs/modules/mysqli-connection-pool

$pool = new MysqliConfig())
            ->withHost(MYSQL_SERVER_HOST)
            ->withPort(MYSQL_SERVER_PORT)
            // ->withUnixSocket('/tmp/mysql.sock')
            ->withDbName(MYSQL_SERVER_DB)
            ->withCharset('utf8mb4')
            ->withUsername(MYSQL_SERVER_USER)
            ->withPassword(MYSQL_SERVER_PWD)
    )

some questions we have are:

• where do we specify the number of connections in the pool?
• and how is this pool available to the web server? I mean what should be the scope of the $pool variable?
  The swoole docs say not to use global variables but a connection pool by necessity is a global variable. Any code sample illustrating how to properly use a connection pool in a HTTP web server?

[deminy]

Hello @siddjain . I'm sorry for not answering your questions earlier. I will add my answers here, and hope it helps you and some other developers. Thanks

Question 1: where do we specify the number of connections in the pool?

In the constructor of class \Swoole\Database\MysqliPool, the 2nd parameter is to define the maximum number of connections in the pool. By default, it's 64. So if you want to cap the maximum # of connections to 5, here it is:

    $pool = new MysqliPool(
        (new MysqliConfig())
            ->withHost(MYSQL_SERVER_HOST)
            ->withPort(MYSQL_SERVER_PORT)
            // ->withUnixSocket('/tmp/mysql.sock')
            ->withDbName(MYSQL_SERVER_DB)
            ->withCharset('utf8mb4')
            ->withUsername(MYSQL_SERVER_USER)
            ->withPassword(MYSQL_SERVER_PWD),
        5
    );

It doesn't mean that the script will maintain 5 connections all the time; instead, it will create new connections only when needed, with 5 connections at most.

Question 2: and how is this pool available to the web server? I mean what should be the scope of the $pool variable?

The $pool variable could be

• A global variable.
• Some static property in a class.
• A non-preserved, self-defined property attached to the $server object, e.g., $server->pool.
• An entry managed by a DI container.

The swoole docs say not to use global variables but a connection pool by necessity is a global variable. This is inaccurate, or maybe you misunderstood what the doc said.

Global variables can be used in Swoole as long as we know how to use them properly. However, if we misuse global variables in Swoole, it will cause lots of headaches. For example, global variables like $_SESSION should never be used in Swoole because it's accessible by different HTTP requests from other users. Many developers used to use global variables under PHP-FPM, and they try to do the same thing under Swoole and experienced various issues. That's probably why many articles suggested not using global variables (unless we know what we are doing).

If you want to use Swoole to build some web applications, I highly recommend using a Swoole-based framework instead of writing everything from scratch. You can find a few Swoole-based frameworks here. Personally, I'd recommend Hyperf, which contains lots of built-in features, examples, tests, and other stuff.