Creating Tables

The code in this topic assumes a SQLite database has been mounted on the drive named "mydb". For instructions on mounting such a drive, see Creating Databases.

To create a table inside of a SQLite database, you use the new-item PowerShell cmdlet, specifying the mounted SQLite drive in the path argument and the table specification for the table as the value argument. For instance:

PS> new-item -path mydb:/Users -value "id INTEGER PRIMARY KEY, username TEXT NOT NULL, userid INTEGER"
path specifies the path to the SQLite database drive, and optionally the new table (Users in this example)
value is a valid table specification, suitable for inclusion in a CREATE TABLE DDL statement

The SQLite provider allows you to express the table specification in several ways. You can use a DDL string, as shown above. You can also specify the table using a hashtable of column definitions; for example, this code is functionally equivalent to the previous example:

PS> new-item -path mydb:/Users -value @{ id="INTEGER PRIMARY KEY"; username="TEXT NOT NULL"; userid="INTEGER" }

In addition, the provider offers a simplified syntax that allows you to express the table specification as a sequence of parameters to new-item. The example below is functionally equivalent to the previous two examples:

PS> new-item -path mydb:/Users -id INTEGER PRIMARY KEY -username TEXT NOT NULL -userid INTEGER;

Last edited Jan 19, 2012 at 10:03 PM by beefarino, version 1


Yooakim May 10, 2014 at 11:24 AM 
When I try this line (as copied from the project homepage) I get an error:

new-item db:/MyTable -id integer primary key -username text -userid integer not null

The table does not exist and the database is freshly created in-memory.

new-item : SQLite error
table "MyTable" has more than one primary key
At line:1 char:1
+ new-item MyDB:\MyTable -id integer primary key -username text -userid integer no ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ([Data Source=:memory:]\MyTable:String) [New-Item], SQLiteException
+ FullyQualifiedErrorId : NewItem.Invoke,Microsoft.PowerShell.Commands.NewItemCommand

Is there something specific I missed in terms of how the simplified syntax works?

beefarino Jan 25, 2012 at 8:52 PM 
The shortcut syntax uses a single dynamic parameter with the ValueFromRemainingArguments flag on the Parameter attribute. The order of the parameters is retained, so the provider can pass them through a small state machine to produce the DDL.

halr9000 Jan 24, 2012 at 2:24 PM 
How does the shortcut syntax work? Looking at it as a PowerShell statement, it appears as if "INTEGER" would be mapped to a value to the "id" parameter, but that because you have an unquoted string with spaces, "PRIMARY" would be an unmapped value that would get assigned to the next ordered parameter according to existing PowerShell command-line parsing rules.