Best SQL library to use with local desktop app

[H. S. Teoh]

On Wed, Jan 03, 2018 at 12:14:19PM +0000, wakhshti via Digitalmars-d wrote:
> 
> what is best (SQLite?) @small @local @offline database library to use
> in D?
[...]

I've been using SQLite for this type of usage, and it's served me pretty
well.  I've been using Adam Ruppe's SQLite bindings:

	https://github.com/adamdruppe/arsd/blob/master/sqlite.d

which is very nice, lightweight, and doesn't have heavy dependencies
(all you need is database.d and sqlite.d, plop them into a subdirectory
called arsd, and then just `import arsd.sqlite` and you're all set to
go).

Recently, though, I decided to write my own bindings due to certain
design decisions in Adam's sqlite.d that made it a little awkward to use
for my particular application.  My bindings are now usable for basic
database operation and have a rather nice API, if I do say so myself
(well, it is modelled after Adam's sqlite.d which already has a nice and
simple API), but feature-wise it's still quite bare (doesn't support
precompiled SQL statements yet).

The current main features are:

- Basic database operation: open a database file, execute SQL
  statements (on par with Adam's sqlite.d).

- Automatic binding (on par with Adam's sqlite.d), e.g.:
	long id = 123;
	string name = "abc";
	auto rs = db.query("SELECT * FROM product WHERE id=? AND name=?",
			id, name);

- Supports binding floating-point values.

- Supports binary blobs in the form of ubyte[].

- Supports integers up to signed 64-bit (long).

- Supports nested transactions (via SAVEPOINT and RELEASE).

- Range-based API for iterating over result sets.

- Convenient automatic conversions to/from SQLite data types, e.g.:

	float maxPrice = 100.00;
	auto rs = db.query("SELECT count, price FROM product WHERE price < ?",
			maxPrice);
	foreach (row; rs)
	{
		int count = row[0].to!int;
		float price = row[1].to!float;
	}

- Fully-automated transcription of result sets to array of structs, or
  individual rows to structs, and convenience functions for extracting
  single-column result sets into an array of scalar values, or
  individual single-column rows into scalar values. Example:

  	struct S {
		int name;
		float price;
		ubyte[] image; // BLOB
	}

	// Transcribe individual rows to struct
	foreach (row; db.query("SELECT name,price,image FROM product"))
	{
		S s = row.to!S;
	}

	// Transcribe entire result set to array of structs
	S[] results = db.query("SELECT name,price,image FROM product")
			.to!(S[]);


If you wish, I can give you a copy of the code -- it's just a single
file that you can import directly, no other dependencies besides the
SQLite library itself.  It's not quite in the shape to be posted to a
public repository like github just yet, but depending on what you need,
it might be good enough.


T

-- 
An elephant: A mouse built to government specifications. -- Robert Heinlein