Schema, Table & Column APIs

Create Table APIs​

OrmLite's CreateTable APIs can be used to create RDBMS tables from your C# POCO Data Models.

If you also need to ensure tables are populated with Seed Data when they're created, use CreateTableIfNotExists, e.g:

using var db = appHost.Resolve<IDbConnectionFactory>().Open();

if (db.CreateTableIfNotExists<Person>())
{
    db.Insert(new Person { Id = 1, Name = "John Doe" });
}

Create Tables​

Or if you just want to create tables that don't exist:

db.CreateTableIfNotExists<Person>()
db.CreateTable<Person>(overwrite:false);

// Runtime Type
db.CreateTableIfNotExists(typeof(Person));
db.CreateTables(overwrite:false, typeof(Person));

// Multiple Tables
db.CreateTableIfNotExists(typeof(TableA), typeof(TableB), typeof(TableC));
db.CreateTables(overwrite:false, typeof(TableA), typeof(TableB), typeof(TableC));

Recreate Tables​

To ensure existing tables are dropped and new tables are always re-created, use:

db.DropAndCreateTable<Person>();
db.CreateTable<Person>(overwrite:true);


// Multiple Runtime Types
db.DropAndCreateTables(typeof(TableA), typeof(TableB), typeof(TableC));
db.CreateTables(overwrite:true, typeof(TableA), typeof(TableB), typeof(TableC));

Drop and Create Tables​

However if your tables have foreign keys, you'll need to drop and re-create them in the order that satisfies their foreign key constraints, e.g:

db.DropTable<TableB>();
db.DropTable<TableA>();

db.CreateTable<TableA>();
db.CreateTable<TableB>();

// Runtime Type
db.DropTable(typeof(Person));
db.CreateTable(typeof(Person));

// Multiple Runtime Types
db.DropTables(typeof(TableA), typeof(TableB), typeof(TableC));
db.CreateTables(overwrite:true, typeof(TableA), typeof(TableB), typeof(TableC));

Pre / Post Custom SQL Hooks when Creating and Dropping tables​

Pre / Post Custom SQL Hooks allow you to inject custom SQL before and after tables are created or dropped, e.g:

[PostCreateTable("INSERT INTO Person (Name) VALUES ('Foo');" +
                 "INSERT INTO Person (Name) VALUES ('Bar');")]
public class Person
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

Create Schemas​

The CreateSchema APIs can be used to create Schemas in RDBMSs that support it, where they don't already exist:

db.CreateSchema("TheSchema");

Alternatively you can use the typed API to create Schemas defined on Data Models, e.g:

[Schema("Schema")]
public class Person
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

db.CreateSchema<Person>();

Query Existing Tables​

As the queries for retrieving table names can vary amongst different RDBMS's, we've abstracted their implementations behind uniform APIs where you can now get a list of table names and their row counts for all supported RDBMS's with:

List<string> tableNames = db.GetTableNames();

List<KeyValuePair<string,long>> tableNamesWithRowCounts = db.GetTableNamesWithRowCounts();

Both APIs can be called with an optional schema if you only want the tables for a specific schema. It defaults to using the more efficient RDBMS APIs, which if offered typically returns an approximate estimate of rowcounts in each table.

If you need exact table row counts, you can specify live:true:

var tablesWithRowCounts = db.GetTableNamesWithRowCounts(live:true);

Modify Custom Schema​

OrmLite provides Typed APIs for modifying Table Schemas that makes it easy to inspect the state of an RDBMS Table which can be used to determine what modifications you want on it, e.g:

class Poco 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Ssn { get; set; }
}

db.DropTable<Poco>();
db.TableExists<Poco>(); //= false

db.CreateTable<Poco>(); 
db.TableExists<Poco>(); //= true

db.ColumnExists<Poco>(x => x.Ssn); //= true
db.DropColumn<Poco>(x => x.Ssn);
db.ColumnExists<Poco>(x => x.Ssn); //= false

In a future version of your Table POCO you can use ColumnExists to detect which columns haven't been added yet, then use AddColumn to add it, e.g:

class Poco 
{
    public int Id { get; set; }
    public string Name { get; set; }

    [Default(0)]
    public int Age { get; set; }
}

if (!db.ColumnExists<Poco>(x => x.Age)) //= false
    db.AddColumn<Poco>(x => x.Age);

db.ColumnExists<Poco>(x => x.Age); //= true

Modify Schema APIs​

Additional Modify Schema APIs available in OrmLite include:

AlterTable​

