深入淺出 - 資料庫基礎概念

Nolan.Hsu

發佈於WEB全端開發

2024/04/24閱讀時間約 8 分鐘

軟體系統的發展歷程大多相似，首重解決基本需求、提供操作介面，進而提升安全性、擴充功能、優化操作。

資料庫的初衷

管理資料時，我們通常面對幾個基本需求:

資料格式：需要定義資料的結構和格式，通常以表格(table)和欄位(column)的形式呈現。

資料間需要相互參考：資料之間可能需要相互參考，可以通過關聯式或非關聯式的方式實現。

資料操作：對資料進行新增、修改和刪除等操作是必不可少的。

查詢資料：需要能夠根據特定條件查詢資料，以獲取所需的信息。

語意介面: 請資料庫做事 - SQL語法

為了解決基本需求，我們必須有一組跟資料庫的對話方式，而最常見的對話方式就是透過SQL語法。

只要對資料庫發送有效的SQL語法，就會得到預期的操作結果，以搜尋名稱為'admin'的使用者為例:

SELECT * FROM users WHERE name = 'admin';

以上語法對於任一SQL資料庫(PostgreSQL、MySQL、Oracle、Microsoft SQL Server... )皆有效。

通訊介面: 與資料庫對話 - TCP/IP

有了對話語法後，下一個問題是雙方能透過什麼方式交換SQL語法，這裡則是使用經典的TCP/IP client-server架構。

所有後端教學，一定會有安裝資料庫伺服器程式在自己電腦上的環節。

資料庫伺服器

這個動作其實涉及許多網路服務的概念，也是初學者經常搞不懂的部分。網路服務在程式碼的呈現通常是一組ip:port，例如: 200.100.0.1:80，ip讓使用者能夠連線到主機，而port讓使用者能連線到服務程式。

網路上有多個服務主機，透過ip才能指定要連線到哪一台主機

主機可以運行多個服務程式，透過port才能指定要連線到哪一個服務程式

運行在服務主機上的服務程式就是大家熟知的server-side program，通常會透過一個無限while迴圈持續監聽port來接收訊息、回應執行結果。

將資料庫程式設計成TCP/IP client-server架構有許多優勢:

可擴展性：資料庫容易地擴展以適應不同的地理位置和增加的用戶需求。
資源共享：多個客戶端可以同時訪問同一伺服器上的數據。
安全性：有效地實施安全措施，保護數據不被未授權訪問。

了解網路服務的概念後應該不難理解，各種後端教學裡下載安裝資料庫伺服器程式，其實就是讓自己的電腦成為提供資料庫服務的主機。

只是資料庫服務的使用者是自己正在開發中的後端程式。

以postgreSQL為例，安裝完成後會跑在127.0.0.1:5432之下。

# mac
lsof -i :5432 -n -P

COMMAND  PID     USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME

postgres 320 postgres    7u  IPv6 0xcae2bc7a36b7ade1      0t0  TCP *:5432 (LISTEN)

postgres 320 postgres    8u  IPv4 0xcae2bc756d253de9      0t0  TCP *:5432 (LISTEN)

資料庫客戶端

當我們安裝好資料庫伺服器後，下一步就是如何管理資料庫、操作數據、檢視數據。這個時候cilent-server架構就發揮了強大的威力，有各式各樣的客戶端程式可以連線到資料庫伺服器。

1.命令列工具

通常資料庫伺服器安裝程式都會提供可以在終端機執行的命令列工具，以postgreSQL來說就是psql，底層使用libq函式庫。

2.桌面應用

當專案、資料數量增加了之後，命令列會漸漸難以負荷日益沈重的管理、查閱資料的作業需求，這時候我們通常會需要圖形化的桌面應用程式幫忙。

以postgreSQL來說，原廠提供使用python開發的pgAdmin，畫面簡潔易用，底層使用psycopg2函式庫。

我常用的是開源版本的DBeaver，操作上也是簡潔流暢，除此之外還支援多種資料庫類型，功能非常強大。

3.後端應用程序

後端應用程序，像Django，是前端（比如網頁界面）和資料庫之間的重要橋樑。這些應用程序封裝了複雜的資料庫操作，無需直接使用SQL語句來與資料庫溝通。

這些封裝後的資料庫操作，最後會透過db client傳送給db server，這也是為什麼新建一個Django開發專案時，我們要先下載安裝資料庫客戶端套件:psycopg2, 再將資料庫伺服器設定填寫至設定檔(settings.py) ，才能讓Django順利與資料庫伺服器溝通。

# 安裝資料庫客戶端套件
pip install psycopg2

# Django settings.py
DATABASES = {
'default': {

'ENGINE': 'django.db.backends.postgresql',

 'NAME': 'db-demo',

 'USER': 'db-admin',

 'PASSWORD': '12345678',

 'HOST': 'localhost',

 'PORT': '5432',

 }
}

深度思考 - 資料庫客戶端的底層實現

像libpq、psycopg2這樣的工具，扮演著應用程序與資料庫伺服器之間的橋樑角色，幫助開發者以簡單的方式執行複雜的資料庫操作，無需深入了解背後發生的具體細節。

Libpq 和 Psycopg2

Libpq 是用於連接PostgreSQL資料庫的C語言函式庫，它處理所有底層的網路通信和數據交換。
Psycopg2 基於Libpq，是一個Python函式庫，使Python開發者能夠輕鬆與PostgreSQL資料庫互動，執行如查詢和更新數據等操作。

範例1: django使用psycopg2與database server連線

import psycopg2

# 連接到 PostgreSQL 數據庫
conn = psycopg2.connect(
    dbname="your_dbname", 
    user="your_username", 
    password="your_password", 
    host="your_host", 
    port="your_port"
)

# 創建一個 cursor 對象用來執行 SQL 命令
cur = conn.cursor()

# 執行一個查詢
cur.execute("SELECT * FROM your_table")

# 獲取查詢結果
rows = cur.fetchall()

# 輸出查詢結果
for row in rows:
    print(row)

# 關閉 cursor 和連接
cur.close()
conn.close()

範例2: psycopg2使用libq與database server連線

#include <libpq-fe.h>

void connect_to_database(const char *conninfo) {
    PGconn *conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    } else {
        fprintf(stdout, "Connection to database succeeded\n");
    }
    // Always close the connection after done using it
    PQfinish(conn);
}