migration.ex (50239B)
1 defmodule Ecto.Migration do 2 @moduledoc """ 3 Migrations are used to modify your database schema over time. 4 5 This module provides many helpers for migrating the database, 6 allowing developers to use Elixir to alter their storage in 7 a way that is database independent. 8 9 Migrations typically provide two operations: `up` and `down`, 10 allowing us to migrate the database forward or roll it back 11 in case of errors. 12 13 In order to manage migrations, Ecto creates a table called 14 `schema_migrations` in the database, which stores all migrations 15 that have already been executed. You can configure the name of 16 this table with the `:migration_source` configuration option. 17 18 You can configure a different database for the table that 19 manages your migrations by setting the `:migration_repo` 20 configuration option to a different repository. 21 22 Ecto also locks the `schema_migrations` table when running 23 migrations, guaranteeing two different servers cannot run the same 24 migration at the same time. 25 26 ## Creating your first migration 27 28 Migrations are defined inside the "priv/REPO/migrations" where REPO 29 is the last part of the repository name in underscore. For example, 30 migrations for `MyApp.Repo` would be found in "priv/repo/migrations". 31 For `MyApp.CustomRepo`, it would be found in "priv/custom_repo/migrations". 32 33 Each file in the migrations directory has the following structure: 34 35 ```text 36 NUMBER_NAME.exs 37 ``` 38 39 The NUMBER is a unique number that identifies the migration. It is 40 usually the timestamp of when the migration was created. The NAME 41 must also be unique and it quickly identifies what the migration 42 does. For example, if you need to track the "weather" in your system, 43 you can start a new file at "priv/repo/migrations/20190417140000_add_weather_table.exs" 44 that will have the following contents: 45 46 defmodule MyRepo.Migrations.AddWeatherTable do 47 use Ecto.Migration 48 49 def up do 50 create table("weather") do 51 add :city, :string, size: 40 52 add :temp_lo, :integer 53 add :temp_hi, :integer 54 add :prcp, :float 55 56 timestamps() 57 end 58 end 59 60 def down do 61 drop table("weather") 62 end 63 end 64 65 The `up/0` function is responsible to migrate your database forward. 66 the `down/0` function is executed whenever you want to rollback. 67 The `down/0` function must always do the opposite of `up/0`. 68 Inside those functions, we invoke the API defined in this module, 69 you will find conveniences for managing tables, indexes, columns, 70 references, as well as running custom SQL commands. 71 72 To run a migration, we generally use Mix tasks. For example, you can 73 run the migration above by going to the root of your project and 74 typing: 75 76 $ mix ecto.migrate 77 78 You can also roll it back by calling: 79 80 $ mix ecto.rollback --step 1 81 82 Note rollback requires us to say how much we want to rollback. 83 On the other hand, `mix ecto.migrate` will always run all pending 84 migrations. 85 86 In practice, we don't create migration files by hand either, we 87 typically use `mix ecto.gen.migration` to generate the file with 88 the proper timestamp and then we just fill in its contents: 89 90 $ mix ecto.gen.migration add_weather_table 91 92 ## Mix tasks 93 94 As seen above, Ecto provides many Mix tasks to help developers work 95 with migrations. We summarize them below: 96 97 * `mix ecto.gen.migration` - generates a 98 migration that the user can fill in with particular commands 99 * `mix ecto.migrate` - migrates a repository 100 * `mix ecto.migrations` - shows all migrations and their status 101 * `mix ecto.rollback` - rolls back a particular migration 102 103 Run `mix help COMMAND` for more information on a particular command. 104 For a lower level API for running migrations, see `Ecto.Migrator`. 105 106 ## Change 107 108 Having to write both `up/0` and `down/0` functions for every 109 migration is tedious and error prone. For this reason, Ecto allows 110 you to defined a `change/0` callback with all of the code you want 111 to execute when migrating and Ecto will automatically figure out 112 the `down/0` for you. For example, the migration above can be 113 written as: 114 115 defmodule MyRepo.Migrations.AddWeatherTable do 116 use Ecto.Migration 117 118 def change do 119 create table("weather") do 120 add :city, :string, size: 40 121 add :temp_lo, :integer 122 add :temp_hi, :integer 123 add :prcp, :float 124 125 timestamps() 126 end 127 end 128 end 129 130 However, note that not all commands are reversible. Trying to rollback 131 a non-reversible command will raise an `Ecto.MigrationError`. 132 133 A notable command in this regard is `execute/2`, which is reversible in 134 `change/0` by accepting a pair of plain SQL strings. The first is run on 135 forward migrations (`up/0`) and the second when rolling back (`down/0`). 136 137 If `up/0` and `down/0` are implemented in a migration, they take precedence, 138 and `change/0` isn't invoked. 139 140 ## Field Types 141 142 The Ecto primitive types are mapped to the appropriate database 143 type by the various database adapters. For example, `:string` is 144 converted to `:varchar`, `:binary` to `:bytea` or `:blob`, and so on. 145 146 In particular, note that: 147 148 * the `:string` type in migrations by default has a limit of 255 characters. 149 If you need more or less characters, pass the `:size` option, such 150 as `add :field, :string, size: 10`. If you don't want to impose a limit, 151 most databases support a `:text` type or similar 152 153 * the `:binary` type in migrations by default has no size limit. If you want 154 to impose a limit, pass the `:size` option accordingly. In MySQL, passing 155 the size option changes the underlying field from "blob" to "varbinary" 156 157 Any other type will be given as is to the database. For example, you 158 can use `:text`, `:char`, or `:varchar` as types. Types that have spaces 159 in their names can be wrapped in double quotes, such as `:"int unsigned"`, 160 `:"time without time zone"`, etc. 161 162 ## Executing and flushing 163 164 Instructions inside of migrations are not executed immediately. Instead 165 they are performed after the relevant `up`, `change`, or `down` callback 166 terminates. 167 168 However, in some situations you may want to guarantee that all of the 169 previous steps have been executed before continuing. This is useful when 170 you need to apply a set of changes to the table before continuing with the 171 migration. This can be done with `flush/0`: 172 173 def up do 174 ... 175 flush() 176 ... 177 end 178 179 However `flush/0` will raise if it would be called from `change` function when doing a rollback. 180 To avoid that we recommend to use `execute/2` with anonymous functions instead. 181 For more information and example usage please take a look at `execute/2` function. 182 183 ## Repo configuration 184 185 ### Migrator configuration 186 187 These options configure how the underlying migration engine works: 188 189 * `:migration_source` - Version numbers of migrations will be saved in a 190 table named `schema_migrations` by default. You can configure the name of 191 the table via: 192 193 config :app, App.Repo, migration_source: "my_migrations" 194 195 * `:migration_lock` - By default, Ecto will lock the migration source to throttle 196 multiple nodes to run migrations one at a time. You can disable the `migration_lock` 197 by setting it to `false`. You may also select a different locking strategy if 198 supported by the adapter. See the adapter docs for more information. 199 200 config :app, App.Repo, migration_lock: false 201 202 # Or use a different locking strategy. For example, Postgres can use advisory 203 # locks but be aware that your database configuration might not make this a good 204 # fit. See the Ecto.Adapters.Postgres for more information: 205 config :app, App.Repo, migration_lock: :pg_advisory_lock 206 207 * `:migration_repo` - The migration repository is where the table managing the 208 migrations will be stored (`migration_source` defines the table name). It defaults 209 to the given repository itself but you can configure it via: 210 211 config :app, App.Repo, migration_repo: App.MigrationRepo 212 213 * `:priv` - the priv directory for the repo with the location of important assets, 214 such as migrations. For a repository named `MyApp.FooRepo`, `:priv` defaults to 215 "priv/foo_repo" and migrations should be placed at "priv/foo_repo/migrations" 216 217 * `:start_apps_before_migration` - A list of applications to be started before 218 running migrations. Used by `Ecto.Migrator.with_repo/3` and the migration tasks: 219 220 config :app, App.Repo, start_apps_before_migration: [:ssl, :some_custom_logger] 221 222 ### Migrations configuration 223 224 These options configure how each migration works. **It is generally discouraged 225 to change any of those configurations after your database is deployed to production, 226 as changing these options will retroactively change how all migrations work**. 227 228 * `:migration_primary_key` - By default, Ecto uses the `:id` column with type 229 `:bigserial`, but you can configure it via: 230 231 config :app, App.Repo, migration_primary_key: [name: :uuid, type: :binary_id] 232 233 config :app, App.Repo, migration_primary_key: false 234 235 * `:migration_foreign_key` - By default, Ecto uses the `primary_key` type 236 for foreign keys when `references/2` is used, but you can configure it via: 237 238 config :app, App.Repo, migration_foreign_key: [column: :uuid, type: :binary_id] 239 240 * `:migration_timestamps` - By default, Ecto uses the `:naive_datetime` as the type, 241 `:inserted_at` as the name of the column for storing insertion times, `:updated_at` as 242 the name of the column for storing last-updated-at times, but you can configure it 243 via: 244 245 config :app, App.Repo, migration_timestamps: [ 246 type: :utc_datetime, 247 inserted_at: :created_at, 248 updated_at: :changed_at 249 ] 250 251 * `:migration_default_prefix` - Ecto defaults to `nil` for the database prefix for 252 migrations, but you can configure it via: 253 254 config :app, App.Repo, migration_default_prefix: "my_prefix" 255 256 ## Comments 257 258 Migrations where you create or alter a table support specifying table 259 and column comments. The same can be done when creating constraints 260 and indexes. Not all databases support this feature. 261 262 def up do 263 create index("posts", [:name], comment: "Index Comment") 264 create constraint("products", "price_must_be_positive", check: "price > 0", comment: "Constraint Comment") 265 create table("weather", prefix: "north_america", comment: "Table Comment") do 266 add :city, :string, size: 40, comment: "Column Comment" 267 timestamps() 268 end 269 end 270 271 ## Prefixes 272 273 Migrations support specifying a table prefix or index prefix which will 274 target either a schema (if using PostgreSQL) or a different database (if using 275 MySQL). If no prefix is provided, the default schema or database is used. 276 277 Any reference declared in the table migration refers by default to the table 278 with the same declared prefix. The prefix is specified in the table options: 279 280 def up do 281 create table("weather", prefix: "north_america") do 282 add :city, :string, size: 40 283 add :temp_lo, :integer 284 add :temp_hi, :integer 285 add :prcp, :float 286 add :group_id, references(:groups) 287 288 timestamps() 289 end 290 291 create index("weather", [:city], prefix: "north_america") 292 end 293 294 Note: if using MySQL with a prefixed table, you must use the same prefix 295 for the references since cross-database references are not supported. 296 297 When using a prefixed table with either MySQL or PostgreSQL, you must use the 298 same prefix for the index field to ensure that you index the prefix-qualified 299 table. 300 301 ## Transaction Callbacks 302 303 If possible, each migration runs inside a transaction. This is true for Postgres, 304 but not true for MySQL, as the latter does not support DDL transactions. 305 306 In some rare cases, you may need to execute some common behavior after beginning 307 a migration transaction, or before committing that transaction. For instance, one 308 might desire to set a `lock_timeout` for each lock in the migration transaction. 309 310 You can do so by defining `c:after_begin/0` and `c:before_commit/0` callbacks to 311 your migration. 312 313 However, if you need do so for every migration module, implement this callback 314 for every migration can be quite repetitive. Luckily, you can handle this by 315 providing your migration module: 316 317 defmodule MyApp.Migration do 318 defmacro __using__(_) do 319 quote do 320 use Ecto.Migration 321 322 def after_begin() do 323 repo().query! "SET lock_timeout TO '5s'" 324 end 325 end 326 end 327 end 328 329 Then in your migrations you can `use MyApp.Migration` to share this behavior 330 among all your migrations. 331 332 ## Additional resources 333 334 * The [Safe Ecto Migrations guide](https://fly.io/phoenix-files/safe-ecto-migrations/) 335 336 """ 337 338 @doc """ 339 Migration code to run immediately after the transaction is opened. 340 341 Keep in mind that it is treated like any normal migration code, and should 342 consider both the up *and* down cases of the migration. 343 """ 344 @callback after_begin() :: term 345 346 @doc """ 347 Migration code to run immediately before the transaction is closed. 348 349 Keep in mind that it is treated like any normal migration code, and should 350 consider both the up *and* down cases of the migration. 351 """ 352 @callback before_commit() :: term 353 @optional_callbacks after_begin: 0, before_commit: 0 354 355 defmodule Index do 356 @moduledoc """ 357 Used internally by adapters. 358 359 To define an index in a migration, see `Ecto.Migration.index/3`. 360 """ 361 defstruct table: nil, 362 prefix: nil, 363 name: nil, 364 columns: [], 365 unique: false, 366 concurrently: false, 367 using: nil, 368 include: [], 369 nulls_distinct: nil, 370 where: nil, 371 comment: nil, 372 options: nil 373 374 @type t :: %__MODULE__{ 375 table: String.t, 376 prefix: atom, 377 name: atom, 378 columns: [atom | String.t], 379 unique: boolean, 380 concurrently: boolean, 381 using: atom | String.t, 382 include: [atom | String.t], 383 nulls_distinct: boolean | nil, 384 where: atom | String.t, 385 comment: String.t | nil, 386 options: String.t 387 } 388 end 389 390 defmodule Table do 391 @moduledoc """ 392 Used internally by adapters. 393 394 To define a table in a migration, see `Ecto.Migration.table/2`. 395 """ 396 defstruct name: nil, prefix: nil, comment: nil, primary_key: true, engine: nil, options: nil 397 @type t :: %__MODULE__{name: String.t, prefix: atom | nil, comment: String.t | nil, primary_key: boolean | keyword(), 398 engine: atom, options: String.t} 399 end 400 401 defmodule Reference do 402 @moduledoc """ 403 Used internally by adapters. 404 405 To define a reference in a migration, see `Ecto.Migration.references/2`. 406 """ 407 defstruct name: nil, prefix: nil, table: nil, column: :id, type: :bigserial, 408 on_delete: :nothing, on_update: :nothing, validate: true, 409 with: [], match: nil 410 @type t :: %__MODULE__{table: String.t, prefix: atom | nil, column: atom, type: atom, 411 on_delete: atom, on_update: atom, validate: boolean, 412 with: list, match: atom | nil} 413 end 414 415 defmodule Constraint do 416 @moduledoc """ 417 Used internally by adapters. 418 419 To define a constraint in a migration, see `Ecto.Migration.constraint/3`. 420 """ 421 defstruct name: nil, table: nil, check: nil, exclude: nil, prefix: nil, comment: nil, validate: true 422 @type t :: %__MODULE__{name: atom, table: String.t, prefix: atom | nil, 423 check: String.t | nil, exclude: String.t | nil, comment: String.t | nil, validate: boolean} 424 end 425 426 defmodule Command do 427 @moduledoc """ 428 Used internally by adapters. 429 430 This represents the up and down legs of a reversible raw command 431 that is usually defined with `Ecto.Migration.execute/1`. 432 433 To define a reversible command in a migration, see `Ecto.Migration.execute/2`. 434 """ 435 defstruct up: nil, down: nil 436 @type t :: %__MODULE__{up: String.t, down: String.t} 437 end 438 439 alias Ecto.Migration.Runner 440 441 @doc false 442 defmacro __using__(_) do 443 quote location: :keep do 444 import Ecto.Migration 445 @disable_ddl_transaction false 446 @disable_migration_lock false 447 @before_compile Ecto.Migration 448 end 449 end 450 451 @doc false 452 defmacro __before_compile__(_env) do 453 quote do 454 def __migration__ do 455 [ 456 disable_ddl_transaction: @disable_ddl_transaction, 457 disable_migration_lock: @disable_migration_lock 458 ] 459 end 460 end 461 end 462 463 @doc """ 464 Creates a table. 465 466 By default, the table will also include an `:id` primary key field that 467 has a type of `:bigserial`. Check the `table/2` docs for more information. 468 469 ## Examples 470 471 create table(:posts) do 472 add :title, :string, default: "Untitled" 473 add :body, :text 474 475 timestamps() 476 end 477 478 """ 479 defmacro create(object, do: block) do 480 expand_create(object, :create, block) 481 end 482 483 @doc """ 484 Creates a table if it does not exist. 485 486 Works just like `create/2` but does not raise an error when the table 487 already exists. 488 """ 489 defmacro create_if_not_exists(object, do: block) do 490 expand_create(object, :create_if_not_exists, block) 491 end 492 493 defp expand_create(object, command, block) do 494 quote do 495 table = %Table{} = unquote(object) 496 Runner.start_command({unquote(command), Ecto.Migration.__prefix__(table)}) 497 498 if primary_key = Ecto.Migration.__primary_key__(table) do 499 {name, type, opts} = primary_key 500 add(name, type, opts) 501 end 502 503 unquote(block) 504 Runner.end_command() 505 table 506 end 507 end 508 509 @doc """ 510 Alters a table. 511 512 ## Examples 513 514 alter table("posts") do 515 add :summary, :text 516 modify :title, :text 517 remove :views 518 end 519 520 """ 521 defmacro alter(object, do: block) do 522 quote do 523 table = %Table{} = unquote(object) 524 Runner.start_command({:alter, Ecto.Migration.__prefix__(table)}) 525 unquote(block) 526 Runner.end_command() 527 end 528 end 529 530 @doc """ 531 Creates one of the following: 532 533 * an index 534 * a table with only the :id primary key 535 * a constraint 536 537 When reversing (in a `change/0` running backwards), indexes are only dropped 538 if they exist, and no errors are raised. To enforce dropping an index, use 539 `drop/1`. 540 541 ## Examples 542 543 create index("posts", [:name]) 544 create table("version") 545 create constraint("products", "price_must_be_positive", check: "price > 0") 546 547 """ 548 def create(%Index{} = index) do 549 Runner.execute {:create, __prefix__(index)} 550 index 551 end 552 553 def create(%Constraint{} = constraint) do 554 Runner.execute {:create, __prefix__(constraint)} 555 constraint 556 end 557 558 def create(%Table{} = table) do 559 do_create table, :create 560 table 561 end 562 563 @doc """ 564 Creates an index or a table with only `:id` field if one does not yet exist. 565 566 ## Examples 567 568 create_if_not_exists index("posts", [:name]) 569 570 create_if_not_exists table("version") 571 572 """ 573 def create_if_not_exists(%Index{} = index) do 574 Runner.execute {:create_if_not_exists, __prefix__(index)} 575 end 576 577 def create_if_not_exists(%Table{} = table) do 578 do_create table, :create_if_not_exists 579 end 580 581 defp do_create(table, command) do 582 columns = 583 if primary_key = Ecto.Migration.__primary_key__(table) do 584 {name, type, opts} = primary_key 585 [{:add, name, type, opts}] 586 else 587 [] 588 end 589 590 Runner.execute {command, __prefix__(table), columns} 591 end 592 593 @doc """ 594 Drops one of the following: 595 596 * an index 597 * a table 598 * a constraint 599 600 ## Examples 601 602 drop index("posts", [:name]) 603 drop table("posts") 604 drop constraint("products", "price_must_be_positive") 605 drop index("posts", [:name]), mode: :cascade 606 drop table("posts"), mode: :cascade 607 608 ## Options 609 610 * `:mode` - when set to `:cascade`, automatically drop objects that depend 611 on the index, and in turn all objects that depend on those objects 612 on the table. Default is `:restrict` 613 614 """ 615 def drop(%{} = index_or_table_or_constraint, opts \\ []) when is_list(opts) do 616 Runner.execute {:drop, __prefix__(index_or_table_or_constraint), Keyword.get(opts, :mode, :restrict)} 617 index_or_table_or_constraint 618 end 619 620 @doc """ 621 Drops a table or index if it exists. 622 623 Does not raise an error if the specified table or index does not exist. 624 625 ## Examples 626 627 drop_if_exists index("posts", [:name]) 628 drop_if_exists table("posts") 629 drop_if_exists index("posts", [:name]), mode: :cascade 630 drop_if_exists table("posts"), mode: :cascade 631 632 ## Options 633 634 * `:mode` - when set to `:cascade`, automatically drop objects that depend 635 on the index, and in turn all objects that depend on those objects 636 on the table. Default is `:restrict` 637 638 """ 639 def drop_if_exists(%{} = index_or_table, opts \\ []) when is_list(opts) do 640 Runner.execute {:drop_if_exists, __prefix__(index_or_table), Keyword.get(opts, :mode, :restrict)} 641 642 index_or_table 643 end 644 645 @doc """ 646 Returns a table struct that can be given to `create/2`, `alter/2`, `drop/1`, 647 etc. 648 649 ## Examples 650 651 create table("products") do 652 add :name, :string 653 add :price, :decimal 654 end 655 656 drop table("products") 657 658 create table("products", primary_key: false) do 659 add :name, :string 660 add :price, :decimal 661 end 662 663 ## Options 664 665 * `:primary_key` - when `false`, a primary key field is not generated on table 666 creation. Alternatively, a keyword list in the same style of the 667 `:migration_primary_key` repository configuration can be supplied 668 to control the generation of the primary key field. The keyword list 669 must include `:name` and `:type`. See `add/3` for further options. 670 * `:engine` - customizes the table storage for supported databases. For MySQL, 671 the default is InnoDB. 672 * `:prefix` - the prefix for the table. This prefix will automatically be used 673 for all constraints and references defined for this table unless explicitly 674 overridden in said constraints/references. 675 * `:comment` - adds a comment to the table. 676 * `:options` - provide custom options that will be appended after the generated 677 statement. For example, "WITH", "INHERITS", or "ON COMMIT" clauses. 678 679 """ 680 def table(name, opts \\ []) 681 682 def table(name, opts) when is_atom(name) do 683 table(Atom.to_string(name), opts) 684 end 685 686 def table(name, opts) when is_binary(name) and is_list(opts) do 687 struct(%Table{name: name}, opts) 688 end 689 690 @doc ~S""" 691 Returns an index struct that can be given to `create/1`, `drop/1`, etc. 692 693 Expects the table name as the first argument and the index field(s) as 694 the second. The fields can be atoms, representing columns, or strings, 695 representing expressions that are sent as-is to the database. 696 697 ## Options 698 699 * `:name` - the name of the index. Defaults to "#{table}_#{column}_index". 700 * `:unique` - indicates whether the index should be unique. Defaults to 701 `false`. 702 * `:concurrently` - indicates whether the index should be created/dropped 703 concurrently. 704 * `:using` - configures the index type. 705 * `:prefix` - specify an optional prefix for the index. 706 * `:where` - specify conditions for a partial index. 707 * `:include` - specify fields for a covering index. This is not supported 708 by all databases. For more information on PostgreSQL support, please 709 [read the official docs](https://www.postgresql.org/docs/current/indexes-index-only-scans.html). 710 * `:nulls_distinct` - specify whether null values should be considered 711 distinct for a unique index. Defaults to `nil`, which will not add the 712 parameter to the generated SQL and thus use the database default. 713 This option is currently only supported by PostgreSQL 15+. 714 For MySQL, it is always false. For MSSQL, it is always true. 715 See the dedicated section on this option for more information. 716 * `:comment` - adds a comment to the index. 717 718 ## Adding/dropping indexes concurrently 719 720 PostgreSQL supports adding/dropping indexes concurrently (see the 721 [docs](http://www.postgresql.org/docs/current/static/sql-createindex.html)). 722 However, this feature does not work well with the transactions used by 723 Ecto to guarantee integrity during migrations. 724 725 You can address this with two changes: 726 727 1. Change your repository to use PG advisory locks as the migration lock. 728 Note this may not be supported by Postgres-like databases and proxies. 729 730 2. Disable DDL transactions. Doing this removes the guarantee that all of 731 the changes in the migration will happen at once, so you will want to 732 keep it short. 733 734 If the database adapter supports several migration lock strategies, such as 735 Postgrex, then review those strategies and consider using a strategy that 736 utilizes advisory locks to faciliate running migrations one at a time even 737 across multiple nodes. For example: 738 739 # Config the Repo (PostgreSQL example) 740 config MyApp.Repo, migration_lock: :pg_advisory_lock 741 742 # Migrate with your concurrent operation 743 defmodule MyRepo.Migrations.CreateIndexes do 744 use Ecto.Migration 745 @disable_ddl_transaction true 746 747 def change do 748 create index("posts", [:slug], concurrently: true) 749 end 750 end 751 752 ## Index types 753 754 When creating an index, the index type can be specified with the `:using` 755 option. The `:using` option can be an atom or a string, and its value is 756 passed to the generated `USING` clause as-is. 757 758 For example, PostgreSQL supports several index types like B-tree (the 759 default), Hash, GIN, and GiST. More information on index types can be found 760 in the [PostgreSQL docs](http://www.postgresql.org/docs/current/indexes-types.html). 761 762 ## Partial indexes 763 764 Databases like PostgreSQL and MSSQL support partial indexes. 765 766 A partial index is an index built over a subset of a table. The subset 767 is defined by a conditional expression using the `:where` option. 768 The `:where` option can be an atom or a string; its value is passed 769 to the generated `WHERE` clause as-is. 770 771 More information on partial indexes can be found in the [PostgreSQL 772 docs](http://www.postgresql.org/docs/current/indexes-partial.html). 773 774 ## The `:nulls_distinct` option 775 776 A unique index does not prevent multiple null values by default in most databases. 777 778 For example, imagine we have a "products" table and need to guarantee that 779 sku's are unique within their category, but the category is optional. 780 Creating a regular unique index over the sku and category_id fields with: 781 782 create index("products", [:sku, :category_id], unique: true) 783 784 will allow products with the same sku to be inserted if their category_id is `nil`. 785 The `:nulls_distinct` option can be used to change this behavior by considering 786 null values as equal, i.e. not distinct: 787 788 create index("products", [:sku, :category_id], unique: true, nulls_distinct: false) 789 790 This option is currently only supported by PostgreSQL 15+. 791 As a workaround for older PostgreSQL versions and other databases, an 792 additional partial unique index for the sku can be created: 793 794 create index("products", [:sku, :category_id], unique: true) 795 create index("products", [:sku], unique: true, where: "category_id IS NULL") 796 797 ## Examples 798 799 # With no name provided, the name of the below index defaults to 800 # products_category_id_sku_index 801 create index("products", [:category_id, :sku], unique: true) 802 803 # The name can also be set explicitly 804 create index("products", [:category_id, :sku], name: :my_special_name) 805 806 # Indexes can be added concurrently 807 create index("products", [:category_id, :sku], concurrently: true) 808 809 # The index type can be specified 810 create index("products", [:name], using: :hash) 811 812 # Partial indexes are created by specifying a :where option 813 create index("products", [:user_id], where: "price = 0", name: :free_products_index) 814 815 # Covering indexes are created by specifying a :include option 816 create index("products", [:user_id], include: [:category_id]) 817 818 Indexes also support custom expressions. Some databases may require the 819 index expression to be written between parentheses: 820 821 # Create an index on a custom expression 822 create index("products", ["(lower(name))"], name: :products_lower_name_index) 823 824 # Create a tsvector GIN index on PostgreSQL 825 create index("products", ["(to_tsvector('english', name))"], 826 name: :products_name_vector, using: "GIN") 827 """ 828 def index(table, columns, opts \\ []) 829 830 def index(table, columns, opts) when is_atom(table) do 831 index(Atom.to_string(table), columns, opts) 832 end 833 834 def index(table, column, opts) when is_binary(table) and is_atom(column) do 835 index(table, [column], opts) 836 end 837 838 def index(table, columns, opts) when is_binary(table) and is_list(columns) and is_list(opts) do 839 validate_index_opts!(opts) 840 index = struct(%Index{table: table, columns: columns}, opts) 841 %{index | name: index.name || default_index_name(index)} 842 end 843 844 @doc """ 845 Shortcut for creating a unique index. 846 847 See `index/3` for more information. 848 """ 849 def unique_index(table, columns, opts \\ []) 850 851 def unique_index(table, columns, opts) when is_list(opts) do 852 index(table, columns, [unique: true] ++ opts) 853 end 854 855 defp default_index_name(index) do 856 [index.table, index.columns, "index"] 857 |> List.flatten 858 |> Enum.map(&to_string(&1)) 859 |> Enum.map(&String.replace(&1, ~r"[^\w_]", "_")) 860 |> Enum.map(&String.replace_trailing(&1, "_", "")) 861 |> Enum.join("_") 862 |> String.to_atom 863 end 864 865 @doc """ 866 Executes arbitrary SQL, anonymous function or a keyword command. 867 868 The argument is typically a string, containing the SQL command to be executed. 869 Keyword commands exist for non-SQL adapters and are not used in most situations. 870 871 Supplying an anonymous function does allow for arbitrary code to execute as 872 part of the migration. This is most often used in combination with `repo/0` 873 by library authors who want to create high-level migration helpers. 874 875 Reversible commands can be defined by calling `execute/2`. 876 877 ## Examples 878 879 execute "CREATE EXTENSION postgres_fdw" 880 881 execute create: "posts", capped: true, size: 1024 882 883 execute(fn -> repo().query!("SELECT $1::integer + $2", [40, 2], [log: :info]) end) 884 885 execute(fn -> repo().update_all("posts", set: [published: true]) end) 886 """ 887 def execute(command) when is_binary(command) or is_function(command, 0) or is_list(command) do 888 Runner.execute command 889 end 890 891 @doc """ 892 Executes reversible SQL commands. 893 894 This is useful for database-specific functionality that does not 895 warrant special support in Ecto, for example, creating and dropping 896 a PostgreSQL extension. The `execute/2` form avoids having to define 897 separate `up/0` and `down/0` blocks that each contain an `execute/1` 898 expression. 899 900 The allowed parameters are explained in `execute/1`. 901 902 ## Examples 903 904 defmodule MyApp.MyMigration do 905 use Ecto.Migration 906 907 def change do 908 execute "CREATE EXTENSION postgres_fdw", "DROP EXTENSION postgres_fdw" 909 execute(&execute_up/0, &execute_down/0) 910 end 911 912 defp execute_up, do: repo().query!("select 'Up query …';", [], [log: :info]) 913 defp execute_down, do: repo().query!("select 'Down query …';", [], [log: :info]) 914 end 915 """ 916 def execute(up, down) when (is_binary(up) or is_function(up, 0) or is_list(up)) and 917 (is_binary(down) or is_function(down, 0) or is_list(down)) do 918 Runner.execute %Command{up: up, down: down} 919 end 920 921 @doc """ 922 Gets the migrator direction. 923 """ 924 @spec direction :: :up | :down 925 def direction do 926 Runner.migrator_direction() 927 end 928 929 @doc """ 930 Gets the migrator repo. 931 """ 932 @spec repo :: Ecto.Repo.t 933 def repo do 934 Runner.repo() 935 end 936 937 @doc """ 938 Gets the migrator prefix. 939 """ 940 def prefix do 941 Runner.prefix() 942 end 943 944 @doc """ 945 Adds a column when creating or altering a table. 946 947 This function also accepts Ecto primitive types as column types 948 that are normalized by the database adapter. For example, 949 `:string` is converted to `:varchar`, `:binary` to `:bits` or `:blob`, 950 and so on. 951 952 However, the column type is not always the same as the type used in your 953 schema. For example, a schema that has a `:string` field can be supported by 954 columns of type `:char`, `:varchar`, `:text`, and others. For this reason, 955 this function also accepts `:text` and other type annotations that are native 956 to the database. These are passed to the database as-is. 957 958 To sum up, the column type may be either an Ecto primitive type, 959 which is normalized in cases where the database does not understand it, 960 such as `:string` or `:binary`, or a database type which is passed as-is. 961 Custom Ecto types like `Ecto.UUID` are not supported because 962 they are application-level concerns and may not always map to the database. 963 964 Note: It may be necessary to quote case-sensitive, user-defined type names. 965 For example, PostgreSQL normalizes all identifiers to lower case unless 966 they are wrapped in double quotes. To ensure a case-sensitive type name 967 is sent properly, it must be defined `:'"LikeThis"'` or `:"\"LikeThis\""`. 968 This is not necessary for column names because Ecto quotes them automatically. 969 Type names are not automatically quoted because they may be expressions such 970 as `varchar(255)`. 971 972 ## Examples 973 974 create table("posts") do 975 add :title, :string, default: "Untitled" 976 end 977 978 alter table("posts") do 979 add :summary, :text # Database type 980 add :object, :map # Elixir type which is handled by the database 981 add :custom, :'"UserDefinedType"' # A case-sensitive, user-defined type name 982 end 983 984 ## Options 985 986 * `:primary_key` - when `true`, marks this field as the primary key. 987 If multiple fields are marked, a composite primary key will be created. 988 * `:default` - the column's default value. It can be a string, number, empty 989 list, list of strings, list of numbers, or a fragment generated by 990 `fragment/1`. 991 * `:null` - determines whether the column accepts null values. When not specified, 992 the database will use its default behaviour (which is to treat the column as nullable 993 in most databases). 994 * `:size` - the size of the type (for example, the number of characters). 995 The default is no size, except for `:string`, which defaults to `255`. 996 * `:precision` - the precision for a numeric type. Required when `:scale` is 997 specified. 998 * `:scale` - the scale of a numeric type. Defaults to `0`. 999 * `:comment` - adds a comment to the added column. 1000 * `:after` - positions field after the specified one. Only supported on MySQL, 1001 it is ignored by other databases. 1002 1003 """ 1004 def add(column, type, opts \\ []) when is_atom(column) and is_list(opts) do 1005 validate_precision_opts!(opts, column) 1006 validate_type!(type) 1007 Runner.subcommand {:add, column, type, opts} 1008 end 1009 1010 @doc """ 1011 Adds a column if it not exists yet when altering a table. 1012 1013 If the `type` value is a `%Reference{}`, it is used to add a constraint. 1014 1015 `type` and `opts` are exactly the same as in `add/3`. 1016 1017 This command is not reversible as Ecto does not know about column existence before the creation attempt. 1018 1019 ## Examples 1020 1021 alter table("posts") do 1022 add_if_not_exists :title, :string, default: "" 1023 end 1024 1025 """ 1026 def add_if_not_exists(column, type, opts \\ []) when is_atom(column) and is_list(opts) do 1027 validate_precision_opts!(opts, column) 1028 validate_type!(type) 1029 Runner.subcommand {:add_if_not_exists, column, type, opts} 1030 end 1031 1032 @doc """ 1033 Renames a table. 1034 1035 ## Examples 1036 1037 rename table("posts"), to: table("new_posts") 1038 """ 1039 def rename(%Table{} = table_current, to: %Table{} = table_new) do 1040 Runner.execute {:rename, __prefix__(table_current), __prefix__(table_new)} 1041 table_new 1042 end 1043 1044 @doc """ 1045 Renames a column. 1046 1047 Note that this occurs outside of the `alter` statement. 1048 1049 ## Examples 1050 1051 rename table("posts"), :title, to: :summary 1052 """ 1053 def rename(%Table{} = table, current_column, to: new_column) when is_atom(current_column) and is_atom(new_column) do 1054 Runner.execute {:rename, __prefix__(table), current_column, new_column} 1055 table 1056 end 1057 1058 @doc """ 1059 Generates a fragment to be used as a default value. 1060 1061 ## Examples 1062 1063 create table("posts") do 1064 add :inserted_at, :naive_datetime, default: fragment("now()") 1065 end 1066 """ 1067 def fragment(expr) when is_binary(expr) do 1068 {:fragment, expr} 1069 end 1070 1071 @doc """ 1072 Adds `:inserted_at` and `:updated_at` timestamp columns. 1073 1074 Those columns are of `:naive_datetime` type and by default cannot be null. A 1075 list of `opts` can be given to customize the generated fields. 1076 1077 Following options will override the repo configuration specified by 1078 `:migration_timestamps` option. 1079 1080 ## Options 1081 1082 * `:inserted_at` - the name of the column for storing insertion times. 1083 Setting it to `false` disables the column. 1084 * `:updated_at` - the name of the column for storing last-updated-at times. 1085 Setting it to `false` disables the column. 1086 * `:type` - the type of the `:inserted_at` and `:updated_at` columns. 1087 Defaults to `:naive_datetime`. 1088 * `:default` - the columns' default value. It can be a string, number, empty 1089 list, list of strings, list of numbers, or a fragment generated by 1090 `fragment/1`. 1091 1092 """ 1093 def timestamps(opts \\ []) when is_list(opts) do 1094 opts = Keyword.merge(Runner.repo_config(:migration_timestamps, []), opts) 1095 opts = Keyword.put_new(opts, :null, false) 1096 1097 {type, opts} = Keyword.pop(opts, :type, :naive_datetime) 1098 {inserted_at, opts} = Keyword.pop(opts, :inserted_at, :inserted_at) 1099 {updated_at, opts} = Keyword.pop(opts, :updated_at, :updated_at) 1100 1101 if inserted_at != false, do: add(inserted_at, type, opts) 1102 if updated_at != false, do: add(updated_at, type, opts) 1103 end 1104 1105 @doc """ 1106 Modifies the type of a column when altering a table. 1107 1108 This command is not reversible unless the `:from` option is provided. 1109 When the `:from` option is set, the adapter will try to drop 1110 the corresponding foreign key constraints before modifying the type. 1111 Generally speaking, you want to pass the type and each option 1112 you are modifying to `:from`, so the column can be rolled back properly. 1113 However, note that `:from` cannot be be used to modify primary keys, 1114 as those are generally trickier to revert. 1115 1116 See `add/3` for more information on supported types. 1117 1118 If you want to modify a column without changing its type, 1119 such as adding or dropping a null constraints, consider using 1120 the `execute/2` command with the relevant SQL command instead 1121 of `modify/3`, if supported by your database. This may avoid 1122 redundant type updates and be more efficient, as an unnecessary 1123 type update can lock the table, even if the type actually 1124 doesn't change. 1125 1126 ## Examples 1127 1128 alter table("posts") do 1129 modify :title, :text 1130 end 1131 1132 # Self rollback when using the :from option 1133 alter table("posts") do 1134 modify :title, :text, from: :string 1135 end 1136 1137 # Modify column with rollback options 1138 alter table("posts") do 1139 modify :title, :text, null: false, from: {:string, null: true} 1140 end 1141 1142 ## Options 1143 1144 * `:null` - determines whether the column accepts null values. If this option is 1145 not set, the nullable behaviour of the underlying column is not modified. 1146 * `:default` - changes the default value of the column. 1147 * `:from` - specifies the current type and options of the column. 1148 * `:size` - specifies the size of the type (for example, the number of characters). 1149 The default is no size. 1150 * `:precision` - the precision for a numeric type. Required when `:scale` is 1151 specified. 1152 * `:scale` - the scale of a numeric type. Defaults to `0`. 1153 * `:comment` - adds a comment to the modified column. 1154 """ 1155 def modify(column, type, opts \\ []) when is_atom(column) and is_list(opts) do 1156 validate_precision_opts!(opts, column) 1157 validate_type!(type) 1158 Runner.subcommand {:modify, column, type, opts} 1159 end 1160 1161 @doc """ 1162 Removes a column when altering a table. 1163 1164 This command is not reversible as Ecto does not know what type it should add 1165 the column back as. See `remove/3` as a reversible alternative. 1166 1167 ## Examples 1168 1169 alter table("posts") do 1170 remove :title 1171 end 1172 1173 """ 1174 def remove(column) when is_atom(column) do 1175 Runner.subcommand {:remove, column} 1176 end 1177 1178 @doc """ 1179 Removes a column in a reversible way when altering a table. 1180 1181 `type` and `opts` are exactly the same as in `add/3`, and 1182 they are used when the command is reversed. 1183 1184 If the `type` value is a `%Reference{}`, it is used to remove the constraint. 1185 1186 ## Examples 1187 1188 alter table("posts") do 1189 remove :title, :string, default: "" 1190 end 1191 1192 """ 1193 def remove(column, type, opts \\ []) when is_atom(column) do 1194 validate_type!(type) 1195 Runner.subcommand {:remove, column, type, opts} 1196 end 1197 1198 @doc """ 1199 Removes a column only if the column exists when altering the constraint if the reference type is passed 1200 once it only has the constraint name on reference structure. 1201 1202 This command is not reversible as Ecto does not know about column existence before the removal attempt. 1203 1204 ## Examples 1205 1206 alter table("posts") do 1207 remove_if_exists :title, :string 1208 end 1209 1210 """ 1211 def remove_if_exists(column, type) when is_atom(column) do 1212 validate_type!(type) 1213 Runner.subcommand {:remove_if_exists, column, type} 1214 end 1215 1216 @doc ~S""" 1217 Defines a foreign key. 1218 1219 By default it assumes you are linking to the referenced table 1220 via its primary key with name `:id`. If you are using a non-default 1221 key setup (e.g. using `uuid` type keys) you must ensure you set the 1222 options, such as `:name` and `:type`, to match your target key. 1223 1224 ## Examples 1225 1226 create table("products") do 1227 add :group_id, references("groups") 1228 end 1229 1230 create table("categories") do 1231 add :group_id, :integer 1232 # A composite foreign that points from categories (product_id, group_id) 1233 # to products (id, group_id) 1234 add :product_id, references("products", with: [group_id: :group_id]) 1235 end 1236 1237 ## Options 1238 1239 * `:name` - The name of the underlying reference, which defaults to 1240 "#{table}_#{column}_fkey". 1241 * `:column` - The column name in the referenced table, which defaults to `:id`. 1242 * `:prefix` - The prefix for the reference. Defaults to the prefix 1243 defined by the block's `table/2` struct (the "products" table in 1244 the example above), or `nil`. 1245 * `:type` - The foreign key type, which defaults to `:bigserial`. 1246 * `:on_delete` - What to do if the referenced entry is deleted. May be 1247 `:nothing` (default), `:delete_all`, `:nilify_all`, or `:restrict`. 1248 * `:on_update` - What to do if the referenced entry is updated. May be 1249 `:nothing` (default), `:update_all`, `:nilify_all`, or `:restrict`. 1250 * `:validate` - Whether or not to validate the foreign key constraint on 1251 creation or not. Only available in PostgreSQL, and should be followed by 1252 a command to validate the foreign key in a following migration if false. 1253 * `:with` - defines additional keys to the foreign key in order to build 1254 a composite primary key 1255 * `:match` - select if the match is `:simple`, `:partial`, or `:full`. This is 1256 [supported only by PostgreSQL](https://www.postgresql.org/docs/current/sql-createtable.html) 1257 at the moment. 1258 1259 """ 1260 def references(table, opts \\ []) 1261 1262 def references(table, opts) when is_atom(table) do 1263 references(Atom.to_string(table), opts) 1264 end 1265 1266 def references(table, opts) when is_binary(table) and is_list(opts) do 1267 opts = Keyword.merge(foreign_key_repo_opts(), opts) 1268 reference = struct(%Reference{table: table}, opts) 1269 1270 unless reference.on_delete in [:nothing, :delete_all, :nilify_all, :restrict] do 1271 raise ArgumentError, "unknown :on_delete value: #{inspect reference.on_delete}" 1272 end 1273 1274 unless reference.on_update in [:nothing, :update_all, :nilify_all, :restrict] do 1275 raise ArgumentError, "unknown :on_update value: #{inspect reference.on_update}" 1276 end 1277 1278 reference 1279 end 1280 1281 defp foreign_key_repo_opts() do 1282 case Runner.repo_config(:migration_primary_key, []) do 1283 false -> [] 1284 opts -> opts 1285 end 1286 |> Keyword.take([:type]) 1287 |> Keyword.merge(Runner.repo_config(:migration_foreign_key, [])) 1288 end 1289 1290 @doc ~S""" 1291 Defines a constraint (either a check constraint or an exclusion constraint) 1292 to be evaluated by the database when a row is inserted or updated. 1293 1294 ## Examples 1295 1296 create constraint("users", :price_must_be_positive, check: "price > 0") 1297 create constraint("size_ranges", :no_overlap, exclude: ~s|gist (int4range("from", "to", '[]') WITH &&)|) 1298 drop constraint("products", "price_must_be_positive") 1299 1300 ## Options 1301 1302 * `:check` - A check constraint expression. Required when creating a check constraint. 1303 * `:exclude` - An exclusion constraint expression. Required when creating an exclusion constraint. 1304 * `:prefix` - The prefix for the table. 1305 * `:validate` - Whether or not to validate the constraint on creation (true by default). Only 1306 available in PostgreSQL, and should be followed by a command to validate the new constraint in 1307 a following migration if false. 1308 * `:comment` - adds a comment to the constraint. 1309 1310 """ 1311 def constraint(table, name, opts \\ []) 1312 1313 def constraint(table, name, opts) when is_atom(table) do 1314 constraint(Atom.to_string(table), name, opts) 1315 end 1316 1317 def constraint(table, name, opts) when is_binary(table) and is_list(opts) do 1318 struct(%Constraint{table: table, name: name}, opts) 1319 end 1320 1321 @doc "Executes queue migration commands." 1322 defmacro flush do 1323 quote do 1324 if direction() == :down and not function_exported?(__MODULE__, :down, 0) do 1325 raise "calling flush() inside change when doing rollback is not supported." 1326 else 1327 Runner.flush() 1328 end 1329 end 1330 end 1331 1332 # Validation helpers 1333 defp validate_type!(:datetime) do 1334 raise ArgumentError, "the :datetime type in migrations is not supported, " <> 1335 "please use :utc_datetime or :naive_datetime instead" 1336 end 1337 1338 defp validate_type!(type) when is_atom(type) do 1339 case Atom.to_string(type) do 1340 "Elixir." <> _ -> 1341 raise ArgumentError, 1342 "#{inspect type} is not a valid database type, " <> 1343 "please use an atom like :string, :text and so on" 1344 _ -> 1345 :ok 1346 end 1347 end 1348 1349 defp validate_type!({type, subtype}) when is_atom(type) and is_atom(subtype) do 1350 validate_type!(subtype) 1351 end 1352 1353 defp validate_type!({type, subtype}) when is_atom(type) and is_tuple(subtype) do 1354 for t <- Tuple.to_list(subtype), do: validate_type!(t) 1355 end 1356 1357 defp validate_type!(%Reference{} = reference) do 1358 reference 1359 end 1360 1361 defp validate_type!(type) do 1362 raise ArgumentError, """ 1363 invalid migration type: #{inspect(type)}. Expected one of: 1364 1365 * an atom, such as :string 1366 * a quoted atom, such as :"integer unsigned" 1367 * an Ecto.Type, such as Ecto.UUID 1368 * a tuple of the above, such as {:array, :integer} or {:array, Ecto.UUID} 1369 * a reference, such as references(:users) 1370 1371 All Ecto types are allowed and properly translated. 1372 All other types are sent to the database as is. 1373 """ 1374 end 1375 1376 defp validate_index_opts!(opts) when is_list(opts) do 1377 if opts[:nulls_distinct] != nil and opts[:unique] != true do 1378 raise ArgumentError, "the `nulls_distinct` option can only be used with unique indexes" 1379 end 1380 1381 case Keyword.get_values(opts, :where) do 1382 [_, _ | _] -> 1383 raise ArgumentError, 1384 "only one `where` keyword is supported when declaring a partial index. " <> 1385 "To specify multiple conditions, write a single WHERE clause using AND between them" 1386 1387 _ -> 1388 :ok 1389 end 1390 end 1391 1392 defp validate_index_opts!(opts), do: opts 1393 1394 defp validate_precision_opts!(opts, column) when is_list(opts) do 1395 if opts[:scale] && !opts[:precision] do 1396 raise ArgumentError, "column #{Atom.to_string(column)} is missing precision option" 1397 end 1398 end 1399 1400 @doc false 1401 def __prefix__(%{prefix: prefix} = index_or_table) do 1402 runner_prefix = Runner.prefix() 1403 1404 cond do 1405 is_nil(prefix) -> 1406 prefix = runner_prefix || Runner.repo_config(:migration_default_prefix, nil) 1407 %{index_or_table | prefix: prefix} 1408 is_nil(runner_prefix) or runner_prefix == to_string(prefix) -> 1409 index_or_table 1410 true -> 1411 raise Ecto.MigrationError, message: 1412 "the :prefix option `#{prefix}` does not match the migrator prefix `#{runner_prefix}`" 1413 end 1414 end 1415 1416 @doc false 1417 def __primary_key__(table) do 1418 case table.primary_key do 1419 false -> false 1420 1421 true -> 1422 case Runner.repo_config(:migration_primary_key, []) do 1423 false -> false 1424 opts when is_list(opts) -> pk_opts_to_tuple(opts) 1425 end 1426 1427 opts when is_list(opts) -> pk_opts_to_tuple(opts) 1428 1429 _ -> 1430 raise ArgumentError, ":primary_key option must be either a boolean or a keyword list of options" 1431 end 1432 end 1433 1434 defp pk_opts_to_tuple(opts) do 1435 opts = Keyword.put(opts, :primary_key, true) 1436 {name, opts} = Keyword.pop(opts, :name, :id) 1437 {type, opts} = Keyword.pop(opts, :type, :bigserial) 1438 {name, type, opts} 1439 end 1440 end