New Addon: SQL Driver for Cockpit

Addons
#1

This addon allows to use MySQL/ MariaDB/ PostgreSQL databases instead of default Mongo/ SQLite.

Requirements

  • Cockpit CMS (next or legacy)
  • MySQL 5.7.9/ MariaDB 10.2.6/ PostgreSQL 9.4
  • PHP 7.1
  • Enabled PHP extensions: pdo, pdo_mysql/ pdo_pgsql
6 Likes
SQLite performance (indexing) and Discussion to Switch to MongoDB
#2

:eyes: thank you for making this effort!

#3

This is great! I added it to the addon list :slight_smile:

#4

Thanks @piotr-cz!
Is this solution has been tested in large application context? I’m in project case Sqlite seems to be too light solution and client server does not allow mongodb usage.

#5

Honestly no, it hasn’t been tested on large databases on production.
My goal was to get access to database for 3p services and SQL tools when developing locally.

In my short testing I just found that for example MySQL/ PostgreSQL is slower that SQLite, but that’s not surprise as it’s basically file access vs database server connection.

However you may and should perform some database optimisations (as described here https://github.com/piotr-cz/cockpit-sql-driver#manual-database-optimisations) like for example adding indexes to columns that are usually searched trough.

#6

Thanks a lot, I gonna test it.

#7

I’ve prepared simple script for performance tests: https://github.com/piotr-cz/cockpit-sql-driver/blob/master/stress-tests.php

It measures time to retrieve all items from all collections in database and there’s an option to pass path to custom config file.

I’d imagine that the MySQL/ PostgreSQL with column indexes would outperform SQLite when using large datasets and retrieving results with conditions or sorting.

The difference is that Cockpit’s SQLite driver executes compiled php function on every row, while this SQL Driver uses MySQL/ PostgreSQL statements.

#8

Is anyone out there using this addon in production?

If so, I’m interested in you experiences (possible problems, performance, and so on).

#9

I’ve just released v1.0.0-beta.2 which fixes compatibility with Cockpit v0.9.3+.
See related Issue.

Problem occurs when deleting (moving to trash) entries in Cockpit v0.9.3.

Please be warned that as at this moment Cockpit doesn’t have public API for storage drivers, this addon may become dysfunctional whenever storage API changes in future Cockpit releases.

#10

I will be using it in production. Settin up a new site atm using Cockpit and this driver

#11

Sadly my version is not supported and my choice of DB.

Driver requires database server version >= 10.2.6, got 10.1.44-MariaDB-0ubuntu0.18.04.1
#12

Unfortunately too many shared hosting providers are still on old database versions.
For example MySQL 5.6.x which has been released with feature set as of 2013.

#13

Can this project use couchbase instead of mongodb?

#14

Sorry, I don’t have experience with Couchbase.
At this moment addon uses php PDO extensions which doesn’t support Couchbase.

It’s possible to prepare a Couchbase driver, however it would take some effort as some of the PDO features would have to be ported.

#15

no, couchbase is not supported.

#16

Hey,

I just configured cockpit to use your addon, thanks for this!

So far it works great, but I ran into a problem:
when I try to upload an image to the assets section, the file is not uploaded.
When the assets section is refreshed I can see an entry, but its corrupt (no meta, no image).

I checked the issues on github but didnt find any hint.

Do you have an idea?

Thanks!

#17

Hi,
Thanks for reporting, there was a bug that prevented storing correctly asset entries in database.
I’m not using assets in Cockpit so this passed my manual tests.

New version v1.0.0-rc.2 contains bugifx.

If you encounter any errors on the Cockpit assets page (check DevTools console) or any other strange behavior related to assets, it may be necessary to remove asset entries manually from database:

  • look up the cockpit/assets table in database editor and remove all entries
  • you may also remove uploaded files in COCKPIT_STORAGE_FOLDER/uploads/