facil_reference.yaml

# This document explains everything you can specify in facil.yaml. You can of course often
# get away with a much simpler config file. Click the link below to view the minimal
# config file that is added to your project if you build without an existing config file:
# https://github.com/cmeeren/Facil/blob/master/src/Facil.Generator/facil_minimal.yaml


# The optional 'configs' section allows you to specify where variables are resolved from.
# Each array item translates more or less exactly into a call to the corresponding
# Microsoft.Extensions.Configuration.ConfigurationBuilder.Add* method, in the order you
# specify here (i.e., later config sources will override already added config sources if
# there are collisions). Any config type may be added multiple times. This section is only
# required if you are using variables (e.g. for the connection string). For more
# information, see:
# - https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration
# - https://docs.microsoft.com/en-us/dotnet/api/microsoft.extensions.configuration.configurationbuilder
configs:

  # 'appSettings' allow you to use configuration values from appsettings.json.
  - appSettings: path/from/project/to/appsettings.json

  # 'userSecrets' allow you to use configuration values from user secrets.
  - userSecrets: 80d5e7ec-f577-4360-b60a-f10ea004da1d

  # 'envVars' allow you to use configuration values from environment variables. Supply the
  # prefix that environment variable names must start with. The prefix will be removed
  # from the environment variable names.
  - envVars: ''


