Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
4
Help us understand the problem. What is going on with this article?
@dyoshikawa

F# Npgsql+Dapper でPostgreSQLを操作

More than 1 year has passed since last update.

F#+Npgsql+Dapperの素振り。
Npgsql.FsharpというF#向けのラッパーもあるが今回は使わない。

環境

  • DotNetCore 2.2.0
  • FSharp.Core 4.6.2
  • Npgsql 4.1.0-preview2
  • Dapper 2.0.4

準備

docker-compose.yml
version: '3'
services:
  db:
    image: postgres:alpine
    environment:
      POSTGRES_PASSWORD: secret
    ports:
      - 5432:5432

こんな感じでpostgresコンテナを立ち上げておく。

コード

// Learn more about F# at http://fsharp.org

open System
open Npgsql
open Dapper

let createConnection () =
    let dbHost = "localhost"
    let dbPort = 5432
    let dbDatabase = "postgres"
    let dbUser = "postgres"
    let dbPassword = "secret"
    let builder =
        new NpgsqlConnectionStringBuilder(Host = dbHost, Port = dbPort,
                                          Username = dbUser,
                                          Password = dbPassword,
                                          Database = dbDatabase,
                                          SslMode = SslMode.Disable)
    new NpgsqlConnection(builder.ToString())

type User =
    { id : int
      name : string }

[<EntryPoint>]
let main argv =
    let conn = createConnection ()
    conn.Open()

    // テーブル作成
    conn.Execute("""
        CREATE TABLE users (
            id   INTEGER NOT NULL PRIMARY KEY,
            name VARCHAR NOT NULL
        )
    """) |> ignore

    // レコード挿入
    let insertCmd = new NpgsqlCommand("""
        INSERT INTO users(id, name)
        VALUES (@id, @name)
    """, conn)
    insertCmd.Parameters.AddWithValue("id", 1) |> ignore
    insertCmd.Parameters.AddWithValue("name", "John Doe") |> ignore
    insertCmd.ExecuteNonQuery() |> ignore

    // レコード取得
    let users = conn.Query<User>("SELECT id, name FROM users").AsList()

    // テーブル削除
    conn.Execute("DROP TABLE users") |> ignore

    conn.Close()
    0 // return an integer exit code

接続設定

createConnection 関数の内容通り。
今回は各値ベタ書きしているが実際には環境変数から取ることになるだろう。

SELECT

SELECT文は conn.Query<'T> を使う。
Dapperの効用により渡した型のリストで返ってくる。

CREATE TABLE / DROP TABLE / INSERT

それ以外の操作は

  1. conn.Execute("SQL")
  2. let cmd = new NpgsqlCommand("SQL", conn)cmd.ExecuteNonQuery()

のいずれかで行う。
2のやり方は冗長なので基本的に1を使えば良さそう。

パラメータ注入

conn.Execute("SQL")の場合

第2引数にレコードを渡す。
4.6から使える匿名レコードを使うのが楽そう。

let sql = "INSERT INTO users VALUES(@id, @name)"
cmd.Excute(sql, {| id = 1; name = "John Doe" |})

new NpgsqlCommand("SQL", conn)の場合

let insertCmd = new NpgsqlCommand("""
    INSERT INTO users(id, name)
    VALUES (@id, @name)
""", conn)
insertCmd.Parameters.AddWithValue("id", 1) |> ignore
insertCmd.Parameters.AddWithValue("name", "John Doe") |> ignore
insertCmd.ExecuteNonQuery() |> ignore

やはり冗長。

参考

Getting Started | Npgsql Documentation
PostgreSQL で Dapper を使う

4
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
dyoshikawa
広島でクジラTシャツを着て仕事しています

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
4
Help us understand the problem. What is going on with this article?