When maximum flexibility is needed to perform Alter table commands with custom SQL, e.g:

db.AlterTable<Poco>("ADD Email VARCHAR(255)");
db.AlterTable(typeof(Poco), "ADD Email VARCHAR(255)");

AddColumn​

For adding a new column definition to an existing table, e.g:

public class Poco
{
    [Index]
    public string Code { get; set; }
}

db.AddColumn<Poco>(x => x.Code);
db.AddColumn(table:"Poco", new FieldDefinition { Name = "Code", FieldType = typeof(string) });

AlterColumn​

For modifying an existing column definition, e.g:

public class Poco
{
    [Index]
    public string Code { get; set; }
}

db.AlterColumn<Poco>(x => x.Code);
db.AlterColumn(table:"Booking", new FieldDefinition { Name = "Code", FieldType = typeof(string), IsIndexed = true });

RenameColumn​

For renaming an existing column, e.g:

db.RenameColumn<Poco>(x => x.ToName, fromName);
db.RenameColumn(typeof(Poco), fromName, toName);
db.RenameColumn(table:"Poco", oldColumn:fromName, newColumn:toName);

DropColumn​

For dropping an existing column, e.g:

db.DropColumn<Poco>(x => x.Name);
db.DropColumn(typeof(Poco), "Name");
db.DropColumn(table:"Poco", column:"Name");

AddForeignKey​

To add a Foreign Key relationship to existing tables, e.g:

db.AddForeignKey<Poco, ReferencedType>(
    field: t => t.RefId, 
    foreignField: tr => tr.Id,
    onUpdate: OnFkOption.NoAction, 
    onDelete: OnFkOption.Cascade, 
    foreignKeyName);

DropForeignKey​

To delete an existing Foreign Key:

db.DropForeignKey<Poco>(foreignKeyName);

CreateIndex​

To create an index on an existing column, e.g:

db.CreateIndex<Poco>(x => x.Code);
db.CreateIndex<Poco>(x => x.Code, indexName);
db.CreateIndex<Poco>(x => x.Code, indexName, unique:true);

DropIndex​

To drop an existing index, e.g:

db.DropIndex<Poco>(indexName);

Custom SQL​

When you need functionality beyond what's available in the Modify Schema APIs like needing to access RDBMS-specific features you can drop down to SQL:

db.ExecuteSql("ALTER TABLE Track ADD Rand INT default 0");
db.ExecuteSql("UPDATE Track SET Rand = abs(random()) % 1000");

Db.ExecuteSql("INSERT INTO page_stats (ref_id, fav_count) VALUES (@refId, @favCount)",
              new { refId, favCount })

//Async:
Db.ExecuteSqlAsync("UPDATE page_stats SET view_count = view_count + 1 WHERE id = @id", new { id })

//PostgreSQL Arrays
await Db.ExecuteSqlAsync(@"UPDATE notification SET emailed_user_ids = emailed_user_ids || @userId WHERE id = @id", 
    new { userId, id = notificationId });

Typed Sql.Cast() SQL Modifier​

The Sql.Cast() provides a cross-database abstraction for casting columns or expressions in SQL queries, e.g:

db.Insert(new SqlTest { Value = 123.456 });

var results = db.Select<(int id, string text)>(db.From<SqlTest>()
    .Select(x => new {
        x.Id,
        text = Sql.Cast(x.Id, Sql.VARCHAR) + " : " + Sql.Cast(x.Value, Sql.VARCHAR) + " : " 
             + Sql.Cast("1 + 2", Sql.VARCHAR) + " string"
    }));

results[0].text //= 1 : 123.456 : 3 string

Typed Column<T> and Table<T> APIs​

You can use the Column<T> and Table<T>() methods to resolve the quoted names of a Column or Table within SQL Fragments (taking into account any configured aliases or naming strategies).

Usage Example of the new APIs inside a CustomJoin() expression used to join on a custom SELECT expression:

q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(x => x.Id)} ...")
q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(nameof(Job.Id))} ...")

q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(x => x.Id, tablePrefix:true)} ...")
//Equivalent to:
q.CustomJoin($"LEFT JOIN (SELECT {q.Table<Job>()}.{q.Column<Job>(x => x.Id)} ...")

q.Select($"{q.Column<Job>(x => x.Id)} as JobId, {q.Column<Task>(x => x.Id)} as TaskId")
//Equivalent to:
q.Select<Job,Task>((j,t) => new { JobId = j.Id, TaskId = t.Id })

DB Parameter APIs​

