API for SQL queries with PHP PDO I'm writing a very simple CRUD application, and I'm wondering if the way I'm using static methods throughout the code makes sense. I'd very much like to simplify the code if possible.
Any feedback and criticism is very much welcome!

&lt;?php

class Database {

    private const DB_DSN = &quot;127.0.0.1&quot;;
    private const DB_USER = &quot;root&quot;;
    private const DB_PASSWORD = &quot;root&quot;;
    private const DB_NAME = &quot;testDB&quot;;
    private const DB_PORT = 8889;

    static function connect() {
        return new PDO(
            &quot;mysql:host=&quot;.self::DB_DSN.&quot;;port=&quot;.self::DB_PORT.
            &quot;;dbname=&quot;.self::DB_NAME,
            self::DB_USER,
            self::DB_PASSWORD,
            array(PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION),
        );
    }

    static function create() {
        $pdo = new PDO(
            &quot;mysql:host=&quot;.self::DB_DSN.&quot;;port=&quot;.self::DB_PORT,
            self::DB_USER,
            self::DB_PASSWORD,
            array(PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION),
        );
        $pdo-&gt;exec(&quot;CREATE DATABASE IF NOT EXISTS &quot;.self::DB_NAME);
        return self::connect();
    }
}

class User {

    private const TABLE_NAME = &quot;users&quot;;

    public $uid;
    public $username;
    public $email;
    public $hash;
    public $created;
    public $verified;
    public $pdo;

    private function __construct() {
    }

    public static function signUp($pdo, $username, $email, $password) {

        if (self::exists($pdo, $username)) {
            return false;
        }

        $hash = password_hash($password, PASSWORD_DEFAULT);
        $stmt = $pdo-&gt;prepare(&quot;INSERT INTO `users` SET `username`=?, `email`=?, `hash`=?&quot;);
        $stmt-&gt;execute([$username, $email, $hash]);
        return self::fromUId($pdo, $pdo-&gt;lastInsertId());
    }

    public static function login($pdo, $username, $password) {

        if (!self::exists($pdo, $username)) {
            return false;
        }

        $stmt = $pdo-&gt;prepare(&quot;SELECT * FROM `users` WHERE `username` = ?&quot;);
        $stmt-&gt;execute([$username]);
        $user = $stmt-&gt;fetchObject(&quot;User&quot;);
        $user-&gt;pdo = $pdo;
        return password_verify($password, $user-&gt;hash) ? $user : false;
    }

    public static function exists($pdo, $username) {

        $stmt = $pdo-&gt;prepare(&quot;SELECT `uid` FROM `users` WHERE `username` = ?&quot;);
        $stmt-&gt;execute([$username]);
        return $stmt-&gt;fetch(PDO::FETCH_COLUMN);
    }

    public static function fromUId($pdo, $uid) {

        $stmt = $pdo-&gt;prepare(&quot;SELECT * FROM `users` WHERE `uid` = ?&quot;);
        $stmt-&gt;execute([$uid]);
        $user = $stmt-&gt;fetchObject(&quot;User&quot;);
        $user-&gt;pdo = $pdo;
        return $user;
    }

    public function verify() {

        $stmt = $this-&gt;pdo-&gt;prepare(&quot;UPDATE `users` SET `verified` = 1 WHERE `uid` = ?&quot;);
        if ($stmt-&gt;execute([$this-&gt;uid])) {
            $this-&gt;verified = true;
            return true;
        } else {
            return false;
        }
    }
}

$db = Database::create();

$db-&gt;exec(
    &quot;CREATE TABLE IF NOT EXISTS `users` (
        `uid` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `username` varchar(100) NOT NULL UNIQUE,
        `email` varchar(100) NOT NULL UNIQUE,
        `verified` boolean DEFAULT 0,
        `hash` varchar(255) NOT NULL,
        `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    )&quot;
);

$db-&gt;exec(
    &quot;CREATE TABLE IF NOT EXISTS `images` (
        `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `uid` int NOT NULL,
        `image` varchar(255) NOT NULL,
        `like_count` int NOT NULL DEFAULT 0
    )&quot;
);

$user = User::signUp($db, 'JohnDoe', 'john.doe@sample.org', '12345');

?&gt;
On the Database class
In my experience, a language layer never creates a database. It would require a create database privilege for one thing. Although it's sort of OK for the local environment but would you like your live application to connect the database under the root account? I highly doubt so.
Hence I find the create() method superfluous. But just for sake of refactoring I would make the connect method to accept a parameter that would tell it to use or not to use the dbname DSN argument so it could connect with or without setting the database.
What I would rather add to the database class is a handy &quot;prepare and execute in one go&quot; method like:
public function query($sql, $data = []) {
    $stmt = $this-&gt;pdo-&gt;prepare($sql);
    $stmt-&gt;execute($data);
    return $stmt;
}

