Write a network server that serves a SQLite database in your dominant language. Write a client in a different language.

[u/deanmsands3]

[u/[deleted]]

That sounds like an interesting challenge. Here is a possible solution:

Server

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net"
	"net/rpc"

	_ "github.com/mattn/go-sqlite3"
)

// DBService is a type that implements the methods for interacting with the database
type DBService struct {
	db *sql.DB
}

// Query is a type that represents a SQL query and its parameters
type Query struct {
	SQL    string
	Params []interface{}
}

// Result is a type that represents the result of a SQL query
type Result struct {
	Columns []string
	Rows    [][]interface{}
}

// Exec executes a SQL query that does not return any rows
func (dbs *DBService) Exec(query Query, reply *sql.Result) error {
	res, err := dbs.db.Exec(query.SQL, query.Params...)
	if err != nil {
		return err
	}
	*reply = res
	return nil
}

// Query executes a SQL query that returns rows
func (dbs *DBService) Query(query Query, reply *Result) error {
	rows, err := dbs.db.Query(query.SQL, query.Params...)
	if err != nil {
		return err
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return err
	}

	var data [][]interface{}
	for rows.Next() {
		row := make([]interface{}, len(cols))
		for i := range row {
			row[i] = new(interface{})
		}
		err = rows.Scan(row...)
		if err != nil {
			return err
		}
		data = append(data, row)
	}

	err = rows.Err()
	if err != nil {
		return err
	}

	reply.Columns = cols
	reply.Rows = data

	return nil
}

func main() {
	db, err := sql.Open("sqlite3", "./test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	dbs := &DBService{db: db}

	err = rpc.Register(dbs)
	if err != nil {
		log.Fatal(err)
	}

	listener, err := net.Listen("tcp", ":8080")
	if err != nil {
		log.Fatal(err)
	}
	defer listener.Close()

	fmt.Println("Server listening on port 8080")

	for {
		conn, err := listener.Accept()
		if err != nil {
			log.Println(err)
			continue
		}
		go rpc.ServeConn(conn)
	}
}

Client

use std::io::prelude::*;
use std::net::TcpStream;

// Query is a type that represents a SQL query and its parameters
#[derive(Serialize, Deserialize)]
struct Query {
    sql: String,
    params: Vec<String>,
}

// Result is a type that represents the result of a SQL query
#[derive(Serialize, Deserialize)]
struct Result {
    columns: Vec<String>,
    rows: Vec<Vec<String>>,
}

fn main() -> std::io::Result<()> {
    // Connect to the server
    let mut stream = TcpStream::connect("127.0.0.1:8080")?;

    // Create a query object
    let query = Query {
        sql: "SELECT * FROM users WHERE id = ?".to_string(),
        params: vec!["1".to_string()],
    };

    // Serialize the query object into JSON
    let json = serde_json::to_string(&query)?;

    // Write the JSON to the stream with a newline delimiter
    stream.write_all(json.as_bytes())?;
    stream.write_all(b"\n")?;

    // Read the response from the stream until the newline delimiter
    let mut response = String::new();
    stream.read_to_string(&mut response)?;

    // Deserialize the response into a result object
    let result: Result = serde_json::from_str(&response)?;

    // Print the result object
    println!("{:?}", result);

    Ok(())
}