To enable even finer-grained control of parameterized queries we've added new overloads that take a collection of IDbDataParameter's:

List<T> Select<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
T Single<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
T Scalar<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
List<T> Column<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
IEnumerable<T> ColumnLazy<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
HashSet<T> ColumnDistinct<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
Dictionary<K, List<V>> Lookup<K, V>(string sql, IEnumerable<IDbDataParameter> sqlParams)
List<T> SqlList<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
List<T> SqlColumn<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)
T SqlScalar<T>(string sql, IEnumerable<IDbDataParameter> sqlParams)

The new APIs let you execute parameterized SQL with finer-grained control over the IDbDataParameter used, e.g:

IDbDataParameter pAge = db.CreateParam("age", 40, dbType:DbType.Int16);
db.Select<Person>("SELECT * FROM Person WHERE Age > @pAge", new[] { pAge });

The new CreateParam() extension method above is a useful helper for creating custom IDbDataParameter's.

Customize null values​

The new OrmLiteConfig.OnDbNullFilter lets you replace DBNull values with a custom value, so you could convert all null strings to be populated with "NULL" using:

OrmLiteConfig.OnDbNullFilter = fieldDef => 
    fieldDef.FieldType == typeof(string)
        ? "NULL"
        : null;

Modify Schema Versioning Examples​

OrmLite provides Typed APIs for modifying Table Schemas that makes it easy to inspect the state of an RDBMS Table which can be used to determine what modifications you want to apply to it to upgrade it to the latest version:

public class Track
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Album { get; set; }
    public int ArtistId { get; set; } 
}

// Map to same "Track" RDBMS Table, not needed when Track is refactored
[Alias("Track")]
public class Track_v2
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Album { get; set; }
    public int ArtistId { get; set; } 
    [Default(5)]
    public int Rating { get; set; }  // ADD
}

var v1TableExists = db.TableExists<Track>();
$"Table Exists: {v1TableExists}".Print();
if (!v1TableExists)
{
	db.CreateTable<Track>(); 
	v1TableExists = db.TableExists<Track>();
}

var v1RatingExists = db.ColumnExists<Track_v2>(x => x.Rating);
$"Rating Exists v1: {v1RatingExists}".Print();
if (!v1RatingExists)
{
    db.AddColumn<Track_v2>(x => x.Rating);
    var v2RatingExists = db.ColumnExists<Track_v2>(x => x.Rating);
}

Create Table Examples​

As a code-first ORM, creating tables is effortless in OrmLite that uses your POCO Type definition to generate RDBMS Table schemas that cleanly maps .NET data types 1:1 to the most appropriate RDBMS column definition:

public class AllFields
{
    public string Id { get; set; } //implicit Primary Key
    public int Int { get; set; }
    public int? NInt { get; set; }
    public long Long { get; set; }
    public long? NLong { get; set; }
    public uint Uint { get; set; }
    public uint? NUint { get; set; }
    public Guid Guid { get; set; }
    public Guid? NGuid { get; set; }
    public bool Bool { get; set; }
    public bool? NBool { get; set; }
    public DateTime DateTime { get; set; }
    public DateTime? NDateTime { get; set; }
    public float Float { get; set; }
    public float? NFloat { get; set; }
    public double Double { get; set; }
    public double? NDouble { get; set; }
    public decimal Decimal { get; set; }
    public decimal? NDecimal { get; set; }
    public TimeSpan TimeSpan { get; set; }
    public TimeSpan? NTimeSpan { get; set; }
}

if (db.CreateTableIfNotExists<AllFields>())  //= true; if table was created
{
    db.Insert(new AllFields { 
        Id = "Id", Int = 1, Long = 2, Uint = 3, Guid = Guid.NewGuid(), Bool = true, DateTime = DateTime.UtcNow,
        Float = 1.1f, Double = 2.2d, Decimal = 3.3m, TimeSpan = new TimeSpan(1,1,1,1) });
}

var allFields = db.SingleById<AllFields>("Id");

db.DropAndCreateTable<AllFields>();
var emptyAllFieldsCount = db.Count<AllFields>();

db.DropTable<AllFields>();
var oldTableExists = db.TableExists<AllFields>();

db.CreateTable<AllFields>();
var newTableExists = db.TableExists<AllFields>();

Create Tables with Complex Types​

OrmLite also supports persisting rich complex types which are blobbed by default or you can use the [Reference] support to persist Nested Complex Types in their own Table Definitions:

