Category: PostgreSQL

まずテーブル作成
$ sudo -u postgres psql
$ CREATE TABLE “todo” (
id UUID primary key,
description varchar(100) not null,
deadline_at timestamp not null
);

postgres=# \dt
List of relations
Schema | Name | Type | Owner
——–+——–+——-+———-
public | todo | table | postgres

chrono = "0.4.31"
sqlx = { version = "0.7.2", features = ["runtime-tokio-native-tls", "chrono", "uuid"]}
uuid = { version = "1.6.1", fatures = ["v4", "serde"] }

use axum::{Router, extract::State, response::Html, routing::get};
use tera::{Context, Tera};
use serde::{Serialize, Deserialize};
use sqlx::{FromRow, PgPool};
use uuid::Uuid;

//

#[derive(Clone)]
struct ServiceState {
    tera: Tera,
    pool: PgPool,
}

//

#[tokio::main]
async fn main() {

    let pool = PgPool::connect("postgres://postgres:password@localhost:5432/postgres").await.unwrap();

    let tera = match Tera::new("templates/**/*.html"){
        Ok(t) => t,
        Err(e) => {
            println!("Parsing error(s): {}", e);
            ::std::process::exit(1);
        }
    };

    
    let app = Router::new()
        .route("/", get(index))
        .with_state(ServiceState {tera, pool});
    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

http://192.168.33.10:3000/create_todo

### 入力結果の表示
templates/todos.html

<a href="/create_todo">Todoを作成</a>
<table>
    <thread>
        <tr>
            <th scope="col">description</th>
            <th scope="col">deadline at</th>
        </tr>
    </thread>
    <tbody>
        {% for todo in todos %}
        <tr id="{{ todo.id }}">
            <td>{{ todo.description }}</td>
            <td>{{ todo.deadline_at }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

use axum::{Router, extract::State, response::{Redirect, Html}, Form, routing::get};
use chrono::NaiveDateTime;
use tera::{Context, Tera};
use serde::{Serialize, Deserialize};
use sqlx::{FromRow, PgPool};
use uuid::Uuid;

pub fn deserialize_date<'de, D: serde::Deserializer<'de>>(
    deserializer: D,
) -> Result<NaiveDateTime, D::Error> {
    let s = String::deserialize(deserializer)?;
    NaiveDateTime::parse_from_str(&s, "%Y-%m-%dT%H:%M")
        .map_err(serde::de::Error::custom)
}

#[derive(Serialize)]
struct Index {
    name: String
}

#[derive(Clone)]
struct ServiceState {
    tera: Tera,
    pool: PgPool,
}

#[derive(Debug, Serialize, FromRow)]
struct Todo {
    id: Uuid,
    description: String,
    deadline_at: NaiveDateTime,
}

#[derive(Debug, Deserialize)]
struct CreateTodo {
    description: String,
    #[serde(deserialize_with = "deserialize_date")]
    deadline_at: NaiveDateTime,
}

async fn index(State(state): State<ServiceState>) -> Html<String> {
    let index = Index { name: String::from("test") };
    let page = state.tera.render("index.html", &Context::from_serialize(&index).unwrap()).unwrap();
    Html(page.to_owned())
}

async fn get_create_todo(State(state): State<ServiceState>) -> Html<String> {
    let page = state.tera.render("create_todo.html", &Context::new()).unwrap();
    Html(page.to_owned())
}

async fn post_create_todo(
    State(state): State<ServiceState>,
    Form(todo): Form<CreateTodo>,
) -> Redirect {
    let todo = Todo {
        id: Uuid::new_v4(),
        description: todo.description,
        deadline_at: todo.deadline_at,
    };

    sqlx::query("INSERT INTO todo VALUES ($1, $2, $3);")
        .bind(todo.id)
        .bind(todo.description)
        .bind(todo.deadline_at)
        .execute(&state.pool)
        .await
        .expect("todoの取得に失敗しました");

    Redirect::to("/todos")
}

async fn get_todos(
    State(state): State<ServiceState>,
) -> Html<String> {
    let todos = sqlx::query_as::<_, Todo>("SELECT * FROM todo")
        .fetch_all(&state.pool)
        .await
        .expect("todoの取得に失敗しました");
    let mut context = Context::new();
    context.insert("todos", &todos);

    let page = state.tera.render("todos.html", &context).expect("todoの描画に失敗しました");
    Html(page)
}

#[tokio::main]
async fn main() {

    let pool = PgPool::connect("postgres://postgres:password@localhost:5432/postgres").await.unwrap();

    let tera = match Tera::new("templates/**/*.html"){
        Ok(t) => t,
        Err(e) => {
            println!("Parsing error(s): {}", e);
            ::std::process::exit(1);
        }
    };

    
    let app = Router::new()
        .route("/", get(index))
        .route("/todos", get(get_todos))
        .route("/create_todo", get(get_create_todo).post(post_create_todo))
        .with_state(ServiceState {tera, pool});
    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

http://192.168.33.10:3000/todos
Todoを作成
description deadline at
test 2025-01-01T15:54:00
aaa 2025-01-01T15:57:00

なんだこれは…

$ cargo new psql

crateはpostgresを使用する
https://docs.rs/postgres/latest/postgres/#example

[dependencies]
postgres = "0.19.9"

use postgres::{Client, NoTls};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=localhost user=postgres password=password", NoTls)?;

    client.batch_execute("
        CREATE TABLE person (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            data BYTEA
        )
    ")?;

    let name = "Ferris";
    let data = None::<&[u8]>;
    client.execute(
        "INSERT INTO person (name, data) VALUES ($1, $2)",
        &[&name, &data],
    )?;

    for row in client.query("SELECT id, name, data From person", &[])? {
        let id: i32 = row.get(0);
        let name: &str = row.get(1);
        let data: Option<&[u8]> = row.get(2);

        println!("found person: {} {} {:?}", id, name, data);
    }
    Ok(())
}

$ cargo run
Compiling psql v0.1.0 (/home/vagrant/dev/rust/psql)
Finished `dev` profile [unoptimized + debuginfo] target(s) in 2.54s
Running `target/debug/psql`
found person: 1 Ferris None

おおおおおおおおおお
create tableはrustではなく、setup.shでpythonで実行して、rustではinsertとselectのみを実行するようにしたいですね。

setup.sh

pip install psycopg2-binary
yes | sudo apt install libpq-dev
pip install psycopg2

python3 init.py

init.py

import psycopg2

connection = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='password'
)

cur = connection.cursor()

cur.execute('CREATE TABLE person ( \
            id SERIAL PRIMARY KEY, \
            name TEXT NOT NULL, \
            data BYTEA \
        );')

connection.commit()
cur.close()
connection.close()

$ cargo run
Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.17s
Running `target/debug/psql`
found person: 1 Ferris None

転送元ノードのIPを取得してDBに入れようと思ったけど、ちょっと難しいな…