How to configure phoenix for sqlite

staymetal · January 24, 2018, 12:12am

Can one guide me step by step in configuring phoenix with sqlite ?

P.S. im on elixir 1.6 and latest phoenix

Thanks a ton !!

AstonJ · January 24, 2018, 12:34am

You might find this thread of interest:

And this post:

And I have to ask, why not just use Postgres?

sergio · January 24, 2018, 1:23am

Some of the replies in that thread are a trainwreck.

This guy comes in with a clear question and some people just go “use postgresql”. Thankfully Chris came in and replied with some actual information.

@staymetal check out this link: http://twentyeighttwelve.com/creating-a-phoenix-framework-application-with-sqlite/

Seems like a simple adapter switch.

AstonJ · January 24, 2018, 1:38am

If the replies are helpful (and aren’t too off-topic) then that can definitely be a good thing

Sometimes I put myself in the shoes of the person asking the question, and ask myself, if it were me, what would I wish I was told or made aware of? Postgres is excellent and I can’t see any good reason to use Sqlite over it in Phoenix - so why not help the OP by sharing this info?

I think in topics like this it can not only advance the OP, but be generally progressive as well - just like we’ve seen with the shift from MySQL to Postgres, PHP to Rails, and now, people recommending Phoenix in cases where it makes sense to do so as well

gon782 · January 24, 2018, 6:34am

While I agree that Postgres works fine and is a good enough solution for most people to start with, SQLite is much simpler than all of the “real” databases. It works on a library level, requires no setup (meaning it doesn’t require any privileges on the box you’re working on) whatsoever and has bindings in every language out there. You get trivial database copying out of the box and all in all it’s a much better fit for small projects.

staymetal · January 24, 2018, 6:40am

Thank you for your replies guys…truly elixir has an awesome community !!

staymetal · January 24, 2018, 7:26am

Im learning elixir on archlinux running on android.Postgresql and mariadb are segfaulting on it so im left with sqlite !

gon782 · January 24, 2018, 7:38am

Wow, that is an odd environment. Does that usually work well (apart from apparently with DBs)?

staymetal · January 24, 2018, 8:23am

Yes sir !!..its great !!!

Working : full linux arch environment
Elixir
Phoenix
Java and JVM
Erlang
Gcc,g++
asm
Python
Ruby
sqlite
Haskell
Metasploit
Hydra
Backdoor factory
And a ton of penetration tools

Not working :

             Mariadb
             Postgresql
             Nodejs and Npm

kokolegorille · January 24, 2018, 8:49am

Oh… funny tools. I wonder if we could take advantage of Elixir concurrency with Hydra and Metasploit.

AstonJ · January 24, 2018, 3:28pm

I would still not recommend it is used over Postgres with Phoenix - it’s better to use the same DB in development as production if at all possible. Postgres is not that much more difficult to set-up either Imo (least not on Mac and Linux - not sure about Windows).

Having said that, the OP’s use-case is different - they don’t have any choice

That’s interesting! What kind of hardware is that on?

staymetal · January 25, 2018, 12:18pm

Nexus 5 - armv7

James_E · November 15, 2024, 3:30pm

That solution has bit rot; the packages it recommends are no longer maintained and pin some shared dependencies with Phoenix which have since undergone breaking updates, so you’ll get an error like this:

Because phoenix_live_dashboard >= 0.5.0 depends on ecto ~> 3.6.2 or ~> 3.7 and sqlite_ecto >= 1.0.0 and < 1.1.0 depends on ecto ~> 1.0, phoenix_live_dashboard >= 0.5.0 is incompatible with sqlite_ecto >= 1.0.0 and < 1.1.0.
And because your app depends on phoenix_live_dashboard ~> 0.8.3, sqlite_ecto >= 1.0.0 and < 1.1.0 is forbidden.
So, because your app depends on sqlite_ecto ~> 1.0.0, version solving failed.

Here’s the solution for OP’s request in 2024, for posterity / because this thread is still floating to the top of Google:

# in mix.exs
defp deps do
    [
      …
      {:ecto_sqlite3, "~> 0.17.4"},
      …
    ]
  end

