Help us understand the problem. What is going on with this article?

F# Npgsql+Dapper でPostgreSQLを操作

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 を使う

Why do not you register as a user and use Qiita more conveniently?
  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
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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