Download the PHP package fulfillment/picodb without Composer

PicoDb

PicoDb is a minimalist database query builder for PHP.

Features

• Easy to use, easy to hack, fast and very lightweight
• Supported drivers: Sqlite, Mssql, Mysql, Postgresql
• Requires only PDO
• Use prepared statements
• Handle schema migrations
• Fully unit tested on PHP 5.3, 5.4, 5.5, 5.6 and 7.0
• License: MIT

Requirements

• PHP >= 5.3
• PDO extension
• Sqlite, Mssql, Mysql or Postgresql

Author

Frédéric Guillot

Documentation

Installation

Database connection

Sqlite:

The Sqlite driver enable foreign keys by default.

Microsoft SQL server:

Optional attributes:

• schema_table

Mysql:

Optional attributes:

• charset
• schema_table
• port
• ssl_key
• ssl_cert
• ssl_key
• persistent
• timeout
• verify_server_cert
• case

Postgres:

Optional attributes:

• port
• schema_table

Connecting from an environment variable:

Let's say you have defined an environment variable:

PicoDb can parse automatically this URL for you:

Connecting from a URL

Execute any SQL query

• Returns a PDOStatement if successful
• Returns false if there is a duplicate key error
• Throws a SQLException for other errors

Insertion

or

Fetch last inserted id

Transactions

• Returns true if the callback returns null
• Returns the callback return value otherwise
• Throws an SQLException if something is wrong

or

Fetch all data

Updates

or

Remove records

Sorting

or

or

Multiple sorting:

Limit and offset

Fetch only some columns

Fetch only one column

Many rows:

One row:

Custom select

Distinct

Group by

Count

Sum

Sum column values during update

Add the value 42 to the existing value of the column "mycolumn":

Increment column

Increment a column value in a single query:

Decrement column

Decrement a column value in a single query:

Exists

Returns true if a record exists otherwise false.

Left joins

or

Equals condition

IN condition

IN condition with subquery

Like condition

Case-sensitive (only Mysql and Postgres):

Not case-sensitive:

Lower than condition

Lower than or equal condition

Greater than condition

Greater than or equal condition

IS NULL condition

IS NOT NULL condition

Multiple conditions

Add conditions are joined by a AND.

How to make a OR condition:

Debugging

Log generated queries:

Mesure each query time:

Get the number of queries executed:

Get log messages:

Large objects (LOBs)

Insert a file:

Insert from a stream:

Fetch a large object as a stream (Postgres only):

Fetch a large object as a string:

Drivers:

• Postgres
  • Column type: bytea
• Sqlite and Mysql
  • Column type: BLOB
  • PDO do no not supports the stream feature (returns a string instead)

Hashtable (key/value store)

How to use a table as a key/value store:

Add/Replace some values:

Get all values:

or

Get a specific value:

Schema migrations

Define a migration

• Migrations are defined in simple functions inside a namespace named "Schema".
• An instance of PDO is passed to first argument of the function.
• Function names has the version number at the end.

Example:

Run schema update automatically

• The method check() execute all migrations until the version specified
• If an error occurs, the transaction is rollbacked
• Foreign keys checks are disabled if possible during the migration

Example:

Use a singleton to handle database instances

Setup a new instance:

Get this instance anywhere in your code: