PHPackages ikkez/f3-schema-builder - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. ikkez/f3-schema-builder ActiveLibrary[Database & ORM](/categories/database) ikkez/f3-schema-builder ======================= SQL Schema Builder Plugin for PHP Fat-Free Framework v2.2.6(3y ago)5854.0k↓15.9%14[4 issues](https://github.com/ikkez/f3-schema-builder/issues)[1 PRs](https://github.com/ikkez/f3-schema-builder/pulls)4GPL-3.0PHP Since May 18Pushed 3y ago13 watchersCompare [ Source](https://github.com/ikkez/f3-schema-builder)[ Packagist](https://packagist.org/packages/ikkez/f3-schema-builder)[ Docs](https://github.com/ikkez/f3-schema-builder)[ RSS](/packages/ikkez-f3-schema-builder/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (7)Dependencies (1)Versions (9)Used By (4) SQL Schema Builder ================== [](#sql-schema-builder) ### An extension for creating and altering SQL database tables. [](#an-extension-for-creating-and-altering-sql-database-tables) This plugin provides a SQL table schema builder for the **PHP Fat-Free Framework**. It might be useful for installation scripts, dynamic applications or CMS environments. Currently drivers for MySQL, SQLite, PostgreSQL & SQL Server are supported and fully tested. Further support for Sybase, Oracle and DB2 drivers are already included, but experimental. Handle with care and test your application. No warranty at all. This plugin was made for F3 version 3.x and requires PHP 5.4+ --- Installation ============ [](#installation) Just copy schema.php into F3's `lib/db/sql` folder. Done. If you use composer, you can add this package by running `composer require ikkez/f3-schema-builder:dev-master` Quick Start =========== [](#quick-start) To work with the Schema builder, you need an active SQL Connection. Create one like this: ``` $db = new \DB\SQL('mysql:host=localhost;port=3306;dbname='.$DBname, $user, $pass); ``` Now create a Schema object to work on. Inject the DB object into its constructor: ``` $schema = new \DB\SQL\Schema( $db ); ``` Create Tables ------------- [](#create-tables) Creating new tables is super easy. Let's have a look at this example: ``` $table = $schema->createTable('products'); $table->addColumn('title')->type($schema::DT_VARCHAR128); $table->addColumn('description')->type($schema::DT_TEXT); $table->build(); ``` The `createTable()` method returns a new table object (*instance of TableCreator*) for creation purpose. You may add new columns, indexes and change the primary key with it. New tables will always contain an auto-incremented, primary-key field named `id`, which is required for further SQL\\Mapper usage. All actions on a table object that affects its schema, are collected first and needs an additional `build()` command to be executed to really take effect on the database. If you're unsure of the result, you can run a simulation of that build method and have a look at the generated queries, the Schema Builder would have executed, with the following call: ``` $generated_queries = $table->build(false); print_r($generated_queries); ``` ### Add Columns [](#add-columns) Using the `$table->addColumn()` method will create a new Column object and adds it to the table object. We can use fluent calls for configuring these columns. ``` $column = $table->addColumn('deleted'); $column->type($schema::DT_BOOL); $column->nullable(false); $column->defaults(0); $column->after('id'); // or in a fluent way: $table->addColumn('deleted')->type($schema::DT_BOOL)->nullable(false)->defaults(0)->after('id'); ``` Here is a list of possible configuration methods: #### $column->type( $datatype \[ bool $force = FALSE\]) [](#column-type-datatype--bool-force--false) Set datatype of this column. Usually a constant of type \\DB\\SQL\\Schema::DT\_{datatype}. Have a look at the Column Class API for more details about datatypes. When `$force` is *TRUE*, the `$datatype` string is used as a raw value as it is and passed through the creation query (useful for custom data types). #### $column->nullable( $state ) [](#column-nullable-state-) Set this column as NULL or NOT NULL. Default is true / nullable. #### $column->defaults( $value ) [](#column-defaults-value-) Adds a default value for records. #### $column->after( $name ) [](#column-after-name-) Trys to place the new column behind an existing one. #### $column->index(\[ bool $unique = false \]) [](#column-index-bool-unique--false-) Add an index for that field. `$unique` makes it a UNIQUE INDEX. Alter Tables ------------ [](#alter-tables) Altering existing tables is quite similar to creating them, but offers a bunch more possibilities. A basic example: ``` $table = $schema->alterTable('products'); $table->addColumn('prize')->type($schema::DT_DECIMAL); $table->addColumn('stock')->type($schema::DT_INT); $table->dropColumn('foo_bar'); $table->renameColumn('title','name'); $table->build(); ``` As you can see, `$schema->alterTable()` returns a new table object (*instance of TableModifier*) for altering purpose, which provides all methods of the TableCreator plus some more actions like removing or renaming columns. Here is a list of method you can use: - **renameColumn( string $name, string $new\_name );** - **updateColumn( string $name, string $datatype );** - **dropColumn( string $name );** - **addIndex( string | array $columns, \[ bool $unique = false \]);** - **dropIndex( string | array $columns );** - **listIndex();** - **getCols(\[ bool $types = false \]);** The SchemaBuilder will quote all your table and column identifiers and should be resistant against preserved word errors. --- API Usage ========= [](#api-usage) Schema Class ------------ [](#schema-class) The Schema class provides you the following simple methods for: ### Managing databases [](#managing-databases) #### $schema->getDatabases(); [](#schema-getdatabases) Returns an array of all databases available (*except for SQLite*). Can be useful for installation purpose, when you want the user to select a database to work on. Therefor just create your DB connection without selecting a database like: ``` $db = new \DB\SQL('mysql:host=localhost;port=3306;dbname=', $user, $password); ``` Some DB engine default setups also grants simple read operations, without setting a user / password. ### Managing tables [](#managing-tables) #### $schema->getTables(); [](#schema-gettables) Returns an array of all tables available within the current database. #### $schema->createTable( string $tableName ); [](#schema-createtable-string-tablename-) Returns a new table object for creation purpose. #### $schema->alterTable( string $tableName ); [](#schema-altertable-string-tablename-) Returns a table object for altering operations on already existing tables. #### $schema->renameTable( string $name, string $new\_name, \[ bool $exec = true \]); [](#schema-renametable-string-name-string-new_name--bool-exec--true-) Renames a table. If you set `$exec` to `FALSE`, it will return the generated query instead of executing it. You can also use a short-cut on an altering table object, like `$table->rename( string $new_name, [ bool $exec = true ]);`. #### $schema->truncateTable( string $name, \[ bool $exec = true \]); [](#schema-truncatetable-string-name--bool-exec--true-) Clear the contents of a table. Set `$exec` to `FALSE` will return the generated query instead of executing it. #### $schema->dropTable( string $name, \[ bool $exec = true \]); [](#schema-droptable-string-name--bool-exec--true-) Deletes a table. Set `$exec` to `FALSE` will return the generated query instead of executing it. You can also use a short-cut on an altering table object, like `$table->drop([ bool $exec = true ]);`. #### $schema->isCompatible( string $colType, string $colDef ); [](#schema-iscompatible-string-coltype-string-coldef-) This is useful for reverse lookup. It checks if a data type is compatible with a given column definition, I.e. `$schema->isCompatible('BOOLEAN','tinyint(1)');`. TableCreator Class ------------------ [](#tablecreator-class) This class is meant for creating new tables. It can be created by using `$schema->createTable($name)`. #### $table->addColumn($key,$args = null); Column [](#table-addcolumnkeyargs--null-column) This creates a new Column object and saves a reference to it. You can configure the Column for your needs using further fluent calls, setting its public parameters or directly via config array like this: ``` $table->addColumn('title',array( 'type'=>\DB\SQL\Schema::DT_INT4, 'nullable'=>false, 'default'=>'untitled new entry', 'after'=>'id', 'index'=>true, 'unique'=>true, )); ``` #### $table->addIndex($columns, $unique = FALSE); [](#table-addindexcolumns-unique--false) You can add an index to a column by configuring the Column object while adding the new column, or like this: ``` $table->addIndex('name'); ``` For adding an combined index on multiple columns, just use an array as parameter: ``` $table->addIndex(array('name','email')); ``` #### $table->primary($pkeys); [](#table-primarypkeys) If you like to change the default `id` named primary-key right on the creation of a new table, you can use this one: ``` $table->primary('uid'); ``` This will rename the `id` field to `uid`. If you like to set a primary key on multiple columns (*a composite key*), use an array: ``` $table->primary(array('uid','version')); ``` The first element of this pkey array will always be treated as an auto-incremented field. example: ``` $table = $schema->createTable('news'); $table->addColumn('title')->type($schema::DT_VARCHAR128); $table->addColumn('bodytext')->type($schema::DT_TEXT); $table->addColumn('version')->type($schema::DT_INT8)->nullable(false)->defaults(1); $table->primary(array('id', 'version')); $table->build(); ``` Now your primary key is build upon 2 columns, to use records like `id=1, version=1` and `id=1, version=2`. #### $table->setCharset( string $str \[, $collation = 'unicode' \]); [](#table-setcharset-string-str--collation--unicode-) This method will set a custom charset and default collation to a new table. In example, this will set an `utf8mb4` charset and a `utf8mb4_unicode_ci` collation as default for the new table: ``` $table = $schema->createTable('comments'); $table->setCharset('utf8mb4'); // ... ``` NB: currently only effects *MySQL*. 1-4 Multibyte UTF8 chars work out of the box in Postgre, SQlite. No workaround for SQL Server yet. #### $table->build(\[ bool $exec = true \]); [](#table-build-bool-exec--true-) This will start the table generation process and executes all queries if `$exec` is `TRUE`, otherwise it will just return all queries as array. TableModifier Class ------------------- [](#tablemodifier-class) This class is ment for creating new tables. It can be created by using `$schema->alterTable($name)`. #### $table->addColumn($key,$args = null); Column [](#table-addcolumnkeyargs--null-column-1) Adds a new column #### $table->renameColumn( string $name, string $new\_name ); [](#table-renamecolumn-string-name-string-new_name-) This is used to rename an existing column. #### $table->updateColumn( string $name, string datatype, \[ bool $force = false \]); [](#table-updatecolumn-string-name-string-datatype--bool-force--false-) This is used to modify / update the column's datatype. #### $table->dropColumn( string $name ); [](#table-dropcolumn-string-name-) Tries to removes a column from the table, if it exists. #### $table->addIndex( string | array $columns, \[ bool $unique = false \]); [](#table-addindex-string--array-columns--bool-unique--false-) Creates a index or unique index for one or multiple columns on the table. #### $table->dropIndex( string | array $columns ); [](#table-dropindex-string--array-columns-) Drops an index. #### $table->listIndex(); [](#table-listindex) Returns an associative array with index name as key and `array('unique'=>$value)` as value. #### $table->primary( string | array $pkeys); [](#table-primary-string--array-pkeys) Creates a new primary or composite key on the table. #### $table->getCols(\[ bool $types = false \]); [](#table-getcols-bool-types--false-) Returns an array of existing table columns. If `$types` is set to `TRUE`, it will return an associative array with column name as key and the schema array as value. #### $table->build(\[ bool $exec = true \]); [](#table-build-bool-exec--true--1) This generates the queries needed for the table alteration and executes them when `$exec` is true, otherwise it returns them as array. #### $table->rename( string $new\_name, \[ bool $exec = true \]); [](#table-rename-string-new_name--bool-exec--true-) This will instantly rename the table. Notice: Instead of being executed on calling `build()` the execution is controlled by `$exec`. #### $table->drop(\[ bool $exec = true \]); [](#table-drop-bool-exec--true-) This will instantly drop the table. Notice: Instead of being executed on calling `build()` the execution is controlled by `$exec`. #### $table->isCompatible( string $colType, string $column ); [](#table-iscompatible-string-coltype-string-column-) This is useful for reverse lookup. It checks if a data type is compatible with an existing column type, I.e. `$table->isCompatible('BOOLEAN','hidden');`. #### $table->setCharset( string $str \[, $collation = 'unicode' \]); [](#table-setcharset-string-str--collation--unicode--1) This method will set a custom charset and collation and will convert existing tables upon `build()`. In this example we will convert a table to an `utf8mb4` charset and a `utf8mb4_general_ci` collation: ``` $table = $schema->alterTable('comments'); $table->setCharset('utf8mb4','general'); // ... ``` Column Class ------------ [](#column-class) The method `$table->addColumn($columnName);` adds a further column field to the selected table and creates and returns a new Column object, that can be configured in different ways, before finally building it. #### type( string $datatype, \[ bool $force = false \]) [](#type-string-datatype--bool-force--false-) Set datatype of this column. The `$force` argument will disable the datatype check with the included mappings and uses your raw string as type definition. You can use these available mapped types as constants in \\DB\\SQL\\Schema: Type Description Storage size Save Range DT\_BOOL DT\_BOOLEAN resolves in a numeric at least 1 byte 0,1 DT\_INT1 DT\_TINYINT exact integer at least 1 byte lower: 0, upper; 255 DT\_INT2 DT\_SMALLINT exact integer at least 2 bytes ±32,768 DT\_INT4 DT\_INT exact integer 4 bytes ±2,147,483,648 DT\_INT8 DT\_BIGINT exact integer at most 8 bytes ±2^63 DT\_FLOAT approximate numeric 4 bytes ±1.79E + 308 DT\_DECIMAL DT\_DOUBLE exact numeric at least 5 bytes ±10^38+1 DT\_VARCHAR128 character string 128 bytes 128 chars DT\_VARCHAR256 character string 256 bytes 256 chars DT\_VARCHAR512 character string 512 bytes 512 chars DT\_TEXT character string max length 2,147,483,647 DT\_LONGTEXT character string max length 4,294,967,295 DT\_DATE Y-m-d 3 bytes DT\_DATETIME Y-m-d H:i:s 8 bytes DT\_TIMESTAMP Y-m-d H:i:s 8 bytes DT\_BLOB DT\_BINARY bytes usage: ``` $table = $schema->alterTable('news'); $table->addColumn('author')->type(\DB\SQL\Schema::DT_VARCHAR128); // or $table->addColumn('bodytext')->type($schema::DT_TEXT); // or the shorthand $table->addColumn('bodytext')->type_text(); // save changes to database $table->build(); ``` there are also a bunch of shorthand methods available, you can use instead of `type()`: - **type\_tinyint()** - **type\_smallint()** - **type\_int()** - **type\_bigint()** - **type\_float()** - **type\_decimal()** - **type\_text()** - **type\_longtext()** - **type\_varchar(\[ $length = 255 \])** - **type\_date()** - **type\_datetime()** - **type\_timestamp(\[ $asDefault = FALSE \])** - **type\_blob()** - **type\_bool()** #### passThrough(\[ bool $state = TRUE \]) [](#passthrough-bool-state--true-) When pass-through is enabled, the datatype value is treated as a raw value, which makes it possible to set any other custom data type that is not covered by the included aliases. This is equivalent to `type()` with `$force = TRUE`. #### nullable( bool $state ) [](#nullable-bool-state-) Set this column as NULL or NOT NULL. Default is `TRUE` / nullable. You can set defaults to nullable fields as well. #### defaults( mixed $value ) [](#defaults-mixed-value-) Adds a default value for records. Usually a *string* or *integer* value or `NULL`. **CURRENT\_TIMESTAMP as dynamic default value** But if you like to add a timestamp of the current time to new inserted records, you can use a TIMESTAMP field with a special default value to achieve this. ``` $table->addColumn('creation_date')->type($schema::DT_TIMESTAMP)->defaults($schema::DF_CURRENT_TIMESTAMP); // a shorthand would be: $table->addColumn('creation_date')->type_timestamp(TRUE); ``` #### after( string $name ) [](#after-string-name-) Try to place the new column behind an existing one. (*only works for SQLite and MySQL*) #### index(\[ bool $unique = false \]) [](#index-bool-unique--false-) Add an index for that field. `$unique` makes it a UNIQUE INDEX. #### copyfrom( string | array $args ) [](#copyfrom-string--array-args-) Feed column from array or hive key. #### getColumnArray() [](#getcolumnarray) Returns an array of the current column configuration #### getTypeVal() [](#gettypeval) Returns the resolved column data type. License ------- [](#license) GPLv3 --- Like this Plugin? [![buy me a beer](https://camo.githubusercontent.com/32513bbc6baaf4bbce1bd5a2a9e870f96ea6c4e89766f488d821f33dca3a9b7d/68747470733a2f2f7261772e6769746875622e636f6d2f696b6b657a2f426565722d446f6e6174696f6e2d427574746f6e2f67682d70616765732f696d672f626565725f646f6e6174696f6e5f627574746f6e5f73696e676c652e706e67 "donate")](https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=44UHPNUCVP7QG) ### Health Score 42 — FairBetter than 90% of packages Maintenance19 Infrequent updates — may be unmaintained Popularity43 Moderate usage in the ecosystem Community27 Small or concentrated contributor base Maturity68 Established project with proven stability Bus Factor1 Top contributor holds 94.9% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~403 days Recently: every ~451 days Total 8 Last Release 1190d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/84cac52f5889d2bf6e710f2139dfc884824b2d7ce9c48a3bfe90704a94c85722?d=identicon)[ikkez](/maintainers/ikkez) --- Top Contributors [![ikkez](https://avatars.githubusercontent.com/u/1177647?v=4)](https://github.com/ikkez "ikkez (224 commits)")[![xfra35](https://avatars.githubusercontent.com/u/1838531?v=4)](https://github.com/xfra35 "xfra35 (6 commits)")[![kumy](https://avatars.githubusercontent.com/u/176794?v=4)](https://github.com/kumy "kumy (1 commits)")[![emanwebdev](https://avatars.githubusercontent.com/u/1505372?v=4)](https://github.com/emanwebdev "emanwebdev (1 commits)")[![stevewasiura](https://avatars.githubusercontent.com/u/206041?v=4)](https://github.com/stevewasiura "stevewasiura (1 commits)")[![Vladzimir](https://avatars.githubusercontent.com/u/5346225?v=4)](https://github.com/Vladzimir "Vladzimir (1 commits)")[![nimah79](https://avatars.githubusercontent.com/u/20343056?v=4)](https://github.com/nimah79 "nimah79 (1 commits)")[![krovak](https://avatars.githubusercontent.com/u/1247370?v=4)](https://github.com/krovak "krovak (1 commits)") --- Tags fat-free-frameworkmigrate-databasemssqlmysqlpostgresqlschema-buildersqlsqlitesqlF3fatfree ### Embed Badge ![Health badge](/badges/ikkez-f3-schema-builder/health.svg) ``` [![Health](https://phpackages.com/badges/ikkez-f3-schema-builder/health.svg)](https://phpackages.com/packages/ikkez-f3-schema-builder) ``` ### Alternatives [doctrine/dbal Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management. 9.7k578.4M5.6k](/packages/doctrine-dbal)[jdorn/sql-formatter a PHP SQL highlighting library 3.9k115.1M102](/packages/jdorn-sql-formatter)[doctrine/sql-formatter a PHP SQL highlighting library 1.9k166.0M85](/packages/doctrine-sql-formatter)[illuminate/database The Illuminate Database package. 2.8k52.4M9.4k](/packages/illuminate-database)[phpmyadmin/sql-parser A validating SQL lexer and parser with a focus on MySQL dialect. 47950.4M55](/packages/phpmyadmin-sql-parser)[ifsnop/mysqldump-php PHP version of mysqldump cli that comes with MySQL 1.3k5.5M69](/packages/ifsnop-mysqldump-php) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)