Repository: phoenixframework/phoenix
Pull request #4268 (2021-03-20 03:38:29)

Add `--database sqlite3` option to `phx.new`
# Overview & Background
SQLite3 is an extraordinary powerful embedded database that is suitable for small sites. Since it is not a client/server database, it is much easier to setup and maintain. It is a great candidate for spinning up small Phoenix projects that do not need the full complexity of Postgres. It's primary disadvantage is that it cannot scale beyond multiple machines or nodes, but it is well suited to vertical scaling.

Until recently, there has not been any suitable [Ecto3 SQLite3 adapter](https://github.com/elixir-sqlite/ecto_sqlite3). I believe at some point in the past Phoenix used to support SQLite3 as an option, but removed it when it upgraded to Ecto3 as the existing drivers only supported Ecto2.

Now that there is a fully functional Ecto3 adapter, I thought it would be good to re-integrate SQLite3 into the Phoenix generators.

# Changes
Here I add a new `--database sqlite3` functionality to `phx.new`. 

I also updated the documentation where relevant, searching for any places where e.g., `mysql` or `myxql` was referenced, and adding relevant info about the `sqlite3` related functionality.

Because SQLite in typical usage does not require or need a username, password, or host, I had to fork some of the configuration logic for the generator to support the "embedded database" case where only a database path is needed. This also meant changing some code that assumed all adapters required a `DATABASE_URL`, for example, instead having the code generated refer to a `DATABASE_PATH` instead.

I've opted to have the database files stored at the project root by default. Another location I considered is `priv/` but per the [documentation](https://hexdocs.pm/phoenix/directory_structure.html), it looks like this is intended for more static/unchanging assets. In either case, I updated the `.gitignore` to exclude the db files that will be created in the root. 

One tricky part was umbrella projects, as running e.g., `mix test` from the application root (as opposed to the umbrella root) was creating database files _there_ at the app root, as the CWD would be there. I got around this by making the location of the database files be relative to the config directory in order to ensure they are always placed at the root of the umbrella project, as you can see in the changes. 

I've also opted to use the `.db` extension instead of say, a `.sqlite3` extension that e.g., Rails uses. However, I don't feel strongly either way - in SQLite3's view a file is a file :) 

josevalim: Those changes are absolutely great! Thank you for all of the amazing work on making sqlite3 firstclass for Elixir.

We just need to rebase the PR and I can merge it, thank you! :heart:
kevinlang: @josevalim I rebased all the changes :+1: 

Patch:
diff --git a/guides/authentication/mix_phx_gen_auth.md b/guides/authentication/mix_phx_gen_auth.md
index a7134fd10..3eb396118 100644
--- a/guides/authentication/mix_phx_gen_auth.md
+++ b/guides/authentication/mix_phx_gen_auth.md
@@ -78,7 +78,7 @@ The generated authentication code protects against enumeration attacks on all en
 
 ### Case sensitiveness
 
-The email lookup is made to be case insensitive. Case insensitive lookups are the default in MySQL and MSSQL but use the [`citext` extension in PostgreSQL](https://www.postgresql.org/docs/current/citext.html).
+The email lookup is made to be case insensitive. Case insensitive lookups are the default in MySQL and MSSQL. In SQLite3 we use [`COLLATE NOCASE`](https://www.sqlite.org/datatype3.html#collating_sequences) in the column definition to support it. In PostgreSQL we use the [`citext` extension](https://www.postgresql.org/docs/current/citext.html).
 
 Note `citext` is part of PostgreSQL itself and is bundled with it in most operating systems and package managers. `mix phx.gen.auth` takes care of creating the extension and no extra work is necessary in the majority of cases. If by any chance your package manager splits `citext` into a separate package, you will get an error while migrating and you can most likely solve it by installing the `postgres-contrib` package.
 
diff --git a/guides/ecto.md b/guides/ecto.md
index 271224950..75a07869a 100644
--- a/guides/ecto.md
+++ b/guides/ecto.md
@@ -10,6 +10,7 @@ Phoenix uses Ecto to provide builtin support to the following databases:
 * MySQL (via [`myxql`](https://github.com/elixir-ecto/myxql))
 * MSSQL (via [`tds`](https://github.com/livehelpnow/tds))
 * ETS (via [`etso`](https://github.com/evadne/etso))
+* SQLite3 (via [`ecto_sqlite3`](https://github.com/elixir-sqlite/ecto_sqlite3))
 
 
 Newly generated Phoenix projects include Ecto with the PostgreSQL adapter by default. You can pass the `--database` option to change or `--no-ecto` flag to exclude this.
diff --git a/guides/introduction/installation.md b/guides/introduction/installation.md
index 233060716..b1a86bef7 100644
--- a/guides/introduction/installation.md
+++ b/guides/introduction/installation.md
@@ -60,7 +60,7 @@ macOS users can also install Node.js via [homebrew](https://brew.sh/).
 
 ## PostgreSQL
 
-PostgreSQL is a relational database server. Phoenix configures applications to use it by default, but we can switch to MySQL or MSSQL by passing the `--database` flag when creating a new application.
+PostgreSQL is a relational database server. Phoenix configures applications to use it by default, but we can switch to MySQL, MSSQL, or SQLite3 by passing the `--database` flag when creating a new application.
 
 In order to talk to databases, Phoenix applications use another Elixir package, called [Ecto](https://github.com/elixir-ecto/ecto). If you don't plan to use databases in your application, you can pass the `--no-ecto` flag.
 
diff --git a/installer/lib/mix/tasks/phx.new.ecto.ex b/installer/lib/mix/tasks/phx.new.ecto.ex
index f6c1b1f42..a3a09e2b5 100644
--- a/installer/lib/mix/tasks/phx.new.ecto.ex
+++ b/installer/lib/mix/tasks/phx.new.ecto.ex
@@ -28,6 +28,7 @@ defmodule Mix.Tasks.Phx.New.Ecto do
         * `postgres` - via https://github.com/elixir-ecto/postgrex
         * `mysql` - via https://github.com/elixir-ecto/myxql
         * `mssql` - via https://github.com/livehelpnow/tds
+        * `sqlite3` - via https://github.com/elixir-sqlite/ecto_sqlite3
 
       Please check the driver docs for more information
       and requirements. Defaults to "postgres".
diff --git a/installer/lib/mix/tasks/phx.new.ex b/installer/lib/mix/tasks/phx.new.ex
index bdd04df91..6ed559a46 100644
--- a/installer/lib/mix/tasks/phx.new.ex
+++ b/installer/lib/mix/tasks/phx.new.ex
@@ -29,6 +29,7 @@ defmodule Mix.Tasks.Phx.New do
         * `postgres` - via https://github.com/elixir-ecto/postgrex
         * `mysql` - via https://github.com/elixir-ecto/myxql
         * `mssql` - via https://github.com/livehelpnow/tds
+        * `sqlite3` - via https://github.com/elixir-sqlite/ecto_sqlite3
 
       Please check the driver docs for more information
       and requirements. Defaults to "postgres".
diff --git a/installer/lib/phx_new/generator.ex b/installer/lib/phx_new/generator.ex
index 49a2fb6d0..91400cfe5 100644
--- a/installer/lib/phx_new/generator.ex
+++ b/installer/lib/phx_new/generator.ex
@@ -240,8 +240,7 @@ defmodule Phx.New.Generator do
 
     config_inject(project_path, "config/dev.exs", """
     # Configure your database
-    config :#{binding[:app_name]}, #{binding[:app_module]}.Repo#{kw_to_config(adapter_config[:dev])},
-      pool_size: 10
+    config :#{binding[:app_name]}, #{binding[:app_module]}.Repo#{kw_to_config(adapter_config[:dev])}
     """)
 
     config_inject(project_path, "config/test.exs", """
@@ -254,18 +253,10 @@ defmodule Phx.New.Generator do
     """)
 
     prod_only_config_inject(project_path, "config/runtime.exs", """
-    database_url =
-      System.get_env("DATABASE_URL") ||
-        raise \"""
-        environment variable DATABASE_URL is missing.
-        For example: ecto://USER:PASS@HOST/DATABASE
-        \"""
+    #{adapter_config[:prod_variables]}
 
     config :#{binding[:app_name]}, #{binding[:app_module]}.Repo,
-      # ssl: true,
-      url: database_url,
-      pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
-      socket_options: [:inet6]
+      #{adapter_config[:prod_config]}
     """)
   end
 
@@ -288,10 +279,46 @@ defmodule Phx.New.Generator do
     {:postgrex, Ecto.Adapters.Postgres, db_config(app, module, "postgres", "postgres")}
   end
 
+  defp get_ecto_adapter("sqlite3", app, module) do
+    {:ecto_sqlite3, Ecto.Adapters.SQLite3, db_config(app, module)}
+  end
+
   defp get_ecto_adapter(db, _app, _mod) do
     Mix.raise("Unknown database #{inspect(db)}")
   end
 
+  defp db_config(app, module) do
+    [
+      dev: [
+        database: {:literal, ~s|Path.expand("../#{app}_dev.db", Path.dirname(__ENV__.file))|},
+        pool_size: 5,
+        show_sensitive_data_on_connection_error: true
+      ],
+      test: [
+        database: {:literal, ~s|Path.expand("../#{app}_test.db", Path.dirname(__ENV__.file))|},
+        pool_size: 5,
+        pool: Ecto.Adapters.SQL.Sandbox
+      ],
+      test_setup_all: "Ecto.Adapters.SQL.Sandbox.mode(#{inspect(module)}.Repo, :manual)",
+      test_setup: """
+          pid = Ecto.Adapters.SQL.Sandbox.start_owner!(#{inspect(module)}.Repo, shared: not tags[:async])
+          on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)\
+      """,
+      prod_variables: """
+      database_path =
+        System.get_env("DATABASE_PATH") ||
+          raise \"""
+          environment variable DATABASE_PATH is missing.
+          For example: /etc/#{app}/#{app}.db
+          \"""
+      """,
+      prod_config: """
+      database: database_path,
+      pool_size: String.to_integer(System.get_env("POOL_SIZE") || "5")
+      """
+    ]
+  end
+
   defp db_config(app, module, user, pass) do
     [
       dev: [
@@ -299,19 +326,35 @@ defmodule Phx.New.Generator do
         password: pass,
         database: "#{app}_dev",
         hostname: "localhost",
-        show_sensitive_data_on_connection_error: true
+        show_sensitive_data_on_connection_error: true,
+        pool_size: 10
       ],
       test: [
         username: user,
         password: pass,
         database: {:literal, ~s|"#{app}_test\#{System.get_env("MIX_TEST_PARTITION")}"|},
         hostname: "localhost",
-        pool: Ecto.Adapters.SQL.Sandbox
+        pool: Ecto.Adapters.SQL.Sandbox,
+        pool_size: 10,
       ],
       test_setup_all: "Ecto.Adapters.SQL.Sandbox.mode(#{inspect(module)}.Repo, :manual)",
       test_setup: """
           pid = Ecto.Adapters.SQL.Sandbox.start_owner!(#{inspect(module)}.Repo, shared: not tags[:async])
           on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)\
+      """,
+      prod_variables: """
+      database_url =
+        System.get_env("DATABASE_URL") ||
+          raise \"""
+          environment variable DATABASE_URL is missing.
+          For example: ecto://USER:PASS@HOST/DATABASE
+          \"""
+      """,
+      prod_config: """
+      # ssl: true,
+      url: database_url,
+      pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
+      socket_options: [:inet6]
       """
     ]
   end
diff --git a/installer/templates/phx_single/gitignore b/installer/templates/phx_single/gitignore
index 8eae1ec4d..9a306c7d6 100644
--- a/installer/templates/phx_single/gitignore
+++ b/installer/templates/phx_single/gitignore
@@ -35,3 +35,9 @@ npm-debug.log
 
 # Temporary files, for example, from tests.
 /tmp
+
+<%= if @adapter_app == :ecto_sqlite3 do %>
+# Database files
+*.db
+*.db-*
+<% end %>
diff --git a/installer/templates/phx_umbrella/gitignore b/installer/templates/phx_umbrella/gitignore
index ffa1e1923..f73cfef80 100644
--- a/installer/templates/phx_umbrella/gitignore
+++ b/installer/templates/phx_umbrella/gitignore
@@ -21,3 +21,9 @@ erl_crash.dump
 
 # Temporary files, for example, from tests.
 /tmp
+
+<%= if @adapter_app == :ecto_sqlite3 do %>
+# Database files
+*.db
+*.db-*
+<% end %>
diff --git a/lib/mix/tasks/phx.gen.auth/migration.ex b/lib/mix/tasks/phx.gen.auth/migration.ex
index 53b414614..55e7adfd5 100644
--- a/lib/mix/tasks/phx.gen.auth/migration.ex
+++ b/lib/mix/tasks/phx.gen.auth/migration.ex
@@ -24,6 +24,7 @@ defmodule Mix.Tasks.Phx.Gen.Auth.Migration do
   end
 
   defp column_definition(:email, Ecto.Adapters.Postgres), do: "add :email, :citext, null: false"
+  defp column_definition(:email, Ecto.Adapters.SQLite3), do: "add :email, :string, null: false, collate: :nocase"
   defp column_definition(:email, _), do: "add :email, :string, null: false, size: 160"
 
   defp column_definition(:token, Ecto.Adapters.Postgres), do: "add :token, :binary, null: false"


Test patch:
diff --git a/installer/test/phx_new_test.exs b/installer/test/phx_new_test.exs
index 0f086746c..e3439f61a 100644
--- a/installer/test/phx_new_test.exs
+++ b/installer/test/phx_new_test.exs
@@ -549,6 +549,26 @@ defmodule Mix.Tasks.Phx.NewTest do
     end
   end
 
+  test "new with sqlite3 adapter" do
+    in_tmp "new with sqlite3 adapter", fn ->
+      project_path = Path.join(File.cwd!(), "custom_path")
+      Mix.Tasks.Phx.New.run([project_path, "--database", "sqlite3"])
+
+      assert_file "custom_path/mix.exs", ":ecto_sqlite3"
+      assert_file "custom_path/config/dev.exs", [~r/database: .*_dev.db/]
+      assert_file "custom_path/config/test.exs", [~r/database: .*_test.db/]
+      assert_file "custom_path/config/runtime.exs", [~r/database: database_path/]
+      assert_file "custom_path/lib/custom_path/repo.ex", "Ecto.Adapters.SQLite3"
+
+      assert_file "custom_path/test/support/conn_case.ex", "Ecto.Adapters.SQL.Sandbox.start_owner"
+      assert_file "custom_path/test/support/channel_case.ex", "Ecto.Adapters.SQL.Sandbox.start_owner"
+      assert_file "custom_path/test/support/data_case.ex", "Ecto.Adapters.SQL.Sandbox.start_owner"
+
+      assert_file "custom_path/.gitignore", "*.db"
+      assert_file "custom_path/.gitignore", "*.db-*"
+    end
+  end
+
   test "new with mssql adapter" do
     in_tmp "new with mssql adapter", fn ->
       project_path = Path.join(File.cwd!(), "custom_path")
diff --git a/installer/test/phx_new_umbrella_test.exs b/installer/test/phx_new_umbrella_test.exs
index db5a7a62d..3f41a7364 100644
--- a/installer/test/phx_new_umbrella_test.exs
+++ b/installer/test/phx_new_umbrella_test.exs
@@ -519,6 +519,27 @@ defmodule Mix.Tasks.Phx.New.UmbrellaTest do
     end
   end
 
+  test "new with sqlite3 adapter" do
+    in_tmp "new with sqlite3 adapter", fn ->
+      app = "custom_path"
+      project_path = Path.join(File.cwd!(), app)
+      Mix.Tasks.Phx.New.run([project_path, "--umbrella", "--database", "sqlite3"])
+
+      assert_file app_path(app, "mix.exs"), ":ecto_sqlite3"
+      assert_file app_path(app, "lib/custom_path/repo.ex"), "Ecto.Adapters.SQLite3"
+
+      assert_file root_path(app, "config/dev.exs"), [~r/database: .*_dev.db/]
+      assert_file root_path(app, "config/test.exs"), [~r/database: .*_test.db/]
+      assert_file root_path(app, "config/runtime.exs"), [~r/database: database_path/]
+
+      assert_file web_path(app, "test/support/conn_case.ex"), "Ecto.Adapters.SQL.Sandbox.start_owner"
+      assert_file web_path(app, "test/support/channel_case.ex"), "Ecto.Adapters.SQL.Sandbox.start_owner"
+
+      assert_file root_path(app, ".gitignore"), "*.db"
+      assert_file root_path(app, ".gitignore"), "*.db-*"
+    end
+  end
+
   test "new with mssql adapter" do
     in_tmp "new with mssql adapter", fn ->
       app = "custom_path"
diff --git a/integration_test/README.md b/integration_test/README.md
index 798167cdd..38e04b73f 100644
--- a/integration_test/README.md
+++ b/integration_test/README.md
@@ -17,6 +17,7 @@ To run the test suite with tests that test a specific database, run:
     $ mix test --include database:postgresql
     $ mix test --include database:mysql
     $ mix test --include database:mssql
+    $ mix test --include database:sqlite3
 
 For convenience, there is also a `docker-compose.yml` file that allows for starting up all of the supported databases locally.
 
diff --git a/integration_test/mix.exs b/integration_test/mix.exs
index 09134b8d4..60565ad29 100644
--- a/integration_test/mix.exs
+++ b/integration_test/mix.exs
@@ -33,6 +33,7 @@ defmodule Phoenix.Integration.MixProject do
       {:postgrex, ">= 0.0.0"},
       {:myxql, ">= 0.0.0"},
       {:tds, ">= 0.0.0"},
+      {:ecto_sqlite3, ">= 0.0.0"},
       {:phoenix_live_view, "~> 0.15.0"},
       {:floki, ">= 0.30.0"},
       {:phoenix_html, "~> 2.11"},
diff --git a/integration_test/mix.lock b/integration_test/mix.lock
index c06488281..76ea556dd 100644
--- a/integration_test/mix.lock
+++ b/integration_test/mix.lock
@@ -10,7 +10,9 @@
   "decimal": {:hex, :decimal, "1.9.0", "83e8daf59631d632b171faabafb4a9f4242c514b0a06ba3df493951c08f64d07", [:mix], [], "hexpm", "b1f2343568eed6928f3e751cf2dffde95bfaa19dd95d09e8a9ea92ccfd6f7d85"},
   "ecto": {:hex, :ecto, "3.5.8", "8ebf12be6016cb99313348ba7bb4612f4114b9a506d6da79a2adc7ef449340bc", [:mix], [{:decimal, "~> 1.6 or ~> 2.0", [hex: :decimal, repo: "hexpm", optional: false]}, {:jason, "~> 1.0", [hex: :jason, repo: "hexpm", optional: true]}, {:telemetry, "~> 0.4", [hex: :telemetry, repo: "hexpm", optional: false]}], "hexpm", "ea0be182ea8922eb7742e3ae8e71b67ee00ae177de1bf76210299a5f16ba4c77"},
   "ecto_sql": {:hex, :ecto_sql, "3.5.4", "a9e292c40bd79fff88885f95f1ecd7b2516e09aa99c7dd0201aa84c54d2358e4", [:mix], [{:db_connection, "~> 2.2", [hex: :db_connection, repo: "hexpm", optional: false]}, {:ecto, "~> 3.5.0", [hex: :ecto, repo: "hexpm", optional: false]}, {:myxql, "~> 0.3.0 or ~> 0.4.0", [hex: :myxql, repo: "hexpm", optional: true]}, {:postgrex, "~> 0.15.0 or ~> 1.0", [hex: :postgrex, repo: "hexpm", optional: true]}, {:tds, "~> 2.1.1", [hex: :tds, repo: "hexpm", optional: true]}, {:telemetry, "~> 0.4.0", [hex: :telemetry, repo: "hexpm", optional: false]}], "hexpm", "1fff1a28a898d7bbef263f1f3ea425b04ba9f33816d843238c84eff883347343"},
+  "ecto_sqlite3": {:hex, :ecto_sqlite3, "0.5.3", "af1fc6b89a7b4eb7ab83469e5c4b1788e05023c17de1e1c0d0622feb89212e3e", [:mix], [{:decimal, "~> 1.6 or ~> 2.0", [hex: :decimal, repo: "hexpm", optional: false]}, {:ecto, "~> 3.5", [hex: :ecto, repo: "hexpm", optional: false]}, {:ecto_sql, "~> 3.5", [hex: :ecto_sql, repo: "hexpm", optional: false]}, {:exqlite, "~> 0.5", [hex: :exqlite, repo: "hexpm", optional: false]}], "hexpm", "02c99f693062938a0afe9696b032da616d00272eb56b8c0687c435f40948590c"},
   "elixir_make": {:hex, :elixir_make, "0.6.2", "7dffacd77dec4c37b39af867cedaabb0b59f6a871f89722c25b28fcd4bd70530", [:mix], [], "hexpm", "03e49eadda22526a7e5279d53321d1cced6552f344ba4e03e619063de75348d9"},
+  "exqlite": {:hex, :exqlite, "0.5.1", "fae7e66718fd33318904e826a42769e0ddbe7afc41dfcbff3332cbf3e728daa4", [:make, :mix], [{:db_connection, "~> 2.1", [hex: :db_connection, repo: "hexpm", optional: false]}, {:elixir_make, "~> 0.6", [hex: :elixir_make, repo: "hexpm", optional: false]}], "hexpm", "e04a66c3a51e3e9d1f00189c90591c4902ae32de557b98089d1846e69e44df67"},
   "file_system": {:hex, :file_system, "0.2.10", "fb082005a9cd1711c05b5248710f8826b02d7d1784e7c3451f9c1231d4fc162d", [:mix], [], "hexpm", "41195edbfb562a593726eda3b3e8b103a309b733ad25f3d642ba49696bf715dc"},
   "floki": {:hex, :floki, "0.30.0", "22ebbe681a5d3777cdd830ca091b1b806d33c3449c26312eadca7f7be685c0c8", [:mix], [{:html_entities, "~> 0.5.0", [hex: :html_entities, repo: "hexpm", optional: false]}], "hexpm", "a9e128a4ca9bb71f11affa315b6768a9ad326d5996ff1e92acf1d7a01a10076a"},
   "gettext": {:hex, :gettext, "0.18.2", "7df3ea191bb56c0309c00a783334b288d08a879f53a7014341284635850a6e55", [:mix], [], "hexpm", "f9f537b13d4fdd30f3039d33cb80144c3aa1f8d9698e47d7bcbcc8df93b1f5c5"},
diff --git a/integration_test/test/code_generation/app_with_sqlite3_adapter.exs b/integration_test/test/code_generation/app_with_sqlite3_adapter.exs
new file mode 100644
index 000000000..2f94b1d59
--- /dev/null
+++ b/integration_test/test/code_generation/app_with_sqlite3_adapter.exs
@@ -0,0 +1,111 @@
+defmodule Phoenix.Integration.CodeGeneration.AppWithSQLite3AdapterTest do
+  use Phoenix.Integration.CodeGeneratorCase, async: true
+
+  describe "phx.gen.html" do
+    @tag database: :sqlite3
+    test "has a passing test suite" do
+      with_installer_tmp("app_with_defaults", fn tmp_dir ->
+        {app_root_path, _} =
+          generate_phoenix_app(tmp_dir, "default_sqlite3_app", ["--database", "sqlite3"])
+
+        mix_run!(~w(phx.gen.html Blog Post posts title body:string status:enum:unpublished:published:deleted), app_root_path)
+
+        modify_file(Path.join(app_root_path, "lib/default_sqlite3_app_web/router.ex"), fn file ->
+          inject_before_final_end(file, """
+
+            scope "/", DefaultMysqlAppWeb do
+              pipe_through [:browser]
+
+              resources "/posts", PostController
+            end
+          """)
+        end)
+
+        drop_test_database(app_root_path)
+        assert_tests_pass(app_root_path)
+      end)
+    end
+  end
+
+  describe "phx.gen.json" do
+    @tag database: :sqlite3
+    test "has a passing test suite" do
+      with_installer_tmp("app_with_defaults", fn tmp_dir ->
+        {app_root_path, _} =
+          generate_phoenix_app(tmp_dir, "default_sqlite3_app", ["--database", "sqlite3"])
+
+        mix_run!(~w(phx.gen.json Blog Post posts title body:string status:enum:unpublished:published:deleted), app_root_path)
+
+        modify_file(Path.join(app_root_path, "lib/default_sqlite3_app_web/router.ex"), fn file ->
+          inject_before_final_end(file, """
+
+            scope "/", DefaultMysqlAppWeb do
+              pipe_through [:api]
+
+              resources "/posts", PostController, except: [:new, :edit]
+            end
+          """)
+        end)
+
+        drop_test_database(app_root_path)
+        assert_tests_pass(app_root_path)
+      end)
+    end
+  end
+
+  describe "phx.gen.live" do
+    @tag database: :sqlite3
+    test "has a passing test suite" do
+      with_installer_tmp("app_with_defaults", fn tmp_dir ->
+        {app_root_path, _} =
+          generate_phoenix_app(tmp_dir, "default_sqlite3_app", ["--database", "sqlite3", "--live"])
+
+        mix_run!(~w(phx.gen.live Blog Post posts title body:string status:enum:unpublished:published:deleted), app_root_path)
+
+        modify_file(Path.join(app_root_path, "lib/default_sqlite3_app_web/router.ex"), fn file ->
+          inject_before_final_end(file, """
+
+            scope "/", DefaultMysqlAppWeb do
+              pipe_through [:browser]
+
+              live "/posts", PostLive.Index, :index
+              live "/posts/new", PostLive.Index, :new
+              live "/posts/:id/edit", PostLive.Index, :edit
+
+              live "/posts/:id", PostLive.Show, :show
+              live "/posts/:id/show/edit", PostLive.Show, :edit
+            end
+          """)
+        end)
+
+        drop_test_database(app_root_path)
+        assert_tests_pass(app_root_path)
+      end)
+    end
+  end
+
+  describe "phx.gen.auth + bcrypt" do
+    test "has no compilation or formatter warnings" do
+      with_installer_tmp("new with defaults", fn tmp_dir ->
+        {app_root_path, _} = generate_phoenix_app(tmp_dir, "phx_blog", ["--database", "sqlite3"])
+
+        mix_run!(~w(phx.gen.auth Accounts User users), app_root_path)
+
+        assert_no_compilation_warnings(app_root_path)
+        assert_passes_formatter_check(app_root_path)
+      end)
+    end
+
+    @tag database: :sqlite3
+    test "has a passing test suite" do
+      with_installer_tmp("app_with_defaults", fn tmp_dir ->
+        {app_root_path, _} = generate_phoenix_app(tmp_dir, "default_app", ["--database", "sqlite3"])
+
+        mix_run!(~w(phx.gen.auth Accounts User users), app_root_path)
+
+        drop_test_database(app_root_path)
+        assert_tests_pass(app_root_path)
+      end)
+    end
+  end
+end
diff --git a/test/mix/tasks/phx.gen.auth_test.exs b/test/mix/tasks/phx.gen.auth_test.exs
index 7f6d173d7..058f2c8c3 100644
--- a/test/mix/tasks/phx.gen.auth_test.exs
+++ b/test/mix/tasks/phx.gen.auth_test.exs
@@ -461,6 +461,45 @@ defmodule Mix.Tasks.Phx.Gen.AuthTest do
       end)
     end
 
+    test "when the database is sqlite3", config do
+      in_tmp_phx_project(config.test, fn ->
+        Gen.Auth.run(
+          ~w(Accounts User users),
+          [ecto_adapter: Ecto.Adapters.SQLite3, validate_dependencies?: false]
+        )
+
+        assert [migration] = Path.wildcard("priv/repo/migrations/*_create_users_auth_tables.exs")
+        assert_file migration, fn file ->
+          refute file =~ ~r/execute "CREATE EXTENSION IF NOT EXISTS citext", ""$/m
+          assert file =~ ~r/add :email, :string, null: false, collate: :nocase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_auth_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_confirmation_controller_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_registration_controller_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_reset_password_controller_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_session_controller_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+
+        assert_file "test/my_app_web/controllers/user_settings_controller_test.exs", fn file ->
+          assert file =~ ~r/use MyAppWeb\.ConnCase$/m
+        end
+      end)
+    end
+
     test "when the database is mssql", config do
       in_tmp_phx_project(config.test, fn ->
         Gen.Auth.run(