How to establish Ecto Repo from Exsqlite conn in Livebook?

Hey @jkwchui! If you want to define Ecto schemas in the notebook itself, here’s an example that may give some ideas:

# Ecto

```elixir
Mix.install([
  {:ecto, "~> 3.10"},
  {:ecto_sql, "~> 3.10"},
  {:postgrex, "~> 0.17.3"},
  {:kino, "~> 0.11.0"},
  {:kino_db, "~> 0.2.4"}
])
```

## Setup

```
docker run --rm -it -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:13.2
```

## Repo

```elixir
defmodule Repo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgres
end
```

```elixir
Kino.start_child({Repo, url: "postgres://postgres:postgres@localhost/postgres"})
```

## Queries

```elixir
Ecto.Adapters.SQL.query!(Repo, "create table users (id int)")
```

```elixir
Ecto.Adapters.SQL.query!(Repo, "insert into users values (1)")
```

```elixir
Ecto.Adapters.SQL.query!(Repo, "select * from users")
```

## Ecto migration

```elixir
defmodule Migrations.AddWeatherTable do
  use Ecto.Migration

  def up do
    create table("weather") do
      add(:city, :string, size: 40)
      add(:temp_lo, :integer)
      add(:temp_hi, :integer)
      add(:prcp, :float)

      timestamps()
    end
  end

  def down do
    drop(table("weather"))
  end
end
```

```elixir
Ecto.Migrator.up(Repo, 1, Migrations.AddWeatherTable)
```

## Schema

```elixir
defmodule Weather do
  use Ecto.Schema

  schema "weather" do
    field(:city, :string)
    field(:temp_lo, :integer)
    field(:temp_hi, :integer)
    field(:prcp, :float, default: 0.0)

    timestamps()
  end
end
```

```elixir
weather = %Weather{temp_lo: 0, temp_hi: 23}
Repo.insert!(weather)
```

```elixir
Repo.all(Weather)
|> Kino.DataTable.new()
```

Thank you @jonatanklosko. That sequence of Repo definition was all I needed! Taking this forward, Ash’s new AshSqlite datalayer works very well, and by defining actions and code_interfaces like

actions do
    defaults [:create, :read, :update, :destroy]

    read :top do
      prepare build(limit: 10, sort: [{:views, :desc}])
    end
  end

It becomes possible to work with the data with functions like MyData.Entries.top!(), then bring that into Explorer data frames, then use the smart cells for interactive explorations.

image1200×1296 88.9 KB

My early hunch is that this grows smoothly from exploration in Livebook, to standalone Mix project, and then drop-in to an existing Phoenix app.