# Rulesets allow you to specify what you should generate. Each ruleset may only use a
# single DB and generates a single file. You will often only need one ruleset.
rulesets:

  # 'connectionString' is the only required property of a ruleset. You may use a
  # variable here with the syntax $(VariableName).
  - connectionString: $(ConnectionStrings:Db)

    # The filename and namespace/module declaration of the generated file. The values
    # below are the defaults.
    filename: DbGen.fs
    namespaceOrModuleDeclaration: module DbGen

    # The base path for all scripts (including temp table definitions) in this ruleset,
    # relative to the project directory.
    scriptBasePath: ../some/path

    # 'prelude' contains anything you want to insert at the top of the generated file.
    # This can for example be preprocessor directives to disable compiler warnings.
    # (#nowarn "49" is always inserted by Facil and is just shown as an example.) You
    # don't have to think about the initial indentation; all lines will be deindented so
    # that the least indented line is not intended in the generated file.
    prelude: |
      #nowarn "49"

    # Rules to specify which stored procedures are included in the generation and how they
    # are generated. Each rule requires either 'include' or 'for'. All other properties
    # are optional. The effective config of any stored procedure is taken from all
    # matching rules, with values from later rules overriding values from earlier rules.
    procedures:

      # 'include' contains a regex to be matched against the schema-qualified procedure
      # name (e.g. dbo.MyProcedure). All procedures that match the 'include' pattern for
      # any rule will be included in generation, unless they are also matched by a
      # corresponding 'except' pattern.
      #
      # The rule below specifies that all procedures should be included in generation,
      # except the procedure 'dbo.ProcToExclude', and that all the matched procedures
      # should use ValueOption instead of Option for nullable output columns (unless
      # overridden by later rules).
      #
      # Note that all regex patterns in the YAML file are case sensitive by default.
      # Prefix them with '(?i)' to make them case insensitive (e.g. '(?i)^dbo\.myProc$').
      - include: .*
        except: '^dbo\.ProcToExclude$'
        voptionOut: true

      # 'for' contains a regex that specifies which already included procedures (also from
      # 'include' in later rules) this rule applies to. It will not cause additional
      # procedures to be included.
      #
      # The rule below applies to all procedures containing 'dbo.User_' except those
      # containing 'dbo.User_Id'.
      - for: 'dbo\.User_'
        except: 'dbo\.User_Id'

        # 'result' specifies the type of the returned rows. Valid values:
        #  - 'auto' (default): If a single matching table DTO (see later) is found, use
        #    that. Otherwise, use anonyomus records. Will warn and fall back to anonyomus
        #    records if there are multiple matching table DTOs. Will not match
        #    voption-enabled table DTOs if voptionOut if false, and vice versa.
        #  - 'anonymous': Use anonymous records.
        #  - 'nominal': Generate and use a normal record type (a unique type is defined
        #    for each procedure).
        #  - A qualified table name, e.g. 'dbo.User': If the name matches an included
        #    table DTO (see later), use that. Otherwise, treat as a custom record type as
        #    described in the next point.
        #  - A (sufficiently qualified) user record type, e.g. 'MyModule.MyDto'. This must
        #    be a record whose record constructor is accessible by the generated code.
        result: dbo.User

        # 'paramDto' specifies the parameter type of the WithParameters overload that
        # accepts a generic DTO type. Valid values:
        #  - 'inline' (default): Uses SRTP to accept any type that has the correct
        #    members.
        #  - 'nominal': Generate and use a normal record type (a unique type is defined
        #    for each procedure).
        #  - 'skip': Skips the overload. If you have many procedures and generally don't
        #    use these overloads, removing them may improve IDE performance and
        #    compilation times. Default false.
        paramDto: nominal

        # Deprecated; use 'paramDto: skip' instead.
        #
        # If true, will skip the 'WithParameters' overload that acceps a generic DTO type.
        # If you have many procedures and generally don't use these overloads, removing
        # them may improve IDE performance and compilation times. Default false.
        skipParamDto: true

        # Whether to use ValueOption instead of Option for nullable parameters. Default
        # false.
        voptionIn: true

        # Whether to use ValueOption instead of Option for nullable columns in returned
        # records. Note that if the result is a table DTO, that table DTO must have a
        # matching 'voption' configuration. Defalt false.
        voptionOut: true

        # If true and the result set consists of a single named column, return a record
        # with a single field (e.g. {| col: int |}). If false, returns the scalar value
        # directly (just 'int' in this example). Default false.
        recordIfSingleCol: true

        # If true, the return value of the procedure will be made available (except when
        # using lazy execution).
        useReturnValue: true

        # 'params' allows you to specify settings for individual parameters.
        params:

          # The keys are the names of the parameters without '@'. The special value ''
          # (empty string) specifies base rules inherited by all parameters in this rule.
          '': { }

          someParam:

            # Whether the parameter is nullable. Parameters are nullable by default if
            # they are defined with default value NULL in the procedure. You can override
            # it using this property.
            nullable: true
            # The property name for this parameter if passing parameters using a DTO
            # object. By default, it's equal to the actual parameter name with the first
            # letter upper-cased.
            dtoName: SOMEparam
            # The value this parameter will have during build when inferring the output
            # result set. This is probably only needed if you use dynamic SQL and this
            # parameter represents e.g. a column name. Only string parameters (e.g.
            # NVARCHAR) are supported.
            buildValue: foo

        # If a procedure uses temp tables that must be loaded by Facil (effectively acting as
        # parameters for the procedure), place the definitions here. Note that while this
        # property can be overridden by later matching procedure rules, it is not merged (or
        # removed); the last specified tempTables array for any given procedure will be used.
        #
        # Note that the temp table definitions will be executed during build, so don't do
        # anything dangerous here.
        tempTables:

          # You can supply the definition directly as a string.
          - definition: |
              CREATE TABLE #myTempTable(
                Col1 INT NOT NULL PRIMARY KEY,
                Col2 NVARCHAR(100) NULL
              )

            # If using a single line, remember to enclose in quotes (otherwise everything
            # after # will be a YAML comment and not be part of the definition)
          - definition: 'CREATE TABLE #myTempTable(Col1 INT NOT NULL)'

            # You can also specify a path to a SQL file containing the definition. The
            # path is relative to scriptBasePath (by default the project directory).
          - definition: path/to/my/tempTableDefinition.sql

        # 'columns' allows you to specify settings for individual output columns.
        columns:

          # The keys are the column names. The special value '' (empty string) specifies
          # base rules inherited by all columns in this rule.
          '':

            # Whether the column should be ignored. Can be used e.g. to remove columns
            # with unsupported data types.
            skip: true
          SomeColumn:
            skip: false

            # Override Facil's inferred nullability.
            nullable: false


    # Rules for which SQL script files are included. These work just like stored procedure
    # rules, except that 'include', 'for', and 'except' are glob patterns, not regex
    # patterns. For supported patterns, see the glob implementation Facil uses:
    # https://github.com/kthompson/glob
    #
    # The SQL files must exist under scriptBasePath (by default the project directory);
    # you may not use .. to go out of this directory.
    #
    # The SQL files do not need to be included in the build output; the script contents
    # will be inlined in the generated code.)
    scripts:
      - include: '**/*.sql'
        except: SQL/ScriptToExclude.sql

        # The following work exactly like they do in stored procedure rules.
        result: anonymous
        paramDto: nominal
        skipParamDto: true
        voptionIn: true
        voptionOut: true
        recordIfSingleCol: true
        tempTables: ...


      - for: SQL/MyScript.sql

        # 'params' allows you to specify settings for individual script parameters.
        #
        # Type inference in scripts is limited due to limitations in SQL Server's
        # sp_describe_undeclared_parameters, which Facil uses to get parameter information
        # for sripts. Notably, the following does not work out of the box:
        #   - Parameters used multiple times will give errors
        #   - Table-valued parameters will give errors
        #   - Nullability is not inferred (by default, Facil assumes all script parameters
        #     are non-nullable)
        #
        # To work around this, script rules also supports a 'params' property that allows
        # you to specify, for each problematic parameter (you don't have to specify the
        # ones that work), which SQL type it is and whether it is nullable.
        params:

          # The keys are the names of the parameters without '@'. The special value ''
          # (empty string) specifies base rules inherited by all parameters in this rule.
          '':

            # Whether the parameter is nullable. Default false.
            nullable: true
          someParam:

            # The type of parameter. This is required if the parameter is used more than
            # once in the script or if it is a table-valued parameter (see below).
            type: NVARCHAR(50)
            # The property name for this parameter if passing parameters using a DTO
            # object. By default, it's equal to the actual parameter name with the first
            # letter upper-cased.
            dtoName: SOMEparam
            # The value this parameter will have during build when inferring the output
            # result set. This is probably only needed if you use dynamic SQL and this
            # parameter represents e.g. a column name. Only string parameters (e.g.
            # NVARCHAR) are supported.
            buildValue: foo
          anotherParam:

            # For table-valued parameters, specify the schema-qualified name of a
            # user-defined table type. Note that table-valued parameters can not be
            # nullable.
            type: dbo.MultiColNull

        # 'columns' allows you to specify settings for individual output columns.
        columns:

          # The keys are the column names. The special value '' (empty string) specifies
          # base rules inherited by all columns in this rule.
          '':

            # Whether the column should be ignored. Can be used e.g. to remove columns
            # with unsupported data types.
            skip: true
          SomeColumn:
            skip: false

            # Override Facil's inferred nullability.
            nullable: false


    # Facil can generate simple boilerplate per-table CRUD scripts.
    #
    # These scripts are not stored on disk, but work just like you had written them
    # yourself and placed them in scriptBasePath. You can therefore further configure them
    # using script rules as demonstrated above, except for parameter config, which will be
    # ignored (Facil has enough information to know the type and nullability of all
    # parameters for table scripts).
    tableScripts:
      - include: '.*'
        except: 'dbo\.SomeTable'

        # The scripts to create. For rules where include/for matches more than one table,
        # rules for scripts with the same type and (final) name will be merged, with
        # values for later rules taking precedence (columns will be merged). It is
        # possible to have more than one rule of the same type for any given table.
        scripts:

          # Generates a script to insert a row.
          - type: insert

            # The name of the script. The values '{SchemaName}' and '{TableName}' will be
            # replaced with the schema and table name, respectively. The default is
            # '{TableName}_Insert'. You can use path separators to 'place' the script in a
            # subdirectory.
            name: 'subdir/{SchemaName}_{TableName}_Insert'

            # 'columns' allows you to specify settings for individual output columns.
            columns:

              # Rules inherited by all columns.
              '': { }
              MyIdentityColumn:

                # Don't insert this column. Default true for identity columns, false for
                # normal columns.
                skip: true
                # Output this column. You can output as many columns as you want.
                output: true
              Column With Spaces:

                # The name of the script parameter.
                paramName: columnWithSpaces

            # Generates a script to insert a batch of rows efficiently.
            #
            # The script is the same as if you had witten a manual insert script using a temp table to load the rows. In
            # other words, Facil uses SqlBulkCopy to load the rows, and you can configure the created SqlBulkCopy using
            # ConfigureBulkCopy.
            #
            # Supports the same rules as 'insert'. The default name is '{TableName}_InsertBatch'.
          - type: insertBatch

            # Generates a script to update a row by its primary key.
            #
            # Supports the same rules as 'insert'. The default name is
            # '{TableName}_Update'. Skipping a column means it's not updated. (Identity
            # columns are not skipped by default, but this fact is likely irrelevant for
            # most updates).
          - type: update

            # Generates a script to update a batch of rows efficiently.
            #
            # The script is the same as if you had witten a manual update script using a temp table to load the rows. In
            # other words, Facil uses SqlBulkCopy to load the rows, and you can configure the created SqlBulkCopy using
            # ConfigureBulkCopy.
            #
            # Supports the same rules as 'update'. The default name is '{TableName}_UpdateBatch'.
          - type: updateBatch

            # Generates a script that uses MERGE to insert a row or update a row by its
            # primary key.
            #
            # Supports the same rules as 'insert'. The default name is
            # '{TableName}_Merge'. Skipping a column means it's not inserted/updated.
          - type: merge

            # Add WITH (HOLDLOCK) to the merge statement
            holdlock: true

            # Generates a script to merge a batch of rows efficiently.
            #
            # The script is the same as if you had witten a manual merge script using a temp table to load the rows. In
            # other words, Facil uses SqlBulkCopy to load the rows, and you can configure the created SqlBulkCopy using
            # ConfigureBulkCopy.
            #
            # Supports the same rules as 'merge'. The default name is '{TableName}_MergeBatch'.
          - type: mergeBatch

            # Generates a script to delete a row by its primary key.
            #
            # Supports the same rules as 'insert'. The default name is
            # '{TableName}_Delete'. Column skip rules are irrelevant and ignored.
          - type: delete

            # Generates a script to delete a batch of row efficiently.
            #
            # Supports the same rules as 'insert'. The default name is
            # '{TableName}_DeleteBatch'. Column skip rules are irrelevant and ignored.
          - type: deleteBatch

            # Generates a script to get all rows from a table.
            #
            # Supports the same rules as 'insert'. The default name is '{TableName}_All'.
            # Skipping a column means it's not in the SELECT list. The 'output' and
            # 'paramName' column rules are ignored.
          - type: getAll

            # Generates a script to get a row by its primary key.
            #
            # Supports the same rules as 'insert'. The default name is '{TableName}_ById'.
            # Skipping a column means it's not in the SELECT list. The 'output' and
            # 'paramName' column rules are ignored.
          - type: getById

            # 'selectColumns' is a short way of specifying which columns to include. The value shown here is is simply
            # syntactic sugar for the 'columns' configuration shown below. If you specify both, any 'skip' values under
            # 'columns' take precedence over what's specified in 'includeColumns'.
            selectColumns:
              - Col1
              - Col2

            # 'columns' allows you to specify settings for individual columns.
            columns:
              '':
                skip: true
              Col1:
                skip: false
              Col2:
                skip: false

            # Generates a script to get rows by a batch of primary keys using a
            # table-valued parameter.
            #
            # Supports the same rules as 'getById'. The default name is
            # '{TableName}_ByIds'.
          - type: getByIdBatch

            # Facil can use a table type in this script if:
            #  - For single-column types/PKs: The table type column data type matches the
            #    PK column data type.
            #  - For multi-column types/PKs: The table type columns' name and data types
            #    match those of the primary key columns (the column order is ignored).
            #
            # If there are more than one matching table type, you must specify the fully
            # qualified name of the table type to use.
            tableType: dbo.MyTableType

            # Generates a script to get a row by a set of column values.
            #
            # Supports the same rules as 'getById'. The default name is
            # '{TableName}_By{ColumnNames}' where '{ColumnNames}' is e.g. 'Col1' or
            # 'Col1AndCol2'
          - type: getByColumns

            # The columns to select by. Mandatory.
            filterColumns:
              - Col1
              - Col2

            # Generates a script to get rows by a batch of column values.
            #
            # Supports the same rules as 'getByIdBatch' and 'getByColumns'. The default
            # name is '{TableName}_By{ColumnNames}s' where '{ColumnNames}' is e.g. 'Col1'
            # or 'Col1AndCol2'
          - type: getByColumnsBatch

            # Works like in getByIdBatch scripts.
            tableType: dbo.MyTableType

            # Works like in getByColumns scripts.
            filterColumns:
              - Col1
              - Col2


    # Facil can generate DTO record types for tables. These can then (automatically or
    # manually) be used as the return types of procedures and scripts. This can make it
    # easier for you to map between the DTO and your domain types, because you can just
    # write functions that convert to/from this table DTO, instead of having to write a
    # type annotation with all the fields of the corresponding anonymous record (or use
    # SRTPs).
    tableDtos:

      # 'include', 'for', and 'except' work just like in stored procedure rules, and are
      # matched against the schema-qualified table name.
      - include: '.*'
        except: 'dbo\.SomeTable'

        # Whether to use ValueOption instead of Option for nullable columns. Default
        # false.
        voption: true

        # Adds a constructor that accepts an object whose properties are a superset of the properties of the table DTO.
        # This can be useful if you want to use the table DTO with a script that returns additional columns (for example
        # representing the total number of rows, or an additional value to group by).
        mappingCtor: true

        # 'includeColumns' is a short way of specifying which columns to include. The value shown here is is simply
        # syntactic sugar for the 'columns' configuration shown below. If you specify both, any 'skip' values under
        # 'columns' take precedence over what's specified in 'includeColumns'.
        includeColumns:
          - SomeColumn

        # 'columns' allows you to specify settings for individual columns.
        columns:

          # The keys are the column names. The special value '' (empty string) specifies
          # base rules inherited by all columns in this rule.
          '':

            # Whether the column should be ignored. Can be used e.g. to remove columns
            # with unsupported data types.
            skip: true
          SomeColumn:
            skip: false

            # Override Facil's inferred nullability.
            nullable: false


    # Facil automatically generates table type constructors for table types used as a
    # parameter in any included stored procedure or script.
    tableTypes:

      # 'for' and 'except' work just like in stored procedure rules, and are matched
      # against the schema-qualified table type name. Note that 'include' is not
      # supported; Facil always generates wrappers for the table types used in included
      # procedure and script parameters.
      - for: '.*'
        except: 'dbo\.SomeTableType'

        # Whether to use ValueOption instead of Option for nullable columns. Default
        # false.
        voption: true

        # If true, will skip the 'create' overload that acceps a generic DTO type. If you
        # have many table types and generally don't use these overloads, removing them may
        # improve IDE performance and compilation times. Default false.
        skipParamDto: true


  # Here we start another array item in the top-level 'rulesets' array, to generate another file
  # with a different configuration.
  - connectionString: $(ConnectionStrings:AnotherDb)
    filename: DbGen2.fs
    namespaceOrModuleDeclaration: module DbGen2
    # etc.