I was very impressed at how easy it is to crack open and do stuff with sqlite in Livebook v0.11; exsqlite gives a conn that everything else in Livebook is built around.
There are many sqlite databases out there, and utilities that can export Airtable to sqlite. I think being able to manipulate this data at the application level would open up many interesting uses (an Elixir Datasette alternative). What I don’t know where to start is bringing in Ecto to define schemas. The standard protocol involves setting up config / repos etc. Is this possible within just a livebook? Where should I start reading up on that?
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()
```
7 Likes
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.
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.
3 Likes
