KinoDB SQL Server

Mix.install([
  {:kino_db, "~> 0.2"},
  {:tds, "~> 2.3"}
])

Connect to master database

opts = [
  hostname: "sql_server_for_livebook",
  port: 1433,
  username: "SA",
  password: System.fetch_env!("LB_MSSQL_SA_PASSWORD"),
  database: "master"
]

{:ok, conn} = Kino.start_child({Tds, opts})

Create database

result = Tds.query!(conn, "CREATE DATABASE sample_db", [])

result2 = Tds.query!(conn, "SELECT Name from sys.databases", [])

Connect to sample database

opts = [
  hostname: "sql_server_for_livebook",
  port: 1433,
  username: "SA",
  password: System.fetch_env!("LB_MSSQL_SA_PASSWORD"),
  database: "sample_db"
]

{:ok, conn2} = Kino.start_child({Tds, opts})

Execute Query

result3 =
  Tds.query!(
    conn2,
    """
    CREATE TABLE
      sample
    (
      id integer,
      name varchar(20)
    )
    """,
    []
  )

result4 =
  Tds.query!(
    conn2,
    """
    INSERT INTO
      sample
    (
      id,
      name
    )
    OUTPUT
      inserted.id
    VALUES
    (
      1,
      'John'
    )
    """,
    []
  )

result5 =
  Tds.query!(
    conn2,
    """
    SELECT
      *
    FROM
      sample
    """,
    []
  )