📑
Cline で TiDB Serverless / Serverless Driver のプロジェクトを作成して感じたこと
ふと思い立ってTiDB Serverlessが提供しているServerless DriverをどこまでClineは扱えるのか？というのを試してみました。
試したプロンプトは非常に簡単です。
Tidb serverlessにserverless driverを用いて接続したい。
tidbserverlessフォルダに作成できますか？必要な情報を教えてください。
あらかじめからのtidbserverlessフォルダを作成してClineに聞いてみました。
Clineが求めるApproveボタンを数度落ちながらしばらく待つと以下のようなフォルダ構成が出来上がりました。(node_modulesだけは手でnpm installを実行して作成されたものです）
作成されたものですが
index.js : 基本的なサンプル（DBへの接続情報はベタ打ち）
index-with-env.js ：上記の改修版、DBへの接続情報は.envを使用
advanced-example.js：上記の改修版で様々なクエリを実行
実行を試みると何度かエラーとなったため、都度エラーメッセージをClineに伝えながらApproveをクリックするだけでここまでできてしまいました。（合計2.4594ドルです）
index.js
const { connect } = require('@tidbcloud/serverless');
// TiDB Serverless connection configuration
// You'll need to replace these values with your actual TiDB Serverless credentials
const config = {
host: 'gateway01.ap-northeast-1.prod.aws.tidbcloud.com', // Default TiDB Serverless host
username: 'xxxxxx.root', // e.g., '2aUc1234567890'
password: 'xxxx', // Your TiDB Serverless password
database: 'test', // The database you want to connect to
port: 4000, // Default TiDB port
ssl: true // Enable SSL for secure connection
};
// Validate configuration before connecting
function validateConfig() {
const placeholders = [];
if (config.username === 'YOUR_USERNAME') {
placeholders.push('username');
}
if (config.password === 'YOUR_PASSWORD') {
placeholders.push('password');
}
if (placeholders.length > 0) {
console.error('\x1b[31mError: You need to update the configuration in index.js with your actual TiDB Serverless credentials.\x1b[0m');
console.error(`\x1b[31mPlease replace the following placeholder values: ${placeholders.join(', ')}\x1b[0m`);
console.error('\x1b[33mYou can find your connection details in the TiDB Cloud console:\x1b[0m');
console.error('\x1b[33m1. Log in to https://tidbcloud.com\x1b[0m');
console.error('\x1b[33m2. Navigate to your TiDB Serverless cluster\x1b[0m');
console.error('\x1b[33m3. Click on "Connect" to view your connection details\x1b[0m');
console.error('\x1b[33m4. Select "Connect with Serverless Driver" to get the specific connection string format\x1b[0m');
process.exit(1);
}
}
async function main() {
try {
// Validate configuration
validateConfig();
console.log('Connecting to TiDB Serverless...');
console.log(`Host: ${config.host}, Database: ${config.database}`);
// Create a connection to TiDB Serverless
const conn = await connect({
url: `tidb://${config.username}:${config.password}@${config.host}:${config.port}/${config.database}?ssl=${config.ssl}`
});
console.log('Connected successfully!');
// Example: Create a table
console.log('Creating a table...');
await conn.execute(`
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
// Example: Insert data
console.log('Inserting data...');
await conn.execute(`
INSERT INTO users (name, email) VALUES (?, ?)
`, ['John Doe', 'john@example.com']);
// Example: Query data
console.log('Querying data...');
const [rows] = await conn.execute('SELECT * FROM users');
console.log('Query results:', rows);
// Try to close the connection if possible
try {
// Try different methods to close the connection
if (typeof conn.close === 'function') {
await conn.close();
} else if (typeof conn.end === 'function') {
await conn.end();
} else if (typeof conn.disconnect === 'function') {
await conn.disconnect();
} else {
console.log('Note: Could not find a method to explicitly close the connection.');
console.log('The connection will be closed automatically when the program exits.');
}
console.log('Connection closed.');
} catch (closeError) {
console.log('Note: Could not explicitly close the connection, but operations completed successfully.');
console.log('The connection will be closed automatically when the program exits.');
}
} catch (error) {
console.error('Error:', error);
}
}
main();
感じたこと
1. Clineはとても優秀である一方で雄弁
今回試したスクリプトは以下です。
Tidb serverlessにserverless driverを用いて接続したい。
tidbserverlessフォルダに作成できますか？必要な情報を教えてください。
これだけでtsも含み4つのファイルを作成してくれるため、学習に使うにはいいのですが、実際の開発環境に導入すると本来不要なものもいっぱい作成することが予想されここは事前に許可があればいいなと思いました。
実際はファイルの読み取りや作成のタイミングでApproveボタンを押す必要があるのですが、その前にどういうシナリオを作ろうとしているのか？という選択肢は欲しいと感じました。Clineが発生させるバグやその修正も含めて従量課金でトークンを消費するため、大きい環境などなかなかコスト管理が大変そうです。（もしファイル作成などを消極的に必要最小限だけ行うモードがあればしりたいです）
2. 公式ドキュメントのセルフチェックはとても重要
ClineはTiDB Serverless Driver の公式ドキュメントを読みながら作業を行っていましたが、コネクションのクローズだけてこずり以下のようなコードと最終的になりました。（ここまで３回やり直しをしてもらっています）
if (typeof conn.close === 'function') {
await conn.close();
} else if (typeof conn.end === 'function') {
await conn.end();
} else if (typeof conn.disconnect === 'function') {
await conn.disconnect();
} else {
console.log('Note: Could not find a method to explicitly close the connection.');
console.log('The connection will be closed automatically when the program exits.');
}
console.log('Connection closed.');
つまり試行錯誤の結果コネクションのクローズ方法がわからず手当たり次第に試す形となっています。
なぜここまで賢いのにコネクションのクローズだけできないのだろうか、と不思議に思い公式ドキュメントを見てみると
コネクションの確立、SQLの実行はサンプルがありますがコネクションのクローズについての記載がありませんでした。
Serverless Driver はステートレスなのでコネクションのクローズは不要です。
これはClineが悪いかと問われると、この時代はもはや、生成AIが正しく情報を収集できるようにドキュメントは留意すべきなのかな？って感じました。特にClineは従量課金でトークンをガンガン消費するのでサービスのＵＸというものの定義には生成ＡＩフレンドリーである、ということが含まれる時代になってきたのではないか、と思った次第です。
各API提供ベンダーはClineやPostmanのMCP自動生成機能や、その他開発補助系AIツールが望んだ答えを出さない時は、自社ドキュメントの充実を図る、そういう時代が来ていると感じたテストでした。
参考:
advanced-example.js
const fs = require('fs');
const path = require('path');
const { connect } = require('@tidbcloud/serverless');
// Check if .env file exists
const envPath = path.join(__dirname, '.env');
if (!fs.existsSync(envPath)) {
console.error('\x1b[31mError: .env file not found!\x1b[0m');
console.error('\x1b[33mPlease create a .env file in the project root with your TiDB Serverless credentials.\x1b[0m');
console.error('\x1b[33mYou can use the .env.example file as a template:\x1b[0m');
console.error('\x1b[33m cp .env.example .env\x1b[0m');
console.error('\x1b[33mThen edit the .env file with your actual credentials.\x1b[0m');
process.exit(1);
}
// Load environment variables from .env file
require('dotenv').config();
// TiDB Serverless connection configuration using environment variables
const config = {
host: process.env.TIDB_HOST || 'gateway.tidbcloud.com',
username: process.env.TIDB_USERNAME,
password: process.env.TIDB_PASSWORD,
database: process.env.TIDB_DATABASE || 'test',
port: parseInt(process.env.TIDB_PORT || '4000'),
ssl: process.env.TIDB_SSL !== 'false'
};
// Validate required environment variables
function validateConfig() {
const required = ['username', 'password'];
const missing = required.filter(key => !config[key]);
if (missing.length > 0) {
console.error('\x1b[31mError: Missing required environment variables in .env file!\x1b[0m');
console.error(`\x1b[31mPlease set the following variables: ${missing.map(key => `TIDB_${key.toUpperCase()}`).join(', ')}\x1b[0m`);
console.error('\x1b[33mYou can find your connection details in the TiDB Cloud console:\x1b[0m');
console.error('\x1b[33m1. Log in to https://tidbcloud.com\x1b[0m');
console.error('\x1b[33m2. Navigate to your TiDB Serverless cluster\x1b[0m');
console.error('\x1b[33m3. Click on "Connect" to view your connection details\x1b[0m');
console.error('\x1b[33m4. Select "Connect with Serverless Driver" to get the specific connection string format\x1b[0m');
process.exit(1);
}
}
async function setupDatabase(conn) {
console.log('Setting up database...');
// Create products table
await conn.execute(`
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
// Create orders table
await conn.execute(`
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
// Create order_items table with foreign keys
await conn.execute(`
CREATE TABLE IF NOT EXISTS order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
)
`);
console.log('Database setup complete.');
}
async function seedProducts(conn) {
console.log('Checking if products exist...');
const result = await conn.execute('SELECT COUNT(*) as count FROM products');
// Handle different result structures
let count = 0;
if (Array.isArray(result) && result.length > 0 && Array.isArray(result[0]) && result[0].length > 0) {
// Handle [rows, fields] structure
count = result[0][0].count;
} else if (result && typeof result === 'object' && result.count !== undefined) {
// Handle direct object with count
count = result.count;
} else if (Array.isArray(result) && result.length > 0 && result[0] && result[0].count !== undefined) {
// Handle [{ count: X }] structure
count = result[0].count;
}
if (count > 0) {
console.log(`${count} products already exist, skipping seed.`);
return;
}
console.log('Seeding products...');
// Prepare sample products
const sampleProducts = [
{ name: 'Laptop', price: 1299.99, stock: 10 },
{ name: 'Smartphone', price: 699.99, stock: 15 },
{ name: 'Headphones', price: 149.99, stock: 20 },
{ name: 'Tablet', price: 499.99, stock: 8 },
{ name: 'Smartwatch', price: 249.99, stock: 12 }
];
// Insert products in a batch
const insertQuery = 'INSERT INTO products (name, price, stock) VALUES (?, ?, ?)';
for (const product of sampleProducts) {
await conn.execute(insertQuery, [product.name, product.price, product.stock]);
}
console.log(`${sampleProducts.length} products seeded successfully.`);
}
async function createOrder(conn, customerName, items) {
console.log(`Creating order for ${customerName}...`);
try {
// Note: We'll perform operations without explicit transaction management
// since the driver might handle transactions differently
console.log('Performing order operations...');
// Calculate total amount
let totalAmount = 0;
for (const item of items) {
// Get product details and check stock
const result = await conn.execute(
'SELECT price, stock FROM products WHERE id = ?',
[item.productId]
);
// Handle different result structures
let products = [];
if (Array.isArray(result) && result.length > 0) {
if (Array.isArray(result[0])) {
products = result[0];
} else {
products = result;
}
}
if (!products || products.length === 0) {
throw new Error(`Product with ID ${item.productId} not found`);
}
const product = products[0];
if (product.stock < item.quantity) {
throw new Error(`Not enough stock for product ID ${item.productId}`);
}
// Update product stock
await conn.execute(
'UPDATE products SET stock = stock - ? WHERE id = ?',
[item.quantity, item.productId]
);
// Add to total amount
totalAmount += product.price * item.quantity;
}
// Create order
const orderResult = await conn.execute(
'INSERT INTO orders (customer_name, total_amount) VALUES (?, ?)',
[customerName, totalAmount]
);
// Debug the result structure
console.log('Order insertion result structure:', JSON.stringify(orderResult, null, 2));
// Handle different result structures to get insertId
let orderId;
if (Array.isArray(orderResult)) {
console.log('Result is an array with length:', orderResult.length);
if (orderResult.length > 0) {
if (orderResult[0] && typeof orderResult[0] === 'object') {
console.log('First element is an object:', orderResult[0]);
if (orderResult[0].insertId !== undefined) {
orderId = orderResult[0].insertId;
console.log('Found insertId in first element:', orderId);
} else if (orderResult[0].affectedRows && orderResult[0].insertId === undefined) {
// For some drivers, the insert operation might return the last inserted ID differently
// Try to get the last inserted ID with a separate query
console.log('No insertId in result, trying to get last inserted ID with a separate query');
const lastIdResult = await conn.execute('SELECT LAST_INSERT_ID() as id');
console.log('Last ID query result:', JSON.stringify(lastIdResult, null, 2));
if (Array.isArray(lastIdResult) && lastIdResult.length > 0 && Array.isArray(lastIdResult[0]) && lastIdResult[0].length > 0) {
orderId = lastIdResult[0][0].id;
console.log('Found ID from LAST_INSERT_ID():', orderId);
} else if (lastIdResult && lastIdResult[0] && lastIdResult[0].id) {
orderId = lastIdResult[0].id;
console.log('Found ID from LAST_INSERT_ID() (alt format):', orderId);
}
}
}
}
} else if (orderResult && typeof orderResult === 'object') {
console.log('Result is an object:', orderResult);
if (orderResult.insertId !== undefined) {
orderId = orderResult.insertId;
console.log('Found insertId in result object:', orderId);
} else if (orderResult.affectedRows && orderResult.insertId === undefined) {
// Try to get the last inserted ID with a separate query
console.log('No insertId in result object, trying to get last inserted ID with a separate query');
const lastIdResult = await conn.execute('SELECT LAST_INSERT_ID() as id');
console.log('Last ID query result:', JSON.stringify(lastIdResult, null, 2));
if (Array.isArray(lastIdResult) && lastIdResult.length > 0 && Array.isArray(lastIdResult[0]) && lastIdResult[0].length > 0) {
orderId = lastIdResult[0][0].id;
console.log('Found ID from LAST_INSERT_ID():', orderId);
} else if (lastIdResult && lastIdResult[0] && lastIdResult[0].id) {
orderId = lastIdResult[0].id;
console.log('Found ID from LAST_INSERT_ID() (alt format):', orderId);
}
}
}
if (!orderId) {
// As a last resort, try to find the order by customer name and total amount
console.log('Still no order ID, trying to find the order by customer name and total amount');
const findOrderResult = await conn.execute(
'SELECT id FROM orders WHERE customer_name = ? AND total_amount = ? ORDER BY created_at DESC LIMIT 1',
[customerName, totalAmount]
);
console.log('Find order result:', JSON.stringify(findOrderResult, null, 2));
if (Array.isArray(findOrderResult) && findOrderResult.length > 0) {
if (Array.isArray(findOrderResult[0]) && findOrderResult[0].length > 0) {
orderId = findOrderResult[0][0].id;
console.log('Found order ID by query:', orderId);
} else if (findOrderResult[0] && findOrderResult[0].id) {
orderId = findOrderResult[0].id;
console.log('Found order ID by query (alt format):', orderId);
}
}
if (!orderId) {
throw new Error('Failed to get order ID after insertion');
}
}
// Create order items
for (const item of items) {
const priceResult = await conn.execute(
'SELECT price FROM products WHERE id = ?',
[item.productId]
);
// Handle different result structures for price
let price = 0;
if (Array.isArray(priceResult) && priceResult.length > 0) {
if (Array.isArray(priceResult[0]) && priceResult[0].length > 0) {
price = priceResult[0][0].price;
} else if (priceResult[0] && priceResult[0].price) {
price = priceResult[0].price;
}
}
if (!price) {
throw new Error(`Could not determine price for product ID ${item.productId}`);
}
await conn.execute(
'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
[orderId, item.productId, item.quantity, price]
);
}
console.log(`Order created successfully with ID: ${orderId}`);
return orderId;
} catch (error) {
console.error('Error creating order:', error.message);
throw error;
}
}
async function getOrderDetails(conn, orderId) {
console.log(`Getting details for order ID: ${orderId}`);
// Get order information
const orderResult = await conn.execute(
'SELECT * FROM orders WHERE id = ?',
[orderId]
);
// Handle different result structures for orders
let orders = [];
if (Array.isArray(orderResult) && orderResult.length > 0) {
if (Array.isArray(orderResult[0])) {
orders = orderResult[0];
} else {
orders = orderResult;
}
}
if (!orders || orders.length === 0) {
throw new Error(`Order with ID ${orderId} not found`);
}
const order = orders[0];
// Get order items
const itemsResult = await conn.execute(`
SELECT oi.*, p.name as product_name
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = ?
`, [orderId]);
// Handle different result structures for items
let items = [];
if (Array.isArray(itemsResult) && itemsResult.length > 0) {
if (Array.isArray(itemsResult[0])) {
items = itemsResult[0];
} else {
items = itemsResult;
}
}
return {
...order,
items
};
}
async function main() {
try {
// Validate configuration before attempting to connect
validateConfig();
console.log('Connecting to TiDB Serverless...');
// Create a connection to TiDB Serverless
const conn = await connect({
url: `tidb://${config.username}:${config.password}@${config.host}:${config.port}/${config.database}?ssl=${config.ssl}`
});
console.log('Connected successfully!');
// Setup database schema
await setupDatabase(conn);
// Seed products
await seedProducts(conn);
// Create a sample order
const orderItems = [
{ productId: 1, quantity: 1 }, // 1 Laptop
{ productId: 3, quantity: 2 } // 2 Headphones
];
const orderId = await createOrder(conn, 'Jane Smith', orderItems);
// Get and display order details
const orderDetails = await getOrderDetails(conn, orderId);
console.log('Order Details:');
console.log(JSON.stringify(orderDetails, null, 2));
// Display updated product inventory
const productResult = await conn.execute('SELECT * FROM products');
// Handle different result structures for products
let products = [];
if (Array.isArray(productResult) && productResult.length > 0) {
if (Array.isArray(productResult[0])) {
products = productResult[0];
} else {
products = productResult;
}
}
console.log('Updated Product Inventory:');
console.log(JSON.stringify(products, null, 2));
// Try to close the connection if possible
try {
// Try different methods to close the connection
if (typeof conn.close === 'function') {
await conn.close();
} else if (typeof conn.end === 'function') {
await conn.end();
} else if (typeof conn.disconnect === 'function') {
await conn.disconnect();
} else {
console.log('Note: Could not find a method to explicitly close the connection.');
console.log('The connection will be closed automatically when the program exits.');
}
console.log('Connection closed.');
} catch (closeError) {
console.log('Note: Could not explicitly close the connection, but operations completed successfully.');
console.log('The connection will be closed automatically when the program exits.');
}
} catch (error) {
console.error('Error:', error.message);
process.exit(1);
}
}
main();
Discussion