SQLite or MySQL: which one fits a small project

Databases··· 293 views· 4 min read

The short answer: SQLite fits a small application with few users writing at the same time and nobody from outside needing the database. MySQL (here, MariaDB) fits anything that is a website with several visitors, dashboards, plugins and growth. If in doubt, and it is a site to publish, use the account’s MariaDB: it comes ready, with phpMyAdmin and the daily copies.

The difference that decides

MariaDB is a server: an always-on service, with users and passwords, that applications connect to. SQLite is a library: the database is a single file, and the program itself reads and writes it. There is no service, no user, no port.

SQLite MariaDB / MySQL
How it is stored One file in the application folder In a service on the server, managed from cPanel
Simultaneous writes One at a time: requests wait their turn Many at once, that is what it exists for
Users and privileges None: whoever reads the file reads everything Users with privileges per database
Access from another machine None Possible, through an SSH tunnel
Installing and maintaining Nothing to install Already on the server; you create it in cPanel
Backup Copy the file, with the application stopped Export, plus the account’s daily copies
Who uses it out of the box Small tools, prototypes, tests WordPress, shops, most CMSs

When SQLite is the right choice

1 An internal tool or a prototype, with a handful of users.
2 A program that reads a lot and writes little: a catalogue, a contact list, a reporting tool.
3 Tests and development, so you do not depend on a server.
4 A Python application, where the sqlite3 module already ships with the language.

When you should go to MariaDB

When the site has forms, accounts, a cart or a dashboard that several people write to at once; when WordPress, Joomla or any CMS demands it (they ask for MySQL or MariaDB); when another machine needs to talk to the data. You can migrate later: export the tables from SQLite and import them into MariaDB, but the differences in column types force you to review the file, so decide early.

The SQLite file must not sit in the public folder. If the .sqlite or .db is inside public_html, anyone who guesses the name downloads it, with everything in it. Keep it in a folder outside the public one, with permissions only your account can read.
In PHP, the sqlite3 extension comes switched on; pdo_sqlite exists and is switched on in Select PHP Version, in the extensions list. Check your version’s list before writing the code. For comparing with PostgreSQL, when the project grows: PostgreSQL or MySQL: which one for your project.

Not sure which fits your project? Describe it in two lines and we will tell you what fits on your account.

Open a support ticket

Where to see it in your account

Every hosting plan includes unlimited databases, so the account's MariaDB will not run out on you, whichever plan you have. You create one in cPanel under MySQL Databases: create the database, create a user and give that user privileges on it. To see how many you already have, open the Statistics panel in the cPanel sidebar, which lists the databases in use together with the files, e-mail accounts and space of the account. phpMyAdmin, mentioned above, is reached from the same cPanel.

SEE ALSO

MySQL or MariaDB: which one runs here, and does it matter

PostgreSQL or MySQL: which one for your project

Creating databases and using phpMyAdmin

Connecting to MySQL from PHP: mysqli and PDO, with a working example

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from ₦9.900,00/mo (3-year plan, with coupon)

See plans
Was this answer helpful? Your answer helps us improve this article.

Related Articles

Databases 1,030 Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
Databases 744 What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Databases 583 Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Databases 635 Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
Databases 630 When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Databases 108 MongoDB on a VPS: install it, lock it down, and why not to expose it The short answer: MongoDB is a database that stores documents (like JSON) instead of tables. It is not part of cPanel’s...