WackoWiki: Using SQLite

https://wackowiki.org/doc     Version: 14 (12/09/2025 10:24)

Using SQLite

add user guide ...



SQLite is suitable for small to medium wikis with low to moderate traffic and is particularly advantageous for portable installations, such as those running from a USB stick, due to its single-file database structure that simplifies backups and migration. It does not require a separate database server, reducing administrative overhead and potential points of failure. However, SQLite does not scale beyond a single server, making it unsuitable for wikis that may grow to have many concurrent users or large amounts of content.

1. Issues to consider

  1. For advanced search capabilities, MySQL or MariaDB are strongly recommended.
  2. The decision to use SQLite should ideally be made before creating the wiki, as migrating from SQLite to MySQL later is difficult.
  3. The database file is typically stored in a directory outside the web root.
    1. ../data/db_name.sqlite
  4. To ensure optimal performance, the database should be configured in WAL (Write-Ahead Logging) mode.
    1. PRAGMA journal_mode=wal;
  5. For full text search functionality, SQLite must be compiled with the FTS5 module, which is commonly included in modern builds.
  6. Access control is handled through the file-system permissions of the database file.
  7. SQLite requires that index names be unique across the entire database, not just within a single table.
  8. The entire database is stored in a single file, which simplifies backup procedures. If the database is offline, a simple file copy suffices for backup.
  9. A SQLite database file should not be used for more than one wiki. The cache table could be inserted into an additional database file.

2. Installation

2.1. Authorization

Please enter the password you saved in the file config/lock_setup, which you temporarily placed in your Wacko directory.
WackoWiki R6.2 installation step 0: authorization
There is an empty file named lock_setup in the configuration folder. Before you can continue with the installation or update, you must add a password.

The password in the file is in plain text and should not be the same as the recovery password. When correct the installer creates a form token valid for 10 minutes which is passed via POST. In the case the token expires the user gets redirected back to the login screen, all data input is discarded.

The password must be set, it won't accept an empty password. However if you remove the config/lock_setup file, it will no longer ask for a password for the installer.

SQLite is a serverless relational database management system which by default requires no username and password to created or access a database. Therefor it is imperative to require a password for the installer to prevent an unauthorized setup.

2.2. SQLite settings

The same goes for the database name and path. For security reasons it does not allow the user to set a random database name and path for the SQLite database. The installer suggests a few default Data Directory Locations depending on OS and environment. The database name is predefined.
WackoWiki Installation: Database Configuration for SQLite
The user can change the database name and path after the installation. In the next upgrade the installer won't touch it, same for backup & restore.

3. Convert your database to SQLite


To convert your database from MySQL to SQLite you can do the following:

mysql-to-sqlite3[link1] – Transfer data from MySQL to SQLite

Import your database
mysql2sqlite -f /home/user/Download/wacko.sqlite -d db_name -u root -p -K	


user@localhost:~> mysql2sqlite -f /home/user/Download/wacko.sqlite -d wacko -u root -K -p
MySQL password: 
mysql2sqlite version 2.4.1 Copyright (c) 2019-2025 Klemen Tusar
2025-11-18 10:26:51 INFO     Transferring table doc_page
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  6.58it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_page_link
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 16.82it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_referrer
2025-11-18 10:26:52 INFO     Transferring table doc_revision
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  6.14it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_user
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 239.81it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_user_setting
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 225.21it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_usergroup
100%|███████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 2972.58it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_usergroup_member
100%|███████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 1455.85it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_watch
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 45.21it/s]
2025-11-18 10:26:52 INFO     Transferring table doc_bad_behaviour
2025-11-18 10:26:52 INFO     Transferring table doc_word
2025-11-18 10:26:52 INFO     Transferring table doc_acl
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  8.58it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_auth_token
100%|███████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 2910.69it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_cache
100%|███████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 4156.89it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_config
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 315.60it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_category
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 409.68it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_category_assignment
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 104.83it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_external_link
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 34.22it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_file
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 65.26it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_file_link
100%|█████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 73.51it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_log
100%|███████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 2690.38it/s]
2025-11-18 10:26:53 INFO     Transferring table doc_menu
100%|████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 226.39it/s]
2025-11-18 10:26:53 INFO     Done!


config.php
'db_charset' => '',
'db_collation' => '',
'db_driver' => 'sqlite',
'db_engine' => 'SQLite3',
'db_vendor' => '',
'db_host' => '',
'db_port' => '',
'db_name' => 'file/data/wacko.sqlite',
'db_user' => '',
'db_password' => '',
'sql_mode' => '0',
'table_prefix' => 'doc_',


Add the table for the search index (change the table prefix if different) or use the {{adminupdate}} action to create the missing table.
CREATE VIRTUAL TABLE doc_page_fts USING fts5(
    title, 
    body,
    content='{$pref}page', 
    content_rowid='page_id'
);

If you have a local installation all should work as expected.

4. Tools

5. Further Reading

  1. SQLite
    1. SQLite Release History[link7]
    2. Appropriate Uses For SQLite[link8] – This page provides insights on when to use SQLite over other database systems, which can be helpful for someone considering the transition from MySQL to SQLite.
  2. PHP 
    1. How to compile PHP with SQLite support[link9]
    2. SQLite PDO Driver[link10]
    3. PHP Database Extensions SQLite3[link11]
  3. SQLite Tutorial[link12] – A comprehensive guide to SQLite, including how to get started with SQLite, commands, and converting databases. This can be useful for someone transitioning from MySQL.