Skip to content

Instantly share code, notes, and snippets.

@schveiguy
Last active June 7, 2021 15:17
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save schveiguy/cb15600c2e8a75ba0886cb2fc26525b8 to your computer and use it in GitHub Desktop.
Show sqlbuilder usage
import sqlbuilder.uda;
import sqlbuilder.dataset;
import sqlbuilder.dialect.sqlite;
// Note all of these are sql-injection-proof
struct users
{
@primaryKey @autoIncrement int id; // a non-unique primary key doesn't make sense
string name;
string email;
}
DataSet!users ds; // make a dataset out of it
string dropSQL = dropTableSql!users;
string createSQL = createTableSql!users; // generate create table statement
enum createSQLEnum = createTableSql!users; // works at compile-time
auto insertSQL = insert(users(-1, "John Smith", "john.smith@example.com"));
// or
auto insertSQL2 = insert(users.tableDef)
.set(ds.name, "John Smith".param)
.set(ds.email, "john.smith@example.com".param);
// no insertOrReplace support yet
// Select query example with all columns returned
auto getUserByEmail = select(ds).where(ds.email, " = ", "john.smith@example.com".param);
// Select query example with a specific column returned
auto getNameByEmail = select(ds.name).where(ds.email, " = ", "john.smith@example.com".param);
// some actual usage with a real connection:
auto conn = makeConnection; // makeConnection is 3rd party, not part of sqlbuilder library
conn.exec(dropSQL);
conn.exec(createSQL);
// using insert without the specific connection "create" command doesn't store
// the new id, but you can get it from the 3rd-party library.
conn.perform(insertSQL);
// or using create just gives you everything you need
users newUser = conn.create(users(-1, "John Smith", "john.smith@example.com"));
users u1 = conn.fetchOne(getUserByEmail); // throws exception if it doesn't exist
string n1 = conn.fetchOne(getNameByEmail, "(No user by that email)"); // uses a default value if it doesn't exist
// do counting
auto c = conn.fetchTotal(select(ds.id));
auto c2 = conn.fetchTotal(select(ds.id).where(ds.name, " = ", "john".param));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment