If I want to build a website with a very small number of users, how should I choose data storage?

I want to build a website with a very small number of users. The number of users is from a few to more than a dozen. This number of users is really very small.
The backend uses php, and now I want to know how to store data.

Because the number of users is very small, the host configuration will also be very low. Using mysql feels overkill, and may affect performance because the host configuration is too low.

I have considered saving it directly into a file (the data has a relatively simple structure, and json can handle it), but if there is a related query (such as mysql's join), then you have to use PHP to do the related query yourself. It feels like Some trouble.

Is there any better way? Like a super lightweight database?

PS: You don’t need redis because the host configuration is low and the memory is definitely tight. Companies like mysql and redis that work hard to produce miracles cannot exert their strength in such a sloppy environment.

Reply content:

I want to build a website with a very small number of users. The number of users is from a few to more than a dozen. This number of users is really very small.
The backend uses php, and now I want to know how to store data.

Because the number of users is very small, the host configuration will also be very low. Using mysql feels overkill, and may affect performance because the host configuration is too low.

I have considered saving it directly into a file (the data has a relatively simple structure, and json can handle it), but if there is a related query (such as mysql's join), then you have to use PHP to do the related query yourself. It feels like Some trouble.

Is there any better way? Like an ultra-lightweight database?

PS: You don’t need redis because the host configuration is low and the memory is definitely tight. Companies like mysql and redis that work hard to produce miracles cannot exert their strength in such a sloppy environment.

Use SQLite, or simply file storage. If file storage is used, the data files should be placed outside the webroot.

The correct answer upstairs, sqlite. It is lightweight and does not require services, just files. The client of the project that I participated in the research and development before used sqlite

It’s still mysql. The efficiency of file reading and writing is lower than using mysql, and it will also cause additional difficulties when you develop. I suggest you try it first. Will the host configuration be lower than that of the Raspberry Pi? Of course, if the host configuration is already low, there is no way to install Windows.

If you are developing using ORM, choosing a database in the early stage is not something you have to consider.
If you are writing sql development, then you have to consider that if the database is changed in the future, the sql you write may not be compatible.

No matter how low the host configuration is, it is not as exaggerated as you think. Mysql and postgres can definitely run

SQLite is recommended, PHP comes with its own extensions. Although the functions and data types are much simpler than MySQL, the commonly used ones are fine, and the performance is also very good.

If you use SQLite, it is best not to place the data file in the root directory of the website, otherwise people will download it if they know the URL.
Of course you can configure Apache/Nginx to deny requests to access specified files, but it is still a hidden danger after all,
So still Put it outside the root directory of the website.
In addition, if you use SQLite, you have to write SQL language and use the PDO set of functions to operate:

<code><?php

function db() {
    static $db;
    if ($db) {
        return $db;
    } else {
        try {
            $db = new PDO('sqlite:'.$_SERVER['DOCUMENT_ROOT'].'/../data.db3');
        } catch (PDOException $e) {
            echo $e->getMessage();
            exit();
        }
        return $db;
    }
}

function insert($title = '', $content = '') {
    global $app;
    $db = db();
    $stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');
    $stmt->bindParam(1, $title,   PDO::PARAM_STR);
    $stmt->bindParam(2, $content, PDO::PARAM_STR);
    $stmt->execute();
    return ($stmt->rowCount() !== 0) ? 
        array(true,  'lastInsertId' => $db->lastInsertId()) : 
        array(false, 'lastInsertId' => $db->lastInsertId());
}

function select($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);
    } else {
        return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);
    }
}

function select_v2($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        $stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
    } else {
        $stmt = $db->prepare('SELECT * FROM posts');
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function update($id, $title = '', $content = '') {
    global $app;
    $db = db();
    
    //echo PDO::ATTR_AUTOCOMMIT; //返回0可见PDO默认禁用自动提交事务.
    //echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); //返回1可见MySQL默认会自动提交事务.
    
    //SQLite不支持设置PDO::ATTR_AUTOCOMMIT:
    //SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'
    //$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
    $db->beginTransaction();
    $stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
    
    $stmt->execute(array($title,$content,$id)); //所有值视作PDO::PARAM_STR处理
    //$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id));
    //$stmt->bind_param('ssi', $title, $content, $id); //对比mysqli
    
    echo 'sleep(3);'."\n";
    sleep(3);
    
    $db->commit();
    //$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为true
    return ($stmt->rowCount() !== 0) ? true : false;
}

function delete($id) {
    global $app;
    $db = db();
    return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false;
}

function delete_v2($id) {
    global $app;
    $db = db();
    $stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
    $stmt->bindParam(1, $id, PDO::PARAM_INT);
    $stmt->execute();
    return ($stmt->rowCount() !== 0) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

$sqlite = "CREATE TABLE IF NOT EXISTS posts (
    id           INTEGER PRIMARY KEY,
    post_title   VARCHAR(255) NOT NULL,
    post_content TEXT         NOT NULL
)";

db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($sqlite) or exit();

//并发时,SQLite在insert时因为库文件被其他请求锁住而导致阻塞
echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";</code>

I wonder if you have such an experience, PHP is actually an array-oriented programming language.
In fact, you can consider directly exporting PHP arrays into files to store data.

<code><?php
header('Content-Type: text/plain; charset=utf-8');
$file = __DIR__.'/data.php'; //数据文件,别人直接URL访问也下载不了
if(!file_exists($file)) {
    file_put_contents($file, '<?php return array();'); //file_put_contents($file, '');
}
$fp = fopen($file, 'r+'); //读写方式打开,将文件指针指向文件头
if(flock($fp, LOCK_EX)) { //阻塞到获取排它锁
    //锁定数据文件后再进行读写
    $arr = require $file; //$arr = unserialize(file_get_contents($file));
    $arr[] = date('Y-m-d H:i:s');
    ftruncate($fp, 0); //清空文件
    fwrite($fp, '<?php return '.var_export($arr, true).';'); //fwrite($fp, serialize($arr));
    fflush($fp); //在释放锁之前刷新输出
    //sleep(10); //睡眠10秒,在此期间其他工作进程的请求将被阻塞
    flock($fp, LOCK_UN); //释放锁定
    echo file_get_contents($file)."\n";
}
fclose($fp);</code>

The comments also provide methods to serialize/unserialize serialized storage like PHP sessions.
It is worth mentioning that the performance of serialize/unserialize is much better than var_export/require.
But the advantage of var_export/require is that it is not afraid of others For direct access, you can put the file in the root directory and it is more readable.