public class ArtistWithBlobTracks
{
    public int Id { get; set; }
    public string Name { get; set; }
    //By default Complex Types are blobbed with the containing record
    public List<Track> Tracks { get; set; }
}
public class Artist
{
    public int Id { get; set; }
    public string Name { get; set; }
    //Complex Type References are persisted in own table
    [Reference] public List<Track> Tracks { get; set; }
}
public class Track
{
    [AutoIncrement] 
    public int Id { get; set; }
    public string Name { get; set; }
    public string Album { get; set; }
    public int ArtistId { get; set; } // Implicit Reference Id
}

db.CreateTable<ArtistWithBlobTracks>();
db.CreateTable<Artist>();
db.CreateTable<Track>();

db.Insert(new ArtistWithBlobTracks { 
    Id = 1, Name = "Faith No More", 
    Tracks = new List<Track> { 
        new Track { Name = "Everythings Ruined", Album = "Angel Dust" },
        new Track { Name = "Ashes to Ashes", Album = "Album of the Year" } } 
});
var artistWithBlobTracks = db.SingleById<ArtistWithBlobTracks>(1);
$"Artist with blobbed Tracks: {artistWithBlobTracks.Dump()}".Print();
$"\nBlob Tracks Count: {db.Count<Track>()}".Print();

db.Save(new Artist { 
    Id = 1, Name = "Faith No More", 
    Tracks = new List<Track> { 
        new Track { Name = "Everythings Ruined", Album = "Angel Dust" },
        new Track { Name = "Ashes to Ashes", Album = "Album of the Year" } }
}, references: true);

var artistWithRefTracks = db.LoadSingleById<Artist>(1);
$"\nArtist with referenced Tracks: {artistWithRefTracks.Dump()}".Print();
$"\nReferenced Tracks Count: {db.Count<Track>()}".Print();

Customize Tables using Attributes​

When needed you can markup your POCO's with .NET Attributes to allow further specialization of your Table schema and unlock RDBMS server features:

[Schema("TheSchema")]
[Alias("TableAlias")]
public class CustomTable
{
    [PrimaryKey]
    [AutoIncrement]
    public int CustomKey { get; set; }
    
    [Alias("RDBMS_NAME")]
    public string CSharpName { get; set; }
    
    [Index(Unique = true)]
    public string IndexColumn { get; set; }

    [Default(100)]
    public int? DefaultValue { get; set; }
    
    [Default(OrmLiteVariables.SystemUtc)]
    public DateTime CurrentDate { get; set; }

    [Required]
    [StringLength(3)]
    public string RequiredCustomLength { get; set; } //= NOT NULL
    
    [DecimalLength(18,4)]
    public decimal? CustomDecimalPrecision { get; set; }
    
    [CustomField("DECIMAL(18,4)")]
    public decimal? CustomProperty { get; set; }
    
    // Completely ignored in OrmLite (used in Serialization only)
    [Ignore]
    public int IgnoredProperty { get; set; }

    // Doesn't exist on Table, only used in SELECT Statements
    [CustomSelect("CustomKey + DefaultValue")] 
	public int SelectOnlyProperty { get; set; }
}

db.CreateTable<CustomTable>();

var id = db.Insert(new CustomTable { CSharpName = "Name", IndexColumn = "bar", RequiredCustomLength = "foo",
	CustomDecimalPrecision = 1.111m, CustomProperty = 2.222m }, selectIdentity:true);

var customTableRow = db.SingleById<CustomTable>(id);

Create Tables with Foreign Keys​

A popular use-case where you'd want to use Attributes is to define Foreign Keys:

public class Artist
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Album
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ForeignKey(typeof(Album), OnDelete = "CASCADE")]
    public int ArtistId { get; set; }
}

public class Track
{
    public int Id { get; set; }
    public string Name { get; set; }

    [References(typeof(Album))]
    public int AlbumId { get; set; } // db-agnostic attribute, generates FK to Artist

    [ForeignKey(typeof(Artist), OnDelete = "CASCADE")]
    public int ArtistId { get; set; }
}


db.CreateTable<Artist>();
db.CreateTable<Album>();
db.CreateTable<Track>(); //Order is important for tables with Foreign Keys

db.Insert(new Artist { Id = 1, Name = "Nirvana" });
db.Insert(new Album { Id = 2, Name = "Nevermind", ArtistId = 1 });
db.Insert(new Track { Id = 3, Name = "Smells Like Teen Spirit", AlbumId = 2, ArtistId = 1 });

var artist = db.SingleById<Artist>(1);
var album = db.SingleById<Album>(2);
var track = db.SingleById<Track>(3);