sqlmesh.cli.main

   1from __future__ import annotations
   2
   3import logging
   4import os
   5import sys
   6import typing as t
   7from pathlib import Path
   8
   9import click
  10
  11from sqlmesh import configure_logging, remove_excess_logs
  12from sqlmesh.cli import error_handler
  13from sqlmesh.cli import options as opt
  14from sqlmesh.cli.project_init import (
  15    InitCliMode,
  16    ProjectTemplate,
  17    init_example_project,
  18    interactive_init,
  19)
  20from sqlmesh.core.analytics import cli_analytics
  21from sqlmesh.core.config import load_configs
  22from sqlmesh.core.console import configure_console, get_console
  23from sqlmesh.core.context import Context
  24from sqlmesh.utils import Verbosity
  25from sqlmesh.utils.date import TimeLike
  26from sqlmesh.utils.errors import MissingDependencyError, SQLMeshError
  27
  28logger = logging.getLogger(__name__)
  29
  30
  31SKIP_LOAD_COMMANDS = (
  32    "clean",
  33    "create_external_models",
  34    "destroy",
  35    "environments",
  36    "invalidate",
  37    "janitor",
  38    "migrate",
  39    "rollback",
  40    "run",
  41    "table_name",
  42)
  43SKIP_CONTEXT_COMMANDS = ("init", "ui")
  44LOCAL_ONLY_COMMANDS = ("format",)
  45
  46
  47def _sqlmesh_version() -> str:
  48    try:
  49        from sqlmesh import __version__
  50
  51        return __version__
  52    except ImportError:
  53        return "0.0.0"
  54
  55
  56@click.group(no_args_is_help=True)
  57@click.version_option(version=_sqlmesh_version(), message="%(version)s")
  58@opt.paths
  59@opt.config
  60@click.option(
  61    "--gateway",
  62    type=str,
  63    help="The name of the gateway.",
  64    envvar="SQLMESH_GATEWAY",
  65)
  66@click.option(
  67    "--ignore-warnings",
  68    is_flag=True,
  69    help="Ignore warnings.",
  70    envvar="SQLMESH_IGNORE_WARNINGS",
  71)
  72@click.option(
  73    "--debug",
  74    is_flag=True,
  75    help="Enable debug mode.",
  76)
  77@click.option(
  78    "--log-to-stdout",
  79    is_flag=True,
  80    help="Display logs in stdout.",
  81)
  82@click.option(
  83    "--log-file-dir",
  84    type=str,
  85    help="The directory to write log files to.",
  86)
  87@click.option(
  88    "--dotenv",
  89    type=click.Path(exists=True, path_type=Path),
  90    help="Path to a custom .env file to load environment variables.",
  91    envvar="SQLMESH_DOTENV_PATH",
  92)
  93@click.pass_context
  94@error_handler
  95def cli(
  96    ctx: click.Context,
  97    paths: t.List[str],
  98    config: t.Optional[str] = None,
  99    gateway: t.Optional[str] = None,
 100    ignore_warnings: bool = False,
 101    debug: bool = False,
 102    log_to_stdout: bool = False,
 103    log_file_dir: t.Optional[str] = None,
 104    dotenv: t.Optional[Path] = None,
 105) -> None:
 106    """SQLMesh command line tool."""
 107    if "--help" in sys.argv:
 108        return
 109
 110    configure_logging(
 111        debug,
 112        log_to_stdout,
 113        log_file_dir=log_file_dir,
 114        ignore_warnings=ignore_warnings,
 115    )
 116    configure_console(ignore_warnings=ignore_warnings)
 117
 118    load = True
 119    # Local-only gating must hold for any number of --paths, so it stays outside the block below.
 120    load_state = ctx.invoked_subcommand not in LOCAL_ONLY_COMMANDS
 121
 122    if len(paths) == 1:
 123        path = os.path.abspath(paths[0])
 124        if ctx.invoked_subcommand in SKIP_CONTEXT_COMMANDS:
 125            ctx.obj = path
 126            return
 127        if ctx.invoked_subcommand in SKIP_LOAD_COMMANDS:
 128            load = False
 129
 130    configs = load_configs(config, Context.CONFIG_TYPE, paths, dotenv_path=dotenv)
 131    log_limit = list(configs.values())[0].log_limit
 132
 133    remove_excess_logs(log_file_dir, log_limit)
 134
 135    try:
 136        context = Context(
 137            paths=paths,
 138            config=configs,
 139            gateway=gateway,
 140            load=load,
 141            load_state=load_state,
 142        )
 143    except Exception:
 144        if debug:
 145            logger.exception("Failed to initialize SQLMesh context")
 146        raise
 147
 148    if load and not context.models:
 149        raise click.ClickException(
 150            f"`{paths}` doesn't seem to have any models... cd into the proper directory or specify the path(s) with -p."
 151        )
 152
 153    ctx.obj = context
 154
 155
 156@cli.command("init")
 157@click.argument("engine", required=False)
 158@click.option(
 159    "-t",
 160    "--template",
 161    type=str,
 162    help="Project template. Supported values: dbt, dlt, default, empty.",
 163)
 164@click.option(
 165    "--dlt-pipeline",
 166    type=str,
 167    help="DLT pipeline for which to generate a SQLMesh project. Use alongside template: dlt",
 168)
 169@click.option(
 170    "--dlt-path",
 171    type=str,
 172    help="The DLT pipelines working directory, where DLT stores pipeline state (by default ~/.dlt/pipelines). Use alongside template: dlt",
 173)
 174@click.pass_context
 175@error_handler
 176@cli_analytics
 177def init(
 178    ctx: click.Context,
 179    engine: t.Optional[str] = None,
 180    template: t.Optional[str] = None,
 181    dlt_pipeline: t.Optional[str] = None,
 182    dlt_path: t.Optional[str] = None,
 183) -> None:
 184    """Create a new SQLMesh repository."""
 185    project_template = None
 186    if template:
 187        try:
 188            project_template = ProjectTemplate(template.lower())
 189        except ValueError:
 190            template_strings = "', '".join([template.value for template in ProjectTemplate])
 191            raise click.ClickException(
 192                f"Invalid project template '{template}'. Please specify one of '{template_strings}'."
 193            )
 194
 195    if engine or project_template == ProjectTemplate.DBT:
 196        init_example_project(
 197            path=ctx.obj,
 198            template=project_template or ProjectTemplate.DEFAULT,
 199            engine_type=engine,
 200            pipeline=dlt_pipeline,
 201            dlt_path=dlt_path,
 202        )
 203        return
 204
 205    import sqlmesh.utils.rich as srich
 206
 207    console = srich.console
 208
 209    project_template, engine_type, cli_mode = interactive_init(ctx.obj, console, project_template)
 210
 211    config_path = init_example_project(
 212        path=ctx.obj,
 213        template=project_template,
 214        engine_type=engine_type,
 215        cli_mode=cli_mode or InitCliMode.DEFAULT,
 216        pipeline=dlt_pipeline,
 217        dlt_path=dlt_path,
 218    )
 219
 220    engine_install_text = ""
 221    if engine_type and engine_type not in ("duckdb", "motherduck"):
 222        install_text = (
 223            "pyspark" if engine_type == "spark" else f"sqlmesh\\[{engine_type.replace('_', '')}]"
 224        )
 225        engine_install_text = f'• Run command in CLI to install your SQL engine\'s Python dependencies: pip install "{install_text}"\n'
 226    # interactive init does not support DLT template
 227    next_step_text = {
 228        ProjectTemplate.DEFAULT: f"{engine_install_text}• Update your gateway connection settings (e.g., username/password) in the project configuration file:\n    {config_path}",
 229        ProjectTemplate.DBT: "",
 230    }
 231    next_step_text[ProjectTemplate.EMPTY] = next_step_text[ProjectTemplate.DEFAULT]
 232
 233    quickstart_text = {
 234        ProjectTemplate.DEFAULT: "Quickstart guide:\nhttps://sqlmesh.readthedocs.io/en/stable/quickstart/cli/",
 235        ProjectTemplate.DBT: "dbt guide:\nhttps://sqlmesh.readthedocs.io/en/stable/integrations/dbt/",
 236    }
 237    quickstart_text[ProjectTemplate.EMPTY] = quickstart_text[ProjectTemplate.DEFAULT]
 238
 239    console.print(f"""──────────────────────────────
 240
 241Your SQLMesh project is ready!
 242
 243Next steps:
 244{next_step_text[project_template]}
 245• Run command in CLI: sqlmesh plan
 246• (Optional) Explain a plan: sqlmesh plan --explain
 247
 248{quickstart_text[project_template]}
 249
 250Need help?
 251• Docs:   https://sqlmesh.readthedocs.io
 252• Slack:  https://www.tobikodata.com/slack
 253• GitHub: https://github.com/SQLMesh/sqlmesh/issues
 254""")
 255
 256
 257@cli.command("render")
 258@click.argument("model")
 259@opt.start_time
 260@opt.end_time
 261@opt.execution_time
 262@opt.expand
 263@click.option(
 264    "--dialect",
 265    type=str,
 266    help="The SQL dialect to render the query as.",
 267)
 268@click.option("--no-format", is_flag=True, help="Disable fancy formatting of the query.")
 269@opt.format_options
 270@click.pass_context
 271@error_handler
 272@cli_analytics
 273def render(
 274    ctx: click.Context,
 275    model: str,
 276    start: TimeLike,
 277    end: TimeLike,
 278    execution_time: t.Optional[TimeLike] = None,
 279    expand: t.Optional[t.Union[bool, t.Iterable[str]]] = None,
 280    dialect: t.Optional[str] = None,
 281    no_format: bool = False,
 282    **format_kwargs: t.Any,
 283) -> None:
 284    """Render a model's query, optionally expanding referenced models."""
 285    model = ctx.obj.get_model(model, raise_if_missing=True)
 286
 287    rendered = ctx.obj.render(
 288        model,
 289        start=start,
 290        end=end,
 291        execution_time=execution_time,
 292        expand=expand,
 293    )
 294
 295    format_config = ctx.obj.config_for_node(model).format
 296    format_kwargs = {
 297        **format_config.generator_options,
 298        **{k: v for k, v in format_kwargs.items() if v is not None},
 299    }
 300
 301    sql = rendered.sql(
 302        pretty=True,
 303        dialect=ctx.obj.config.dialect if dialect is None else dialect,
 304        **format_kwargs,
 305    )
 306    if no_format:
 307        print(sql)
 308    else:
 309        ctx.obj.console.show_sql(sql)
 310
 311
 312@cli.command("evaluate")
 313@click.argument("model")
 314@opt.start_time
 315@opt.end_time
 316@opt.execution_time
 317@click.option(
 318    "--limit",
 319    type=int,
 320    help="The number of rows which the query should be limited to.",
 321)
 322@click.pass_context
 323@error_handler
 324@cli_analytics
 325def evaluate(
 326    ctx: click.Context,
 327    model: str,
 328    start: TimeLike,
 329    end: TimeLike,
 330    execution_time: t.Optional[TimeLike] = None,
 331    limit: t.Optional[int] = None,
 332) -> None:
 333    """Evaluate a model and return a dataframe with a default limit of 1000."""
 334    df = ctx.obj.evaluate(
 335        model,
 336        start=start,
 337        end=end,
 338        execution_time=execution_time,
 339        limit=limit,
 340    )
 341    if hasattr(df, "show"):
 342        df.show(limit)
 343    else:
 344        ctx.obj.console.log_success(df)
 345
 346
 347@cli.command("format")
 348@click.argument("paths", nargs=-1)
 349@click.option(
 350    "-t",
 351    "--transpile",
 352    type=str,
 353    help="Transpile project models to the specified dialect.",
 354)
 355@click.option(
 356    "--check",
 357    is_flag=True,
 358    help="Whether or not to check formatting (but not actually format anything).",
 359    default=None,
 360)
 361@click.option(
 362    "--rewrite-casts/--no-rewrite-casts",
 363    is_flag=True,
 364    help="Rewrite casts to use the :: syntax.",
 365    default=None,
 366)
 367@click.option(
 368    "--append-newline",
 369    is_flag=True,
 370    help="Include a newline at the end of each file.",
 371    default=None,
 372)
 373@opt.format_options
 374@click.pass_context
 375@error_handler
 376@cli_analytics
 377def format(
 378    ctx: click.Context, paths: t.Optional[t.Tuple[str, ...]] = None, **kwargs: t.Any
 379) -> None:
 380    """Format all SQL models and audits."""
 381    if not ctx.obj.format(**{k: v for k, v in kwargs.items() if v is not None}, paths=paths):
 382        ctx.exit(1)
 383
 384
 385@cli.command("diff")
 386@click.argument("environment")
 387@click.pass_context
 388@error_handler
 389@cli_analytics
 390def diff(ctx: click.Context, environment: t.Optional[str] = None) -> None:
 391    """Show the diff between the local state and the target environment."""
 392    if ctx.obj.diff(environment, detailed=True):
 393        exit(1)
 394
 395
 396@cli.command("plan")
 397@click.argument("environment", required=False)
 398@opt.start_time
 399@opt.end_time
 400@opt.execution_time
 401@click.option(
 402    "--create-from",
 403    type=str,
 404    help="The environment to create the target environment from if it doesn't exist. Default: prod.",
 405)
 406@click.option(
 407    "--skip-tests",
 408    is_flag=True,
 409    help="Skip tests prior to generating the plan if they are defined.",
 410    default=None,
 411)
 412@click.option(
 413    "--skip-linter",
 414    is_flag=True,
 415    help="Skip linting prior to generating the plan if the linter is enabled.",
 416    default=None,
 417)
 418@click.option(
 419    "--restate-model",
 420    "-r",
 421    type=str,
 422    multiple=True,
 423    help="Restate data for specified models and models downstream from the one specified. For production environment, all related model versions will have their intervals wiped, but only the current versions will be backfilled. For development environment, only the current model versions will be affected.",
 424)
 425@click.option(
 426    "--no-gaps",
 427    is_flag=True,
 428    help="Ensure that new snapshots have no data gaps when comparing to existing snapshots for matching models in the target environment.",
 429    default=None,
 430)
 431@click.option(
 432    "--skip-backfill",
 433    "--dry-run",
 434    is_flag=True,
 435    help="Skip the backfill step and only create a virtual update for the plan.",
 436    default=None,
 437)
 438@click.option(
 439    "--empty-backfill",
 440    is_flag=True,
 441    help="Produce empty backfill. Like --skip-backfill no models will be backfilled, unlike --skip-backfill missing intervals will be recorded as if they were backfilled.",
 442    default=None,
 443)
 444@click.option(
 445    "--forward-only",
 446    is_flag=True,
 447    help="Create a plan for forward-only changes.",
 448    default=None,
 449)
 450@click.option(
 451    "--allow-destructive-model",
 452    type=str,
 453    multiple=True,
 454    help="Allow destructive forward-only changes to models whose names match the expression.",
 455)
 456@click.option(
 457    "--allow-additive-model",
 458    type=str,
 459    multiple=True,
 460    help="Allow additive forward-only changes to models whose names match the expression.",
 461)
 462@click.option(
 463    "--effective-from",
 464    type=str,
 465    required=False,
 466    help="The effective date from which to apply forward-only changes on production.",
 467)
 468@click.option(
 469    "--no-prompts",
 470    is_flag=True,
 471    help="Disable interactive prompts for the backfill time range. Please note that if this flag is set and there are uncategorized changes, plan creation will fail.",
 472    default=None,
 473)
 474@click.option(
 475    "--auto-apply",
 476    is_flag=True,
 477    help="Automatically apply the new plan after creation.",
 478    default=None,
 479)
 480@click.option(
 481    "--no-auto-categorization",
 482    is_flag=True,
 483    help="Disable automatic change categorization.",
 484    default=None,
 485)
 486@click.option(
 487    "--include-unmodified",
 488    is_flag=True,
 489    help="Include unmodified models in the target environment.",
 490    default=None,
 491)
 492@click.option(
 493    "--select-model",
 494    type=str,
 495    multiple=True,
 496    help="Select specific model changes that should be included in the plan.",
 497)
 498@click.option(
 499    "--backfill-model",
 500    type=str,
 501    multiple=True,
 502    help="Backfill only the models whose names match the expression.",
 503)
 504@click.option(
 505    "--no-diff",
 506    is_flag=True,
 507    help="Hide text differences for changed models.",
 508    default=None,
 509)
 510@click.option(
 511    "--run",
 512    is_flag=True,
 513    help="Run latest intervals as part of the plan application (prod environment only).",
 514    default=None,
 515)
 516@click.option(
 517    "--enable-preview",
 518    is_flag=True,
 519    help="Enable preview for forward-only models when targeting a development environment.",
 520    default=None,
 521)
 522@click.option(
 523    "--diff-rendered",
 524    is_flag=True,
 525    help="Output text differences for the rendered versions of the models and standalone audits.",
 526    default=None,
 527)
 528@click.option(
 529    "--explain",
 530    is_flag=True,
 531    help="Explain the plan instead of applying it.",
 532    default=None,
 533)
 534@click.option(
 535    "--ignore-cron",
 536    is_flag=True,
 537    help="Run all missing intervals, ignoring individual cron schedules. Only applies if --run is set.",
 538    default=None,
 539)
 540@click.option(
 541    "--min-intervals",
 542    default=None,
 543    help="For every model, ensure at least this many intervals are covered by a missing intervals check regardless of the plan start date",
 544)
 545@opt.verbose
 546@click.pass_context
 547@error_handler
 548@cli_analytics
 549def plan(
 550    ctx: click.Context,
 551    verbose: int,
 552    environment: t.Optional[str] = None,
 553    **kwargs: t.Any,
 554) -> None:
 555    """Apply local changes to the target environment."""
 556    context = ctx.obj
 557    restate_models = kwargs.pop("restate_model") or None
 558    select_models = kwargs.pop("select_model") or None
 559    allow_destructive_models = kwargs.pop("allow_destructive_model") or None
 560    allow_additive_models = kwargs.pop("allow_additive_model") or None
 561    backfill_models = kwargs.pop("backfill_model") or None
 562    ignore_cron = kwargs.pop("ignore_cron") or None
 563    setattr(get_console(), "verbosity", Verbosity(verbose))
 564
 565    context.plan(
 566        environment,
 567        restate_models=restate_models,
 568        select_models=select_models,
 569        allow_destructive_models=allow_destructive_models,
 570        allow_additive_models=allow_additive_models,
 571        backfill_models=backfill_models,
 572        ignore_cron=ignore_cron,
 573        **kwargs,
 574    )
 575
 576
 577@cli.command("run")
 578@click.argument("environment", required=False)
 579@opt.start_time
 580@opt.end_time
 581@click.option("--skip-janitor", is_flag=True, help="Skip the janitor task.")
 582@click.option(
 583    "--ignore-cron",
 584    is_flag=True,
 585    help="Run for all missing intervals, ignoring individual cron schedules.",
 586)
 587@click.option(
 588    "--select-model",
 589    type=str,
 590    multiple=True,
 591    help="Select specific models to run. Note: this always includes upstream dependencies.",
 592)
 593@click.option(
 594    "--exit-on-env-update",
 595    type=int,
 596    help="If set, the command will exit with the specified code if the run is interrupted by an update to the target environment.",
 597)
 598@click.option(
 599    "--no-auto-upstream",
 600    is_flag=True,
 601    help="Do not automatically include upstream models. Only applicable when --select-model is used. Note: this may result in missing / invalid data for the selected models.",
 602)
 603@click.pass_context
 604@error_handler
 605@cli_analytics
 606def run(ctx: click.Context, environment: t.Optional[str] = None, **kwargs: t.Any) -> None:
 607    """Evaluate missing intervals for the target environment."""
 608    context = ctx.obj
 609    select_models = kwargs.pop("select_model") or None
 610    completion_status = context.run(environment, select_models=select_models, **kwargs)
 611    if completion_status.is_failure:
 612        raise click.ClickException("Run failed.")
 613
 614
 615@cli.command("invalidate")
 616@click.argument("environment", required=True)
 617@click.option(
 618    "--sync",
 619    "-s",
 620    is_flag=True,
 621    help="Wait for the environment to be deleted before returning. If not specified, the environment will be deleted asynchronously by the janitor process. This option requires a connection to the data warehouse.",
 622)
 623@click.pass_context
 624@error_handler
 625@cli_analytics
 626def invalidate(ctx: click.Context, environment: str, **kwargs: t.Any) -> None:
 627    """Invalidate the target environment, forcing its removal during the next run of the janitor process."""
 628    context = ctx.obj
 629    context.invalidate_environment(environment, **kwargs)
 630
 631
 632@cli.command("janitor")
 633@click.option(
 634    "--ignore-ttl",
 635    is_flag=True,
 636    help="Cleanup snapshots that are not referenced in any environment, regardless of when they're set to expire. Has no effect when --environment is specified.",
 637)
 638@click.option(
 639    "--force-delete",
 640    is_flag=True,
 641    help="Delete expired environment and snapshot state records even when the physical table or view drops fail. "
 642    "Any objects that could not be dropped become orphaned and must be removed manually.",
 643)
 644@click.option(
 645    "--environment",
 646    "-e",
 647    default=None,
 648    help="Scope cleanup to a single expired environment. Global snapshot and interval compaction are skipped.",
 649)
 650@click.pass_context
 651@error_handler
 652@cli_analytics
 653def janitor(
 654    ctx: click.Context,
 655    ignore_ttl: bool,
 656    force_delete: bool,
 657    environment: t.Optional[str],
 658    **kwargs: t.Any,
 659) -> None:
 660    """
 661    Run the janitor process on-demand.
 662
 663    The janitor cleans up old environments and expired snapshots.
 664    """
 665    ctx.obj.run_janitor(ignore_ttl, force_delete=force_delete, environment=environment, **kwargs)
 666
 667
 668@cli.command("destroy")
 669@click.pass_context
 670@error_handler
 671@cli_analytics
 672def destroy(ctx: click.Context, **kwargs: t.Any) -> None:
 673    """
 674    The destroy command removes all project resources.
 675
 676    This includes engine-managed objects, state tables, the SQLMesh cache and any build artifacts.
 677    """
 678    ctx.obj.destroy(**kwargs)
 679
 680
 681@cli.command("dag")
 682@click.argument("file", required=True)
 683@click.option(
 684    "--select-model",
 685    type=str,
 686    multiple=True,
 687    help="Select specific models to include in the dag.",
 688)
 689@click.pass_context
 690@error_handler
 691@cli_analytics
 692def dag(ctx: click.Context, file: str, select_model: t.List[str]) -> None:
 693    """Render the DAG as an html file."""
 694    rendered_dag_path = ctx.obj.render_dag(file, select_model)
 695    if rendered_dag_path:
 696        ctx.obj.console.log_success(f"Generated the dag to {rendered_dag_path}")
 697
 698
 699@cli.command("create_test")
 700@click.argument("model")
 701@click.option(
 702    "-q",
 703    "--query",
 704    "queries",
 705    type=(str, str),
 706    multiple=True,
 707    default=[],
 708    help="Queries that will be used to generate data for the model's dependencies.",
 709)
 710@click.option(
 711    "-o",
 712    "--overwrite",
 713    "overwrite",
 714    is_flag=True,
 715    default=False,
 716    help="When true, the fixture file will be overwritten in case it already exists.",
 717)
 718@click.option(
 719    "-v",
 720    "--var",
 721    "variables",
 722    type=(str, str),
 723    multiple=True,
 724    help="Key-value pairs that will define variables needed by the model.",
 725)
 726@click.option(
 727    "-p",
 728    "--path",
 729    "path",
 730    help=(
 731        "The file path corresponding to the fixture, relative to the test directory. "
 732        "By default, the fixture will be created under the test directory and the file "
 733        "name will be inferred based on the test's name."
 734    ),
 735)
 736@click.option(
 737    "-n",
 738    "--name",
 739    "name",
 740    help="The name of the test that will be created. By default, it's inferred based on the model's name.",
 741)
 742@click.option(
 743    "--include-ctes",
 744    "include_ctes",
 745    is_flag=True,
 746    default=False,
 747    help="When true, CTE fixtures will also be generated.",
 748)
 749@click.pass_obj
 750@error_handler
 751@cli_analytics
 752def create_test(
 753    obj: Context,
 754    model: str,
 755    queries: t.List[t.Tuple[str, str]],
 756    overwrite: bool = False,
 757    variables: t.Optional[t.List[t.Tuple[str, str]]] = None,
 758    path: t.Optional[str] = None,
 759    name: t.Optional[str] = None,
 760    include_ctes: bool = False,
 761) -> None:
 762    """Generate a unit test fixture for a given model."""
 763    obj.create_test(
 764        model,
 765        input_queries=dict(queries),
 766        overwrite=overwrite,
 767        variables=dict(variables) if variables else None,
 768        path=path,
 769        name=name,
 770        include_ctes=include_ctes,
 771    )
 772
 773
 774@cli.command("test")
 775@opt.match_pattern
 776@opt.verbose
 777@click.option(
 778    "--preserve-fixtures",
 779    is_flag=True,
 780    default=False,
 781    help="Preserve the fixture tables in the testing database, useful for debugging.",
 782)
 783@click.argument("tests", nargs=-1)
 784@click.pass_obj
 785@error_handler
 786@cli_analytics
 787def test(
 788    obj: Context,
 789    k: t.List[str],
 790    verbose: int,
 791    preserve_fixtures: bool,
 792    tests: t.List[str],
 793) -> None:
 794    """Run model unit tests."""
 795    result = obj.test(
 796        match_patterns=k,
 797        tests=tests,
 798        verbosity=Verbosity(verbose),
 799        preserve_fixtures=preserve_fixtures,
 800    )
 801    if not result.wasSuccessful():
 802        exit(1)
 803
 804
 805@cli.command("audit")
 806@click.option(
 807    "--model",
 808    "models",
 809    multiple=True,
 810    help="A model to audit. Multiple models can be audited.",
 811)
 812@opt.start_time
 813@opt.end_time
 814@opt.execution_time
 815@click.pass_obj
 816@error_handler
 817@cli_analytics
 818def audit(
 819    obj: Context,
 820    models: t.Iterator[str],
 821    start: TimeLike,
 822    end: TimeLike,
 823    execution_time: t.Optional[TimeLike] = None,
 824) -> None:
 825    """Run audits for the target model(s)."""
 826    if not obj.audit(models=models, start=start, end=end, execution_time=execution_time):
 827        exit(1)
 828
 829
 830@cli.command("check_intervals")
 831@click.option(
 832    "--no-signals",
 833    is_flag=True,
 834    help="Disable signal checks and only show missing intervals.",
 835    default=False,
 836)
 837@click.argument("environment", required=False)
 838@click.option(
 839    "--select-model",
 840    type=str,
 841    multiple=True,
 842    help="Select specific models to show missing intervals for.",
 843)
 844@opt.start_time
 845@opt.end_time
 846@click.pass_context
 847@error_handler
 848@cli_analytics
 849def check_intervals(
 850    ctx: click.Context,
 851    environment: t.Optional[str],
 852    no_signals: bool,
 853    select_model: t.List[str],
 854    start: TimeLike,
 855    end: TimeLike,
 856) -> None:
 857    """Show missing intervals in an environment, respecting signals."""
 858    context = ctx.obj
 859    context.console.show_intervals(
 860        context.check_intervals(
 861            environment,
 862            no_signals=no_signals,
 863            select_models=select_model,
 864            start=start,
 865            end=end,
 866        )
 867    )
 868
 869
 870@cli.command("fetchdf")
 871@click.argument("sql")
 872@click.pass_context
 873@error_handler
 874@cli_analytics
 875def fetchdf(ctx: click.Context, sql: str) -> None:
 876    """Run a SQL query and display the results."""
 877    context = ctx.obj
 878    context.console.log_success(context.fetchdf(sql))
 879
 880
 881@cli.command("info")
 882@click.option(
 883    "--skip-connection",
 884    is_flag=True,
 885    help="Skip the connection test.",
 886)
 887@opt.verbose
 888@click.pass_obj
 889@error_handler
 890@cli_analytics
 891def info(obj: Context, skip_connection: bool, verbose: int) -> None:
 892    """
 893    Print information about a SQLMesh project.
 894
 895    Includes counts of project models and macros and connection tests for the data warehouse.
 896    """
 897    obj.print_info(skip_connection=skip_connection, verbosity=Verbosity(verbose))
 898
 899
 900@cli.command("ui")
 901@click.option(
 902    "--host",
 903    type=str,
 904    default="127.0.0.1",
 905    help="Bind socket to this host. Default: 127.0.0.1",
 906)
 907@click.option(
 908    "--port",
 909    type=int,
 910    default=8000,
 911    help="Bind socket to this port. Default: 8000",
 912)
 913@click.option(
 914    "--mode",
 915    type=click.Choice(["ide", "catalog", "docs", "plan"], case_sensitive=False),
 916    default="ide",
 917    help="Mode to start the UI in. Default: ide",
 918)
 919@click.pass_context
 920@error_handler
 921@cli_analytics
 922def ui(ctx: click.Context, host: str, port: int, mode: str) -> None:
 923    """Start a browser-based SQLMesh UI."""
 924    from sqlmesh.core.console import get_console
 925
 926    get_console().log_warning(
 927        "The UI is deprecated and will be removed in a future version. Please use the SQLMesh VSCode extension instead. "
 928        "Learn more at https://sqlmesh.readthedocs.io/en/stable/guides/vscode/"
 929    )
 930
 931    try:
 932        import uvicorn
 933    except ModuleNotFoundError as e:
 934        raise MissingDependencyError(
 935            "Missing UI dependencies. Run `pip install 'sqlmesh[web]'` to install them."
 936        ) from e
 937
 938    os.environ["PROJECT_PATH"] = ctx.obj
 939    os.environ["UI_MODE"] = mode
 940    if ctx.parent:
 941        config = ctx.parent.params.get("config")
 942        gateway = ctx.parent.params.get("gateway")
 943        if config:
 944            os.environ["CONFIG"] = config
 945        if gateway:
 946            os.environ["GATEWAY"] = gateway
 947    uvicorn.run(
 948        "web.server.app:app",
 949        host=host,
 950        port=port,
 951        log_level="info",
 952        timeout_keep_alive=300,
 953    )
 954
 955
 956@cli.command("migrate")
 957@click.pass_context
 958@error_handler
 959@cli_analytics
 960def migrate(ctx: click.Context) -> None:
 961    """Migrate SQLMesh to the current running version."""
 962    ctx.obj.migrate()
 963
 964
 965@cli.command("rollback")
 966@click.pass_obj
 967@error_handler
 968@cli_analytics
 969def rollback(obj: Context) -> None:
 970    """Rollback SQLMesh to the previous migration."""
 971    obj.rollback()
 972
 973
 974@cli.command("create_external_models")
 975@click.option(
 976    "--strict",
 977    is_flag=True,
 978    help="Raise an error if the external model is missing in the database",
 979)
 980@click.pass_obj
 981@error_handler
 982@cli_analytics
 983def create_external_models(obj: Context, **kwargs: t.Any) -> None:
 984    """Create a schema file containing external model schemas."""
 985    obj.create_external_models(**kwargs)
 986
 987
 988@cli.command("table_diff")
 989@click.argument("source_to_target", required=True, metavar="SOURCE:TARGET")
 990@click.argument("model", required=False)
 991@click.option(
 992    "-o",
 993    "--on",
 994    type=str,
 995    multiple=True,
 996    help="The column to join on. Can be specified multiple times. The model grain will be used if not specified.",
 997)
 998@click.option(
 999    "-s",
1000    "--skip-columns",
1001    type=str,
1002    multiple=True,
1003    help="The column(s) to skip when comparing the source and target table.",
1004)
1005@click.option(
1006    "--where",
1007    type=str,
1008    help="An optional where statement to filter results.",
1009)
1010@click.option(
1011    "--limit",
1012    type=int,
1013    default=20,
1014    help="The limit of the sample dataframe.",
1015)
1016@click.option(
1017    "--show-sample",
1018    is_flag=True,
1019    help="Show a sample of the rows that differ. With many columns, the output can be very wide.",
1020)
1021@click.option(
1022    "-d",
1023    "--decimals",
1024    type=int,
1025    default=3,
1026    help="The number of decimal places to keep when comparing floating point columns. Default: 3",
1027)
1028@click.option(
1029    "--skip-grain-check",
1030    is_flag=True,
1031    help="Disable the check for a primary key (grain) that is missing or is not unique.",
1032)
1033@click.option(
1034    "--warn-grain-check",
1035    is_flag=True,
1036    help="Warn if any selected model is missing a grain, and compute diffs for the remaining models.",
1037)
1038@click.option(
1039    "--temp-schema",
1040    type=str,
1041    help="Schema used for temporary tables. It can be `CATALOG.SCHEMA` or `SCHEMA`. Default: `sqlmesh_temp`",
1042)
1043@click.option(
1044    "--select-model",
1045    "-m",
1046    type=str,
1047    multiple=True,
1048    help="Specify one or more models to data diff. Use wildcards to diff multiple models. Ex: '*' (all models with applied plan diffs), 'demo.model+' (this and downstream models), 'git:feature_branch' (models with direct modifications in this branch only)",
1049)
1050@click.option(
1051    "--schema-diff-ignore-case",
1052    is_flag=True,
1053    help="If set, when performing a schema diff the case of column names is ignored when matching between the two schemas. For example, 'col_a' in the source schema and 'COL_A' in the target schema will be treated as the same column.",
1054)
1055@click.pass_obj
1056@error_handler
1057@cli_analytics
1058def table_diff(
1059    obj: Context, source_to_target: str, model: t.Optional[str], **kwargs: t.Any
1060) -> None:
1061    """Show the diff between two tables or a selection of models when they are specified."""
1062    source, target = source_to_target.split(":")
1063    select_model = kwargs.pop("select_model", None)
1064
1065    if model and select_model:
1066        raise SQLMeshError(
1067            "The --select-model option cannot be used together with a model argument. Please choose one of them."
1068        )
1069
1070    select_models = {model} if model else select_model
1071    obj.table_diff(
1072        source=source,
1073        target=target,
1074        select_models=select_models,
1075        **kwargs,
1076    )
1077
1078
1079@cli.command("rewrite")
1080@click.argument("sql")
1081@click.option(
1082    "--read",
1083    type=str,
1084    help="The input dialect of the sql string.",
1085)
1086@click.option(
1087    "--write",
1088    type=str,
1089    help="The output dialect of the sql string.",
1090)
1091@click.pass_obj
1092@error_handler
1093@cli_analytics
1094def rewrite(obj: Context, sql: str, read: str = "", write: str = "") -> None:
1095    """Rewrite a SQL expression with semantic references into an executable query.
1096
1097    https://sqlmesh.readthedocs.io/en/latest/concepts/metrics/overview/
1098    """
1099    obj.console.show_sql(
1100        obj.rewrite(sql, dialect=read).sql(pretty=True, dialect=write or obj.config.dialect),
1101    )
1102
1103
1104@cli.command("clean")
1105@click.pass_obj
1106@error_handler
1107@cli_analytics
1108def clean(obj: Context) -> None:
1109    """Clears the SQLMesh cache and any build artifacts."""
1110    obj.clear_caches()
1111
1112
1113@cli.command("table_name")
1114@click.argument("model_name", required=True)
1115@click.option(
1116    "--environment",
1117    "--env",
1118    help="The environment to source the model version from.",
1119)
1120@click.option(
1121    "--prod",
1122    is_flag=True,
1123    default=False,
1124    help="If set, return the name of the physical table that will be used in production for the model version promoted in the target environment.",
1125)
1126@click.pass_obj
1127@error_handler
1128@cli_analytics
1129def table_name(
1130    obj: Context,
1131    model_name: str,
1132    environment: t.Optional[str] = None,
1133    prod: bool = False,
1134) -> None:
1135    """Prints the name of the physical table for the given model."""
1136    print(obj.table_name(model_name, environment, prod))
1137
1138
1139@cli.command("dlt_refresh")
1140@click.argument("pipeline", required=True)
1141@click.option(
1142    "-t",
1143    "--table",
1144    type=str,
1145    multiple=True,
1146    help="The specific dlt tables to refresh in the SQLMesh models.",
1147)
1148@click.option(
1149    "-f",
1150    "--force",
1151    is_flag=True,
1152    default=False,
1153    help="If set, existing models are overwritten with the new DLT tables.",
1154)
1155@click.option(
1156    "--dlt-path",
1157    type=str,
1158    help="The DLT pipelines working directory, where DLT stores pipeline state (by default ~/.dlt/pipelines).",
1159)
1160@click.pass_context
1161@error_handler
1162@cli_analytics
1163def dlt_refresh(
1164    ctx: click.Context,
1165    pipeline: str,
1166    force: bool,
1167    table: t.List[str] = [],
1168    dlt_path: t.Optional[str] = None,
1169) -> None:
1170    """Attaches to a DLT pipeline with the option to update specific or all missing tables in the SQLMesh project."""
1171    from sqlmesh.integrations.dlt import generate_dlt_models
1172
1173    sqlmesh_models = generate_dlt_models(ctx.obj, pipeline, list(table or []), force, dlt_path)
1174    if sqlmesh_models:
1175        model_names = "\n".join([f"- {model_name}" for model_name in sqlmesh_models])
1176        ctx.obj.console.log_success(f"Updated SQLMesh project with models:\n{model_names}")
1177    else:
1178        ctx.obj.console.log_success("All SQLMesh models are up to date.")
1179
1180
1181@cli.command("environments")
1182@click.pass_obj
1183@error_handler
1184@cli_analytics
1185def environments(obj: Context) -> None:
1186    """Prints the list of SQLMesh environments with its expiry datetime."""
1187    obj.print_environment_names()
1188
1189
1190@cli.command("lint")
1191@click.option(
1192    "--models",
1193    "--model",
1194    multiple=True,
1195    help="A model to lint. Multiple models can be linted. If no models are specified, every model will be linted.",
1196)
1197@click.pass_obj
1198@error_handler
1199@cli_analytics
1200def lint(
1201    obj: Context,
1202    models: t.Iterator[str],
1203) -> None:
1204    """Run the linter for the target model(s)."""
1205    obj.lint_models(models)
1206
1207
1208@cli.group(no_args_is_help=True)
1209def state() -> None:
1210    """Commands for interacting with state"""
1211    pass
1212
1213
1214@state.command("export")
1215@click.option(
1216    "-o",
1217    "--output-file",
1218    required=True,
1219    help="Path to write the state export to",
1220    type=click.Path(dir_okay=False, writable=True, path_type=Path),
1221)
1222@click.option(
1223    "--environment",
1224    multiple=True,
1225    help="Name of environment to export. Specify multiple --environment arguments to export multiple environments",
1226)
1227@click.option(
1228    "--local",
1229    is_flag=True,
1230    help="Export local state only. Note that the resulting file will not be importable",
1231)
1232@click.option(
1233    "--no-confirm",
1234    is_flag=True,
1235    help="Do not prompt for confirmation before exporting existing state",
1236)
1237@click.pass_obj
1238@error_handler
1239@cli_analytics
1240def state_export(
1241    obj: Context,
1242    output_file: Path,
1243    environment: t.Optional[t.Tuple[str]],
1244    local: bool,
1245    no_confirm: bool,
1246) -> None:
1247    """Export the state database to a file"""
1248    confirm = not no_confirm
1249
1250    if environment and local:
1251        raise click.ClickException("Cannot specify both --environment and --local")
1252
1253    environment_names = list(environment) if environment else None
1254    obj.export_state(
1255        output_file=output_file,
1256        environment_names=environment_names,
1257        local_only=local,
1258        confirm=confirm,
1259    )
1260
1261
1262@state.command("import")
1263@click.option(
1264    "-i",
1265    "--input-file",
1266    help="Path to the state file",
1267    required=True,
1268    type=click.Path(exists=True, dir_okay=False, readable=True, path_type=Path),
1269)
1270@click.option(
1271    "--replace",
1272    is_flag=True,
1273    help="Clear the remote state before loading the file. If omitted, a merge is performed instead",
1274)
1275@click.option(
1276    "--no-confirm",
1277    is_flag=True,
1278    help="Do not prompt for confirmation before updating existing state",
1279)
1280@click.pass_obj
1281@error_handler
1282@cli_analytics
1283def state_import(obj: Context, input_file: Path, replace: bool, no_confirm: bool) -> None:
1284    """Import a state export file back into the state database"""
1285    confirm = not no_confirm
1286    obj.import_state(input_file=input_file, clear=replace, confirm=confirm)