# PODNAME: Yancy::Guides::Schema
# ABSTRACT: How to connect to and describe your schema



=head1 NAME

Yancy::Guides::Schema - How to connect to and describe your schema

=head1 VERSION

version 1.088


    use Mojolicious::Lite;
    plugin Yancy => {
        backend => 'pg://localhost/myapp',
        read_schema => 1,
        schema => {
            users => {
                title => 'Users',
                description => 'The authorized user accounts',


This document describes how to configure a database connection
(L<Yancy::Backend>) and how to add annotations to your schema.

For information about how to use backends, see L<Yancy::Guides::Model>
or L<Yancy::Backend> for the backend API documentation.

=head1 Database Backend

The C<backend> URL defines what database to use and how to connect to
it. Each backend has its own format of URL.


=item L<Postgres backend|Yancy::Backend::Pg>

=item L<MySQL backend|Yancy::Backend::Mysql>

=item L<SQLite backend|Yancy::Backend::Sqlite>

=item L<DBIx::Class backend|Yancy::Backend::Dbic>


=head1 Declaring a Schema

The C<schema> data structure defines what data is in the database.
Each key in this structure refers to the name of a schema, and the
value describe the fields for items inside the schema.

Each backend may define a schema differently. For a relational
database like Postgres or MySQL, a schema is a table, and the fields
are columns. For an ORM like DBIx::Class, the schemas are ResultSet
objects. For a document store like MongoDB, the schemas are
collections. See your backend's documentation for more information.

Schemas are configured using L<JSON Schema|http://json-schema.org>.
The JSON Schema defines what fields (properties) an item has, and what
type of data those field have. The JSON Schema also can define
constraints like required fields or validate strings with regular
expressions. The schema can also contain metadata like a C<title>,
C<description>, and even an C<example> value. For more information on
what can be defined, see L<the docs on JSON Schema|http://json-schema.org>.

=head2 Yancy Generated Schema

By default, Yancy will read your database to fill in as much schema
information as it can. This includes the field types (C<type>), field
order (C<x-order>), enumerated values (C<enum>), required fields
(C<required>), ID fields (C<x-id-field>), foreign keys
(C<x-foreign-key>), and some formatting (date/time mostly). You can (and
should) add your own annotations and corrections while configuring Yancy
(especially friendly titles and descriptions). The C<schema>
configuration will be merged with the information Yancy reads from the
database, with the configuration overriding the defaults from the

For a schema named C<people> that has 3 fields (an integer C<id> and two
strings, C<name> (not nullable) and C<email> (nullable)), Yancy will
generate a JSON schema that looks like this:

    schema => {
        people => {
            required => [ 'name' ],
            properties => {
                id => {
                    type => 'integer',
                    readOnly => 1,
                    'x-order' => 1,
                name => {
                    type => 'string',
                    'x-order' => 2,
                email => {
                    type => [ 'string', 'null' ],
                    'x-order' => 3,

=head2 Types

Yancy generates input elements based on the C<type>, and C<format> of
the object's properties.


=item * C<< type => "boolean" >> - A Yes/No field.  Boolean fields
support input values C<0>, C<1>, C<"true">, and C<"false">. They will be
stored as C<0>, and C<1> in the database.

=item * C<< type => "integer" >> - A number field (C<< <input type="number" > >>)

=item * C<< type => "number" >> - A number field (C<< <input type="number" > >>)

=item * C<< type => "string", format => "date" >> - A date field (C<< <input type="date"> >>)

=item * C<< type => "string", format => "date-time" >> - A date/time field (C<< <input type="datetime-local"> >>)
Date/time fields can have a special C<default> value: C<now>. This will
be replaced with the current date/time in the database.

=item * C<< type => "string", format => "email" >> - A e-mail address (C<< <input type="email"> >>)

=item * C<< type => "string", format => "url" >> - A URL input (C<< <input type="url"> >>)

=item * C<< type => "string", format => "tel" >> - A telephone number (C<< <input type="tel"> >>)

=item * C<< type => "string", format => "textarea" >> - A multiline text field (C<< <textarea> >>)

=item * C<< type => "string", format => "markdown" >> - A Markdown field
that shows a live preview of the rendered HTML. The Markdown can be
saved as HTML in another field by adding C<< x-html-field => $field_name >>
to that field.

=item * C<< enum => [...], type => "..." >> - A C<< <select> >> element.
This can be of any type.

=item * C<< type => "string", format => "filepath" >> - A file upload
field (C<< <input type="file"> >>). See L<Yancy::Plugin::File> for more

=item * C<< type => "string", format => "binary" >> - A field containing binary data.
This currently does not generate any input field, but it may become
another way to upload files in the future.


JSON schemas allow specifying multiple types for a field using an array.
If a field has multiple types, the generated form will use the first
type to decide what kind of field to display.

=head2 Field Validation

These additional fields can be used to validate the data:


=item * C<readOnly> will set the input field as read-only

=item * C<pattern> for string fields, a string that can be used as a regex, like C<< pattern => '^foo-\d+$' >>.

=item * C<minimum> for numeric fields, the minimum value

=item * C<maximum> for numeric fields, the maximum value

=item * C<minLength> for string fields, the minimum length

=item * C<maxLength> for string fields, the maximum length


=head2 ID Fields

The C<x-id-field> schema config sets the name of the schema's ID field
to use to uniquely identify individual items. By default, Yancy tries to
find your ID field(s) from the database. If you want the schema to use
some other identifier (e-mail address or username for example), you
should set this configuration key.

    people => {
        'x-id-field' => 'email',
        properties => { ... },

This field can be any unique identifier, but it will be the ID that
Yancy uses for all of its operations. This means that it will appear in
URLs and other internal identifiers.

Composite keys are defined with an array reference of columns.

=head2 Required Values

JSON Schema allows marking properties as required using the C<required>
property, which must be an array of property names. Yancy will discover
any fields that your database absolutely requires, but you may override
this if necessary.

    schema => {
        people => {
            required => [ 'name', 'email' ],
            properties => {
                id => {
                    type => 'integer',
                    readOnly => 1,
                name => {
                    type => 'string',
                email => {
                    type => 'string',

Required values will be marked as such in the HTML.

=head2 Default Values

The default value for a field will be set in the C<default>. This is
what will be set (by the database) if the field is missing or C<undef>
(C<null> in JavaScript).

The special value C<"now"> on date-time fields will be replaced with the
current date/time from the database.

=head2 Nullable Values

If a value can be C<null> (C<undef> in Perl terms) in addition to its
declared type (C<string>, C<integer>, etc...), you must add it to the
C<type> field by using an array of types:

    schema => {
        people => {
            required => [ 'name' ],
            properties => {
                id => {
                    type => 'integer',
                    readOnly => 1,
                name => {
                    type => 'string', # Required and must be a string
                email => {
                    type => [ 'string', 'null' ], # Can be null

If you don't do this, and still include the field in an object, you will
get an error: C<Expected string - Got null.>. The correct way to fix
this error is to add C<null> as an option for the field's type.

=head2 Relationships (Foreign Keys)

Yancy will detect foreign key relationships and set the C<x-foreign-key>
field of the property to the schema it links to.

    schema => {
        user => {
            'x-id-field' => 'username',
            properties => {
                username => {
                    type => 'string',
        comment => {
            properties => {
                username => {
                    type => 'string',
                    'x-foreign-key' => 'user',

By default, the target schema's first list column (if C<x-list-columns>
is defined) or the schema's ID field is used to show the current value
of the relationship. This can be changed by setting C<x-display-field> to
the field in the target schema you want to use.

By default, the target schema's ID field (C<x-id-field> or C<id>) will
be used as the value for the foreign key. This can be changed by setting
C<x-value-field> to the field in the target schema you want to use.

B<NOTE:> This support is experimental and will need further development
to support more possibilities of foreign key linkages. Patches

=head2 Filters

The C<x-filter> key is an array of filter names to run when setting or
creating an item. Filters can allow for hashing passwords, for example.
Filters are added by plugins or during configuration of
L<Mojolicious::Plugin::Yancy>. See
L<Mojolicious::Plugin::Yancy/yancy.filter.add> for how to create
a filter in your app.

Instead of a filter name, you can provide an array. The first member
will be the name, and any further members will be passed to the filter
code-ref as parameters after the mandatory three.

B<NOTE>: The filters can be bypassed by using the backend API directly,
and are not currently handled by L<Yancy::Model>. In the future, filters
will be moved to L<Yancy::Model>.

=head2 Field Ordering

Yancy will read the order of the fields in your table and set the
C<x-order> property. Fields in the list view and edit forms are be
sorted by their C<x-order>, and then by their name (alphabetically).
Fields that do not have C<x-order> set will be sorted after fields that

=head1 Documenting Your Schema

There are some extended fields you can add to your schema definition
to control how it is treated by Yancy.

=head2 Titles and Descriptions

The C<title> and C<description> fields are the most common and important
fields for documenting your schema. The title is what appears in the
Yancy editor as the name of the schema or field, and the description can
help users with how to edit the data inside.

    use Mojolicious::Lite;
    use Mojo::Util qw( unindent trim );
    plugin Yancy => {
        schema => {
            employees => {
                title => 'Employees',
                description => <<~END,
                    The employees of Planet Express.

                    * [View the employee health plan](/decapod-life)
                    * [Latest Good News](/news)
                properties => {
                    name => {
                        title => 'Full Name',
                        description => 'A full, legal name.',

=head2 Hiding and Ignoring

If the C<x-hidden> field is set to true, the schema will be hidden from
the list in the Yancy web app. This does not prevent using the API to
edit this data.

However, if the C<x-ignore> field is true, Yancy will ignore this schema
entirely. It will not be added to the API, and not shown in the editor.

Individual fields can also be hidden by setting C<x-hidden>. Fields
currently cannot be ignored.

=head2 Configuring the List View

By default, the Yancy editor will display all of the columns in the
table with minimal formatting.  The C<x-list-columns> key should be an
array of columns to display on the list view, in order. This helps put
only the most useful information on the list page.

    people => {
        'x-list-columns' => [ 'name', 'email' ],
        properties => { ... },

Instead of field names, virtual columns can also be made out of
templates using a hash with C<title> and C<template> keys. Inside the
template key, use fields from the row with C<{field}>. HTML will be
rendered, so you can add custom formatting (colors, icons).

    people => {
        'x-list-columns' => [
            { title => "Person", template => '{name} <a href="{email}">Mail</a>)' },

=head2 Additional Actions

If there is a main page in the application to display the data in the
schema, you can set the C<x-view-url> field to add a link to that page.

If there is a main page to view a single item in the schema, you can set
the C<x-view-item-url> field to add an icon to each row in the list to
view that row in the app. Like list column templates, you can add data
from the row into the URL using C<{field}>.

    users => {
        'x-view-url' => '/users/search',
        'x-view-item-url' => '/users/{user_id}/profile',

=head1 SEE ALSO

L<Yancy::Guides::Model>, L<Yancy::Backend>, L<Yancy>,

=head1 AUTHOR

Doug Bell <preaction@cpan.org>


This software is copyright (c) 2021 by Doug Bell.

This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.