@anonymous-matomo-user opened this Issue on April 25th 2013

I'm developing a new "plugin" or "how you want to be named" that allow to use SQLite instead of MySQL. Currently, works with all queries except the queries that use variables inside (@variable).

The SQLite support requires to add 2 files:

  • core/Db/Adapter/Pdo/Sqlite.php
  • core/Db/Schema/Sqlite.php

It's in alfa state, but I want to be usable in a short time.

If some one want to test it, please, email me to josep.sanz@saltos.net.

Keywords: SQLite MySQL Database

@halfdan commented on April 26th 2013 Member

Can you just post a link to a public repository here?

@anonymous-matomo-user commented on April 26th 2013

Here is the link to the public repository:

It's in alfa version. I have to solve some problems yet!!!

@mattab commented on April 28th 2013 Member

Nice to know Piwik will run on SQLite, I wouldn't dream of it being possible actually ;-)

Good luck with the fork, let us know if there's anything we can change in core to make your life easier.

@anonymous-matomo-user commented on May 5th 2013

The current commit works fine now with the tracker. I had to modify the Tracker.php to add to the factory function the Pdo_Sqlite switch case for create the database object. It's a good idea to think how to do this automaticaly without modify the core piwik code. This is the first thing that would be nice to change in the piwik core.

@mattab commented on May 5th 2013 Member

thanks for suggestion. I would prefer to make all changes at once. Maybe you could send a patch at the end of your testing and we can improve + commit to Piwik git master? that would be awesome (and we can help making patch better, but would help to get started)

@mattab commented on February 6th 2014 Member

Dear Joseph, do you plan to maintain the fork in the future? if most features that we use would work wity SQLite, maybe we could add the support into Piwik core itself?

If it's as easy as adding one or two files in Piwik, maybe we could try them and enable our full tests suite to run using SQLite and check all features still work. Let us know if you're interested in that!

@anonymous-matomo-user commented on February 12th 2014

Hi Matthieu.

I paused the development of this plugin because I thought nobody used it. If you want to add the SQLite driver to the main code, I will continue with the development of this driver for SQLite and finish the pending tasks.

Where I can find information about how prepare a local test environment??? My objective is to execute the same unit tests that you are executing with MySQL database, but using the SQLite dababase.


@mattab commented on February 12th 2014 Member

We've updated the FAQ about other databases recently: http://piwik.org/faq/how-to-install/faq_55/
it also mentions we'd like to support SQLite :)

you can install webserver + Php + Mysql,
Alternatively you could also prepare a local test environment using this project: https://github.com/piwik/piwik-dev-environment#piwik-development-environment

Then to setup unit tests and be able to run the tests see: https://github.com/piwik/piwik/blob/master/tests/README.md

Let me know if you have any question!

If you make some progress and find that SQLIte support is possible without too much work, we will definitely consider it for Piwik core :) cheers!

@anonymous-matomo-user commented on March 9th 2014

I would love to test the sqlite version, I also posted about this in forums last year and its good to see someone is working on this.

@Astaoth commented on October 27th 2014

How is the SQlite support ? Does the module made by Anonymous-piwik-user work ? Can I tanke the files from https://github.com/josepsanzcamp/sqlite-for-piwik and use them without any issues ?

@mattab commented on October 28th 2014 Member

@Astaoth Likely not, there will be issues, since it's not official and not maintained since 2013.

@lensovet commented on November 9th 2015

Would be nice to actually get this. Lack of SQLite support is literally the only reason I haven't been running piwik on some websites for 2+ years now.

@lensovet commented on February 6th 2019

3+ years later this is still the primary reason why I'm not using Piwik/Matomo. The overhead of running an entire MySQL instance simply makes no sense for my use case.

@volomike commented on August 13th 2020

My two cents from a seasoned developer. SQLite is a database that performs extremely well on high-speed reads, but not great at all on high-speed writes, especially writes from multiple PHP connections at the same time (which is essentially what would be happening on an active campaign). In fact, the bulk of Piwik/Matomo activities would be in writes, not reads. Reads are something it would do for checking uniqueness of a connection, and for admin activities (like getting stats in a report to see how a campaign is going). Most of the time, you want that Piwik/Matomo to be high-availability on writes or you'll start losing traffic data due to not being available. This is where MySQL, and especially PostgreSQL, performs very well. So, one may be thrilled to get it going with SQLite, but don't count on it to handle a high-volume campaign very well. You may be thrilled to see the admin interface be snappier, but at the expense of data loss for data that never had a chance to make it into the database because the SQLite database was overwhelmed with too many connections at the same time on a highly active campaign.

So, no, I don't recommend SQLite at all for this kind of project. You can learn the architecture of how SQLite saves data, versus how MySQL or PostgreSQL saves data, and see how it's functional but not fast at all. Turn it around on reads, however, and SQLite is blazingly fast against MySQL and PostgreSQL on simultaneous connections.

@lensovet commented on August 13th 2020

I’m well aware of the perf impact of using SQLite vs. MySQL. I’m not looking to use Piwik on Google.com. I’m looking to use it on some internal websites that get at best 100 hits a day just to get a sense of what the most popular pages are.

@mattab commented on July 9th 2022 Member

Realistically we won't work on this ourselves in the future, so closing

This Issue was closed on July 9th 2022
Powered by GitHub Issue Mirror