NAME

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

VERSION

version 1.077

SYNOPSIS

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

DESCRIPTION

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

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

Database Backend

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

Postgres backend
MySQL backend
SQLite backend
DBIx::Class backend

Declaring a Schema

The 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 JSON Schema. 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 title, description, and even an example value. For more information on what can be defined, see the docs on JSON Schema.

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 (type), field order (x-order), enumerated values (enum), required fields (required), ID fields (x-id-field), foreign keys (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 schema configuration will be merged with the information Yancy reads from the database, with the configuration overriding the defaults from the database.

For a schema named people that has 3 fields (an integer id and two strings, name (not nullable) and 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,
                },
            },
        },
    },

Types

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

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

  • type => "integer" - A number field (<input type="number" >)

  • type => "number" - A number field (<input type="number" >)

  • type => "string", format => "date" - A date field (<input type="date">)

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

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

  • type => "string", format => "url" - A URL input (<input type="url">)

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

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

  • 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 x-html-field => $field_name to that field.

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

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

  • 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.

Field Validation

These additional fields can be used to validate the data:

  • readOnly will set the input field as read-only

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

  • minimum for numeric fields, the minimum value

  • maximum for numeric fields, the maximum value

  • minLength for string fields, the minimum length

  • maxLength for string fields, the maximum length

ID Fields

The 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.

Required Values

JSON Schema allows marking properties as required using the 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.

Default Values

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

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

Nullable Values

If a value can be null (undef in Perl terms) in addition to its declared type (string, integer, etc...), you must add it to the 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: Expected string - Got null.. The correct way to fix this error is to add null as an option for the field's type.

Relationships (Foreign Keys)

Yancy will detect foreign key relationships and set the 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 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 x-display-field to the field in the target schema you want to use.

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

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

Filters

The 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 Mojolicious::Plugin::Yancy. See "yancy.filter.add" in Mojolicious::Plugin::Yancy 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.

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

Field Ordering

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

Documenting Your Schema

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

Titles and Descriptions

The title and 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)
                    END
                properties => {
                    name => {
                        title => 'Full Name',
                        description => 'A full, legal name.',
                    },
                },
            },
        },
    };

Hiding and Ignoring

If the 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 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 x-hidden. Fields currently cannot be ignored.

Configuring the List View

By default, the Yancy editor will display all of the columns in the table with minimal formatting. The 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 title and template keys. Inside the template key, use fields from the row with {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>)' },
        ],
    },

Additional Actions

If there is a main page in the application to display the data in the schema, you can set the 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 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 {field}.

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

SEE ALSO

Yancy::Guides::Model, Yancy::Backend, Yancy, Mojolicious::Plugin::Yancy

AUTHOR

Doug Bell <preaction@cpan.org>

COPYRIGHT AND LICENSE

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.