How to import multiple SQLite databases into a Postgres database using Ecto and Phoenix

Phoenix Forum Questions / Help
, ,
1

Hey everyone,

I am designing an app with Phoenix and am running into a mental block with how to architect importing and exporting user data. I need to allow users to import and export their data from a competitor app by uploading a SQLite file. I would like to validate and process this file for each user and then add their data to my app’s main Postgres database.

The Postgres database is easy–I will just design its schema with Ecto with the docs as my guide. I also have been able to manually add the SQLite schema and that works fine with Ecto if I hardcode the db path.

I have run into two issues:

  1. the standard way in the docs for Ecto and SQLite is to hard code the database path. I need to use a dynamic path at run time after the user uploads their SQLite database via the web app.

  2. As far as I can tell Phoenix is set up to work with one database. How can I use the SQLite AND Postgres in the same app?

Hopefully this makes sense.

Thanks! :smiley:

Jack

2

Using two repos in the same application is pretty easy, particularly if one of them is read-only so you don’t have to worry about migrations. In your config.exs, search for ecto_repos: - you will see it points to a list rather than a single value, so you can add extra repos in there. Your main application.ex reads this to figure out which repos to start on system startup.

The bigger problem will be dynamically connecting to different SQLite files.

To be honest, I’d bypass Ecto on the SQLite site and use Exqlite directly. The read api using Exqlite.Basic is pretty straightforward and likely all you need. It goes along the lines of:

  1. Get a connection to the file:
{:ok, db} = Exqlite.Connection.connect(database: <path to database file>)
  1. Execute your fave select queries:
    {:ok, rows, cols} =
      db
      |> Exqlite.Basic.exec("SELECT stuff, more_stuff FROM table_of_stuff WHERE owner=?", ["jack"])
      |> Exqlite.Basic.rows()
  1. Close the connection when you’re finished (it probably falls out of scope and cleans up itself, but better safe than sorry)
Exqlite.Basic.close(db)
  1. Do what you want with the data in rows 
rows |> Enum.map(...mangle_data_to_fit_new...) |> Enum.each(...insert into new repo...)

Writing is pretty similar. You may want to set a few different parameters on the initial connection to ensure the SQLite file is creating with the right settings, then you will use CREATE & INSERT statements to create the destination tables and add data.

The only drama I have had with this approach is execution (i.e. Exqlite.Basic.exec(...)) failing silently sometimes - e.g. if you have 5 parameter placeholders and only supply 4 parameters. I haven’t dug into it, but make sure you build in some testing!

4 Likes
3

Thanks so much for your help! People weren’t exaggerating about the Elixir community being super welcoming.

Makes sense about bypassing Ecto–that was my plan B, unless someone suggested it as a working okay for a plan A lol.

3 Likes
4

I did something similar I needed to export a part of our postgres database to a sqlite a year ago.

The dynamic repo and the proper cleanup took me some time to get right but it work well.

I got a dynamic repo to start a sqlite and stop and rm the sqlite database after I upload it on s3

defmodule YourApp.DynamicRepo do
  use Ecto.Repo, otp_app: :evoluflor_api, adapter: Ecto.Adapters.SQLite3

  require Logger

  @doc """
  Create a dynamic sqlite repo.
  Check https://hexdocs.pm/ecto/replicas-and-dynamic-repositories.html for more details
  """
  def open_database(db_path) do
    __MODULE__.start_link(
      name: nil,
      otp_app: :your_app,
      adapter: Ecto.Adapters.SQLite3,
      database: db_path,
      # important we can copy the app at any time otherwise some data can be missing
      journal_mode: :off
    )
  end

  def cleanup_database(%{repo_pid: repo_pid, db_path: db_path}) do
    :ok = Supervisor.stop(repo_pid)
    :ok = cleanup_database(db_path)
    Logger.debug("✔️ cleanup successfully stopped and cleaned up the sqlite database")
    :ok
  end

  def cleanup_database(db_path) do
    File.rm!(db_path)
  end
end

In my case the sqlite database I open already have the tables created
I have a lot of code to map the data from postgres to sqlite change the boolean to 1 or 2, handle the timestamp, the jsonb …
the file look like that
Then I insert the list result in the sqlite database

{:ok, repo_pid} = DynamicRepo.open_database(db_path)
# Set the repo_pid for the current process
DynamicRepo.put_dynamic_repo(repo_pid)
# Query and Map data from postgres to sqlite
data = [[id: 1, count: 1, type: "ticket"]]
# Insert the data in my sqlite database
DynamicRepo.insert_all(table_name, data, on_conflict: :raise)
# I upload the sqlite database to s3 and use my cleanup function that stop the process and delete the local database
DynamicRepo.cleanup_database(%{repo_pid: repo_pid, db_path: db_path})

The only problem we got is when the postgres database got new migration and our mapper is not updated.

3 Likes