as it will make all methods of the User class to lose a lot of weight. As a general rule, when you see repetitive code, think of creating a function to do all the repetitions in one go.
Also it would make sense to make $pdo a public property of the database class.
Now to the User class
First of all, I think that all your doubts with static class is for naught. Yes, in theory it's simple to call User::something() from anywhere without the need to pass around the instance of the user class. But... you need to pass around the instance of PDO anyway! The same problem, different angle.
So you can make your code much cleaner and doubtless by making all methods dynamic, and putting the pdo (or rather a Database class) instance inside. Then you will have to pass around the instance of the user class instead of the instance of the PDO class.
Another repetition that can be spotted is two methods that select a user by username. That's nearly identical code! Always try to reuse the code that already exists in your class. Moreover, for some reason login method runs these two identical queries one after another. But why? Is once not enough?
Another problem is signup function. It's a no-go when it just silently returns false when a user exists. It should be vocal about that. The generic solution is to throw a custom built exception that can be caught and conveyed to the user.
On the return values: Most of time it's not needed. After all, what would you do with that return value from the verify() method?
On the names: it is Better to be more explicit with the function names. &quot;verify&quot; is ambiguous - you never can tell what does it verify and why. The same goes for &quot;exists&quot;.
The ActiveRecord vs. DataMapper controversy
From the way you are asking, I can tell you already feel the ambiguity of your class, which serves for two purposes at once, being both a user and a User persistence layer. That's always so with the ActiveRecord pattern to which your class belongs even if you don't probably realize that. You even tried to mae a distinction between these two roles, where static methods are manipulating the database and dynamic methods and properties are manipulating the class data.
You need to make just a logical step further and separate these roles explicitly. A better approach to be considered is to split this class in two - User class and a UserMapper class, where the latter one would do all the database work.
the Username controversy
As long as there is an email to identify the user, there is absolutely no sense in using a dedicated username field. In practice, it adds helluvalot of confusion. A user signs up with a user name and n email, then forgets it and signs up again with email as a username and everything becomes so entangled that it makes it really hard for the support staff. Make it only email to identify the user and get rid of that username nonsense completely. In case you plan to use it as a display name then make it a display name that is not used in the login process at all.
The code
So according to the above suggestion I would create three classes. The User itself
&lt;?php
class User {
    public $uid;
    public $username;
    public $email;
    public $hash;
    public $created;
    public $verified;

    public function login($password)
    {
        return password_verify($password, $this-&gt;hash);
    }

}

A class with a custom exception
class UserException Extends InvalidArgumentException {}

And a UserMapper class
class UserMapper
{
    private $db;
    private $table = 'users';
    private $class = 'User';

    public function __construct(Database $db) {
        $this-&gt;db = $db;
    }
    public function signUp($email, $password)
    {
        if ($this-&gt;getByEmail($email)) {
            throw new UserException(&quot;User already exists&quot;);
        }
        $hash = password_hash($password, PASSWORD_DEFAULT);
        $sql = &quot;INSERT INTO `$this-&gt;table` SET `email`=?, `hash`=?&quot;;
        $this-&gt;db-&gt;query($sql, [$email, $hash]);
        return $this-&gt;getById($this-&gt;db-&gt;pdo-&gt;lastInsertId());
    }
    public function getByEmail($email)
    {
        $sql = &quot;SELECT * FROM `$this-&gt;table` WHERE `email` = ?&quot;;
        return $this-&gt;db-&gt;query($sql, [$email])-&gt;fetchObject($this-&gt;class);
    }
    public function getById($uid)
    {
        $sql = &quot;SELECT * FROM `$this-&gt;table` WHERE `uid` = ?&quot;;
        return $this-&gt;db-&gt;query($sql, [$uid])-&gt;fetchObject($this-&gt;class);
    }
    public function setVerified()
    {
        $sqk = &quot;UPDATE `$this-&gt;table` SET `verified` = 1 WHERE `uid` = ?&quot;;
        $this-&gt;db-&gt;query($sql, [$this-&gt;uid]);
    }
}

then it can be used like this
$userMapper = new UserMapper($db);
try {
    $user = UserMapper-&gt;signUp('john.doe@sample.org', '12345');
} catch (UserException $e) {
    $error = $e-&gt;getMessage;
    // show it back to user, etc
}

$user = $userMapper-&gt;getByEmail($_POST['email']);
if ($user &amp;&amp; $user-&gt;login($_POST['password'])) {
     // authorize
} else {
     // invalid login or password
}