[Migration(1)]
public class Migration001_CreateInitialScopeAndCollection : Migrate
{
public override void Up()
{
Create.Scope("myScope")
.WithCollection("myCollection1");
}
public override void Down()
{
Delete.Scope("myScope");
}
}Posts tagged with 'nosql'
This blog post is a part of the 2022 C# Advent. Every day of Advent, two contributions from C# enthusiasts are revealed. Thanks for reading, and Merry Christmas!
I’ve written about How I use Fluent Migrator and Lessons learned about Fluent Migrator. I like using Fluent Migrator when I worked with relational databases.
However, I spend most of my time working with the NoSQL document database Couchbase, these days. If the idea of Fluent Migrator for Couchbase sounds interesting, read on!
What are database migrations?
Database migrations (not to be confused with data migrations) are a series of scripts/definitions that build the structure of a database. For instance, the first script could create 15 tables. The next script might alter a table, or add a 16th table. And so on.
Why are they important?
Having database migrations are important because they make sure anyone interacting with the database is on the same page, whether they’re working on a local database, test database, staging database, production, etc. Migrations can be part of the CI/CD process, as well.
What good is that for NoSQL?
If you’re not familiar with NoSQL, you might think that migrations wouldn’t make any sense. However, NoSQL databases (especially document databases), while flexible, are not devoid of structure. For instance, in place of Database/Schema/Table/Row, Couchbase has the concepts of Bucket/Scope/Collection/Document that roughly correspond.
It’s true that a NoSQL migration would not have as much predefined detail (there’s nowhere to specify column names and data types, for instance), but the benefits of migrations can still help developers working with Couchbase.
Introducing NoSqlMigrator for Couchbase
I’ve created a brand new project called NoSqlMigrator, to offer the same style of migration as Fluent Migrator.
Important notes:
-
This is not a fork or plugin of Fluent Migrator. It’s a completely separate project.
-
This is not an official Couchbase, Inc product. It’s just me creating it as an open source, community project.
-
It’s very much a WIP/alpha release. Please leave suggestions, criticisms, PRs, and issues!
Setting up migrations in C#
The process is very similar to Fluent Migrator.
-
Create a project that will contain your migration classes (you technically don’t have to, but I recommend it).
-
Add NoSqlMigrator from NuGet to that project.
-
Create classes that inherit from
Migratebase class. Define anUpandDown. Add aMigrationattribute and give it a number (typically sequential). -
When you’re ready to run migrations, you can use the CLI migration runner, available as a release on GitHub, or you can use the
MigrationRunnerclass, and run the migrations from whatever code you like. (I recommend the CLI).
Currently, NoSqlMigrator only supports Couchbase. Unlike the relational world, there is a lot of variance in structure and naming in the NoSQL world, so adding support for other NoSQL databases might be awkward (But I won’t rule that out for the far future).
Creating your first migration
Here’s an example of a migration that creates a scope and a collection (a bucket must already exist) when going "Up". When going down, the scope is deleted (along with any collections in it).
If you are new to migrations, the Down can sometimes be tricky. Not everything can be neatly "downed". However, I find that I tend to only use Down during development, and rarely does it execute in test/integration/production. But it is possible, so do your best when writing Down, but don’t sweat it if you can’t get it perfect.
Running the migration
Here’s an example of using the command line to run a migration:
> NoSqlMigrator.Runner.exe MyMigrations.dll couchbase://localhost Administrator password myBucketNameThat will run all the Up implementations (that haven’t already been run: it keeps track with a special document in Couchbase).
You can also specify a limit (e.g. run all migrations up to/down from #5) and you can specify a direction (up/down). Here’s an example of running Up (explicitly) with a limit of 5:
> NoSqlMigrator.Runner.exe MyMigrations.dll couchbase://localhost Administrator password myBucketName -l 5 -d UpMore features for NoSQL migrations
Here are all the commands currently available:
-
Create.Scope
-
Create.Collection
-
Create.Index
-
Delete.Scope
-
Delete.Collection
-
Delete.FromCollection
-
Delete.Index
-
Insert.IntoCollection
-
Execute.Sql
-
Update.Document
-
Update.Collection
I’m working on a few more, and I have some GitHub issues listing commands that I plan to add. But if you can think of any others, please create an issue!
Tips and Conclusion
Many of the Lessons learned still apply to NoSqlMigrator.
In that post, I mention Octopus, but imagine any CI/CD/deployment tool of your choice in its place (e.g. Jenkins, GitHub Actions, etc).
I think NoSqlMigrator can be a very handy tool for keeping databases in sync with your team, checking knowledge into source control, and providing a quick way to get a minimum database structure built, to make your day of coding a little easier.
Please give it a try in your own project! If you’ve not used Couchbase, check out a free trial of Couchbase Capella DBaaS.
Jeffrey Miller is using Neo4j. This episode is sponsored by Smartsheet.
Show Notes:
-
CosmosDB on Azure
-
Magic: The Gathering is a collectible card game that I once sunk a lot of money into
-
Michael Hunger developer relations with Neo4j
-
link: Presentation slides by Jeffrey
Want to be on the next episode? You can! All you need is the willingness to talk about something technical.
Music is by Joe Ferg, check out more music on JoeFerg.com!
This is a repost that originally appeared on the Couchbase Blog: CSV tooling for migrating to Couchbase from Relational.
CSV (Comma-seperated values) is a file format that can be exported from a relational database (like Oracle or SQL Server). It can then be imported into Couchbase Server with the cbimport utility.
Note: cbimport comes with Couchbase Enterprise Edition. For Couchbase Community Edition, you can use the more limited cbtransfer tool or go with cbdocloader if JSON is an option.
A straight relational→CSV→Couchbase ETL probably isn’t going to be the complete solution for data migration. In a later post, I’ll write about data modeling decisions that you’ll have to consider. But it’s a starting point: consider this data as "staged".
Note: for this post, I’m using SQL Server and a Couchbase Server cluster, both installed locally. The steps will be similar for SQL Server, Oracle, MySQL, PostgreSQL, etc.
Export to CSV
The first thing you need to do is export to CSV. I have a relational database with two tables: Invoices and InvoiceItems.
I’m going to export the data from these two tables into two CSV files. With SQL Server Management Studio, this can be done a number of different ways. You can use sqlcmd or bcp at the command line. Or you can use Powershell’s Invoke-Sqlcmd and pipe it through Export-Csv. You can also use the SQL Server Management Studio UI.
Other relational databases will have command line utilities, UI tools, etc to export CSV.
Here is an example of a CSV export from a table called "Invoices":
Id,InvoiceNum,InvoiceDate,BillTo,ShipTo
1,ABC123,2018-01-15 00:00:00.000,Lynn Hess,"Herman Trisler, 4189 Oak Drive"
2,XYZ987,2017-06-23 00:00:00.000,Yvonne Pollak,"Clarence Burton, 1470 Cost Avenue"
3,FOO777,2018-01-02 00:00:00.000,Phillip Freeman,"Ronda Snell, 4685 Valley Lane"Here’s an export from a related table called "InvoiceItems":
InvoiceId,Product,Quantity,Price
1,Tire,2,20.00
1,Steering Wheel,5,10.00
1,Engine Oil,10,15.00
1,Brake Pad,24,1000.00
2,Mouse pad,1,3.99
2,Mouse,1,14.99
2,Computer monitor,1,199.98
3,Cupcake,12,.99
3,Birthday candles,1,.99
3,Delivery,1,30.00Load CSV into Couchbase
Let’s import these into a Couchbase bucket. I’ll assume you’ve already created an empty bucket named "staging".
First, let’s import invoices.csv.
Loading invoices
C:\Program Files\Couchbase\Server\bin\cbimport csv -c localhost -u Administrator -p password -b staging -d file://invoices.csv --generate-key invoice::%Id%Note: with Linux/Mac, instead of C:\Program Files\Couchbase\Server\bin, the path will be different.
Let’s break this down:
-
cbimport: This is the command line utility you’re using
-
csv: We’re importing from a CSV file. You can also import from JSON files.
-
-c localhost: The location of your Couchbase Server cluster.
-
-u Administrator -p password: Credentials for your cluster. Hopefully you have more secure credentials than this example!
-
-b staging: The name of the Couchbase bucket you want the data to end up in
-
--generate-key invoice::%Id% The template that will be used to create unique keys in Couchbase. Each line of the CSV will correspond to a single document. Each document needs a unique key. I decided to use the primary key (integer) with a prefix indicating that it’s an invoice document.
The end result of importing a 3 line file is 3 documents:
At this point, the staging bucket only contains invoice documents, so you may want to perform transformations now. I may do this in later modeling examples, but for now let’s move on to the next file.
Loading invoice items
C:\Program Files\Couchbase\Server\bin\cbimport csv -c localhost -u Administrator -p password -b staging -d file://invoice_items.csv --generate-key invoiceitem::#UUID#This is nearly identical to the last import. One difference is that it’s a new file (invoice_items.csv). But the most important difference is --generate—key. These records only contain foreign keys, but each document in Couchbase must have a unique key. Ultimately, we may decide to embed these records into their parent Invoice documents. But for now I decided to use UUID to generate unique keys for the records.
The end result of importing this 10 line file is 10 more documents:
What’s next?
Once you have a CSV file, it’s very easy to get data into Couchbase. However, this sort of direct translation is often not going to be enough on its own. I’ve explored some aspects of data modeling in a previous blog post on migrating from SQL Server, but I will revisit this Invoices example in a refresher blog post soon.
In the meantime, be sure to check out How Couchbase Beats Oracle for more information on why companies are replacing Oracle for certain use cases. And also take a look at the Moving from Relational to NoSQL: How to Get Started white paper.
If you have any questions or comments, please feel free to leave them here, contact me on Twitter @mgroves, or ask your question in the Couchbase Forums.
Eric Maxwell talks Realm's NoSQL database for mobile.
Show Notes:
- Realm.io
- The NoSQL Database Podcast
- Eric Maxwell's episode will be published in June
- Subscribe to The NoSQL Database Podcast on iTunes, or check out the podcast on LibSyn
- There was some discussion of the difference between an object database and a document database
- Realm on Github
- Eric was kind enough to share his email address in the podcast.
- #Hashtag Comedy Troupe in Columbus, Ohio
Want to be on the next episode? You can! All you need is the willingness to talk about something technical.
Theme music is "Crosscutting Concerns" by The Dirty Truckers, check out their music on Amazon or iTunes.
This is a repost that originally appeared on the Couchbase Blog: ASP.NET with NoSQL Workshop.
I delivered an ASP.NET with NoSQL workshop at the recent Indy.Code() conference in Indianapolis. I had a lot of fun at this conference, and I recommend you go next year. If you were unable to attend, don’t worry, because I’ve got the next best thing for you: all the material that I used in my workshop.
ASP.NET Workshop in 4 parts
This workshop contained four main parts:
-
Install a NoSQL database (Couchbase Server)
-
Interact with Couchbase Server (using both the Web Console and the .NET (or .NET Core) SDK)
-
Create a RESTful API using ASP.NET (or ASP.NET Core) WebAPI
-
Consume the RESTful API with an Angular frontend
Try it yourself
If you’d like to try it yourself, the ASP.NET with NoSQL Workshop materials are available on GitHub. Each part of the workshop contains a PPT and PDF file for you to follow along. Also, the "completed" version of each workshop is available.
If you get stuck or have any questions, please ask away in the Couchbase .NET Forums. Also check out the Couchbase Developer Portal for more information on the .NET SDK and Couchbase in general.
You can also contact me at Twitter @mgroves.