# in config/dev.exs
config :myApp, MyApp.Repo,
  adapter: Ecto.Adapters.SQLite3,
  database: "./dev.db",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true

# in config/test.exs
config :myApp, MyApp.Repo,
  adapter: Ecto.Adapters.SQLite3,
  database: "./test#{System.get_env("MIX_TEST_PARTITION")}.db",
  pool: Ecto.Adapters.SQL.Sandbox,
  pool_size: System.schedulers_online() * 2

# in lib/myApp/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :myApp,
    adapter: Ecto.Adapters.SQLite3
end

LostKobrakai · November 15, 2024, 4:30pm

Phoenix can generate that with mix phx.new --database sqlite3. No need to manually do that.

ariandanim · November 16, 2024, 11:58pm

I facing some difficult in production mode when using sqlite3, heres my step in vps production, still need help does it all righ the url database sqlite3 or not

export SECRET_KEY_BASE=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
export DATABASE_URL=ecto://var/www/myweb.com/myweb/myapp_prod.db
mix deps.get --only prod
MIX_ENV=prod mix compile
MIX_ENV=prod mix assets.deploy
PORT=4000 MIX_ENV=prod mix phx.server

At the end when hit enter after typing the number 6 command, my production code showing error like this :

23:49:12.502 [notice] Application mywebapp exited: Myweb.Application.start(:normal, []) returned an error: shutdown: failed to start child: Myweb.Repo
    ** (EXIT) an exception was raised:
        ** (Ecto.InvalidURLError) invalid URL ecto://var/www/myweb.com/mywebapp/mywebdb_prod.db, path should be a database name. The parsed URL is: %URI{scheme: "ecto", authority: "var", userinfo: nil, host: "var", port: nil, path: "/www/myweb.com/mywebapp/mywebdb_prod.db", query: nil, fragment: nil}
            (ecto 3.12.4) lib/ecto/repo/supervisor.ex:111: Ecto.Repo.Supervisor.parse_url/1
            (ecto 3.12.4) lib/ecto/repo/supervisor.ex:29: Ecto.Repo.Supervisor.init_config/4
            (ecto 3.12.4) lib/ecto/repo/supervisor.ex:190: Ecto.Repo.Supervisor.init/1
            (stdlib 6.1.2) supervisor.erl:869: :supervisor.init/1
            (stdlib 6.1.2) gen_server.erl:2229: :gen_server.init_it/2
            (stdlib 6.1.2) gen_server.erl:2184: :gen_server.init_it/6
            (stdlib 6.1.2) proc_lib.erl:329: :proc_lib.init_p_do_apply/3
** (exit) :terminating
    (kernel 10.1.1) application_controller.erl:511: :application_controller.call/2
    (kernel 10.1.1) application.erl:367: :application."-ensure_all_started/3-lc$^0/1-0-"/1
    (kernel 10.1.1) application.erl:367: :application.ensure_all_started/3
    (mix 1.17.3) lib/mix/tasks/app.start.ex:72: Mix.Tasks.App.Start.start/3
    (mix 1.17.3) lib/mix/task.ex:495: anonymous fn/3 in Mix.Task.run_task/5
    (mix 1.17.3) lib/mix/tasks/run.ex:129: Mix.Tasks.Run.run/5
    (mix 1.17.3) lib/mix/tasks/run.ex:85: Mix.Tasks.Run.run/1
    (mix 1.17.3) lib/mix/task.ex:495: anonymous fn/3 in Mix.Task.run_task/5

kokolegorille · November 17, 2024, 1:11am

An easy way to check the config would be to create a side project with --database sqlite3.

You would see DATABASE_URL is not used, but DATABASE_PATH

You would find this code…

  database_path =
    System.get_env("DATABASE_PATH") ||
      raise """
      environment variable DATABASE_PATH is missing.
      For example: /etc/kokolite/kokolite.db
      """

Which means You need to adapt your config/runtime.exs to sqlite3

No, it is really not the way to start a release… You need to set all the required env variables and use the release script.

Once You know how to start the release, You’ll need to write a service script for systemd, if You are on Linux.

It’s hard to deploy a release, but only the first time…

Then, You’ll learn that deploying nodes in a cluster is even much harder