Google Cloud Spanner dialect and driver for NHibernate.
All supported features have been tested and verified to work with the test configurations. There may be configurations and/or data model variations that have not yet been covered by the tests and that show unexpected behavior. Please report any problems that you might encounter by creating a new issue.
Add a reference to the Google.Cloud.Spanner.NHibernate nuget package.
Set the Cloud Spanner NHibernate Dialect and a valid Cloud Spanner connection string in your NHibernate configuration to connect to a Cloud Spanner database using NHibernate:
Configuration = new Configuration().DataBaseIntegration(db =>
{
db.Dialect<SpannerDialect>();
db.ConnectionString = "Data Source=projects/MY-PROJECT/instances/MY-INSTANCE/databases/MY-DATABASE";
});The Google.Cloud.Spanner.NHibernate.Samples project contains a number of ready to run samples.
Follow these simple steps to run a sample:
- Clone or download this repository to your local computer.
- Open a command prompt of your choice and navigate to the
Google.Cloud.Spanner.NHibernate.Samplesproject folder. - Execute the command
dotnet run <SampleName>(Example:dotnet run Quickstart). Executedotnet runto get a list of available sample names.
Browse the Google.Cloud.Spanner.NHibernate.Samples/Snippets directory to view the source code of each sample.
First set up a .NET development environment for Google Cloud Spanner.
The following code snippet shows how you can configure NHibernate and the entity mappings entirely in code.
var configuration = new Configuration().DataBaseIntegration(db =>
{
db.Dialect<SpannerDialect>();
db.ConnectionString = "Data Source=projects/MY-PROJECT/instances/MY-INSTANCE/databases/MY-DATABASE";
db.BatchSize = 100;
});
var mapper = new ModelMapper();
mapper.AddMapping<SingerMapping>();
mapper.AddMapping<AlbumMapping>();
var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
configuration.AddMapping(mapping);
var sessionFactory = Configuration.BuildSessionFactory();
using var session = sessionFactory.OpenSession();
// Create a new Singer and save it. Note that we don't assign the record a value for the Id column. The id
// is automatically generated by NHibernate when the entity is saved.
var transaction = session.BeginTransaction();
var singer = new Singer
{
FirstName = "Emilia",
LastName = "Qillaq"
};
session.Save(singer);
transaction.Commit();public class Singer
{
public virtual string Id { get; set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
}
public class SingerMapping : ClassMapping<Singer>
{
public SingerMapping()
{
Table("Singers");
Persister<SpannerSingleTableEntityPersister>();
// DynamicUpdate(true) ensures that NHibernate will generate UPDATE statements that only modify the columns
// of the table that have actually been updated.
DynamicUpdate(true);
Id(x => x.Id, m =>
{
// The UUIDHexGeneratorDef automatically generates and assigns a UUID as the primary key value of a new
// entity. The value is not generated by Cloud Spanner.
m.Generator(new UUIDHexGeneratorDef());
m.Column(c =>
{
c.NotNullable(true);
c.Length(36);
});
});
Property(x => x.FirstName, m => m.Length(200));
Property(x => x.LastName, m =>
{
m.Length(200);
m.NotNullable(true);
});
Bag(x => x.Albums, c =>
{
// Always mark the collection end of a one-to-many relationship as Inverse(true).
// This will ensure that NHibernate will always first insert the parent record (the Singer)
// before the child record (the Album). This reduces the number of DML statements that are
// needed for a one-to-many relationship significantly.
c.Inverse(true);
c.Key(k => k.Column("SingerId"));
}, r => r.OneToMany());
}
}
public class Album
{
public virtual string Id { get; set; }
public virtual Singer Singer { get; set; }
public virtual string Title { get; set; }
}
public class AlbumMapping : ClassMapping<Album>
{
public AlbumMapping()
{
Table("Albums");
Persister<SpannerSingleTableEntityPersister>();
DynamicUpdate(true);
ManyToOne(x => x.Singer, m =>
{
m.Column(c =>
{
c.Name("SingerId");
c.NotNullable(true);
c.Length(36);
});
m.ForeignKey("FK_Albums_Singers");
});
Property(x => x.Title, m =>
{
m.NotNullable(true);
m.Length(100);
m.Index("Idx_Albums_Title");
});
}
}NHibernate supports automatically generating the data model from source code. The Cloud Spanner dialect also supports this, but requires the usage of the specific SpannerSchemaExport for this purpose. Automatically generating the data model is only recommended for development purposes, as it will drop the existing schema and recreate every time it is executed. For this reason, it is also recommended that this feature is mainly used when developing and testing an application against the Cloud Spanner emulator, as dropping and recreating an entire schema can take a long time on Cloud Spanner.
The following code snippet shows how to automatically drop and recreate the schema.
var configuration = new Configuration().DataBaseIntegration(db =>
{
db.Dialect<SpannerDialect>();
db.ConnectionString = "Data Source=projects/MY-PROJECT/instances/MY-INSTANCE/databases/MY-DATABASE";
db.BatchSize = 100;
});
var mapper = new ModelMapper();
mapper.AddMapping<SingerMapping>();
mapper.AddMapping<AlbumMapping>();
var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
configuration.AddMapping(mapping);
var sessionFactory = Configuration.BuildSessionFactory();
using var session = sessionFactory.OpenSession();
// Create a SpannerSchemaExporter and use this to drop and re-create the database schema from the entity
// model. The mapping model must include all metadata for the schema, such as which columns are not-nullable,
// the length of STRING columns, etc.
// Tables are created in the order that they are added to the mapping. Tables are dropped in the opposite
// order. This means that if the model includes interleaved tables, the parent table must be added to the
// mapping BEFORE the child table.
var exporter = new SpannerSchemaExport(configuration.Configuration);
// This will automatically execute a drop-and-recreate script and print the statements that are executed to
// the console (StdOut).
exporter.Create(true /*useStdOut*/, true /*execute*/);The table below shows the mapping of Cloud Spanner data types to Clr types. It is recommended to use the default type mapping,
unless you know that the values in the database will never exceed the maximum range of another chosen Clr type. Failure to do
so for numeric types will lead to an overflow exception for INT64 and NUMERIC types, and to silent loss of precision for
FLOAT64 types.
Non-nullable primitive types can be replaced by the corresponding nullable type. That is, bool? can be used instead of bool etc.
| Spanner Type | Clr Type |
|---|---|
| BOOL | bool |
| BYTES | byte[] |
| STRING | string |
| INT64 | long |
| FLOAT64 | double |
| NUMERIC | SpannerNumeric |
| DATE | SpannerDate |
| TIMESTAMP | DateTime |
| JSON | SpannerJson |
Array types must be mapped to the specific SpannerArray types.
| Array Type | Clr Type |
|---|---|
| ARRAY<BOOL> | SpannerBoolArray |
| ARRAY<BYTES> | SpannerBytesArray |
| ARRAY<STRING> | SpannerStringArray |
| ARRAY<INT64> | SpannerInt64Array |
| ARRAY<FLOAT64> | SpannerFloat64Array |
| ARRAY<NUMERIC> | SpannerNumericArray |
| ARRAY<DATE> | SpannerDateArray |
| ARRAY<TIMESTAMP> | SpannerTimestampArray |
| ARRAY<JSON> | SpannerJsonArray |
Cloud Spanner has some specific features that are not supported by other relational databases. Using these with NHibernate requires you to include some additional configuration or mapping in your code.
Interleaved tables define a parent-child relationship between two tables where the rows of the child table are physically stored together with the parent rows.
Use the InterleavedTableForeignKey.InterleaveInParent string constant as the name of the foreign key for a
many-to-one mapping that should be generated as an INTERLEAVE IN PARENT relationship instead of a traditional
FOREIGN KEY. Interleaved child tables must always include all the columns of the parent table in the primary
key definition. This means that the id mapping in NHibernate must include multiple columns, where one of the
columns is also a many-to-one relationship with the parent table.
Mapping example:
// The primary key of the table Tracks consists of the columns (Id, TrackNumber). The Id column also
// references a row in the Albums table (the parent table).
ComponentAsId(x => x.TrackIdentifier, m =>
{
m.ManyToOne(id => id.Album, albumMapper =>
{
albumMapper.Column(c =>
{
c.Name("Id");
c.NotNullable(true);
c.Length(36);
});
albumMapper.UniqueKey("Idx_Tracks_AlbumId_Title");
albumMapper.ForeignKey(InterleavedTableForeignKey.InterleaveInParent);
});
m.Property(id => id.TrackNumber, propertyMapper => propertyMapper.NotNullable(true));
});Identifier example:
[Serializable]
public class TrackIdentifier
{
public virtual Album Album { get; private set; }
public virtual long TrackNumber { get; private set; }
}Cloud Spanner can write the commit timestamp of a transaction to a column in a table. This can be used to keep track of the creation and/or last update time of a row.
Use the SpannerCommitTimestampSqlType to indicate that the data type of a column should include the option to
set the value to the commit timestamp of the transaction. Set the default value of the column to PENDING_COMMIT_TIMESTAMP()
to instruct the Cloud Spanner NHibernate driver to fill the column with the commit timestamp when the entity is
inserted/updated.
Example:
// Using the SpannerSingleTableEntityPersister is required for the commit timestamp feature.
Persister<SpannerSingleTableEntityPersister>();
Property(x => x.CreatedAt, m =>
{
// The following prevents NHibernate from assigning a value to this property when the entity is updated.
// This might seem counter-intuitive, as we want this value to be filled during inserts. This is however
// correct, as we don't want NHibernate to assign a value to the column in the INSERT statement, and
// instead we want the value to be assigned its default value.
m.Insert(false);
m.Column(c =>
{
// This will add the PENDING_COMMIT_TIMESTAMP() as the default value for the column.
c.Default("PENDING_COMMIT_TIMESTAMP()");
// This ensures that `OPTIONS (allow_commit_timestamp=true)` is added to the column definition.
c.SqlType(SpannerCommitTimestampSqlType.NotNullInstance);
});
});
Property(x => x.LastUpdatedAt, m =>
{
// The following prevents that NHibernate assigns a value to this property when the entity is updated.
// This might seem counter-intuitive, as we want this value to be modified during updates. This is
// however correct, as we don't want NHibernate to assign a value to the column in the UPDATE statement,
// and instead we want the value to be assigned its default value when it is updated.
m.Update(false);
m.Column(c =>
{
// This will add the PENDING_COMMIT_TIMESTAMP() as the default value for the column.
c.Default("PENDING_COMMIT_TIMESTAMP()");
// This ensures that `OPTIONS (allow_commit_timestamp=true)` is added to the column definition.
c.SqlType(SpannerCommitTimestampSqlType.NullableInstance);
});
});Cloud Spanner supports generated columns
that are calculated using a deterministic expression based on other columns in the same table. These columns may not be
updated by client applications. To prevent NHibernate to write values to these columns, they must be marked
with Generated(PropertyGeneration.Always).
Example:
Property(x => x.FullName, m =>
{
m.Generated(PropertyGeneration.Always);
});Note that generated columns will be read back by NHibernate during any transaction that inserts or updates an entity that has at least one generated column. This has a couple of drawbacks:
- Any
INSERT/UPDATEstatement will automatically be followed by aSELECTstatement to read back the generated value, even if the value is not needed by the application. - Generated columns do not work with transactions that use Mutations instead of DML, as modifications that are executed as mutations cannot be read back during the transaction.
One possible workaround for both the above is to define the column as a generated column in your database, but not
mark it as generated in the NHibernate mapping. Instead the column should be marked as not-insertable and not-updatable
to prevent NHibernate from writing a value to the column. The value will still be generated by Cloud Spanner, and if
the generated value is needed by the application, a call to session.Refresh(entity) will read the value from the database.
Example:
// Mapping
Property(x => x.FullName, m =>
{
m.Insert(false);
m.Update(false);
});
// Usage
var singer = new Singer { .. };
session.Save(singer);
// This will manually reload all properties from the database, including the generated FullName column.
session.Refresh(singer);Cloud Spanner can use Mutations instead of DML to modify data. Mutations can be faster than DML, but Mutations do not support read-your-writes. It is recommended to read https://cloud.google.com/spanner/docs/dml-versus-mutations if you are not familiar with the differences between Mutations and DML in Cloud Spanner.
The Cloud Spanner NHibernate driver supports executing transactions that will use Mutations instead of DML. These transactions do not support read-your-writes. Any modifications will only be readable after the transaction has committed. Manually flushing the session during a transaction will also not send any modifications to Cloud Spanner.
using var session = sessionFactory.OpenSession();
using var transactionWithMutations = session.BeginTransaction(MutationUsage.Always);
singer = session.Load<Singer>(singerId);
singer.LastName = "Yates-Fish";
// Committing the transaction will send the Mutation to Cloud Spanner and the updated value should be visible.
transactionWithMutations.Commit();
singersWithLastNameYatesFish = session.Query<Singer>().Where(s => s.LastName.Equals("Yates-Fish")).ToList();Cloud Spanner does not support sequences, identity columns, or other value generators in the database that will generate a unique value that could be used as a primary key value. Instead, the best option is to use a client side Guid generator for a primary key if your table does not contain a natural primary key. Always make sure not to use a monotonically increasing value as the first part of the primary key, as this will cause all inserts to happen at the end of the key space. Cloud Spanner divides data among servers using key ranges, and using a monotonically increasing primary key value will cause one server to receive all inserts. See https://cloud.google.com/spanner/docs/schema-design#primary-key-prevent-hotspots for more information.
NHibernate supports a wide range of different configurations and mappings. Some of these can lead to a large number of DML statements being executed when only one entity is updated, or can cause queries to be generated that need to join multiple tables. The following list contains recommendations for how to use NHibernate as efficiently as possible with Cloud Spanner.
| Feature | Recommendation |
|---|---|
| Transactions | If possible, avoid using read/write transactions for transactions that only read data. Use a read-only transaction to ensure that all reads use the same consistent view of your data, or execute the read operations without a transaction to let each read see the most recent version of the data at the moment the operation is executed. |
| Batching | The Cloud Spanner driver will set adonet.batch_size to 100 to enable batching by default. The recommended value is between 20 and 200. The Cloud Spanner driver will translate ADO.NET batches into Batch DML requests for Cloud Spanner. Setting this value to 0 will disable batching and have a negative impact on performance. |
| Dynamic Update | Set dynamic-update=true for all entity mappings. This ensures that NHibernate will only update those columns that have actually been modified. |
| Many-to-Many | Avoid ManyToMany collections. Instead, define an entity for the relationship and define many-to-one / one-to-many mappings for each side. See the CollectionMapping sample. |
| One-to-Many | Make sure that OneToMany collections are marked with Inverse(true). See the SingerMapping.Albums mapping for an example. |
| Generated Properties | Disable generated properties when executing transactions using mutations. Also consider disabling generated properties altogether in the NHibernate mapping, and replacing the property generation with a column that is not insertable and updateable. |