We help startups start up.
x
In this new world of NoSql databases, it is interesting to see how relational databases like postgresql have responded to the new trend. As you may know, most NoSql databases store information in a document based format. One of the more popular NoSql databases, MongoDB, uses BSON or binary json. BSON is very efficient due to the ability to easily encode for storage and decode it for use as a JSON document.
Since Postgresql version 9.4, there has been a new column type introduced called jsonb. While one could store json as text, this column type allows for json to be validated and stored in the database and also to be queried against using the json-specific functions and operators.
This is an awesome feature for easily integrating documents into your relational data store, but one issue I was confronted with initially was how to best integrate it with our ORM NHibernate. NHibernate allows programmers to easily extend the default functionality with new user types, in this case, a jsonb user type.
First things first, you need to make sure you install the Npgsql nuget package here: https://www.nuget.org/packages/Npgsql/. Npgsql is a .Net Data Provider that allows .Net client application to send and receive data with a PostgreSQL server.
Once you have done that, you will have to create an extended version of the NHibernate SqlType that can also accept an NpgsqlDbType parameter:
Once that is done, you can then create an extended driver class:
Once the extended driver is set up, we can now use that to pass in the specific NpgSqlType that we are working with. You will want to set the “connection.driver_class” property in your NHibernate Configuration as well.
If you are still having trouble setting up the NpgSql driver, this is a great article to reference and should get your new configuration running: http://nhibernate.info/doc/howto/various/use-postgresql-arrays-with-nhibernate.html
Last and most importantly, let’s set up the jsonb user type:
[Serializable]
public class JsonbUserType : IUserType
{
public new bool Equals(object x, object y)
{
if (x == null && y == null)
return true;
if (x == null || y == null)
return false;
return ((string)y).Equals((string)x);
}
public int GetHashCode(object x)
{
if (x == null)
return 0;
return x.GetHashCode();
}
public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
if (names.Length != 1)
throw new InvalidOperationException("Only expecting one column...");
var val = rs[names[0]] as string;
if (!string.IsNullOrWhiteSpace(val))
{
return val;
}
return null;
}
public void NullSafeSet(IDbCommand cmd, object value, int index)
{
var parameter = (DbParameter)cmd.Parameters[index];
if (value == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = value;
}
}
public object DeepCopy(object value)
{
if (value == null)
return null;
return value;
}
public object Replace(object original, object target, object owner)
{
return original;
}
public object Assemble(object cached, object owner)
{
var str = cached as string;
if (string.IsNullOrWhiteSpace(str))
return null;
return str;
}
public object Disassemble(object value)
{
if (value == null)
return null;
return value;
}
public SqlType[] SqlTypes
{
get
{
return new SqlType[] { new NpgsqlExtendedSqlType(DbType.Binary, NpgsqlDbType.Jsonb) };
}
}
public Type ReturnedType
{
get { return typeof(string); }
}
public bool IsMutable
{
get { return true; }
}
}
This implementation works if you want the json document to be a string on the entity. Now if you would like to have a custom entity as the property to be serialized as json and passed to the database and then deserialized when retrieving the entity, you would instead use this user type:
[Serializable]
public class BlobbedJsonbUserType<T> : IUserType where T : class
{
public new bool Equals(object x, object y)
{
if (x == null && y == null)
return true;
if (x == null || y == null)
return false;
var xdocX = JsonConvert.SerializeObject(x);
var xdocY = JsonConvert.SerializeObject(y);
return xdocY == xdocX;
}
public int GetHashCode(object x)
{
return x == null ? 0 : x.GetHashCode();
}
public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
if (names.Length != 1)
throw new InvalidOperationException("Only expecting one column...");
var val = rs[names[0]] as string;
if (val != null && !string.IsNullOrWhiteSpace(val))
return JsonConvert.DeserializeObject<T>(val);
return null;
}
public void NullSafeSet(IDbCommand cmd, object value, int index)
{
var parameter = (DbParameter)cmd.Parameters[index];
if (value == null)
parameter.Value = DBNull.Value;
else
parameter.Value = JsonConvert.SerializeObject(value);
}
public object DeepCopy(object value)
{
if (value == null)
return null;
var serialized = JsonConvert.SerializeObject(value);
return JsonConvert.DeserializeObject<T>(serialized);
}
public object Replace(object original, object target, object owner)
{
return original;
}
public object Assemble(object cached, object owner)
{
var str = cached as string;
return string.IsNullOrWhiteSpace(str) ? null : JsonConvert.DeserializeObject<T>(str);
}
public object Disassemble(object value)
{
return value == null ? null : JsonConvert.SerializeObject(value);
}
public SqlType[] SqlTypes
{
//we must write extended SqlType and return it here
get
{
return new SqlType[] { new NpgsqlExtendedSqlType(DbType.Binary, NpgsqlTypes.NpgsqlDbType.Jsonb) };
}
}
public Type ReturnedType
{
get { return typeof(T); }
}
public bool IsMutable
{
get { return true; }
}
}
-----
